继续说明这个玩意儿不是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 -------->|
由于我的工具直接处理SQL语句，需要一个SqlType <-->CTS Type的映射表：
SQL Type <--> CTS Type Mapping Table
#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数组：
生成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就是上次提到的那个 示例里的代码转换的源码。
Sql2Class
#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("',\r\n/// by Birdshome AutoCode Engine V.1.0\r\n/// Copyright (C) 2004.1 Birdshome, HIT\r\n/// Create at : "); 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
关于这种Mapping的好处就是自己对数据库访问代码的可控性很强，有问题可以根据自己的需求马上改，而不用去适应一些并不在目前情况下使用的东西，效率也可以做到最好，同时由于代码是自动生成，也不会有太多的重复劳动。操作数据库条目也巨方便，IDE里能做到Sql Field的IntelliSence。
AutoCode下载，除了它的SQL转SqlField数组的功能外，其它代码都是为了一次项目而构建的，不必关注。
1838
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
