有一段时间大家都在讨论生成SQL操作数据库代码的AutoCode工具,我也趁着热闹把我做的一个
SQL AutoCode工具拿出来讨论,不过后来忘掉了这事,今天看见有人
回复,希望把源码拿出来参考参考,索性就再说说我对制作这种工具的看法和遇到的问题。
继续说明这个玩意儿不是OR Mapping,因为它只有Mapping而没有任何的Relation。我觉得非要取个名字叫Object DataRow Mapping还比较恰当。如果你在做数据库方面的内容,而且还在自己拼接SQL语句来更新数据库数据行的话,那么这篇文章将非常适合你的胃口。
在各种各样的项目中,中小型项目中是数量众多的,很多这样的项目需要数据库,而又不会有十分复杂的表间依赖关系。在操作数据库是的代码也不是一成不变的,很多时候会有一些custom的内容需要加在代码里,想做一个大而全的AutoCode来通吃也非常的困难,我觉得更好的办法是把分解SQL语言的功能模块化,然后自己在项目中根具自己的需求来AutoCode就行了。
在我的AutoCode里,我把每个数据库表的字段分解为:
public
struct
SqlField
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
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的映射表:
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 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sc.Length = 0;
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sc.Length = int.Parse(m.Groups["Length"].Value);
}
catch
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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;
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
namespace CodeConvert
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](/Images/OutliningIndicators/ContractedSubBlock.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// Summary description for SqlToCSharp.
/// </summary>
public class Sql2Class
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
private SqlFieldCollection m_SqlFields;
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public Sql2Class(string strSqlCode)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
m_SqlFields = new SqlFieldCollection(strSqlCode);
CreateClass();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public Sql2Class(SqlFieldCollection sfc)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
m_SqlFields = sfc;
CreateClass();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
private string CreateClass()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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 + @"()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlCmd = new System.Data.SqlClient.SqlCommand();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public " + m_SqlFields.TableName + @"(int id) : this()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if ( id <= 0 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new System.Exception(""The value of id must be greater than zero."");
}
m_" + m_SqlFields.Identity.Name + @" = id;
Load();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public " + m_SqlFields.TableName + @"(string field, string @value) : this()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if ( field == null || field.Length == 0 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new System.NullReferenceException(""field"");
}
if ( @value == null || field.Length == 0 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new System.NullReferenceException(""@value"");
}
Load(field, @value);
}
");
strbCode.Append(@"
protected void Load()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if ( m_" + m_SqlFields.Identity.Name + @" <= 0 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new System.Exception(""The value of m_ID must be greater than zero."");
}
Load(m_" + m_SqlFields.Identity.Name + @");
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
protected void Load(int id)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
string strSql = ""Select * From [
{0}] Where ([ID] =
{1})"";
RunSql(string.Format(strSql, m_TableName, id));
}
");
strbCode.Append(@"
protected void Load(string field, string @value)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if ( @value.IndexOf('\'') != -1 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
@value = @value.Replace(""'"", ""''"");
}
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
string strSql = ""Select * From [
{0}] Where ([
{1}] = '{2}')"";
RunSql(string.Format(strSql, m_TableName, field, @value));
}
");
strbCode.Append(@"
private void RunSql(string strSql)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlCmd.Connection = GetSqlConnection();
SqlCmd.CommandText = strSql;
System.Data.SqlClient.SqlDataReader drFields;
drFields = SqlCmd.ExecuteReader();
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
ReadData(drFields);
}
catch(System.Exception exp)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw exp;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
private string DefineVariables(SqlField [] fields)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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++ )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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++ )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strbVariables.Append("\tprivate bool bSet");
strbVariables.Append((fields[i]).Name);
strbVariables.Append("\t= false;\r\n");
}
return strbVariables.ToString();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
private string DefineAttributes(SqlField [] fields)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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++ )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strName = (fields[i]).Name;
type = (fields[i]).Type;
strType = type.ToString();
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
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) )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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");
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
if ( type == typeof(System.Byte[])
|| type == typeof(System.Object)
|| type == typeof(System.String) )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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");
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
if ( type == typeof(System.Boolean)
|| type == typeof(System.DateTime)
|| type == typeof(System.Decimal)
|| type == typeof(System.Double)
|| type == typeof(System.Guid) )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
private string GetReadDataFunction(SqlField [] fields)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
StringBuilder strbReadData = new StringBuilder();
strbReadData.AppendFormat(@"
private void ReadData(System.Data.SqlClient.SqlDataReader drFields)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
{
bool bLoadSuccess = false;
if ( drFields.Read() )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
object obj
{0} = drFields[""
{0}""];
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
m_
{0} = (System.Int32)obj
{0};
", m_SqlFields.Identity.Name);
string strName;
for( int i=0 ; i < fields.Length ; i++ )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strName = (fields[i]).Name;
strbReadData.AppendFormat(@"
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
object obj
{0} = drFields[""
{0}""];
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if ( !( obj
{0} is System.DBNull ) )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
m_
{0} = (
{1})obj
{0};
}}", strName, (fields[i]).Type.ToString());
}
strbReadData.Append(@"
bLoadSuccess = true;
}
bool bNonUnique = drFields.Read();
if ( bNonUnique || !bLoadSuccess )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strMessage = ""The identity isn't unique."";
throw new System.Exception(strMessage);
}
}
");
return strbReadData.ToString();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
private string GetSaveFunction(SqlField [] fields)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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++ )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
private string GetSaveAsFunction(SqlField [] fields)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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++ )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new Exception(""Save item failed."");
}
m_" + m_SqlFields.Identity.Name + @" = System.Convert.ToInt32(obj);
SqlCmd.Connection.Close();
SqlCmd.Parameters.Clear();
}
}
");
return strbSaveAs.ToString();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
private string GetCopyToFunction(SqlField [] fields)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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++ )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
private string GetExtraFunctions()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strCode = @"
private static int GetValue(string strSql)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return -1;
}
return (int)obj;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public static int GetMaxID()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
string strSql = ""Select Max(ID) From [
{0}]"";
return GetValue(string.Format(strSql, m_TableName));
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public static bool IsValueExist(string strUnique, string strValue, int iExceptID)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
System.Data.SqlClient.SqlCommand SqlCmd;
SqlCmd = new System.Data.SqlClient.SqlCommand();
SqlCmd.Connection = GetSqlConnection();
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
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 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
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);
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public static bool IsValueExist(string strUnique, string strValue)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return IsValueExist(strUnique, strValue, -1);
}";
return strCode;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public string Convert()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return CreateClass().Replace("\r\n", "\n").Replace("\n", "\r\n").Trim();
}
}
}
#endregion
同时我也根据项目需要写了几个Sql2Aspx.cs和Sql2AspxCS.cs来生成表单代码,后面这两的通用性就更低了,不过都需要SqlField里的数据来支持转换生成。这些转换中也没有使用CodeDom,而完全使用字符串拼接,甚至直接一段一段的代码写里面,这样做就是为了需要有更改的时候方便,写成CodeDom方式那就完蛋了,时间长了要看半天才能找到要在什么地方做改动。
关于这种Mapping的好处就是自己对数据库访问代码的可控性很强,有问题可以根据自己的需求马上改,而不用去适应一些并不在目前情况下使用的东西,效率也可以做到最好,同时由于代码是自动生成,也不会有太多的重复劳动。操作数据库条目也巨方便,IDE里能做到Sql Field的IntelliSence。
AutoCode下载,除了它的SQL转SqlField数组的功能外,其它代码都是为了一次项目而构建的,不必关注。