实现SQL -> C# Class Code工具的问题-阿里云开发者社区

有一段时间大家都在讨论生成SQL操作数据库代码的AutoCode工具，我也趁着热闹把我做的一个 SQL AutoCode工具拿出来讨论，不过后来忘掉了这事，今天看见有人回复，希望把源码拿出来参考参考，索性就再说说我对制作这种工具的看法和遇到的问题。

    继续说明这个玩意儿不是OR Mapping，因为它只有Mapping而没有任何的Relation。我觉得非要取个名字叫Object DataRow Mapping还比较恰当。如果你在做数据库方面的内容，而且还在自己拼接SQL语句来更新数据库数据行的话，那么这篇文章将非常适合你的胃口。

    在各种各样的项目中，中小型项目中是数量众多的，很多这样的项目需要数据库，而又不会有十分复杂的表间依赖关系。在操作数据库是的代码也不是一成不变的，很多时候会有一些custom的内容需要加在代码里，想做一个大而全的AutoCode来通吃也非常的困难，我觉得更好的办法是把分解SQL语言的功能模块化，然后自己在项目中根具自己的需求来AutoCode就行了。

    在我的AutoCode里，我把每个数据库表的字段分解为：

public struct SqlField

{

public string Name;

public SqlDbType DbType;

public System.Type Type;

public int Length;

public string Sort;

public bool IsNull;

}

分解的办法也非常的简单，就用正则表达式了：

string strMatch = @"(\s*\[?(?<Name>\w+)\]?\s+\[?(?<DbType>\w+)\]?){1}(\s*\(?(?<Length>\d+(,\d+)?)\)?)?(\s+(?<Sort>COLLATE\s+\w+))?(\s+(?<IsNull>(NOT\s+)?NULL))?\s*\,";

// ^---------------- Name + DbType ----------------^|<----------- Length ---------->|^---------- Sort ----------^|<--------- Is Null -------->|

// 在[宋体]下就能对齐这个pattern的这各部分的功能和其说明了

由于我的工具直接处理SQL语句，需要一个SqlType <-->CTS Type的映射表：

#region SQL Type <--> CTS Type Mapping Table

// Sql data type mapping to .NET CTS type

m_Type = new Hashtable();

m_Type["bigint"] = "System.Int64";

m_Type["binary"] = "System.Byte[]";

m_Type["bit"] = "System.Boolean";

m_Type["char"] = "System.String"; // object

m_Type["datetime"] = "System.DateTime";

m_Type["decimal"] = "System.Decimal";

m_Type["float"] = "System.Double";

m_Type["image"] = "System.Byte[]"; // object

m_Type["int"] = "System.Int32";

m_Type["money"] = "System.Decimal";

m_Type["nchar"] = "System.String"; // object

m_Type["ntext"] = "System.String"; // object

// m_Type["numeric"] = "System.Decimal";

m_Type["nvarchar"] = "System.String"; // object

m_Type["real"] = "System.Single";

m_Type["smalldatetime"] = "System.DateTime";

m_Type["smallint"] = "System.Int16";

m_Type["smallmoney"] = "System.Decimal";

m_Type["variant"] = "System.Object"; // object

m_Type["text"] = "System.String"; // object

m_Type["timestamp"] = "System.Byte[]"; // object

m_Type["tinyint"] = "System.Byte";

m_Type["uniqueidentifier"] = "System.Guid"; // object

m_Type["varbinary"] = "System.Byte[]"; // object

m_Type["varchar"] = "System.String"; // object

#endregion

在SqlDataReader里面本来有一个映射表，
SqlDataReader.MetaData[int index].metaType.SqlType对应SqlDataReader.MetaData[int index].metaType.TypeName，可是MetaData属性不是public的，读不出来，郁闷。

通过遍历正则表达式的所有Match，获得一个SqlField数组：

#region 生成SqlField数组的代码

Match m = Regex.Match(strFields, strMatch, RegexOptions.IgnoreCase);

while( m.Success )

{

SqlField sc = new SqlField();

sc.Name = m.Groups["Name"].Value;

string strDbType = m.Groups["DbType"].Value.ToLower();

if ( strDbType == "sql_variant" ) strDbType = "variant";

if ( strDbType == "numeric" ) strDbType = "decimal";

sc.DbType = (SqlDbType)Enum.Parse(typEnum, strDbType, true);

string strLength = m.Groups["Length"].Value;

if ( strLength.Length == 0 )

{

sc.Length = 0;

}

else

{

try

{

sc.Length = int.Parse(m.Groups["Length"].Value);

}

catch

{

sc.Length = 0;

}

sc.Type = System.Type.GetType(m_Type[strDbType].ToString());

sc.Sort = m.Groups["Sort"].Value;

sc.IsNull = (m.Groups["IsNull"].Value.ToLower() == "null");

alstFields.Add(sc);

// strSql = strSql.Replace(m.Value, "");

m = m.NextMatch();

}

m_Fields = (SqlField [])alstFields.ToArray( typeof(SqlField));

#endregion

上次我示例的代码就是我那次项目需要的代码而已，而有了SqlField数组后，要生成什么代码都巨方便了。下面这个Sql2Class.cs就是上次提到的那个示例里的代码转换的源码。

#region Sql2Class

using System;

using System.Collections;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Text.RegularExpressions;

namespace CodeConvert

{

/// <summary>

/// Summary description for SqlToCSharp.

/// </summary>

public class Sql2Class

{

private SqlFieldCollection m_SqlFields;

public Sql2Class(string strSqlCode)

{

m_SqlFields = new SqlFieldCollection(strSqlCode);

CreateClass();

}

public Sql2Class(SqlFieldCollection sfc)

{

m_SqlFields = sfc;

CreateClass();

}

private string CreateClass()

{

if ( m_SqlFields.FieldsCount == 0 ) return "";

StringBuilder strbCode = new StringBuilder();

strbCode.Append("/// <summary>\r\n/// Automatically Code for Table '");

strbCode.Append(m_SqlFields.TableName);

strbCode.Append(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));

strbCode.Append("\r\n/// </summary>\r\npublic class ");

strbCode.Append(m_SqlFields.TableName);

strbCode.Append("\r\n{\r\n");

strbCode.Append(DefineVariables(m_SqlFields.Fields));

strbCode.Append("\r\n\tprivate static string m_TableName = \"");

strbCode.Append(m_SqlFields.TableName);

strbCode.Append("\";\r\n\r\n");

strbCode.Append(DefineAttributes(m_SqlFields.Fields));

strbCode.Append(@"

public " + m_SqlFields.TableName + @"()

{

SqlCmd = new System.Data.SqlClient.SqlCommand();

}

public " + m_SqlFields.TableName + @"(int id) : this()

{

if ( id <= 0 )

{

throw new System.Exception(""The value of id must be greater than zero."");

}

m_" + m_SqlFields.Identity.Name + @" = id;

Load();

}

public " + m_SqlFields.TableName + @"(string field, string @value) : this()

{

if ( field == null || field.Length == 0 )

{

throw new System.NullReferenceException(""field"");

}

if ( @value == null || field.Length == 0 )

{

throw new System.NullReferenceException(""@value"");

}

Load(field, @value);

}

");

strbCode.Append(@"

protected void Load()

{

if ( m_" + m_SqlFields.Identity.Name + @" <= 0 )

{

throw new System.Exception(""The value of m_ID must be greater than zero."");

}

Load(m_" + m_SqlFields.Identity.Name + @");

}

protected void Load(int id)

{

string strSql = ""Select * From [{0}] Where ([ID] = {1})"";

RunSql(string.Format(strSql, m_TableName, id));

}

");

strbCode.Append(@"

protected void Load(string field, string @value)

{

if ( @value.IndexOf('\'') != -1 )

{

@value = @value.Replace(""'"", ""''"");

}

string strSql = ""Select * From [{0}] Where ([{1}] = '{2}')"";

RunSql(string.Format(strSql, m_TableName, field, @value));

}

");

strbCode.Append(@"

private void RunSql(string strSql)

{

SqlCmd.Connection = GetSqlConnection();

SqlCmd.CommandText = strSql;

System.Data.SqlClient.SqlDataReader drFields;

drFields = SqlCmd.ExecuteReader();

try

{

ReadData(drFields);

}

catch(System.Exception exp)

{

throw exp;

}

finally

{

drFields.Close();

drFields = null;

SqlCmd.Connection.Close();

SqlCmd.Parameters.Clear();

}

");

strbCode.Append(GetReadDataFunction(m_SqlFields.Fields).Replace("\r\n", "\n").Replace("\n", "\r\n"));

strbCode.Append("\r\n\t/// <summary>\r\n\t/// Save datas\r\n\t/// </summary>\r\n\tpublic void Save()\r\n\t{\r\n\t\tSave(m_");

strbCode.Append(m_SqlFields.Identity.Name);

strbCode.Append(");\r\n\t}\r\n");

strbCode.Append(GetSaveFunction(m_SqlFields.Fields));

strbCode.Append("\r\n");

strbCode.Append(GetSaveAsFunction(m_SqlFields.Fields));

strbCode.Append("\r\n");

strbCode.Append(GetCopyToFunction(m_SqlFields.Fields));

strbCode.Append("\r\n\r\n\t~");

strbCode.Append(m_SqlFields.TableName);

strbCode.Append("()\r\n\t{\r\n\t\tSqlCmd.Dispose();\r\n\t}\r\n\r\n\t/// <summary>\r\n\t/// Create and return the database connection\r\n\t/// </summary>\r\n\t/// <returns>the opened database connection</returns>\r\n\tprotected static System.Data.SqlClient.SqlConnection GetSqlConnection()\r\n\t{\r\n\t\tstring strConn = \"SqlConnectionString\";\r\n\t\tstrConn = System.Configuration.ConfigurationSettings.AppSettings[strConn];\r\n\t\tSystem.Data.SqlClient.SqlConnection SqlConn;\r\n\t\tSqlConn = new System.Data.SqlClient.SqlConnection(strConn);\r\n\t\tSqlConn.Open();\r\n\t\treturn SqlConn;\r\n\t}\r\n");

strbCode.Append(GetExtraFunctions());

strbCode.Append("\r\n}");

return strbCode.ToString();

}

private string DefineVariables(SqlField [] fields)

{

StringBuilder strbVariables = new StringBuilder();

strbVariables.Append("\tprivate ");

strbVariables.Append(m_SqlFields.Identity.Type.ToString());

strbVariables.Append(" m_");

strbVariables.Append(m_SqlFields.Identity.Name);

strbVariables.Append(";\r\n");

for( int i=0 ; i < fields.Length ; i++ )

{

strbVariables.Append("\tprivate ");

strbVariables.Append((fields[i]).Type.ToString());

strbVariables.Append(" m_");

strbVariables.Append((fields[i]).Name);

strbVariables.Append(";\r\n");

}

strbVariables.Append("\tprivate System.Data.SqlClient.SqlCommand SqlCmd;\r\n\r\n");

for( int i=0 ; i < fields.Length ; i++ )

{

strbVariables.Append("\tprivate bool bSet");

strbVariables.Append((fields[i]).Name);

strbVariables.Append("\t= false;\r\n");

}

return strbVariables.ToString();

}

private string DefineAttributes(SqlField [] fields)

{

string strName, strType;

StringBuilder strbAttributes = new StringBuilder();

strbAttributes.Append("\tpublic ");

strbAttributes.Append(m_SqlFields.Identity.Type.ToString());

strbAttributes.Append(" " + m_SqlFields.Identity.Name);

strbAttributes.Append("\r\n\t{\r\n\t\tget\r\n\t\t{\r\n\t\t\treturn m_");

strbAttributes.Append(m_SqlFields.Identity.Name);

strbAttributes.Append(";\r\n\t\t}\r\n\t}\r\n");

System.Type type;

for( int i=0 ; i < fields.Length ; i++ )

{

strName = (fields[i]).Name;

type = (fields[i]).Type;

strType = type.ToString();

strbAttributes.Append("\r\n\tpublic ");

strbAttributes.Append(strType);

strbAttributes.Append(" ");

strbAttributes.Append(strName);

strbAttributes.Append("\r\n\t{\r\n\t\tget\r\n\t\t{\r\n\t\t\treturn m_");

strbAttributes.Append(strName);

strbAttributes.Append(";\r\n\t\t}\r\n\t\tset\r\n\t\t{\r\n\t\t\t");

if ( type == typeof(System.Byte)

|| type == typeof(System.Int16)

|| type == typeof(System.Int32)

|| type == typeof(System.Int64)

|| type == typeof(System.Single) )

{

strbAttributes.Append("if ( m_");

strbAttributes.Append(strName);

strbAttributes.Append(" == 0 || m_");

strbAttributes.Append(strName);

strbAttributes.Append(" != value )\r\n\t\t\t{\r\n\t\t\t\tm_");

strbAttributes.Append(strName);

strbAttributes.Append(" = value;\r\n\t\t\t\tbSet");

strbAttributes.Append(strName);

strbAttributes.Append(" = true;\r\n\t\t\t}\r\n");

}

if ( type == typeof(System.Byte[])

|| type == typeof(System.Object)

|| type == typeof(System.String) )

{

strbAttributes.Append("if ( value != null && m_");

strbAttributes.Append(strName);

strbAttributes.Append(" != value )\r\n\t\t\t{\r\n\t\t\t\tm_");

strbAttributes.Append(strName);

strbAttributes.Append(" = value;\r\n\t\t\t\tbSet");

strbAttributes.Append(strName);

strbAttributes.Append(" = true;\r\n\t\t\t}\r\n");

}

if ( type == typeof(System.Boolean)

|| type == typeof(System.DateTime)

|| type == typeof(System.Decimal)

|| type == typeof(System.Double)

|| type == typeof(System.Guid) )

{

strbAttributes.Append("m_");

strbAttributes.Append(strName);

strbAttributes.Append(" = value;\r\n\t\t\tbSet");

strbAttributes.Append(strName);

strbAttributes.Append(" = true;\r\n");

}

strbAttributes.Append("\t\t}\r\n\t}\r\n");

}

return strbAttributes.ToString();

}

private string GetReadDataFunction(SqlField [] fields)

{

StringBuilder strbReadData = new StringBuilder();

strbReadData.AppendFormat(@"

private void ReadData(System.Data.SqlClient.SqlDataReader drFields)

{{

bool bLoadSuccess = false;

if ( drFields.Read() )

{{

object obj{0} = drFields[""{0}""];

m_{0} = (System.Int32)obj{0};

", m_SqlFields.Identity.Name);

string strName;

for( int i=0 ; i < fields.Length ; i++ )

{

strName = (fields[i]).Name;

strbReadData.AppendFormat(@"

object obj{0} = drFields[""{0}""];

if ( !( obj{0} is System.DBNull ) )

{{

m_{0} = ({1})obj{0};

}}", strName, (fields[i]).Type.ToString());

}

strbReadData.Append(@"

bLoadSuccess = true;

}

bool bNonUnique = drFields.Read();

if ( bNonUnique || !bLoadSuccess )

{

string strMessage = ""The identity isn't unique."";

throw new System.Exception(strMessage);

}

");

return strbReadData.ToString();

}

private string GetSaveFunction(SqlField [] fields)

{

StringBuilder strbSave = new StringBuilder();

strbSave.Append("\r\n\t/// <summary>\r\n\t/// Save the datas of row which ID equal iIdentity.\r\n\t/// </summary>\r\n\t/// <param name=\"iIdentity\"></param>\r\n\tprivate void Save(int iIdentity)\r\n\t{\r\n\t\tif ( iIdentity <= 0 )\r\n\t\t{\r\n\t\t\tSaveAs();\r\n\t\t\treturn;\r\n\t\t}\r\n\t\tSystem.Text.StringBuilder strbSql;\r\n\t\tstrbSql = new System.Text.StringBuilder();\r\n\t\tstrbSql.Append(\"Update [\" + m_TableName + \"] Set\");\r\n\t\tSystem.Data.SqlClient.SqlParameterCollection spc;\r\n\t\tspc = SqlCmd.Parameters;\r\n");

string strName;

for( int i=0 ; i < fields.Length ; i++ )

{

SqlField sc = fields[i];

strName = sc.Name;

strbSave.Append("\t\tif ( bSet");

strbSave.Append(strName);

strbSave.Append(" )\r\n\t\t{\r\n\t\t\tstrbSql.Append(\", [");

strbSave.Append(strName);

strbSave.Append("] = @");

strbSave.Append(strName);

strbSave.Append("\");\r\n\t\t\tspc.Add(\"@");

strbSave.Append(strName);

strbSave.Append("\", System.Data.SqlDbType.");

strbSave.Append(sc.DbType.ToString());

if ( sc.Length > 0 )

{

strbSave.Append(", ");

strbSave.Append(sc.Length);

}

strbSave.Append(");\r\n\t\t\tspc[\"@");

strbSave.Append(strName);

strbSave.Append("\"].Value = m_");

strbSave.Append(strName);

strbSave.Append(";\r\n\t\t\tbSet");

strbSave.Append(strName);

strbSave.Append(" = false;\r\n\t\t}\r\n");

}

strbSave.Append("\t\tif( spc.Count > 0 )\r\n\t\t{\r\n\t\t\tspc.Add(\"@");

strbSave.Append(m_SqlFields.Identity.Name);

strbSave.Append("\", System.Data.SqlDbType.");

strbSave.Append(m_SqlFields.Identity.DbType.ToString());

strbSave.Append(");\r\n\t\t\tspc[\"@");

strbSave.Append(m_SqlFields.Identity.Name);

strbSave.Append("\"].Value = m_");

strbSave.Append(m_SqlFields.Identity.Name);

strbSave.Append(";\r\n\t\t\tstrbSql.Append(\" Where ([");

strbSave.Append(m_SqlFields.Identity.Name);

strbSave.Append("] = @");

strbSave.Append(m_SqlFields.Identity.Name);

strbSave.Append(")\");\r\n\t\t\tstrbSql.Replace(\" Set,\", \" Set \");\r\n\t\t\tSqlCmd.CommandText = strbSql.ToString();\r\n\t\t\tSqlCmd.Connection = GetSqlConnection();\r\n\t\t\tSqlCmd.ExecuteNonQuery();\r\n\t\t\tSqlCmd.Connection.Close();\r\n\t\t\tSqlCmd.Parameters.Clear();\r\n\t\t\tm_");

strbSave.Append(m_SqlFields.Identity.Name);

strbSave.Append(" = iIdentity;\r\n\t\t}\r\n\t}\r\n");

return strbSave.ToString();

}

private string GetSaveAsFunction(SqlField [] fields)

{

StringBuilder strbSaveAs = new StringBuilder();

strbSaveAs.Append("\t/// <summary>\r\n\t/// Save the datas of current row to the new data row.\r\n\t/// </summary>\r\n\tpublic void SaveAs()\r\n\t{\r\n\t\tSystem.Data.SqlClient.SqlParameterCollection spc;\r\n\t\tspc = SqlCmd.Parameters;\r\n\t\tSystem.Text.StringBuilder strbValues;\r\n\t\tstrbValues = new System.Text.StringBuilder();\r\n");

string strName;

for( int i=0 ; i < fields.Length ; i++ )

{

SqlField sc = fields[i];

strName = sc.Name;

strbSaveAs.Append("\r\n\t\tif ( bSet");

strbSaveAs.Append(strName);

strbSaveAs.Append(" )\r\n\t\t{\r\n\t\t\tstrbValues.Append(\", @");

strbSaveAs.Append(strName);

strbSaveAs.Append("\");\r\n\t\t\tspc.Add(\"@");

strbSaveAs.Append(strName);

strbSaveAs.Append("\", System.Data.SqlDbType.");

strbSaveAs.Append(sc.DbType.ToString());

if ( sc.Length > 0 )

{

strbSaveAs.Append(", ");

strbSaveAs.Append(sc.Length);

}

strbSaveAs.Append(");\r\n\t\t\tspc[\"@");

strbSaveAs.Append(strName);

strbSaveAs.Append("\"].Value = m_");

strbSaveAs.Append(strName);

strbSaveAs.Append(";\r\n\t\t\tbSet");

strbSaveAs.Append(strName);

strbSaveAs.Append(" = false;\r\n\t\t}");

}

strbSaveAs.Append(@"

string strFields, strValues;

if ( strbValues.Length > 3 )

{

SqlCmd.Connection = GetSqlConnection();

strValues = strbValues.ToString().Substring(2);

strFields = strbValues.Replace("", @"", ""], ["").ToString();

strFields = strFields.Substring(3);

SqlCmd.CommandText = ""Insert Into ["" + m_TableName + ""] ("" + strFields

+ ""]) Values("" + strValues + "")\r\n Select SCOPE_IDENTITY() AS [SCOPE_IDENTITY]"";

object obj = SqlCmd.ExecuteScalar();

if ( obj == null || obj is System.DBNull )

{

throw new Exception(""Save item failed."");

}

m_" + m_SqlFields.Identity.Name + @" = System.Convert.ToInt32(obj);

SqlCmd.Connection.Close();

SqlCmd.Parameters.Clear();

}

");

return strbSaveAs.ToString();

}

private string GetCopyToFunction(SqlField [] fields)

{

StringBuilder strbCopyTo = new StringBuilder();

strbCopyTo.Append("\t/// <summary>\r\n\t/// Copy the row to destination row which ID equal iDesID.\r\n\t/// </summary>\r\n\t/// <param name=\"iDesID\"></param>\r\n\tpublic void CopyTo(int iDesID)\r\n\t{\r\n");

for( int i=0 ; i < fields.Length ; i++ )

{

strbCopyTo.Append("\t\tbSet");

strbCopyTo.Append((fields[i]).Name);

strbCopyTo.Append("\t= true;\r\n");

}

strbCopyTo.Append("\r\n\t\tSave(iDesID);\r\n\t}");

return strbCopyTo.ToString();

}

private string GetExtraFunctions()

{

string strCode = @"

private static int GetValue(string strSql)

{

System.Data.SqlClient.SqlCommand SqlCmd;

SqlCmd = new System.Data.SqlClient.SqlCommand();

SqlCmd.Connection = GetSqlConnection();

SqlCmd.CommandText = strSql;

object obj = SqlCmd.ExecuteScalar();

SqlCmd.Connection.Close();

if ( obj == null || obj is System.DBNull )

{

return -1;

}

return (int)obj;

}

public static int GetMaxID()

{

string strSql = ""Select Max(ID) From [{0}]"";

return GetValue(string.Format(strSql, m_TableName));

}

public static bool IsValueExist(string strUnique, string strValue, int iExceptID)

{

System.Data.SqlClient.SqlCommand SqlCmd;

SqlCmd = new System.Data.SqlClient.SqlCommand();

SqlCmd.Connection = GetSqlConnection();

string strSql = ""Select Count(*) From [{0}] Where ([{1}] = @Value)"";

strSql = string.Format(strSql, m_TableName, strUnique);

SqlCmd.Parameters.Add(""@Value"", System.Data.SqlDbType.NVarChar).Value = strValue;

if ( iExceptID >= 0 )

{

strSql = string.Format(""{0} AND ([" + m_SqlFields.Identity.Name + @"] <> '{1}')"", strSql, iExceptID);

}

SqlCmd.CommandText = strSql;

int iCount = (int)SqlCmd.ExecuteScalar();

SqlCmd.Connection.Close();

return !(iCount == 0);

}

public static bool IsValueExist(string strUnique, string strValue)

{

return IsValueExist(strUnique, strValue, -1);

}";

return strCode;

}

public string Convert()

{

return CreateClass().Replace("\r\n", "\n").Replace("\n", "\r\n").Trim();

}

#endregion

同时我也根据项目需要写了几个Sql2Aspx.cs和Sql2AspxCS.cs来生成表单代码，后面这两的通用性就更低了，不过都需要SqlField里的数据来支持转换生成。这些转换中也没有使用CodeDom，而完全使用字符串拼接，甚至直接一段一段的代码写里面，这样做就是为了需要有更改的时候方便，写成CodeDom方式那就完蛋了，时间长了要看半天才能找到要在什么地方做改动。

关于这种Mapping的好处就是自己对数据库访问代码的可控性很强，有问题可以根据自己的需求马上改，而不用去适应一些并不在目前情况下使用的东西，效率也可以做到最好，同时由于代码是自动生成，也不会有太多的重复劳动。操作数据库条目也巨方便，IDE里能做到Sql Field的IntelliSence。

本文转自博客园鸟食轩的博客，原文链接：http://www.cnblogs.com/birdshome/，如需转载请自行联系原博主。

实现SQL -> C# Class Code工具的问题

热门文章

最新文章

相关课程

相关电子书

相关实验场景