笔者近期在Vs2005平台和SqlServer2005平台之上研发了一个C#代码生成器,支持数据库表和C#数据对象的直接映射,以及CRUD操作代码的自动生成,用户只需要实现IDAL接口即可,该工具能够显著提高开发速度,让开发人员从繁重的数据库表对象封装编码中解脱出来(工具下载地址:http://itabby.com/index-5.asp),现特将核心处理代码进行发表,仅供参考:
///数据对象提取部分数据库类型和C#对象类型之间的转换过程
private string ConvertSqlDataReader(string sqlReader,string type, int i)
{
switch (type.Trim().ToLower())
{
case "bigint": return sqlReader + ".GetInt64(" + i.ToString() + ")";
///case "binary": return sqlReader + ".GetValue(" + i.ToString() + ")";
case "bit": return sqlReader + ".GetBoolean(" + i.ToString() + ")";
case "char": return sqlReader + ".GetString(" + i.ToString() + ")";
case "datetime": return sqlReader + ".GetDateTime(" + i.ToString() + ")";
case "decimal": return sqlReader + ".GetDecimal(" + i.ToString() + ")";
case "float": return sqlReader + ".GetFloat(" + i.ToString() + ")";
///case "image": return sqlReader + ".GetValue(" + i.ToString() + ")";
case "int": return sqlReader + ".GetInt32(" + i.ToString() + ")";
case "money": return sqlReader + ".GetDecimal(" + i.ToString() + ")";
case "nchar": return sqlReader + ".GetString(" + i.ToString() + ")";
case "ntext": return sqlReader + ".GetString(" + i.ToString() + ")";
case "nvarchar": return sqlReader + ".GetString(" + i.ToString() + ")";
case "real": return sqlReader + ".GetFloat(" + i.ToString() + ")";
case "smalldatetime": return sqlReader + ".GetDateTime(" + i.ToString() + ")";
case "smallint": return sqlReader + ".GetInt16(" + i.ToString() + ")";
case "smallmoney": return sqlReader + ".GetDecimal(" + i.ToString() + ")";
case "text": return sqlReader + ".GetString(" + i.ToString() + ")";
case "timestamp": return sqlReader + ".GetDateTime(" + i.ToString() + ")";
case "tinyint": return sqlReader + ".GetByte(" + i.ToString() + ")";
case "udt": return sqlReader + ".GetValue(" + i.ToString() + ")";
case "uniqueidentifier": return sqlReader + ".GetGuid(" + i.ToString() + ")";
///case "varbinary": return sqlReader + ".GetValue(" + i.ToString() + ")";
case "varchar": return sqlReader + ".GetString(" + i.ToString() + ")";
case "variant": return sqlReader + ".GetValue(" + i.ToString() + ")";
case "xml": return sqlReader + ".GetString(" + i.ToString() + ")";
case "numeric": return sqlReader + ".GetDecimal(" + i.ToString() + ")";
default: throw new ArgumentException(type + " not in Itabby.Product.SqlCode.SqlCode.ConvertGetMethod process list.");
}
}
///数据库类型完全限定名称还原
private string ConvertSqlDbType(string type)
{
switch (type.Trim().ToLower())
{
case "bigint": return "System.Data.SqlDbType.BigInt";
case "binary": return "System.Data.SqlDbType.Binary";
case "bit": return "System.Data.SqlDbType.Bit";
case "char": return "System.Data.SqlDbType.Char";
case "datetime": return "System.Data.SqlDbType.DateTime";
case "decimal": return "System.Data.SqlDbType.Decimal";
case "float": return "System.Data.SqlDbType.Float";
case "image": return "System.Data.SqlDbType.Image";
case "int": return "System.Data.SqlDbType.Int";
case "money": return "System.Data.SqlDbType.Money";
case "nchar": return "System.Data.SqlDbType.NChar";
case "ntext": return "System.Data.SqlDbType.NText";
case "nvarchar": return "System.Data.SqlDbType.NVarChar";
case "real": return "System.Data.SqlDbType.Real";
case "smalldatetime": return "System.Data.SqlDbType.SmallDateTime";
case "smallint": return "System.Data.SqlDbType.SmallInt";
case "smallmoney": return "System.Data.SqlDbType.SmallMoney";
case "text": return "System.Data.SqlDbType.Text";
case "timestamp": return "System.Data.SqlDbType.Timestamp";
case "tinyint": return "System.Data.SqlDbType.TinyInt";
case "udt": return "System.Data.SqlDbType.Udt";
case "uniqueidentifier": return "System.Data.SqlDbType.UniqueIdentifier";
case "varbinary": return "System.Data.SqlDbType.VarBinary";
case "varchar": return "System.Data.SqlDbType.VarChar";
case "variant": return "System.Data.SqlDbType.Variant";
case "xml": return "System.Data.SqlDbType.Xml";
case "numeric": return "System.Data.SqlDbType.Decimal";
default: throw new ArgumentException(type + " not in Itabby.Product.SqlCode.SqlCode.ConvertSqlDbType process list.");
}
}
///加载数据库表对象
public ArrayList LoadTables(string sConnectionString)
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConvertConnectionString(sConnectionString)))
{
_sTables.Clear();
conn.Open();
System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' Order by TABLE_NAME";
System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
_sTables.Add(dr.GetString(0));
}
dr.Close();
conn.Close();
}
return _sTables;
}
///加载数据库列对象
public ArrayList LoadColumns(string sConnectionString,string sTable)
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConvertConnectionString(sConnectionString)))
{
_sColumns.Clear();
conn.Open();
System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
///get pk in current table
cmd.CommandText = "SELECT Column_Name FROM INFORMATION_SCHEMA .KEY_COLUMN_USAGE where Table_Name = '" + sTable + "' and constraint_name in(select name from sys.objects where parent_object_id = ( select OBJECT_ID('" + sTable + "')) and type = 'pk')";
System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
ArrayList pks = new ArrayList();
while (dr.Read())
{
pks.Add(dr.GetString(0));
}
dr.Close();
///get columns in current table
cmd.CommandText = "SELECT COLUMN_NAME,Data_Type,Character_maximum_length,numeric_precision,numeric_scale,COLUMNPROPERTY( OBJECT_ID('" + sTable + "'),COLUMN_NAME,'IsIdentity') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + sTable + "' Order by COLUMN_NAME";
dr = cmd.ExecuteReader();
bool pk = false;
while (dr.Read())
{
///pk
foreach (string s in pks)
{
if (s.Equals(dr.GetString(0)))
{
pk = true;
}
}
_sColumns.Add(dr.GetString(0) + ";" + dr.GetString(1) + ";" + Convert.ToString(dr.IsDBNull(2) ? string.Empty : dr.GetValue(2).ToString()) + ";" + Convert.ToString(dr.IsDBNull(3) ? string.Empty : dr.GetValue(3).ToString()) + ";" + Convert.ToString(dr.IsDBNull(4) ? string.Empty : dr.GetValue(4).ToString()) + ";" + pk.ToString() + ";" + dr.GetValue(5).Equals(1).ToString());
pk = false;
}
dr.Close();
conn.Close();
}
return _sColumns;
}
///加载一个数据对象
public string GetObjectCode(string sConnectionString, string sTable, ArrayList columns, bool[] operations)
{
System.Text.StringBuilder sObjectCode = new StringBuilder();
sObjectCode.Append(USING_DECLARE + "\r\n" + NAMESPACE_DECLARE + "\r\n" + NAMESPACE_BEGIN + "\r\n");
sObjectCode.Append("\t" + CLASS_DECLARE + sTable + "\r\n\t" + CLASS_BODY_BEGIN + "\r\n");
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConvertConnectionString(sConnectionString)))
{
System.Data.SqlClient.SqlDataAdapter adpt = new System.Data.SqlClient.SqlDataAdapter("select top 1 * from " + sTable,conn);
DataTable cols = new DataTable();
adpt.Fill(cols);
sObjectCode.Append("\t\t" + REGION_BEGIN + "Attribute Block" + "\r\n");
if (operations[0])
{
foreach (System.Data.DataColumn column in cols.Columns)
{
///private var;
sObjectCode.Append("\t\t" + column.DataType.FullName + " _" + column.ColumnName + ";" + "\r\n");
///attribute
sObjectCode.Append("\t\tpublic " + column.DataType.FullName + " " + column.ColumnName.ToUpper() + ATTRIBUTE_BEGIN);
///get
sObjectCode.Append(GET_ATTRIBUTE_BEGIN + " return " + " _" + column.ColumnName + ";");
sObjectCode.Append(GET_ATTRIBUTE_END);
///set
sObjectCode.Append(SET_ATTRIBUTE_BEGIN + " _" + column.ColumnName + " = value;");
sObjectCode.Append(SET_ATTRIBUTE_END);
sObjectCode.Append(ATTRIBUTE_END + "\r\n");
}
}
sObjectCode.Append("\t\t" + REGION_END);
sObjectCode.Append("\r\n\t\t" + REGION_BEGIN + "Method Blok");
///Add operation
if (operations[1]) sObjectCode.Append(GetAddCode("DataObject_" + sTable, columns));
///Update operation
if (operations[2]) sObjectCode.Append(GetUpdateCode("DataObject_" + sTable, columns));
///Delete operation
if (operations[3]) sObjectCode.Append(GetDeleteCode("DataObject_" + sTable, columns));
///Select operation
if (operations[4]) sObjectCode.Append(GetGetCode("DataObject_" + sTable, columns));
///List operation
if (operations[5]) sObjectCode.Append(GetListCode("DataObject_" + sTable, columns));
sObjectCode.Append("\r\n\t\t" + REGION_END);
}
sObjectCode.Append("\r\n\t" + CLASS_BODY_END);
sObjectCode.Append("\r\n" + NAMESPACE_END);
return sObjectCode.ToString();
}
///生成Add方法代码
private string GetAddCode(string sObject,ArrayList columns)
{
System.Text.StringBuilder sObjectCode = new StringBuilder();
sObjectCode.Append("\r\n\t\tpublic static int Add(" + sObject + " obj,IDAL idal,SqlTransaction trans)");
sObjectCode.Append("\r\n\t\t{");
sObjectCode.Append("\r\n\t\t\tconst string sSql = \"insert into " + sObject.Substring(11));
sObjectCode.Append("(");
int iKey = 0;
foreach (string s in columns)
{
///All columns without identity columns
if (s.Split(';')[6].ToLower().Equals("true")) continue;
sObjectCode.Append(" " + s.Split(';')[0] + ",");
iKey += 1;
}
sObjectCode.Remove(sObjectCode.Length - 1, 1);
sObjectCode.Append(")");
sObjectCode.Append(" values ");
sObjectCode.Append("(");
foreach (string s in columns)
{
///All columns without identity columns
if (s.Split(';')[6].ToLower().Equals("true")) continue;
sObjectCode.Append(" @" + s.Split(';')[0] + ",");
}
sObjectCode.Remove(sObjectCode.Length - 1, 1);
sObjectCode.Append(")");
sObjectCode.Append(";\";\r\n");
sObjectCode.Append("\t\t\t" + REGION_BEGIN + "Parameters Block" + "\r\n");
sObjectCode.Append("\t\t\tSqlParameter[] parameters = new SqlParameter[" + iKey + "];\r\n");
iKey = 0;
for (int i = 0; i < columns.Count; i++)
{
///All columns without identity columns
if (columns[i].ToString().Split(';')[6].ToLower().Equals("true")) continue;
sObjectCode.Append("\t\t\tparameters[" + iKey + "] = new SqlParameter();\r\n");
sObjectCode.Append("\t\t\tparameters[" + iKey + "].ParameterName = \"@" + columns[i].ToString().Split(';')[0] + "\";\r\n");
if (columns[i].ToString().Split(';')[2].Equals(string.Empty))
{
if (!columns[i].ToString().Split(';')[3].Equals(string.Empty))
{
sObjectCode.Append("\t\t\tparameters[" + iKey + "].Size = " + columns[i].ToString().Split(';')[3] + ";\r\n");
}
}
else
{
sObjectCode.Append("\t\t\tparameters[" + iKey + "].Size = " + columns[i].ToString().Split(';')[2] + ";\r\n");
}
sObjectCode.Append("\t\t\tparameters[" + iKey + "].SqlDbType = " + ConvertSqlDbType(columns[i].ToString().Split(';')[1]) + ";\r\n");
sObjectCode.Append("\t\t\tparameters[" + iKey + "].Value = obj." + columns[i].ToString().Split(';')[0].ToUpper() + ";\r\n");
iKey += 1;
}
sObjectCode.Append("\t\t\t" + REGION_END + "\r\n");
sObjectCode.Append("\t\t\treturn idal.ExecuteNonQuery(sSql,parameters,trans);");
sObjectCode.Append("\r\n\t\t}");
return sObjectCode.ToString();
}
