sql语句转为Model

   在跟数据库打交道的时候，有一个常用的应用，就是把数据库中的表转为程序中的对象，也就是说表中的列转为对象的属性。对于字段比较少的，我们可以直接复制过去改，但是字段数比较多的时候，借助工具类实现比较方便而且不易出错，看下我的代码：

 1         /// <summary>
 2         /// 从数据库sql语句中生成实体model
 3         /// </summary>
 4         /// <param name="sql"></param>
 5         /// <returns></returns>
 6         public static string GenerateModelFromSql(string sql)
 7         {
 8             StringBuilder modelBuild = new StringBuilder();
 9 
10             string tableNamePatten = @"CREATE\s+TABLE\s+(?:\[dbo\]\.)?\[(\w+)\]";
11 
12             var m = Regex.Match(sql, tableNamePatten);
13             var gs = m.Groups;
14             var tableName = "";
15             if (gs.Count > 1)
16             {
17                 tableName = gs[1].Value;
18                 modelBuild.AppendLine("///<summary>");
19                 modelBuild.AppendLine(string.Format(" ///{0}", tableName));
20                 modelBuild.AppendLine("///</summary>");
21 
22                 modelBuild.AppendLine(string.Format("public class {0}", tableName));
23 
24                 modelBuild.AppendLine("{");
25             }
26 
27             string fieldPatten = @"\s*\[(\w+)\]\s+\[(\w+)\]\s*(\((\d+)\))?";
28             string primarykeyPattern = @"primary\s+key";
29             var matches = Regex.Matches(sql, fieldPatten);
30 
31             string fieldName = "";
32             string fieldType = "";
33             string stringLength = "";
34 
35             int i = 0;
36             foreach (Match item in matches)
37             {
38                 i++;
39                 var groups = item.Groups;
40 
41                 if (groups.Count > 2)
42                 {
43                     fieldName = groups[1].Value;
44                     fieldType = groups[2].Value;
45 
46                     if (groups.Count > 3)
47                     {
48                         stringLength = groups[3].Value;
49                     }
50 
51                     var type = Tools.ConvertToCSharpType(fieldType);
52 
53                     if (Regex.IsMatch(sql, primarykeyPattern) && i == 1)
54                     {
55                         modelBuild.AppendLine(" [PrimaryKey]");
56                     }
57                     else if (type == "string")
58                     {
59                         modelBuild.AppendLine(string.Format("   [StringLength({0})]", stringLength));
60                     }
61 
62                     string core = string.Format("   public {0} {1} [set; get;]", type, fieldName);
63 
64                     core = core.Replace('[', '{');
65                     core = core.Replace(']', '}');
66                     modelBuild.AppendLine(core);
67                 }
68             }
69             modelBuild.AppendLine("}");
70             return modelBuild.ToString();
71         }

使用最多的是正则匹配。再看第51行的类型转换：

 1         public static string ConvertToCSharpType(string fieldType)
 2         {
 3             string dataType = null;
 4             var t = fieldType.ToLower();
 5             if (t == "varchar" || t == "nvarchar")
 6             {
 7                 dataType = "string";
 8             }
 9             else if (t == "bit")
10             {
11                 dataType = "bool";
12             }
13             else if (t == "bigint")
14             {
15                 dataType = "long";
16             }
17             else if (t == "datetime")
18             {
19                 dataType = "DateTime";
20             }
21             else if (t == "byte" || t == "binary" || t == "varbinary" || t == "image")
22             {
23                 dataType = "byte[]";
24             }
25             else if (t == "uniqueidentifier")
26             {
27                 dataType = "Guid";
28             }
29             return dataType ?? t;
30         }

 

posted @ 2017-12-05 13:02  micDavid  阅读(599)  评论(0)  编辑  收藏  举报