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

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

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

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

    在我的AutoCode里,我把每个数据库表的字段分解为:
None.gif     public  struct SqlField
ExpandedBlockStart.gif    {
InBlock.gif         public  string Name;
InBlock.gif         public SqlDbType DbType;
InBlock.gif         public System.Type Type;
InBlock.gif         public  int Length;
InBlock.gif         public  string Sort;
InBlock.gif         public  bool IsNull;
ExpandedBlockEnd.gif    }

    分解的办法也非常的简单,就用正则表达式了:
None.gif string strMatch = @"(\s*\[?(?<Name>\w+)\]?\s+\[?(?<DbType>\w+)\]?){1}(\s*\(?(?<Length>\d+(,\d+)?)\)?)?(\s+(?<Sort>COLLATE\s+\w+))?(\s+(?<IsNull>(NOT\s+)?NULL))?\s*\,";
None.gif //                   ^---------------- Name + DbType ----------------^|<----------- Length ---------->|^---------- Sort ----------^|<--------- Is Null -------->|
None.gif
    // 在[宋体]下就能对齐这个pattern的这各部分的功能和其说明了 

    由于我的工具直接处理SQL语句,需要一个SqlType <-->CTS Type的映射表:
ExpandedBlockStart.gif #region SQL Type <--> CTS Type Mapping Table
InBlock.gif     //  Sql data type mapping to .NET CTS type
InBlock.gif
    m_Type =  new Hashtable();
InBlock.gif    m_Type["bigint"]    = "System.Int64";
InBlock.gif    m_Type["binary"]    = "System.Byte[]";
InBlock.gif    m_Type["bit"]        = "System.Boolean";
InBlock.gif    m_Type["char"]        = "System.String";         // object
InBlock.gif
    m_Type["datetime"]    = "System.DateTime";
InBlock.gif    m_Type["decimal"]    = "System.Decimal";
InBlock.gif    m_Type["float"]        = "System.Double";
InBlock.gif    m_Type["image"]        = "System.Byte[]";         // object
InBlock.gif
    m_Type["int"]        = "System.Int32";
InBlock.gif    m_Type["money"]        = "System.Decimal";
InBlock.gif    m_Type["nchar"]        = "System.String";         // object
InBlock.gif
    m_Type["ntext"]        = "System.String";         // object
InBlock.gif    
// m_Type["numeric"]    = "System.Decimal";
InBlock.gif
    m_Type["nvarchar"]    = "System.String";         // object
InBlock.gif
    m_Type["real"]        = "System.Single";
InBlock.gif    m_Type["smalldatetime"]    = "System.DateTime";
InBlock.gif    m_Type["smallint"]        = "System.Int16";
InBlock.gif    m_Type["smallmoney"]    = "System.Decimal";
InBlock.gif    m_Type["variant"]    = "System.Object";         // object
InBlock.gif
    m_Type["text"]        = "System.String";         // object
InBlock.gif
    m_Type["timestamp"]    = "System.Byte[]";         // object
InBlock.gif
    m_Type["tinyint"]    = "System.Byte";
InBlock.gif    m_Type["uniqueidentifier"]    = "System.Guid"; // object
InBlock.gif
    m_Type["varbinary"]    = "System.Byte[]";         // object
InBlock.gif
    m_Type["varchar"]    = "System.String";         // object
ExpandedBlockEnd.gif
#endregion

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

    通过遍历正则表达式的所有Match,获得一个SqlField数组:
ExpandedBlockStart.gif #region 生成SqlField数组的代码
InBlock.gif            Match m = Regex.Match(strFields, strMatch, RegexOptions.IgnoreCase);
InBlock.gif             while( m.Success )
ExpandedSubBlockStart.gif            {
InBlock.gif                SqlField sc =  new SqlField();
InBlock.gif                sc.Name = m.Groups["Name"].Value;
InBlock.gif                 string strDbType = m.Groups["DbType"].Value.ToLower();
InBlock.gif                 if ( strDbType == "sql_variant" ) strDbType = "variant";
InBlock.gif                 if ( strDbType == "numeric" ) strDbType = "decimal";
InBlock.gif                sc.DbType = (SqlDbType)Enum.Parse(typEnum, strDbType,  true);
InBlock.gif                 string strLength = m.Groups["Length"].Value;
InBlock.gif                 if ( strLength.Length == 0 )
ExpandedSubBlockStart.gif                {
InBlock.gif                    sc.Length = 0;
ExpandedSubBlockEnd.gif                }
InBlock.gif                 else
ExpandedSubBlockStart.gif                {
InBlock.gif                     try
ExpandedSubBlockStart.gif                    {
InBlock.gif                        sc.Length =  int.Parse(m.Groups["Length"].Value);
ExpandedSubBlockEnd.gif                    }
InBlock.gif                     catch
ExpandedSubBlockStart.gif                    {
InBlock.gif                        sc.Length = 0;
ExpandedSubBlockEnd.gif                    }
ExpandedSubBlockEnd.gif                }
InBlock.gif                sc.Type = System.Type.GetType(m_Type[strDbType].ToString());
InBlock.gif                sc.Sort = m.Groups["Sort"].Value;
InBlock.gif                sc.IsNull = (m.Groups["IsNull"].Value.ToLower() == "null");
InBlock.gif                alstFields.Add(sc);
InBlock.gif                 // strSql = strSql.Replace(m.Value, "");
InBlock.gif
                m = m.NextMatch();
ExpandedSubBlockEnd.gif            }
InBlock.gif            m_Fields = (SqlField [])alstFields.ToArray( typeof(SqlField));
ExpandedBlockEnd.gif #endregion

    上次我示例的代码就是我那次项目需要的代码而已,而有了SqlField数组后,要生成什么代码都巨方便了。下面这个Sql2Class.cs就是上次提到的那个 示例里的代码转换的源码。
ExpandedBlockStart.gif #region Sql2Class
InBlock.gifusing System;
InBlock.gifusing System.Collections;
InBlock.gifusing System.Text;
InBlock.gifusing System.Data;
InBlock.gifusing System.Data.SqlClient;
InBlock.gifusing System.Text.RegularExpressions;
InBlock.gif
InBlock.gifnamespace CodeConvert
ExpandedSubBlockStart.gif{
ExpandedSubBlockStart.gif    /// <summary>
InBlock.gif    
/// Summary description for SqlToCSharp.
ExpandedSubBlockEnd.gif    
/// </summary>
InBlock.gif    public class Sql2Class
ExpandedSubBlockStart.gif    {
InBlock.gif        private SqlFieldCollection m_SqlFields;
InBlock.gif
InBlock.gif        public Sql2Class(string strSqlCode)
ExpandedSubBlockStart.gif        {
InBlock.gif            m_SqlFields = new SqlFieldCollection(strSqlCode);
InBlock.gif            CreateClass();
ExpandedSubBlockEnd.gif        }
InBlock.gif
InBlock.gif        public Sql2Class(SqlFieldCollection sfc)
ExpandedSubBlockStart.gif        {
InBlock.gif            m_SqlFields = sfc;
InBlock.gif            CreateClass();
ExpandedSubBlockEnd.gif        }
InBlock.gif
InBlock.gif        private string CreateClass()
ExpandedSubBlockStart.gif        {
InBlock.gif            if ( m_SqlFields.FieldsCount == 0 ) return "";
InBlock.gif            StringBuilder strbCode = new StringBuilder();
InBlock.gif            strbCode.Append("/// <summary>\r\n/// Automatically Code for Table '");
InBlock.gif            strbCode.Append(m_SqlFields.TableName);
InBlock.gif            strbCode.Append("',\r\n/// by Birdshome AutoCode Engine V.1.0\r\n/// Copyright (C) 2004.1 Birdshome, HIT\r\n/// Create at : ");
InBlock.gif            strbCode.Append(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
InBlock.gif            strbCode.Append("\r\n/// </summary>\r\npublic class ");
InBlock.gif            strbCode.Append(m_SqlFields.TableName);
InBlock.gif            strbCode.Append("\r\n{\r\n");
InBlock.gif            strbCode.Append(DefineVariables(m_SqlFields.Fields));
InBlock.gif            strbCode.Append("\r\n\tprivate static string m_TableName = \"");
InBlock.gif            strbCode.Append(m_SqlFields.TableName);
InBlock.gif            strbCode.Append("\";\r\n\r\n");
InBlock.gif            strbCode.Append(DefineAttributes(m_SqlFields.Fields));
InBlock.gif            strbCode.Append(@"
InBlock.gif    public " + m_SqlFields.TableName + @"()
ExpandedSubBlockStart.gif    {
InBlock.gif        SqlCmd = new System.Data.SqlClient.SqlCommand();
ExpandedSubBlockEnd.gif    }
InBlock.gif
InBlock.gif    public " + m_SqlFields.TableName + @"(int id) : this()
ExpandedSubBlockStart.gif    {
InBlock.gif        if ( id <= 0 )
ExpandedSubBlockStart.gif        {
InBlock.gif            throw new System.Exception(""The value of id must be greater than zero."");
ExpandedSubBlockEnd.gif        }
InBlock.gif        m_" + m_SqlFields.Identity.Name + @" = id;
InBlock.gif        Load();
ExpandedSubBlockEnd.gif    }
InBlock.gif
InBlock.gif    public " + m_SqlFields.TableName + @"(string field, string @value) : this()
ExpandedSubBlockStart.gif    {
InBlock.gif        if ( field == null || field.Length == 0 )
ExpandedSubBlockStart.gif        {
InBlock.gif            throw new System.NullReferenceException(""field"");
ExpandedSubBlockEnd.gif        }
InBlock.gif        if ( @value == null || field.Length == 0 )
ExpandedSubBlockStart.gif        {
InBlock.gif            throw new System.NullReferenceException(""@value"");
ExpandedSubBlockEnd.gif        }
InBlock.gif        Load(field, @value);
ExpandedSubBlockEnd.gif    }
InBlock.gif");
InBlock.gif
InBlock.gif            strbCode.Append(@"
InBlock.gif    protected void Load()
ExpandedSubBlockStart.gif    {
InBlock.gif        if ( m_" + m_SqlFields.Identity.Name + @" <= 0 )
ExpandedSubBlockStart.gif        {
InBlock.gif            throw new System.Exception(""The value of m_ID must be greater than zero."");
ExpandedSubBlockEnd.gif        }
InBlock.gif        Load(m_" + m_SqlFields.Identity.Name + @");
ExpandedSubBlockEnd.gif    }
InBlock.gif
InBlock.gif    protected void Load(int id)
ExpandedSubBlockStart.gif    {
ExpandedSubBlockStart.gif        string strSql = ""Select * From [{0}] Where ([ID] = {1})"";
InBlock.gif        RunSql(string.Format(strSql, m_TableName, id));
ExpandedSubBlockEnd.gif    }
InBlock.gif");
InBlock.gif
InBlock.gif            strbCode.Append(@"
InBlock.gif    protected void Load(string field, string @value)
ExpandedSubBlockStart.gif    {
InBlock.gif        if ( @value.IndexOf('\'') != -1 )
ExpandedSubBlockStart.gif        {
InBlock.gif            @value = @value.Replace(""'"", ""''"");
ExpandedSubBlockEnd.gif        }
ExpandedSubBlockStart.gif        string strSql = ""Select * From [{0}] Where ([{1}] = '{2}')"";
InBlock.gif        RunSql(string.Format(strSql, m_TableName, field, @value));
ExpandedSubBlockEnd.gif    }
InBlock.gif");
InBlock.gif
InBlock.gif            strbCode.Append(@"
InBlock.gif    private void RunSql(string strSql)
ExpandedSubBlockStart.gif    {
InBlock.gif        SqlCmd.Connection = GetSqlConnection();
InBlock.gif        SqlCmd.CommandText = strSql;
InBlock.gif        System.Data.SqlClient.SqlDataReader drFields;
InBlock.gif        drFields = SqlCmd.ExecuteReader();
InBlock.gif        try
ExpandedSubBlockStart.gif        {
InBlock.gif            ReadData(drFields);
ExpandedSubBlockEnd.gif        }
InBlock.gif        catch(System.Exception exp)
ExpandedSubBlockStart.gif        {
InBlock.gif            throw exp;
ExpandedSubBlockEnd.gif        }
InBlock.gif        finally
ExpandedSubBlockStart.gif        {
InBlock.gif            drFields.Close();
InBlock.gif            drFields = null;
InBlock.gif            SqlCmd.Connection.Close();
InBlock.gif            SqlCmd.Parameters.Clear();
ExpandedSubBlockEnd.gif        }
ExpandedSubBlockEnd.gif    }
InBlock.gif");
InBlock.gif
InBlock.gif            strbCode.Append(GetReadDataFunction(m_SqlFields.Fields).Replace("\r\n", "\n").Replace("\n", "\r\n"));
InBlock.gif            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_");
InBlock.gif            strbCode.Append(m_SqlFields.Identity.Name);
InBlock.gif            strbCode.Append(");\r\n\t}\r\n");
InBlock.gif            strbCode.Append(GetSaveFunction(m_SqlFields.Fields));
InBlock.gif            strbCode.Append("\r\n");
InBlock.gif            strbCode.Append(GetSaveAsFunction(m_SqlFields.Fields));
InBlock.gif            strbCode.Append("\r\n");
InBlock.gif            strbCode.Append(GetCopyToFunction(m_SqlFields.Fields));
InBlock.gif            strbCode.Append("\r\n\r\n\t~");
InBlock.gif            strbCode.Append(m_SqlFields.TableName);
InBlock.gif            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");
InBlock.gif            strbCode.Append(GetExtraFunctions());
InBlock.gif            strbCode.Append("\r\n}");
InBlock.gif            return strbCode.ToString();
ExpandedSubBlockEnd.gif        }
InBlock.gif
InBlock.gif        private string DefineVariables(SqlField [] fields)
ExpandedSubBlockStart.gif        {
InBlock.gif            StringBuilder strbVariables = new StringBuilder();
InBlock.gif            strbVariables.Append("\tprivate ");
InBlock.gif            strbVariables.Append(m_SqlFields.Identity.Type.ToString());
InBlock.gif            strbVariables.Append(" m_");
InBlock.gif            strbVariables.Append(m_SqlFields.Identity.Name);
InBlock.gif            strbVariables.Append(";\r\n");
InBlock.gif            forint i=0 ; i < fields.Length ; i++ )
ExpandedSubBlockStart.gif            {
InBlock.gif                strbVariables.Append("\tprivate ");
InBlock.gif                strbVariables.Append((fields[i]).Type.ToString());
InBlock.gif                strbVariables.Append(" m_");
InBlock.gif                strbVariables.Append((fields[i]).Name);
InBlock.gif                strbVariables.Append(";\r\n");
ExpandedSubBlockEnd.gif            }
InBlock.gif            strbVariables.Append("\tprivate System.Data.SqlClient.SqlCommand SqlCmd;\r\n\r\n");
InBlock.gif            forint i=0 ; i < fields.Length ; i++ )
ExpandedSubBlockStart.gif            {
InBlock.gif                strbVariables.Append("\tprivate bool bSet");
InBlock.gif                strbVariables.Append((fields[i]).Name);
InBlock.gif                strbVariables.Append("\t= false;\r\n");
ExpandedSubBlockEnd.gif            }
InBlock.gif            return strbVariables.ToString();
ExpandedSubBlockEnd.gif        }
InBlock.gif
InBlock.gif        private string DefineAttributes(SqlField [] fields)
ExpandedSubBlockStart.gif        {
InBlock.gif            string strName, strType;
InBlock.gif            StringBuilder strbAttributes = new StringBuilder();
InBlock.gif            strbAttributes.Append("\tpublic ");
InBlock.gif            strbAttributes.Append(m_SqlFields.Identity.Type.ToString());
InBlock.gif            strbAttributes.Append(" " + m_SqlFields.Identity.Name);
InBlock.gif            strbAttributes.Append("\r\n\t{\r\n\t\tget\r\n\t\t{\r\n\t\t\treturn m_");
InBlock.gif            strbAttributes.Append(m_SqlFields.Identity.Name);
InBlock.gif            strbAttributes.Append(";\r\n\t\t}\r\n\t}\r\n");
InBlock.gif            System.Type type;
InBlock.gif            forint i=0 ; i < fields.Length ; i++ )
ExpandedSubBlockStart.gif            {
InBlock.gif                strName = (fields[i]).Name;
InBlock.gif                type = (fields[i]).Type;
InBlock.gif                strType = type.ToString();
InBlock.gif
InBlock.gif                strbAttributes.Append("\r\n\tpublic ");
InBlock.gif                strbAttributes.Append(strType);
InBlock.gif                strbAttributes.Append(" ");
InBlock.gif                strbAttributes.Append(strName);
InBlock.gif                strbAttributes.Append("\r\n\t{\r\n\t\tget\r\n\t\t{\r\n\t\t\treturn m_");
InBlock.gif                strbAttributes.Append(strName);
InBlock.gif                strbAttributes.Append(";\r\n\t\t}\r\n\t\tset\r\n\t\t{\r\n\t\t\t");
InBlock.gif                
InBlock.gif                if ( type == typeof(System.Byte) 
InBlock.gif                    || type == typeof(System.Int16) 
InBlock.gif                    || type == typeof(System.Int32) 
InBlock.gif                    || type == typeof(System.Int64) 
InBlock.gif                    || type == typeof(System.Single) )
ExpandedSubBlockStart.gif                {
InBlock.gif                    strbAttributes.Append("if ( m_");
InBlock.gif                    strbAttributes.Append(strName);
InBlock.gif                    strbAttributes.Append(" == 0 || m_");
InBlock.gif                    strbAttributes.Append(strName);
InBlock.gif                    strbAttributes.Append(" != value )\r\n\t\t\t{\r\n\t\t\t\tm_");
InBlock.gif                    strbAttributes.Append(strName);
InBlock.gif                    strbAttributes.Append(" = value;\r\n\t\t\t\tbSet");
InBlock.gif                    strbAttributes.Append(strName);
InBlock.gif                    strbAttributes.Append(" = true;\r\n\t\t\t}\r\n");
ExpandedSubBlockEnd.gif                }
InBlock.gif
InBlock.gif                if ( type == typeof(System.Byte[]) 
InBlock.gif                    || type == typeof(System.Object) 
InBlock.gif                    || type == typeof(System.String) )
ExpandedSubBlockStart.gif                {
InBlock.gif                    strbAttributes.Append("if ( value != null && m_");
InBlock.gif                    strbAttributes.Append(strName);
InBlock.gif                    strbAttributes.Append(" != value )\r\n\t\t\t{\r\n\t\t\t\tm_");
InBlock.gif                    strbAttributes.Append(strName);
InBlock.gif                    strbAttributes.Append(" = value;\r\n\t\t\t\tbSet");
InBlock.gif                    strbAttributes.Append(strName);
InBlock.gif                    strbAttributes.Append(" = true;\r\n\t\t\t}\r\n");
ExpandedSubBlockEnd.gif                }
InBlock.gif
InBlock.gif                if ( type == typeof(System.Boolean) 
InBlock.gif                    || type == typeof(System.DateTime) 
InBlock.gif                    || type == typeof(System.Decimal) 
InBlock.gif                    || type == typeof(System.Double) 
InBlock.gif                    || type == typeof(System.Guid) )
ExpandedSubBlockStart.gif                {
InBlock.gif                    strbAttributes.Append("m_");
InBlock.gif                    strbAttributes.Append(strName);
InBlock.gif                    strbAttributes.Append(" = value;\r\n\t\t\tbSet");
InBlock.gif                    strbAttributes.Append(strName);
InBlock.gif                    strbAttributes.Append(" = true;\r\n");
ExpandedSubBlockEnd.gif                }
InBlock.gif                strbAttributes.Append("\t\t}\r\n\t}\r\n");
ExpandedSubBlockEnd.gif            }
InBlock.gif            return strbAttributes.ToString();
ExpandedSubBlockEnd.gif        }
InBlock.gif
InBlock.gif        private string GetReadDataFunction(SqlField [] fields)
ExpandedSubBlockStart.gif        {
InBlock.gif            StringBuilder strbReadData = new StringBuilder();
InBlock.gif            strbReadData.AppendFormat(@"
InBlock.gif    private void ReadData(System.Data.SqlClient.SqlDataReader drFields)
ExpandedSubBlockStart.gif    {{
InBlock.gif        bool bLoadSuccess = false;
InBlock.gif        if ( drFields.Read() )
ExpandedSubBlockStart.gif        {{
ExpandedSubBlockStart.gif            object obj{0} = drFields[""{0}""];
ExpandedSubBlockStart.gif            m_{0} = (System.Int32)obj{0};
InBlock.gif", m_SqlFields.Identity.Name);
InBlock.gif
InBlock.gif            string strName;
InBlock.gif            forint i=0 ; i < fields.Length ; i++ )
ExpandedSubBlockStart.gif            {
InBlock.gif                strName = (fields[i]).Name;
InBlock.gif                strbReadData.AppendFormat(@"
ExpandedSubBlockStart.gif            object obj{0} = drFields[""{0}""];
ExpandedSubBlockStart.gif            if ( !( obj{0} is System.DBNull ) )
ExpandedSubBlockStart.gif            {{
ExpandedSubBlockStart.gif                m_{0} = ({1})obj{0};
ExpandedSubBlockEnd.gif            }}", strName, (fields[i]).Type.ToString());
ExpandedSubBlockEnd.gif            }
InBlock.gif            strbReadData.Append(@"
InBlock.gif            bLoadSuccess = true;
ExpandedSubBlockEnd.gif        }
InBlock.gif        bool bNonUnique = drFields.Read();
InBlock.gif        if ( bNonUnique || !bLoadSuccess )
ExpandedSubBlockStart.gif        {
InBlock.gif            string strMessage = ""The identity isn't unique."";
InBlock.gif            throw new System.Exception(strMessage);
ExpandedSubBlockEnd.gif        }
ExpandedSubBlockEnd.gif    }
InBlock.gif");
InBlock.gif            return strbReadData.ToString();
ExpandedSubBlockEnd.gif        }
InBlock.gif
InBlock.gif        private string GetSaveFunction(SqlField [] fields)
ExpandedSubBlockStart.gif        {
InBlock.gif            StringBuilder strbSave = new StringBuilder();
InBlock.gif            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");
InBlock.gif            string strName;
InBlock.gif            forint i=0 ; i < fields.Length ; i++ )
ExpandedSubBlockStart.gif            {
InBlock.gif                SqlField sc = fields[i];
InBlock.gif                strName = sc.Name;
InBlock.gif                strbSave.Append("\t\tif ( bSet");
InBlock.gif                strbSave.Append(strName);
InBlock.gif                strbSave.Append(" )\r\n\t\t{\r\n\t\t\tstrbSql.Append(\", [");
InBlock.gif                strbSave.Append(strName);
InBlock.gif                strbSave.Append("] = @");
InBlock.gif                strbSave.Append(strName);
InBlock.gif                strbSave.Append("\");\r\n\t\t\tspc.Add(\"@");
InBlock.gif                strbSave.Append(strName);
InBlock.gif                strbSave.Append("\", System.Data.SqlDbType.");
InBlock.gif                strbSave.Append(sc.DbType.ToString());
InBlock.gif                if ( sc.Length > 0 )
ExpandedSubBlockStart.gif                {
InBlock.gif                    strbSave.Append(", ");
InBlock.gif                    strbSave.Append(sc.Length);
ExpandedSubBlockEnd.gif                }
InBlock.gif                strbSave.Append(");\r\n\t\t\tspc[\"@");
InBlock.gif                strbSave.Append(strName);
InBlock.gif                strbSave.Append("\"].Value = m_");
InBlock.gif                strbSave.Append(strName);
InBlock.gif                strbSave.Append(";\r\n\t\t\tbSet");
InBlock.gif                strbSave.Append(strName);
InBlock.gif                strbSave.Append(" = false;\r\n\t\t}\r\n");
ExpandedSubBlockEnd.gif            }
InBlock.gif            strbSave.Append("\t\tif( spc.Count > 0 )\r\n\t\t{\r\n\t\t\tspc.Add(\"@");
InBlock.gif            strbSave.Append(m_SqlFields.Identity.Name);
InBlock.gif            strbSave.Append("\", System.Data.SqlDbType.");
InBlock.gif            strbSave.Append(m_SqlFields.Identity.DbType.ToString());
InBlock.gif            strbSave.Append(");\r\n\t\t\tspc[\"@");
InBlock.gif            strbSave.Append(m_SqlFields.Identity.Name);
InBlock.gif            strbSave.Append("\"].Value = m_");
InBlock.gif            strbSave.Append(m_SqlFields.Identity.Name);
InBlock.gif            strbSave.Append(";\r\n\t\t\tstrbSql.Append(\" Where ([");
InBlock.gif            strbSave.Append(m_SqlFields.Identity.Name);
InBlock.gif            strbSave.Append("] = @");
InBlock.gif            strbSave.Append(m_SqlFields.Identity.Name);
InBlock.gif            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_");
InBlock.gif            strbSave.Append(m_SqlFields.Identity.Name);
InBlock.gif            strbSave.Append(" = iIdentity;\r\n\t\t}\r\n\t}\r\n");
InBlock.gif            return strbSave.ToString();
ExpandedSubBlockEnd.gif        }
InBlock.gif
InBlock.gif        private string GetSaveAsFunction(SqlField [] fields)
ExpandedSubBlockStart.gif        {
InBlock.gif            StringBuilder strbSaveAs = new StringBuilder();
InBlock.gif            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");
InBlock.gif            string strName;
InBlock.gif            forint i=0 ; i < fields.Length ; i++ )
ExpandedSubBlockStart.gif            {
InBlock.gif                SqlField sc = fields[i];
InBlock.gif                strName = sc.Name;
InBlock.gif                strbSaveAs.Append("\r\n\t\tif ( bSet");
InBlock.gif                strbSaveAs.Append(strName);
InBlock.gif                strbSaveAs.Append(" )\r\n\t\t{\r\n\t\t\tstrbValues.Append(\", @");
InBlock.gif                strbSaveAs.Append(strName);
InBlock.gif                strbSaveAs.Append("\");\r\n\t\t\tspc.Add(\"@");
InBlock.gif                strbSaveAs.Append(strName);
InBlock.gif                strbSaveAs.Append("\", System.Data.SqlDbType.");
InBlock.gif                strbSaveAs.Append(sc.DbType.ToString());
InBlock.gif                if ( sc.Length > 0 )
ExpandedSubBlockStart.gif                {
InBlock.gif                    strbSaveAs.Append(", ");
InBlock.gif                    strbSaveAs.Append(sc.Length);
ExpandedSubBlockEnd.gif                }
InBlock.gif                strbSaveAs.Append(");\r\n\t\t\tspc[\"@");
InBlock.gif                strbSaveAs.Append(strName);
InBlock.gif                strbSaveAs.Append("\"].Value = m_");
InBlock.gif                strbSaveAs.Append(strName);
InBlock.gif                strbSaveAs.Append(";\r\n\t\t\tbSet");
InBlock.gif                strbSaveAs.Append(strName);
InBlock.gif                strbSaveAs.Append(" = false;\r\n\t\t}");
ExpandedSubBlockEnd.gif            }
InBlock.gif            strbSaveAs.Append(@"
InBlock.gif        string strFields, strValues;
InBlock.gif        if ( strbValues.Length > 3 )
ExpandedSubBlockStart.gif        {
InBlock.gif            SqlCmd.Connection = GetSqlConnection();
InBlock.gif            strValues = strbValues.ToString().Substring(2);
InBlock.gif            strFields = strbValues.Replace("", @"", ""], ["").ToString();
InBlock.gif            strFields = strFields.Substring(3);
InBlock.gif            SqlCmd.CommandText = ""Insert Into ["" + m_TableName + ""] ("" + strFields
InBlock.gif                + ""]) Values("" + strValues + "")\r\n Select SCOPE_IDENTITY() AS [SCOPE_IDENTITY]"";
InBlock.gif            object obj = SqlCmd.ExecuteScalar();
InBlock.gif            if ( obj == null || obj is System.DBNull )
ExpandedSubBlockStart.gif            {
InBlock.gif                throw new Exception(""Save item failed."");
ExpandedSubBlockEnd.gif            }
InBlock.gif            m_" + m_SqlFields.Identity.Name + @" = System.Convert.ToInt32(obj);
InBlock.gif            SqlCmd.Connection.Close();
InBlock.gif            SqlCmd.Parameters.Clear();
ExpandedSubBlockEnd.gif        }
ExpandedSubBlockEnd.gif    }
InBlock.gif");
InBlock.gif            return strbSaveAs.ToString();
ExpandedSubBlockEnd.gif        }
InBlock.gif
InBlock.gif        private string GetCopyToFunction(SqlField [] fields)
ExpandedSubBlockStart.gif        {
InBlock.gif            StringBuilder strbCopyTo = new StringBuilder();
InBlock.gif            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");
InBlock.gif            forint i=0 ; i < fields.Length ; i++ )
ExpandedSubBlockStart.gif            {
InBlock.gif                strbCopyTo.Append("\t\tbSet");
InBlock.gif                strbCopyTo.Append((fields[i]).Name);
InBlock.gif                strbCopyTo.Append("\t= true;\r\n");
ExpandedSubBlockEnd.gif            }
InBlock.gif            strbCopyTo.Append("\r\n\t\tSave(iDesID);\r\n\t}");
InBlock.gif            return strbCopyTo.ToString();
ExpandedSubBlockEnd.gif        }
InBlock.gif
InBlock.gif        private string GetExtraFunctions()
ExpandedSubBlockStart.gif        {
InBlock.gif            string strCode = @"
InBlock.gif    private static int GetValue(string strSql)
ExpandedSubBlockStart.gif    {
InBlock.gif        System.Data.SqlClient.SqlCommand SqlCmd;
InBlock.gif        SqlCmd = new System.Data.SqlClient.SqlCommand();
InBlock.gif        SqlCmd.Connection = GetSqlConnection();
InBlock.gif        SqlCmd.CommandText = strSql;
InBlock.gif        object obj = SqlCmd.ExecuteScalar();
InBlock.gif        SqlCmd.Connection.Close();
InBlock.gif        if ( obj == null || obj is System.DBNull )
ExpandedSubBlockStart.gif        {
InBlock.gif            return -1;
ExpandedSubBlockEnd.gif        }
InBlock.gif        return (int)obj;
ExpandedSubBlockEnd.gif    }
InBlock.gif
InBlock.gif    public static int GetMaxID()
ExpandedSubBlockStart.gif    {
ExpandedSubBlockStart.gif        string strSql = ""Select Max(ID) From [{0}]"";
InBlock.gif        return GetValue(string.Format(strSql, m_TableName));
ExpandedSubBlockEnd.gif    }
InBlock.gif
InBlock.gif    public static bool IsValueExist(string strUnique, string strValue, int iExceptID)
ExpandedSubBlockStart.gif    {
InBlock.gif        System.Data.SqlClient.SqlCommand SqlCmd;
InBlock.gif        SqlCmd = new System.Data.SqlClient.SqlCommand();
InBlock.gif        SqlCmd.Connection = GetSqlConnection();
ExpandedSubBlockStart.gif        string strSql = ""Select Count(*) From [{0}] Where ([{1}] = @Value)"";
InBlock.gif        strSql = string.Format(strSql, m_TableName, strUnique);
InBlock.gif        SqlCmd.Parameters.Add(""@Value"", System.Data.SqlDbType.NVarChar).Value = strValue;
InBlock.gif        if ( iExceptID >= 0 )
ExpandedSubBlockStart.gif        {
ExpandedSubBlockStart.gif            strSql = string.Format(""{0} AND ([" + m_SqlFields.Identity.Name + @"] <> '{1}')"", strSql, iExceptID);
ExpandedSubBlockEnd.gif        }
InBlock.gif        SqlCmd.CommandText = strSql;
InBlock.gif        int iCount = (int)SqlCmd.ExecuteScalar();
InBlock.gif        SqlCmd.Connection.Close();
InBlock.gif        return !(iCount == 0);
ExpandedSubBlockEnd.gif    }
InBlock.gif
InBlock.gif    public static bool IsValueExist(string strUnique, string strValue)
ExpandedSubBlockStart.gif    {
InBlock.gif        return IsValueExist(strUnique, strValue, -1);
ExpandedSubBlockEnd.gif    }";
InBlock.gif            return strCode;
ExpandedSubBlockEnd.gif        }
InBlock.gif
InBlock.gif        public string Convert()
ExpandedSubBlockStart.gif        {
InBlock.gif            return CreateClass().Replace("\r\n", "\n").Replace("\n", "\r\n").Trim();
ExpandedSubBlockEnd.gif        }
ExpandedSubBlockEnd.gif    }
ExpandedSubBlockEnd.gif}
ExpandedBlockEnd.gif#endregion
    同时我也根据项目需要写了几个Sql2Aspx.cs和Sql2AspxCS.cs来生成表单代码,后面这两的通用性就更低了,不过都需要SqlField里的数据来支持转换生成。这些转换中也没有使用CodeDom,而完全使用字符串拼接,甚至直接一段一段的代码写里面,这样做就是为了需要有更改的时候方便,写成CodeDom方式那就完蛋了,时间长了要看半天才能找到要在什么地方做改动。

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


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

目录
相关文章
|
2月前
|
SQL 数据管理 关系型数据库
《SQL转换秘籍:Vanna+Qwen双剑合璧,轻松实现私有模型转换》——揭秘如何利用Vanna和Qwen这两款神级工具,让你的SQL数据管理和转换如虎添翼!
【8月更文挑战第17天】Vanna与Qwen是两款优秀的开源数据库管理工具,助力用户高效管理及转换SQL数据。先安装Vanna和Qwen,随后在Vanna中创建并编辑私有模型,定义表结构等。完成模型构建后,导出为SQL文件。接着,在Qwen中导入此文件,并根据目标数据库类型(如MySQL)转换SQL语句。例如,生成创建`users`表的SQL代码。这两款工具显著提升了数据库管理工作流程的便捷性与效率。
99 1
|
2月前
|
SQL 数据处理 数据库
|
2月前
|
SQL 存储 监控
|
2月前
|
Java 开发者 前端开发
Struts 2、Spring MVC、Play Framework 上演巅峰之战,Web 开发的未来何去何从?
【8月更文挑战第31天】在Web应用开发中,Struts 2框架因强大功能和灵活配置备受青睐,但开发者常遇配置错误、类型转换失败、标签属性设置不当及异常处理等问题。本文通过实例解析常见难题与解决方案,如配置文件中遗漏`result`元素致页面跳转失败、日期格式不匹配需自定义转换器、`&lt;s:checkbox&gt;`标签缺少`label`属性致显示不全及Action中未捕获异常影响用户体验等,助您有效应对挑战。
67 0
|
2月前
|
SQL 存储 数据处理
SQL中的运算符:数据操作的核心工具
【8月更文挑战第31天】
82 0
|
2月前
|
SQL 数据挖掘 关系型数据库
|
2月前
|
SQL 数据处理 数据库
SQL正则表达式应用:文本数据处理的强大工具——深入探讨数据验证、模式搜索、字符替换等核心功能及性能优化和兼容性问题
【8月更文挑战第31天】SQL正则表达式是数据库管理和应用开发中处理文本数据的强大工具,支持数据验证、模式搜索和字符替换等功能。本文通过问答形式介绍了其基本概念、使用方法及注意事项,帮助读者掌握这一重要技能,提升文本数据处理效率。尽管功能强大,但在不同数据库系统中可能存在兼容性问题,需谨慎使用以优化性能。
35 0
|
2月前
|
存储 API C#
【Azure Developer】解决Azure Key Vault管理Storage的示例代码在中国区Azure遇见的各种认证/授权问题 - C# Example Code
【Azure Developer】解决Azure Key Vault管理Storage的示例代码在中国区Azure遇见的各种认证/授权问题 - C# Example Code
|
2月前
|
开发框架 .NET 编译器
C# 中的记录(record)类型和类(class)类型对比总结
C# 中的记录(record)类型和类(class)类型对比总结
下一篇
无影云桌面