CodeFirst（反射+特性），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 GO
100
101 SET ANSI_PADDING ON
102 GO
103
104 CREATE TABLE [dbo].[{1}](", SchemaName, tableName, DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss"));
105
106 columnDefinitionList.ForEach(p =>
107 {
108 //組合主鍵 不能定義 IDENTITY
109 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 GO
129
130 SET ANSI_PADDING OFF
131 GO
132 ", 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 GO
140 ", 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 DBHelper
213
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 try
237 {
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 #endregion
267
268 }
269
270 /// <summary>
271 /// 數據庫 列定義
272 /// </summary>
273 public class ColumnDefinition
274 {
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 Attributes
295
296 [AttributeUsage(AttributeTargets.Class)]
297 /// <summary>
298 /// 數據庫 表名
299 /// </summary>
300 public class DBTableNameAttribute : Attribute
301 {
302 public string Name { get; set; }
303 }
304
305 [AttributeUsage(AttributeTargets.Class)]
306 /// <summary>
307 /// 表的TEXTIMAGE ON特性
308 /// </summary>
309 public class TextImageOnAttribute : Attribute
310 {
311
312 }
313
314 [AttributeUsage(AttributeTargets.Property)]
315 /// <summary>
316 /// 主鍵
317 /// </summary>
318 public class PrimaryKeyAttribute : Attribute
319 {
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 : Attribute
335 {
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 : Attribute
345 {
346
347 }
348
349 #endregion
350
351 #region Table Model
352
353 [TextImageOn]
354 /// <summary>
355 ///
356 /// </summary>
357 public class TB_Enterprise
358 {
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 #endregion
494
495 }
