获取数据库表及表结构

来源：互联网发布：硬盘恢复数据价格编辑：程序博客网时间：2024/06/08 16:08

原文：http://www.cnblogs.com/StrangeCity/p/4352753.html

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

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

1 SELECT *2 FROM master..sysdatabases

 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 获取数据库所有表字段信息

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

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

 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 实体类模板

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

 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 标识符替换

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

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

  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             #endregion101 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 主要代码

0 0