自動生成數據對象代碼和CRUD操作的C#代碼生成器

筆者近期在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();          
}