为数据库中的表 生成类的源文件（代码生成器）

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Data;
  4 using System.Data.SqlClient;
  5 using System.IO;
  6 using System.Text;
  7 
  8 namespace ModelCodeGeneratorSample
  9 {
 10     class Program
 11     {
 12         static string ConnectionString;
 13         static string NamespaceName;
 14 
 15         static Program()
 16         {
 17             //载入配置
 18             ConnectionString = "Data Source=192.168.8.119;Initial Catalog=22TopWeb;Integrated Security=False;user=EQCCD_HUNTER;password=zhey1bu2012;";
 19             NamespaceName = "Topuc22Top.Model";
 20         }
 21 
 22         static void Main(string[] args)
 23         {
 24             var content = GetTableCodeContent(ConnectionString, NamespaceName, "TB_Enterprise");
 25             if (!string.IsNullOrWhiteSpace(content)) 
 26             {
 27                 string descFileFolder = @"D:\";
 28                 if (!Directory.Exists(descFileFolder))
 29                     Directory.CreateDirectory(descFileFolder);
 30                 string descFileName = "\\TB_Enterprise.cs";
 31                 File.WriteAllText(descFileFolder + descFileName, content, System.Text.Encoding.UTF8);
 32             }
 33         }
 34 
 35         static string GetTableCodeContent(string conStr, string namespaceName, string tableName, string className = "")
 36             //为什么不直接用全局的 少传一个参数，曾经一个项目 的 经验
 37         {
 38             if (string.IsNullOrWhiteSpace(tableName))
 39             {
 40                 throw new ArgumentException("参数tableName不能为Empty、null或WhiteSpce");
 41             }
 42             var sb = new StringBuilder();
 43             sb.AppendFormat(@"
 44 namespace {0}
 45 {{
 46     public class {1}
 47     {{", namespaceName, (!string.IsNullOrWhiteSpace(className) ? className : tableName));
 48             var dt = GetTableFields(conStr, tableName);
 49             foreach (DataRow row in dt.Rows)
 50             {
 51                 var columnName = row["列名"];
 52                 var typeString = row["类型"];
 53                 var isNullable = row["是否为空"];
 54                 var description = row["列说明"];
 55                 sb.AppendFormat(@"
 56         /// <summary>
 57         /// {3}
 58         /// </summary>
 59         public {1}{2}  {0} {{ get; set; }}", columnName, typeString, (typeString.ToString() != "string" && isNullable.ToString() == "是" ? "?" : ""), description);
 60             }
 61 
 62             sb.AppendFormat(@"
 63     }}
 64 }}
 65 ", NamespaceName);
 66 
 67             return sb.ToString();
 68         }
 69 
 70         static DataTable GetTableFields(string conStr, string tableName = "")
 71         {
 72             var sql = GetSql(tableName);
 73             var dt = ExcuteQuery(conStr, sql);
 74             return dt;
 75         }
 76 
 77         static string GetSql(string tableName = "")
 78         {
 79             var sql = @"select  
 80     [表名]=c.Name, 
 81     [表说明]=isnull(f.[value],''),  
 82     [列序号]=a.Column_id,  
 83     [列名]=a.Name,  
 84     [列说明]=isnull(e.[value],''),  
 85     [数据库类型]=b.Name,    
 86     [类型]= case when b.Name = 'image' then 'byte[]'
 87                  when b.Name in('image','uniqueidentifier','ntext','varchar','ntext','nchar','nvarchar','text','char') then 'string'
 88                  when b.Name in('tinyint','smallint','int','bigint') then 'int'
 89                  when b.Name in('date','datetime','smalldatetime') then 'DateTime'
 90                  when b.Name in('float','decimal','numeric','money','real','smallmoney') then 'decimal'
 91                  when b.Name ='bit' then 'bool' else b.name end ,
 92     [标识]= case when is_identity=1 then '是' else '' end,  
 93     [主键]= case when exists(select 1 from sys.objects x join sys.indexes y on x.Type=N'PK' and x.Name=y.Name  
 94                         join sysindexkeys z on z.ID=a.Object_id and z.indid=y.index_id and z.Colid=a.Column_id)  
 95                     then '是' else '' end,      
 96     [字节数]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G'  
 97                   when b.Name='xml' then '2^31-1字节/2G' 
 98                   else rtrim(a.[max_length]) end,  
 99     [长度]=case when ColumnProperty(a.object_id,a.Name,'Precision')=-1 then '2^31-1' 
100                 else rtrim(ColumnProperty(a.object_id,a.Name,'Precision')) end,  
101     [小数位]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),  
102     [是否为空]=case when a.is_nullable=1 then '是' else '' end,      
103     [默认值]=isnull(d.text,'')      
104 from  
105     sys.columns a  
106 left join 
107     sys.types b on a.user_type_id=b.user_type_id  
108 inner join 
109     sys.objects c on a.object_id=c.object_id and c.Type='U' 
110 left join 
111     syscomments d on a.default_object_id=d.ID  
112 left join 
113     sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1   
114 left join 
115     sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1 
116 where 1 = 1";
117             if (!string.IsNullOrWhiteSpace(tableName))
118             {
119                 sql += "and c.name = '" + tableName + "'";
120             }
121             sql += " order by c.name, is_identity desc, a.Column_id";
122 
123             return sql;
124         }
125 
126         static DataTable ExcuteQuery(string conStr, string cmdText, List<SqlParameter> pars = null)
127         {
128             using (SqlConnection conn = new SqlConnection(conStr))
129             {
130                 using (SqlCommand cmd = new SqlCommand(cmdText, conn))
131                 {
132                     if (pars != null && pars.Count > 0) cmd.Parameters.AddRange(pars.ToArray());
133                     using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
134                     {
135                         DataTable dt = new DataTable();
136                         adp.Fill(dt);
137                         return dt;
138                     }
139                 }
140             }
141         }
142 
143     }
144 }

生成的.cs文件内容

 

转载于:https://www.cnblogs.com/frozenzhang/p/5210146.html