sql语句转为Model

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

        /// <summary>
        /// 从数据库sql语句中生成实体model
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string GenerateModelFromSql(string sql)
        {
            StringBuilder modelBuild = new StringBuilder();

            string tableNamePatten = @"CREATEs+TABLEs+(?:[dbo].)?[(w+)]";

            var m = Regex.Match(sql, tableNamePatten);
            var gs = m.Groups;
            var tableName = "";
            if (gs.Count > 1)
            {
                tableName = gs[1].Value;
                modelBuild.AppendLine("///<summary>");
                modelBuild.AppendLine(string.Format(" ///{0}", tableName));
                modelBuild.AppendLine("///</summary>");

                modelBuild.AppendLine(string.Format("public class {0}", tableName));

                modelBuild.AppendLine("{");
            }

            string fieldPatten = @"s*[(w+)]s+[(w+)]s*(((d+)))?";
            string primarykeyPattern = @"primarys+key";
            var matches = Regex.Matches(sql, fieldPatten);

            string fieldName = "";
            string fieldType = "";
            string stringLength = "";

            int i = 0;
            foreach (Match item in matches)
            {
                i++;
                var groups = item.Groups;

                if (groups.Count > 2)
                {
                    fieldName = groups[1].Value;
                    fieldType = groups[2].Value;

                    if (groups.Count > 3)
                    {
                        stringLength = groups[3].Value;
                    }

                    var type = Tools.ConvertToCSharpType(fieldType);

                    if (Regex.IsMatch(sql, primarykeyPattern) && i == 1)
                    {
                        modelBuild.AppendLine(" [PrimaryKey]");
                    }
                    else if (type == "string")
                    {
                        modelBuild.AppendLine(string.Format("   [StringLength({0})]", stringLength));
                    }

                    string core = string.Format("   public {0} {1} [set; get;]", type, fieldName);

                    core = core.Replace('[', '{');
                    core = core.Replace(']', '}');
                    modelBuild.AppendLine(core);
                }
            }
            modelBuild.AppendLine("}");
            return modelBuild.ToString();
        }

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

        public static string ConvertToCSharpType(string fieldType)
        {
            string dataType = null;
            var t = fieldType.ToLower();
            if (t == "varchar" || t == "nvarchar")
            {
                dataType = "string";
            }
            else if (t == "bit")
            {
                dataType = "bool";
            }
            else if (t == "bigint")
            {
                dataType = "long";
            }
            else if (t == "datetime")
            {
                dataType = "DateTime";
            }
            else if (t == "byte" || t == "binary" || t == "varbinary" || t == "image")
            {
                dataType = "byte[]";
            }
            else if (t == "uniqueidentifier")
            {
                dataType = "Guid";
            }
            return dataType ?? t;
        }