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(反射+特性)
关键词:反射