获取数据库表及表结构

朴灿烈づ我的快乐病毒、 2022-08-07 06:54 244阅读 0赞

原文：[http://www.cnblogs.com/StrangeCity/p/4352753.html][http_www.cnblogs.com_StrangeCity_p_4352753.html]

此例子，博主将使用SQL Server 2008 R2 做数据库，使用Winform做工具的UI展示。

1、执行以下sql，即可从SQL server 数据库得到相关表和字段的基础信息（SQL Server 2008 R2 亲测有效）

![ExpandedBlockStart.gif][]

1 SELECT *
    2 FROM master..sysdatabases

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

1 select  
     2     [表名]=c.Name, 
     3     [表说明]=isnull(f.[value],''),  
     4     [列序号]=a.Column_id,  
     5     [列名]=a.Name,  
     6     [列说明]=isnull(e.[value],''),  
     7     [数据库类型]=b.Name,    
     8     [类型]= case when b.Name = 'image' then 'byte[]'
     9                  when b.Name in('image','uniqueidentifier','ntext','varchar','ntext','nchar','nvarchar','text','char') then 'string'
    10                  when b.Name in('tinyint','smallint','int','bigint') then 'int'
    11                  when b.Name in('datetime','smalldatetime') then 'DateTime'
    12                  when b.Name in('float','decimal','numeric','money','real','smallmoney') then 'decimal'
    13                  when b.Name ='bit' then 'bool' else b.name end ,
    14     [标识]= case when is_identity=1 then '是' else '' end,  
    15     [主键]= case when exists(select 1 from sys.objects x join sys.indexes y on x.Type=N'PK' and x.Name=y.Name  
    16                         join sysindexkeys z on z.ID=a.Object_id and z.indid=y.index_id and z.Colid=a.Column_id)  
    17                     then '是' else '' end,      
    18     [字节数]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G'  
    19                   when b.Name='xml' then '2^31-1字节/2G' 
    20                   else rtrim(a.[max_length]) end,  
    21     [长度]=case when ColumnProperty(a.object_id,a.Name,'Precision')=-1 then '2^31-1' 
    22                 else rtrim(ColumnProperty(a.object_id,a.Name,'Precision')) end,  
    23     [小数位]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),  
    24     [是否为空]=case when a.is_nullable=1 then '是' else '' end,      
    25     [默认值]=isnull(d.text,'')      
    26 from  
    27     sys.columns a  
    28 left join 
    29     sys.types b on a.user_type_id=b.user_type_id  
    30 inner join 
    31     sys.objects c on a.object_id=c.object_id and c.Type='U' 
    32 left join 
    33     syscomments d on a.default_object_id=d.ID  
    34 left join 
    35     sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1   
    36 left join 
    37     sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1 
    38 
    39 获取数据库所有表字段信息

![复制代码][copycode.gif]

2、构建自定义模板，然后替换模板中的标识符

说到自定义模板，无非就是一堆字符串，那这一堆字符串究竟是用XML存储还是TXT文本存储还是其他方式呢？好吧，咱不纠结到底采用哪种存储介质了。基于本文的“30分钟”，博主决定短平快，直接硬编码写死吧！用字符串对象存储起来。

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

1 string modelTmp = @"
     2 using System;
     3 
     4 namespace #ModelNamespace#
     5 {
     6     /// <summary>
     7     /// #ModelClassDescription#
     8     /// Create By Tool #CreateDateTime#
     9     /// </summary>
    10     public class #ModelClassName#
    11     {
    12 #PropertyInfo#
    13     }
    14 }";
    15 
    16 实体类模板

![复制代码][copycode.gif]

![ExpandedBlockStart.gif][]

1 string modelPropertyTmp = @"
    2          /// <summary>
    3          /// #PropertyDescription#
    4          /// </summary>
    5          public  #PropertyType# #PropertyName# { get; set; }";

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

1 propertyInfo += modelPropertyTmp.Replace(" #PropertyDescription#", propertyDescription)
     2                                                         .Replace(" #PropertyType#", propertyType)
     3                                                         .Replace("#PropertyName#", propertyName);
     4 
     5 
     6 modelTmp = modelTmp.Replace("#ModelClassDescription#", this.treeView1.SelectedNode.Tag.ToString())
     7                                        .Replace("#CreateDateTime#", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
     8                                        .Replace("#ModelNamespace#", modelNamespace)
     9                                        .Replace("#ModelClassName#", modelClassName)
    10                                        .Replace("#PropertyInfo#", propertyInfo);
    11 
    12 标识符替换

![复制代码][copycode.gif]

3、采用文本控件将生成的代码打印出来

由于本文旨在让各位读者了解代码生成的原理，故博主花了10多分钟写了一个简单的代码生成器，代码自然有些粗糙，请各位见谅！

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

1 using System;
      2 using System.Collections.Generic;
      3 using System.Data;
      4 using System.Data.SqlClient;
      5 using System.Linq;
      6 using System.Windows.Forms;
      7 
      8 namespace WindowsTest
      9 {
     10     public partial class CodeCreate : Form
     11     {
     12         private static string S_conStr = "Data Source=127.0.0.1;Initial Catalog=master;Integrated Security=False;user=sa;password=******;";
     13 
     14         public CodeCreate()
     15         {
     16             InitializeComponent();
     17         }
     18 
     19         public static DataTable ExcuteQuery(string connectionString, string cmdText, List<SqlParameter> pars = null)
     20         {
     21             using (SqlConnection conn = new SqlConnection(connectionString))
     22             {
     23                 using (SqlCommand cmd = new SqlCommand(cmdText, conn))
     24                 {
     25                     if (pars != null && pars.Count > 0) cmd.Parameters.AddRange(pars.ToArray());
     26                     using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
     27                     {
     28                         DataTable dt = new DataTable();
     29                         adp.Fill(dt);
     30                         return dt;
     31                     }
     32                 }
     33             }
     34         }
     35 
     36         private void CodeCreate_Load(object sender, EventArgs e)
     37         {
     38             #region 获取所有数据库的信息
     39             string sql = @" SELECT *
     40                             FROM master..sysdatabases
     41                             where dbid>6
     42                             ORDER BY dbid";
     43             #endregion
     44 
     45             DataTable dt = ExcuteQuery(S_conStr, sql);
     46             this.treeView1.Nodes.Add("192.168.30.69");
     47             foreach (DataRow dr in dt.Rows)
     48             {
     49                 this.treeView1.Nodes[0].Nodes.Add(dr["name"].ToString());
     50             }
     51 
     52             this.treeView1.ExpandAll();
     53         }
     54 
     55         private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
     56         {
     57             this.tabControl1.SelectedIndex = 0;
     58 
     59             if (e.Node.Level == 0) return;
     60 
     61             #region 获取数据库所有表字段信息的sql
     62             string sql = @"
     63 select  
     64     [表名]=c.Name, 
     65     [表说明]=isnull(f.[value],''),  
     66     [列序号]=a.Column_id,  
     67     [列名]=a.Name,  
     68     [列说明]=isnull(e.[value],''),  
     69     [数据库类型]=b.Name,    
     70     [类型]= case when b.Name = 'image' then 'byte[]'
     71                  when b.Name in('image','uniqueidentifier','ntext','varchar','ntext','nchar','nvarchar','text','char') then 'string'
     72                  when b.Name in('tinyint','smallint','int','bigint') then 'int'
     73                  when b.Name in('datetime','smalldatetime') then 'DateTime'
     74                  when b.Name in('float','decimal','numeric','money','real','smallmoney') then 'decimal'
     75                  when b.Name ='bit' then 'bool' else b.name end ,
     76     [标识]= case when is_identity=1 then '是' else '' end,  
     77     [主键]= case when exists(select 1 from sys.objects x join sys.indexes y on x.Type=N'PK' and x.Name=y.Name  
     78                         join sysindexkeys z on z.ID=a.Object_id and z.indid=y.index_id and z.Colid=a.Column_id)  
     79                     then '是' else '' end,      
     80     [字节数]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G'  
     81                   when b.Name='xml' then '2^31-1字节/2G' 
     82                   else rtrim(a.[max_length]) end,  
     83     [长度]=case when ColumnProperty(a.object_id,a.Name,'Precision')=-1 then '2^31-1' 
     84                 else rtrim(ColumnProperty(a.object_id,a.Name,'Precision')) end,  
     85     [小数位]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),  
     86     [是否为空]=case when a.is_nullable=1 then '是' else '' end,      
     87     [默认值]=isnull(d.text,'')      
     88 from  
     89     sys.columns a  
     90 left join 
     91     sys.types b on a.user_type_id=b.user_type_id  
     92 inner join 
     93     sys.objects c on a.object_id=c.object_id and c.Type='U' 
     94 left join 
     95     syscomments d on a.default_object_id=d.ID  
     96 left join 
     97     sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1   
     98 left join 
     99     sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1 ";
    100             #endregion
    101 
    102             if (e.Node.Level == 1)
    103             {
    104                 DataTable dt = ExcuteQuery(S_conStr.Replace("master", e.Node.Text), sql);
    105                 this.dataGridView1.DataSource = dt;
    106 
    107                 if (dt.Rows.Count > 0)
    108                 {
    109                     e.Node.Nodes.Clear();
    110                     DataRow[] aryDr = new DataRow[dt.Rows.Count];
    111                     dt.Rows.CopyTo(aryDr, 0);
    112                     List<string> listTableName = aryDr.Select(a => a["表名"].ToString()).Distinct().ToList();
    113                     listTableName.ForEach(a =>
    114                     {
    115                         e.Node.Nodes.Add(a, a);
    116                     });
    117                     e.Node.ExpandAll();
    118                 }
    119             }
    120 
    121             if (e.Node.Level == 2)
    122             {
    123                 sql += "where c.Name=@TableName ";
    124                 List<SqlParameter> listSqlParameter = new List<SqlParameter>()
    125                 {
    126                     new SqlParameter("@TableName",e.Node.Text),
    127                 };
    128                 DataTable dt = ExcuteQuery(S_conStr.Replace("master", e.Node.Parent.Text), sql, listSqlParameter);
    129                 if (dt.Columns.Count > 0)
    130                 {
    131                     if (dt.Rows.Count > 0)
    132                     {
    133                         e.Node.Tag = dt.Rows[0]["表说明"].ToString();
    134                     }
    135                     dt.Columns.Remove("表名");
    136                     dt.Columns.Remove("表说明");
    137                 }
    138                 this.dataGridView1.DataSource = dt;
    139             }
    140         }
    141 
    142         private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
    143         {
    144             if (this.tabControl1.SelectedTab.Text == "Model")
    145             {
    146                 if (this.treeView1.SelectedNode.Level == 2 && this.dataGridView1.Rows.Count > 0)
    147                 {
    148                     string modelTmp = @"
    149 using System;
    150 
    151 namespace #ModelNamespace#
    152 {
    153     /// <summary>
    154     /// #ModelClassDescription#
    155     /// Create By Tool #CreateDateTime#
    156     /// </summary>
    157     public class #ModelClassName#
    158     {
    159 #PropertyInfo#
    160     }
    161 }";
    162 
    163                     string modelNamespace = "Model";
    164                     string modelClassName = this.treeView1.SelectedNode.Text;
    165 
    166                     string propertyInfo = string.Empty;
    167                     foreach (DataGridViewRow dgvr in this.dataGridView1.Rows)
    168                     {
    169                         string modelPropertyTmp = @"
    170         /// <summary>
    171         /// #PropertyDescription#
    172         /// </summary>
    173         public  #PropertyType# #PropertyName# { get; set; }";
    174 
    175                         string propertyDescription = dgvr.Cells["列说明"].Value.ToString().Trim();
    176                         string propertyName = dgvr.Cells["列名"].Value.ToString().Trim();
    177                         string propertyType = dgvr.Cells["类型"].Value.ToString().Trim();
    178 
    179                         propertyInfo += modelPropertyTmp.Replace(" #PropertyDescription#", propertyDescription)
    180                                                         .Replace(" #PropertyType#", propertyType)
    181                                                         .Replace("#PropertyName#", propertyName);
    182                         propertyInfo += Environment.NewLine;
    183                     }
    184 
    185                     modelTmp = modelTmp.Replace("#ModelClassDescription#", this.treeView1.SelectedNode.Tag.ToString())
    186                                        .Replace("#CreateDateTime#", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
    187                                        .Replace("#ModelNamespace#", modelNamespace)
    188                                        .Replace("#ModelClassName#", modelClassName)
    189                                        .Replace("#PropertyInfo#", propertyInfo);
    190 
    191                     this.richTextBox1.Text = modelTmp;
    192                     Clipboard.SetDataObject(this.richTextBox1.Text);
    193                 }
    194             }
    195         }
    196     }
    197 }
    198 
    199 主要代码

![复制代码][copycode.gif]

[http_www.cnblogs.com_StrangeCity_p_4352753.html]: http://www.cnblogs.com/StrangeCity/p/4352753.html
[ExpandedBlockStart.gif]: /images/20220805/9977ee92fb084274818e13250b8c0172.png
[copycode.gif]: /images/20220805/25a45fe05f914902aa7d602eba778e85.png