在跟数据库打交道的时候，有一个常用的应用，就是把数据库中的表转为程序中的对象，也就是说表中的列转为对象的属性。对于字段比较少的，我们可以直接复制过去改，但是字段数比较多的时候，借助工具类实现比较方便而且不易出错，看下我的代码：
/// <summary>
/// 从数据库sql语句中生成实体model
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static string GenerateModelFromSql(string sql)
{
StringBuilder modelBuild = new StringBuilder(); string tableNamePatten = @"CREATE\s+TABLE\s+(?:\[dbo\]\.)?\[(\w+)\]"; var m = Regex.Match(sql, tableNamePatten);
var gs = m.Groups;
var tableName = "";
if (gs.Count > )
{
tableName = gs[].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 = @"primary\s+key";
var matches = Regex.Matches(sql, fieldPatten); string fieldName = "";
string fieldType = "";
string stringLength = ""; int i = ;
foreach (Match item in matches)
{
i++;
var groups = item.Groups; if (groups.Count > )
{
fieldName = groups[].Value;
fieldType = groups[].Value; if (groups.Count > )
{
stringLength = groups[].Value;
} var type = Tools.ConvertToCSharpType(fieldType); if (Regex.IsMatch(sql, primarykeyPattern) && i == )
{
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;
}