實作SQL -> C# Class Code工具的問題

    繼續說明這個玩意兒不是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));

#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;

        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("\r\n{\r\n");

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

            strbCode.Append("\r\n\tprivate static string m_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);

");

    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));

    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));

    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(GetCopyToFunction(m_SqlFields.Fields));

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

            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");

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

                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;

                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(";\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(" != value )\r\n\t\t\t{\r\n\t\t\t\tm_");

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

                    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_");

                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(" = value;\r\n\t\t\tbSet");

                    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;

                strbReadData.AppendFormat(@"

            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");

                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("] = @");

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

                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("\"].Value = m_");

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

                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("\"].Value = m_");

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

            strbSave.Append("] = @");

            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(" = 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");

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

                strbSaveAs.Append(strName);

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

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

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

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

                    strbSaveAs.Append(", ");

                    strbSaveAs.Append(sc.Length);

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

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

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

                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);

            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");

                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;

        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)

        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);

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

        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();

}

同時我也根據項目需要寫了幾個Sql2Aspx.cs和Sql2AspxCS.cs來生成表單代碼，後面這兩的通用性就更低了，不過都需要SqlField裡的資料來支援轉換生成。這些轉換中也沒有使用CodeDom，而完全使用字元串拼接，甚至直接一段一段的代碼寫裡面，這樣做就是為了需要有更改的時候友善，寫成CodeDom方式那就完蛋了，時間長了要看半天才能找到要在什麼地方做改動。

    關于這種Mapping的好處就是自己對資料庫通路代碼的可控性很強，有問題可以根據自己的需求馬上改，而不用去适應一些并不在目前情況下使用的東西，效率也可以做到最好，同時由于代碼是自動生成，也不會有太多的重複勞動。操作資料庫條目也巨友善，IDE裡能做到Sql

Field的IntelliSence。   

本文轉自部落格園鳥食軒的部落格，原文連結：http://www.cnblogs.com/birdshome/，如需轉載請自行聯系原部落客。