CodeFirst（反射+特性）

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Reflection; 5 using System.Text; 6 using Dapper; 7 using System.Text.RegularExpressions; 8 using System.Data.SqlClient; 9 using System.ComponentModel; 10  11 namespace CodeFirst 12 { 13   class Program 14   { 15     static readonly string SchemaName; 16     static readonly string ConnectionString; 17  18     static Program() 19     { 20       SchemaName = "22TopWeb"; 21       if (string.IsNullOrWhiteSpace(SchemaName)) 22       { 23         throw new Exception("'SchemaName' load failed"); 24       } 25       if (new[] { "master", "model", "msdb", "tempdb" }.Contains(SchemaName)) 26       { 27         throw new Exception("'SchemaName' illegal"); 28       } 29       ConnectionString = "Data Source=192.168.8.119;User ID=EQCCD_HUNTER;Password=zhey1bu2012;Initial Catalog=master;Pooling=true"; 30       if (string.IsNullOrWhiteSpace(ConnectionString)) 31       { 32         throw new Exception("'ConnectionString' load failed"); 33       } 34       var pattern = @"Initial\s*Catalog\s*=\s*master"; 35       Match match = Regex.Match(ConnectionString, pattern, RegexOptions.IgnoreCase); 36       if (match.Groups.Count > 0) 37       { 38         //可能需要创建数据库 39         CheckSchema(ConnectionString, SchemaName); 40         ConnectionString = ConnectionString.Replace(match.Groups[0].Value, "Initial Catalog=" + SchemaName); 41       } 42     } 43  44     static void Main(string[] args) 45     { 46       var sql = GetTableCreateSql("CodeFirst.TB_Enterprise"); 47  48       ExcuteSql(ConnectionString, sql.Replace("GO", "")); //GO只能在查询分析器里使用 49  50       Console.ReadKey(); 51     } 52  53     /// <summary> 54     ///  55     /// </summary> 56     /// <param name="fullName"></param> 57     /// <param name="overwrite">如果表已存在，drop后重新创建（true慎用）</param> 58     /// <returns></returns> 59     static string GetTableCreateSql(string fullName, bool overwrite = false) 60     { 61       var type = Type.GetType(fullName); 62  63       var columnDefinitionList = GetColumnDefinitionList(type); 64  65       //数据库 表名 66       var tableName = type.Name; 67       var dbTableNameAttr = type.GetCustomAttributes(false).Where(attr => attr.GetType().Name == "DBTableNameAttribute").SingleOrDefault() as 68   dynamic; 69       if (dbTableNameAttr != null) 70         tableName = dbTableNameAttr.Name; 71       //主键列 72       var primaryKeyArr = (from clmn in columnDefinitionList where clmn.IsPrimaryKey select clmn.ColumnName).ToArray(); 73       //是否 TEXTIMAGE ON 74       var isTextImageOn = type.GetCustomAttributes(false).Where(attr => attr.GetType().Name == "TextImageOn").Any(); 75  76       if (!string.IsNullOrWhiteSpace(tableName) && columnDefinitionList.Count > 0) 77       { 78         var sb = new StringBuilder(); 79  80         sb.AppendFormat(@"USE [{0}] 81 GO", SchemaName); 82  83         if (overwrite) 84         { 85           sb.AppendFormat(@" 86  87 if exists (select 1 from sysobjects where id = object_id('{0}') and type = 'U') 88 drop table {0} 89 GO", tableName); 90         } 91  92         sb.AppendFormat(@" 93  94 /****** Object: Table [dbo].[{1}]  Script Date: {2}  Generate By CodeFrist ******/ 95 SET ANSI_NULLS ON 96 GO 97  98 SET QUOTED_IDENTIFIER ON 99 GO100 101 SET ANSI_PADDING ON102 GO103 104 CREATE TABLE [dbo].[{1}](", SchemaName, tableName, DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss"));105 106         columnDefinitionList.ForEach(p =>107         {108           //组合主键 不能定义 IDENTITY109           sb.AppendFormat(@"110   [{0}] [{1}]{2} {3} {4},", p.ColumnName, p.DbType, p.MaxLength > 0 ? "(" + p.MaxLength + ")" : "", p.IsPrimaryKey && primaryKeyArr.Length <= 1 ? "IDENTITY(" + p.Seed + "," + p.Incr + ")" : "", p.IsNullable ? "NULL" : "NOT NULL");111         });112 113         if (primaryKeyArr != null && primaryKeyArr.Length > 0)114         {115           //主键列116           sb.AppendFormat(@"117  CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED 118 (119   {1}120 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]121 ", tableName, primaryKeyArr.Aggregate("", (current, cName) => current += string.Format(",[{0}] ASC", cName)).Trim(','));122         }123         //else //多余的这个逗号可以不去掉124 125         sb.AppendFormat(@"126 ) ON [PRIMARY] {0}127 128 GO129 130 SET ANSI_PADDING OFF131 GO132 ", isTextImageOn ? "TEXTIMAGE_ON [PRIMARY]" : "");133 134         columnDefinitionList.Where(p => !string.IsNullOrWhiteSpace(p.Description)).ToList().ForEach(p =>135         {136           //字段说明137           sb.AppendFormat(@"138 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{2}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{0}', @level2type=N'COLUMN',@level2name=N'{1}'139 GO140 ", tableName, p.ColumnName, ToSqlLike(p.Description));141         });142 143 144         return sb.ToString(); //这个格式和Management Studio生成的sql内容一致145 146       }147 148       return string.Empty;149     }150 151     /// <summary>152     /// 获取所有列定义（此为重点，反射+特性）153     /// </summary>154     /// <param name="type"></param>155     /// <returns></returns>156     static List<ColumnDefinition> GetColumnDefinitionList(Type type)157     {158       var columnDefinitionList = new List<ColumnDefinition>();159 160       var pInfoArr = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);161       foreach (PropertyInfo pInfo in pInfoArr)162       {163         var columnDefinition = new ColumnDefinition() { ColumnName = pInfo.Name };164 165         Console.WriteLine("----------Property Name:{0}-----------", pInfo.Name);166 167         foreach (dynamic attr in pInfo.GetCustomAttributes(false))168         {169           var attributeName = attr.GetType().Name as string;170 171           var attributeInfoStr = string.Format("Attribute Name:{0}", attributeName);172           switch (attributeName)173           {174             case "PrimaryKeyAttribute":175               columnDefinition.IsPrimaryKey = true;176               columnDefinition.Seed = attr.Seed;177               columnDefinition.Incr = attr.Incr;178               columnDefinition.IsPrimaryKey = true;179               Console.WriteLine(attributeInfoStr);180               break;181             case "DataTypeAttribute":182               columnDefinition.DbType = attr.DbType;183               columnDefinition.MaxLength = attr.MaxLength;184               attributeInfoStr += string.Format("(DbType:{0}{1})", columnDefinition.DbType, columnDefinition.MaxLength > 0 ? ",MaxLength:" + columnDefinition.MaxLength : "");185               Console.WriteLine(attributeInfoStr);186               break;187             case "IsNullableAttribute":188               columnDefinition.IsNullable = true;189               Console.WriteLine(attributeInfoStr);190               break;191             case "DescriptionAttribute":192               columnDefinition.Description = attr.Description; //字段说明193               attributeInfoStr += string.Format("(说明:{0})", columnDefinition.Description);194               Console.WriteLine(attributeInfoStr);195               break;196             default:197               break;198           }199         }200 201         if (!string.IsNullOrWhiteSpace(columnDefinition.ColumnName) && !string.IsNullOrWhiteSpace(columnDefinition.DbType))202         {203           columnDefinitionList.Add(columnDefinition);204         }205 206         Console.WriteLine();207       }208 209       return columnDefinitionList;210     }211 212     #region DBHelper213 214     /// <summary>215     /// check数据库是否已存在，不存在则自动创建216     /// </summary>217     /// <param name="connectionString"></param>218     /// <param name="schemaName"></param>219     static void CheckSchema(string connectionString, string schemaName)220     {221       var pattern = @"Initial\s*Catalog\s*=\s*master";222       Match match = Regex.Match(connectionString, pattern, RegexOptions.IgnoreCase);223       if (match.Groups.Count == 0)224       {225         throw new ArgumentException();226       }227       var sql = string.Format(@"228 if not exists(select 1 from sysdatabases where name='{0}')229 create database {0}230 ", schemaName);231       ExcuteSql(connectionString, sql);232     }233 234     static bool ExcuteSql(string connectionString, string sql)235     {236       try237       {238         using (var conn = new SqlConnection(connectionString))239         {240           conn.Execute(sql);241         }242         return true;243       }244       catch (Exception ex)245       {246         return false;247       }248     }249 250     /// <summary>251     /// 对字符串进行sql格式化，并且符合like查询的格式。 252     /// </summary>253     /// <param name="sqlstr"></param>254     /// <returns></returns>255     static string ToSqlLike(string sqlstr)256     {257       if (string.IsNullOrEmpty(sqlstr)) return string.Empty;258       StringBuilder str = new StringBuilder(sqlstr);259       str.Replace("'", "''");260       str.Replace("[", "[[]");261       str.Replace("%", "[%]");262       str.Replace("_", "[_]");263       return str.ToString();264     }265 266     #endregion267 268   }269 270   /// <summary>271   /// 数据库 列定义272   /// </summary>273   public class ColumnDefinition274   {275     public string ColumnName { get; set; }276     public bool IsPrimaryKey { get; set; }277     /// <summary>278     /// 标示种子279     /// </summary>280     public int Seed { get; set; }281     /// <summary>282     /// 标示增量283     /// </summary>284     public int Incr { get; set; }285     public string DbType { get; set; }286     public int MaxLength { get; set; }287     /// <summary>288     /// true 可为空， 否则 false 不可为空289     /// </summary>290     public bool IsNullable { get; set; }291     public string Description { get; set; }292   }293 294   #region Custom Attributes295 296   [AttributeUsage(AttributeTargets.Class)]297   /// <summary>298   /// 数据库 表名299   /// </summary>300   public class DBTableNameAttribute : Attribute301   {302     public string Name { get; set; }303   }304 305   [AttributeUsage(AttributeTargets.Class)]306   /// <summary>307   /// 表的TEXTIMAGE ON特性308   /// </summary>309   public class TextImageOnAttribute : Attribute310   {311 312   }313 314   [AttributeUsage(AttributeTargets.Property)]315   /// <summary>316   /// 主键317   /// </summary>318   public class PrimaryKeyAttribute : Attribute319   {320     /// <summary>321     /// 标示种子322     /// </summary>323     public int Seed { get; set; }324     /// <summary>325     /// 标示增量326     /// </summary>327     public int Incr { get; set; }328   }329 330   [AttributeUsage(AttributeTargets.Property)]331   /// <summary>332   /// 数据类型333   /// </summary>334   public class DataTypeAttribute : Attribute335   {336     public string DbType { get; set; }337     public int MaxLength { get; set; }338   }339 340   [AttributeUsage(AttributeTargets.Property)]341   /// <summary>342   /// 允许Null值343   /// </summary>344   public class IsNullableAttribute : Attribute345   {346 347   }348 349   #endregion350 351   #region Table Model352 353   [TextImageOn]354   /// <summary>355   /// 356   /// </summary>357   public class TB_Enterprise358   {359     [PrimaryKey(Seed = 1, Incr = 1)]360     [DataType(DbType = "int")]361     public int EnterpriseId { get; set; }362 363     [DataType(DbType = "int")]364     public int Status { get; set; }365 366     [DataType(DbType = "int")]367     [IsNullable]368     public int? IsFamous { get; set; }369 370     [DataType(DbType = "int")]371     [IsNullable]372     public int? CustomerLevel { get; set; }373 374     [IsNullable]375     [DataType(DbType = "nvarchar", MaxLength = 256)]376     [Description("企业名称")]377     /// <summary>378     /// 企业名称379     /// </summary>380     public string Name { get; set; }381 382     [IsNullable]383     [DataType(DbType = "nvarchar", MaxLength = 300)]384     public string Industry { get; set; }385 386     [DataType(DbType = "int")]387     [IsNullable]388     public int? Mode { get; set; }389 390     [DataType(DbType = "int")]391     [IsNullable]392     public int? Scale { get; set; }393 394     [DataType(DbType = "nvarchar", MaxLength = 256)]395     [IsNullable]396     public string City { get; set; }397 398     [DataType(DbType = "nvarchar", MaxLength = 512)]399     [IsNullable]400     public string WebSite { get; set; }401 402     [DataType(DbType = "ntext")]403     [IsNullable]404     public string DescText { get; set; }405 406     [DataType(DbType = "datetime")]407     public DateTime CreateDate { get; set; }408 409     [DataType(DbType = "datetime")]410     public DateTime ModifyDate { get; set; }411 412     [DataType(DbType = "datetime")]413     [IsNullable]414     public DateTime? ApproveDate { get; set; }415 416     [DataType(DbType = "nvarchar", MaxLength = 50)]417     [IsNullable]418     public string SourceName { get; set; }419 420     [DataType(DbType = "nvarchar", MaxLength = 256)]421     [IsNullable]422     public string License { get; set; }423 424     [DataType(DbType = "varchar", MaxLength = 20)]425     [IsNullable]426     public string CreateUser { get; set; }427 428     [DataType(DbType = "varchar", MaxLength = 20)]429     [IsNullable]430     public string ModifyUser { get; set; }431 432     [DataType(DbType = "int")]433     [IsNullable]434     public int? ProcessStatus { get; set; }435 436     [DataType(DbType = "varchar", MaxLength = 50)]437     [IsNullable]438     public string Abbr { get; set; }439 440     [DataType(DbType = "varchar", MaxLength = 1)]441     [IsNullable]442     public string NameInitial { get; set; }443 444     [DataType(DbType = "float")]445     [IsNullable]446     public decimal? Activity { get; set; }447 448     [DataType(DbType = "nvarchar", MaxLength = 200)]449     [IsNullable]450     public string Tags { get; set; }451 452     [DataType(DbType = "nvarchar", MaxLength = 50)]453     [IsNullable]454     public string ConsultantName { get; set; }455 456     [DataType(DbType = "nvarchar", MaxLength = 500)]457     [IsNullable]458     public string ConsultantComment { get; set; }459 460     [DataType(DbType = "int")]461     [IsNullable]462     public int? ConsultantId { get; set; }463 464     [DataType(DbType = "int")]465     [IsNullable]466     public int? DecoratePercent { get; set; }467 468     [DataType(DbType = "nvarchar", MaxLength = 100)]469     [IsNullable]470     public string ShortDesc { get; set; }471 472     [DataType(DbType = "int")]473     [IsNullable]474     public int? CertificationStatus { get; set; }475 476     [DataType(DbType = "bit")]477     [IsNullable]478     public bool? IsBDRecommended { get; set; }479 480     [DataType(DbType = "int")]481     [IsNullable]482     public int? ApproveStatus { get; set; }483 484     [DataType(DbType = "varchar", MaxLength = 500)]485     [IsNullable]486     public string ApproveResult { get; set; }487 488     [DataType(DbType = "int")]489     [IsNullable]490     public int? ApproveByUserId { get; set; }491   }492 493   #endregion494 495 }

原标题：CodeFirst（反射+特性）

关键词：反射