自動產生資料對象代碼和CRUD操作的C#代碼產生器

筆者近期在Vs2005平台和SqlServer2005平台之上研發了一個C#代碼產生器,支援資料庫表和C#資料對象的直接映射,以及CRUD作業碼的自動產生,使用者只需要實現IDAL介面即可,該工具能夠顯著提高開發速度,讓開發人員從繁重的資料庫表對象封裝編碼中解脫出來,現特將核心處理代碼進行發表,僅供參考:

///資料對象提取部分資料庫類型和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();          
}