在我們寫程序的時候，特別是數(shù)據(jù)庫應(yīng)用程序的時候，經(jīng)常會遇到這樣的情況：對于一個給定的表，寫出這個表對應(yīng)的類（用一句時髦的話說是實現(xiàn)業(yè)務(wù)實體類），類的數(shù)據(jù)成員是所有的字段，并且類含有該表的添加修改刪除等操作。還有，對于一個給定的存儲過程，要完成根據(jù)存儲過程存取數(shù)據(jù)或別的數(shù)據(jù)庫操作。如下代碼就是我們通常要完成的：
1.表的業(yè)務(wù)實體化
private int iId ;
public int Id
{
get
{
return iId ;
}
set
{
iId = value ;
}
}


private string strName ;
public string Name
{
get
{
return strName ;
}
set
{
strName = value ;
}
}


private string strCode ;
public string Code
{
get
{
return strCode ;
}
set
{
strCode = value ;
}
}


private string strDescription ;
public string Description
{
get
{
return strDescription ;
}
set
{
strDescription = value ;
}
}


private int iFatherid ;
public int Fatherid
{
get
{
return iFatherid ;
}
set
{
iFatherid = value ;
}
}


private int iType ;
public int Type
{
get
{
return iType ;
}
set
{
iType = value ;
}
}


private int iUserId ;
public int UserId
{
get
{
return iUserId ;
}
set
{
iUserId = value ;
}
}


ublic bool Add()
{
SqlConnection conn = SqlConn.Instance().Connection ;

string strSql = "insert into book(id, Name, Code, Description, Fatherid, Type, UserId)"
+"values(@id, @Name, @Code, @Description, @Fatherid, @Type, @UserId)" ;

SqlCommand command = new SqlCommand(strSql,conn) ;

command.Parameters.Add("@id",SqlDbType.Int ) ;
command.Parameters["@id"].value = iId ;

command.Parameters.Add("@Name",SqlDbType.NVarChar ,50) ;
if (strName!= null )
command.Parameters["@Name"].value = strName ;
else
command.Parameters["@Name"].value = DBNull.value ;

command.Parameters.Add("@Code",SqlDbType.NVarChar ,255) ;
if (strCode!= null )
command.Parameters["@Code"].value = strCode ;
else
command.Parameters["@Code"].value = DBNull.value ;

command.Parameters.Add("@Description",SqlDbType.NVarChar ,255) ;
if (strDescription!= null )
command.Parameters["@Description"].value = strDescription ;
else
command.Parameters["@Description"].value = DBNull.value ;

command.Parameters.Add("@Fatherid",SqlDbType.Int ) ;
command.Parameters["@Fatherid"].value = iFatherid ;

command.Parameters.Add("@Type",SqlDbType.Int ) ;
command.Parameters["@Type"].value = iType ;

command.Parameters.Add("@UserId",SqlDbType.Int ) ;
command.Parameters["@UserId"].value = iUserId ;

try
{
conn.Open() ;
command.ExecuteNonQuery() ;
return true ;
}
catch(Exception e)
{
throw(new Exception("Error in the Database"+e.Message)) ;
}
finally
{
conn.Close() ;
}
}
public bool Modify()
{
SqlConnection conn = SqlConn.Instance().Connection ;
string strSql ="update book set id = @id, Name = @Name, Code = @Code, Description = @Description, Fatherid = @Fatherid, Type = @Type, UserId = @UserId "
+ " where id =@id " ;
SqlCommand command = new SqlCommand(strSql,conn) ;
command.Parameters.Add("@id",SqlDbType.Int ) ;
command.Parameters["@id"].value = iId ;

command.Parameters.Add("@Name",SqlDbType.NVarChar ,50) ;
command.Parameters["@Name"].value = strName ;

command.Parameters.Add("@Code",SqlDbType.NVarChar ,255) ;
command.Parameters["@Code"].value = strCode ;

command.Parameters.Add("@Description",SqlDbType.NVarChar ,255) ;
command.Parameters["@Description"].value = strDescription ;

command.Parameters.Add("@Fatherid",SqlDbType.Int ) ;
command.Parameters["@Fatherid"].value = iFatherid ;

command.Parameters.Add("@Type",SqlDbType.Int ) ;
command.Parameters["@Type"].value = iType ;

command.Parameters.Add("@UserId",SqlDbType.Int ) ;
command.Parameters["@UserId"].value = iUserId ;

try
{
conn.Open() ;
command.ExecuteNonQuery() ;
return true ;
}
catch(Exception e)
{
throw(new Exception("Error in the Database"+e.Message)) ;
}
finally
{
conn.Close() ;
}
}

再看一下存儲過程：
public bool ExeSP_ddms_Modify_Trx(
int aiPrsn_trx_no,
int aiUlt_incid_no,
int aiPrsn_trx_status_cd,
DateTime adtTrx_cmpl_dt,
string astrEmail_addr)
{
SqlConnection conn = SqlConn.Instance().Connection ;

string strSPName = "ddms_Modify_Trx" ;
SqlCommand command = new SqlCommand(strSPName,conn) ;
command.CommandType = CommandType.StoredProcedure ;

command.Parameters.Add("@prsn_trx_no",SqlDbType.SmallInt ) ;
command.Parameters["@prsn_trx_no"].value= aiPrsn_trx_no ;

command.Parameters.Add("@ult_incid_no",SqlDbType.Int ) ;
command.Parameters["@ult_incid_no"].value= aiUlt_incid_no ;

command.Parameters.Add("@prsn_trx_status_cd",SqlDbType.Int ) ;
command.Parameters["@prsn_trx_status_cd"].value= aiPrsn_trx_status_cd ;

command.Parameters.Add("@trx_cmpl_dt",SqlDbType.DateTime ) ;
if ( adtTrx_cmpl_dt!= DateTime.Minvalue )
command.Parameters["@trx_cmpl_dt"].value= adtTrx_cmpl_dt ;
else
command.Parameters["@trx_cmpl_dt"].value= DBNull.value ;

command.Parameters.Add("@email_addr",SqlDbType.VarChar ,70) ;
if ( astrEmail_addr!= null )
command.Parameters["@email_addr"].value= astrEmail_addr ;
else
command.Parameters["@email_addr"].value= DBNull.value ;

try
{
conn.Open() ;
command.ExecuteNonQuery() ;
return true ;
}
catch(Exception e)
{
throw(new Exception("Error in the Database"+e.Message)) ;
}
finally
{
conn.Close() ;
}
}

上面處理表的代碼也挺長的，但那只是7個字段，如果一個表含有二三十個字段的話，恐怕代碼更長，同樣，檢查你寫好的代碼是否有錯誤也是一個痛苦的過程。
但是，寫這些代碼本身并沒有多少難度，而且很多工作都是重復(fù)的，這就不能不啟發(fā)我們通過一個程序來完成這些繁瑣易出錯的工作。
假如讓我們來設(shè)計一個程序完成上面的代碼，我們應(yīng)該怎么做哪？

要完成上面的工作，我們首先應(yīng)該找到我們要處理的表或存儲過程。
我們也可以通過編程實現(xiàn)：
1.列出所有的數(shù)據(jù)庫服務(wù)器
public static ArrayList GetServerList()
{
ArrayList alServers = new ArrayList() ;
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass() ;
try
{
SQLDMO.NameList serverList = sqlApp.ListAvailableSQLServers() ;
for(int i = 1;i<= serverList.Count;i++)
{
alServers.Add(serverList.Item(i)) ;
}
}
catch(Exception e)
{
throw(new Exception("取數(shù)據(jù)庫服務(wù)器列表出錯："+e.Message)) ;
}
finally
{
sqlApp.Quit() ;
}
return alServers ;
}
這樣，所有的數(shù)據(jù)庫服務(wù)器迷宮凝成都存在了ArrayList里，我們可以用一個下拉列表來列出所有的服務(wù)器，讓用戶選擇他需要的一個。
2。得到選定服務(wù)器所有的數(shù)據(jù)庫列表
要完成這個工作，我們需要用戶輸入這個數(shù)據(jù)庫服務(wù)器的用戶名和密碼，代碼如下：
public static ArrayList GetDbList(string strServerName,string strUserName,string strPwd)
{
ArrayList alDbs = new ArrayList() ;
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass() ;
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass() ;
try
{
svr.Connect(strServerName,strUserName,strPwd) ;
foreach(SQLDMO.Database db in svr.Databases)
{
if(db.Name!=null)
alDbs.Add(db.Name) ;
}
}
catch(Exception e)
{
throw(new Exception("連接數(shù)據(jù)庫出錯："+e.Message)) ;
}
finally
{
svr.DisConnect() ;
sqlApp.Quit() ;
}
return alDbs ;
}
這樣就得到了數(shù)據(jù)庫的列表，你同樣可以讓用戶選擇一個數(shù)據(jù)庫。
3。列出選定數(shù)據(jù)庫所有的用戶表和存儲過程
在這一步我們不用SQLDMO了，我們通過讀取選定數(shù)據(jù)庫的sysobjects表中的記錄實現(xiàn),當(dāng)然，完成這個工作需要如下信息：你選擇的數(shù)據(jù)庫服務(wù)器名，用戶名，密碼，數(shù)據(jù)庫名等信息：
public static ArrayList GetTableList(string strServerName,string strDBName,string strUserName,string strPwd)
{
string strConStr = " data source="+ strServerName +";initial catalog="+strDBName +
";password="+ strPwd+";persist security info=True;user id="+ strUserName;
SqlConnection conn = new SqlConnection(strConStr) ;

ArrayList alTbs = new ArrayList() ;

string strSql = "select * from sysobjects where (xtype='u' or xtype='p') and category<>2 order by name" ;
SqlCommand comm = new SqlCommand(strSql,conn) ;

SqlDataReader sr = null ;
try
{
conn.Open() ;
sr = comm.ExecuteReader() ;
while(sr.Read())
{
string strName = sr["name"].ToString() ;
bool blTable = sr["xtype"].ToString().Trim().ToUpper() == "U"?true:false ;
TableInfo tb = new TableInfo(strName,blTable) ;
alTbs.Add(tb) ;
}

}
catch(Exception err)
{
throw(new Exception("取表明列表出錯："+err.Message)) ;
}
finally
{
if (sr != null)
{
sr.Close() ;
sr = null ;
}
conn.Close() ;
}
return alTbs ;
}
這樣我們就得到了所有的用戶表和存儲過程，注意這一句：
select * from sysobjects where (xtype='u' or xtype='p') and category<>2 order by name，這是關(guān)鍵的sql語句，其中xtype='u' 表示用戶表，xtype='p'表示存儲過程，category<>2表示不是系統(tǒng)存儲過程。


下一步是取得選定表的所有字段或存儲過程的所有參數(shù)
我們從表syscolumns取得我們想要到的東西：
strTableName = tbInfo.Name ;
string strSql = "select * from syscolumns where id=( " +
" select id from sysobjects where name='"+ strTableName + "')" ;

SqlDataAdapter sa = new SqlDataAdapter(strSql,conn) ;
DataSet ds = new DataSet() ;
sa.Fill(ds) ;
這樣我們就把某個表（或存儲過程）的所有字段信息放到了結(jié)果集ds里。
在syscolumns 里有一個字段xtype，該字段表示列的數(shù)據(jù)類型，俺定義了兩個函數(shù)，能根據(jù)xtype的值得到對應(yīng)的dotnet中的數(shù)據(jù)類型和sqldb數(shù)據(jù)類型：
private string getType(int iType)
{
string strResult = "" ;
switch(iType)
{
case 34:
strResult = "Byte[]" ;
break ;
case 35:
case 99:
case 167:
case 175:
case 231:
case 239:
strResult = "string" ;
break ;
case 48:
case 52:
case 56:
case 127:
strResult = "int" ;
break ;
case 58:
case 61:
strResult = "DateTime" ;
break ;
case 59:
strResult = "Single" ;
break ;
case 60:
case 106:
case 108:
case 122:
strResult = "Decimal" ;
break ;
case 62:
strResult = "Double" ;
break ;
case 104:
strResult = "bool" ;
break ;
default:
strResult = "None" ;//"unknow" ;
break ;
}
return strResult + " " ;
}

private string getSqlDBType(int iType)
{
string strResult = "" ;
switch(iType)
{
case 34:
strResult = "SqlDbType.Image" ;
break ;
case 35:
strResult = "SqlDbType.Text" ;
break ;
case 48:
strResult = "SqlDbType.TinyInt" ;
break ;
case 52:
strResult = "SqlDbType.SmallInt" ;
break ;
case 56:
strResult = "SqlDbType.Int" ;
break ;
case 58:
strResult = "SqlDbType.SmallDateTime" ;
break ;
case 59:
strResult = "SqlDbType.Real" ;
break ;
case 60:
strResult = "SqlDbType.Money" ;
break ;
case 61:
strResult = "SqlDbType.DateTime" ;
break ;
case 62:
strResult = "SqlDbType.Float" ;
break ;
case 99:
strResult = "SqlDbType.NText" ;
break ;
case 104:
strResult = "SqlDbType.Bit" ;
break ;
case 106:
strResult = "SqlDbType.Decimal" ;
break ;
case 108:
strResult = "SqlDbType.Decimal" ;
break ;
case 122:
strResult = "SqlDbType.SmallMoney" ;
break ;
case 127:
strResult = "SqlDbType.BigInt" ;
break ;
case 165:
strResult = "SqlDbType.VarBinary" ;
break ;
case 167:
strResult = "SqlDbType.VarChar" ;
break ;
case 173:
strResult = "SqlDbType.Binary" ;
break ;
case 175:
strResult = "SqlDbType.Char" ;
break ;
case 189:
strResult = "SqlDbType.Timestamp" ;
break ;
case 231:
strResult = "SqlDbType.NVarChar" ;
break ;
case 239:
strResult = "SqlDbType.NChar" ;
break ;
default:
strResult = "None" ;//"unknow" ;
break ;
}
return strResult + " " ;
}
當(dāng)然，還有一個函數(shù)先介紹一下，后面有它的具體用法：
private string getTypeShort(int iType)
{
string strResult = "" ;
switch(getType(iType).Trim())
{
case "Byte[]":
strResult = "img" ;
break ;
case "string":
strResult = "str" ;
break ;
case "int":
strResult = "i" ;
break ;
case "DateTime":
strResult = "dt" ;
break ;
case "Single":
strResult = "sig" ;
break ;
case "Decimal":
strResult = "dec" ;
break ;
case "Double":
strResult = "db" ;
break ;
case "bool":
strResult = "bl" ;
break ;
default:
strResult = "unknow" ;
break ;
}
return strResult ;
}

現(xiàn)在有了上面的介紹和函數(shù)基礎(chǔ)，我們可以逐步實現(xiàn)我們的目標(biāo)了：
1.生成數(shù)據(jù)實體類的數(shù)據(jù)成員
大家看一下，對于一個字段id，整形，我們這樣定義：
private int iId ;
public int Id
{
get
{
return iId ;
}
set
{
iId = value ;
}
}
其中iId表示數(shù)據(jù)類型+字段名稱，其中字段名稱的第一個字母大寫，代碼實現(xiàn)如下：
private string BuildMember(DataSet ds)
{
StringBuilder sb = new StringBuilder() ;
foreach(DataRow dr in ds.Tables[0].Rows)
{
int iType = (byte)dr["xtype"] ;
string strType = getType(iType) ;
string strName = dr["name"].ToString() ;
strName = UpFirstChar(strName) ;
string strShortType = getTypeShort(iType) ;
string strPrivateline = "private " + strType + strShortType +strName +" ;\r\n" ;
sb.Append(strPrivateline) ;
string strPublicline = "public " + strType + strName +" \r\n" ;
sb.Append(strPublicline) ;
sb.Append("{\r\n") ;
sb.Append("\tget\r\n") ;
sb.Append("\t{\r\n") ;
sb.Append("\t\treturn " +strShortType +strName+ " ;\r\n") ;
sb.Append("\t}\r\n") ;
sb.Append("\tset\r\n") ;
sb.Append("\t{\r\n") ;
sb.Append("\t\t" +strShortType +strName+ " = value ;\r\n") ;
sb.Append("\t}\r\n") ;
sb.Append("}\r\n") ;
sb.Append("\r\n") ;
sb.Append("\r\n") ;
}
return sb.ToString();
}

參數(shù)DataSet ds中的ds是通過上一頁的sa.Fill(ds)得到的，dr["name"]表示字段名稱，dr["xtype"] 是字段類型。
UpFirstChar函數(shù)負(fù)責(zé)把字符串的第一個字母大寫：
private string UpFirstChar(string strvalue)
{
return strvalue.Substring(0,1).ToUpper() + strvalue.Substring(1,strvalue.Length - 1) ;
}
這樣就生成了數(shù)據(jù)實體的數(shù)據(jù)成員。
2。生成Add方法
Add方法生成起來有點難度，可以參考第一頁的程序，我們這里是通過參數(shù)的方法來實現(xiàn)add的。有一點要注意，就是在對參數(shù)賦值時要考慮參數(shù)值為空的情況。代碼實現(xiàn)如下：
private string BuildAddFunction(DataSet ds)
{
StringBuilder sb = new StringBuilder() ;

sb.Append("public bool Add()\r\n") ;
sb.Append("{\r\n") ;
sb.Append("\tSqlConnection conn = SqlConn.Instance().Connection ;\r\n") ;
sb.Append("\r\n") ;
string strInsert = "\"insert into " + strTableName + "(";
string strFiledList = "" ;
string strParamList = "" ;
foreach(DataRow dr in ds.Tables[0].Rows)
{
string strName = dr["name"].ToString() ;
strFiledList = strFiledList + strName + ", " ;
strParamList = strParamList + "@" + strName + ", " ;
}
strFiledList = strFiledList.Trim().TrimEnd(',') ;
strParamList = strParamList.Trim().TrimEnd(',') ;
sb.Append("\tstring strSql = " + strInsert + strFiledList+ ")\"\r\n") ;
sb.Append("\t+\"values(" + strParamList+ ")\" ;\r\n") ;
sb.Append("\r\n") ;
sb.Append("\tSqlCommand command = new SqlCommand(strSql,conn) ;\r\n") ;
sb.Append("\r\n") ;
foreach(DataRow dr in ds.Tables[0].Rows)
{
string strName = dr["name"].ToString() ;
string strSqlType = getSqlDBType((byte)dr["xtype"]) ;
string strShortType = getTypeShort((byte)dr["xtype"]) ;
string strvalueName = strShortType + UpFirstChar(strName) ;
int iLen = (Int16)dr["prec"] ;

string strLen = "" ;
if (strShortType == "str")
strLen = strLen + "," + iLen ;
string strCommandName = "\tcommand.Parameters.Add(\"@" + strName + "\"," + strSqlType + strLen+ ") ;\r\n" ;

string strCommandvalue = "\tcommand.Parameters[\"@" + strName + "\"].value = " + strvalueName + " ;\r\n" ;
string strCommandNullvalue = "\tcommand.Parameters[\"@" + strName + "\"].value = DBNull.value ;\r\n" ;

sb.Append(strCommandName) ;
if (strShortType == "dt")
sb.Append("\tif (" + strvalueName + "!= DateTime.Minvalue )\r\n") ;
else if (strShortType == "str")
sb.Append("\tif (" + strvalueName + "!= null )\r\n") ;
else if (strShortType == "img")
sb.Append("\tif (" + strvalueName + "!= null )\r\n") ;
else
sb.Append("") ;
sb.Append(strCommandvalue) ;
if (strShortType == "dt" strShortType == "str" strShortType == "img")
{
sb.Append("\telse\r\n") ;
sb.Append(strCommandNullvalue) ;
}
sb.Append("\r\n") ;
}

sb.Append(AddCatchString()) ;

sb.Append("}\r\n") ;
return sb.ToString();
}


3。edit，delete的實現(xiàn)方法類似與add，就不詳細(xì)說了，edit方法代碼如下：
private string BuildModifyFunction(DataSet ds)
{
StringBuilder sb = new StringBuilder() ;

sb.Append("public bool Modify()\r\n") ;
sb.Append("{\r\n") ;
sb.Append("\tSqlConnection conn = SqlConn.Instance().Connection ;\r\n") ;
string strModify = "string strSql =\"update " + strTableName + " set ";

string strParamList = "" ;
foreach(DataRow dr in ds.Tables[0].Rows)
{
string strName = dr["name"].ToString() ;
strParamList = strParamList + strName + " = @" + strName + ", " ;
}
strParamList = strParamList.Trim().TrimEnd(',') ;
sb.Append("\t" + strModify + strParamList + " \"\r\n") ;
sb.Append("\t+ \" where id =@id \" ;\r\n") ;

sb.Append("\tSqlCommand command = new SqlCommand(strSql,conn) ;\r\n") ;

foreach(DataRow dr in ds.Tables[0].Rows)
{
string strName = dr["name"].ToString() ;
string strSqlType = getSqlDBType((byte)dr["xtype"]) ;
string strShortType = getTypeShort((byte)dr["xtype"]) ;
string strvalueName = strShortType + UpFirstChar(strName) ;
int iLen = (Int16)dr["prec"] ;

string strLen = "" ;
if (strShortType == "str")
strLen = strLen + "," + iLen ;

string strCommandName = "\tcommand.Parameters.Add(\"@" + strName + "\"," + strSqlType + strLen + ") ;\r\n" ;
string strCommandvalue = "\tcommand.Parameters[\"@" + strName + "\"].value = " + strvalueName + " ;\r\n" ;
sb.Append(strCommandName) ;
sb.Append(strCommandvalue) ;
sb.Append("\r\n") ;
}

sb.Append(AddCatchString()) ;

sb.Append("}\r\n") ;
return sb.ToString();
}

4。生成存儲過程的執(zhí)行方法和返回結(jié)果方法。存儲過程的參數(shù)也在syscolumns表里有詳細(xì)的說明，里面還記錄了每個參數(shù)是不是傳出參數(shù)，不過在這個函數(shù)里我沒有考慮參數(shù)的方向，當(dāng)然，要考慮進(jìn)去也不是很麻煩。
private string BuildSPExec(DataSet ds)
{
StringBuilder sb = new StringBuilder() ;
string strFuncParam = "" ;

foreach(DataRow dr in ds.Tables[0].Rows)
{
int iType = (byte)dr["xtype"] ;
string strName = UpFirstChar(dr["name"].ToString().TrimStart('@')) ;
string strType = getType(iType) ;
string strSqlType = getSqlDBType(iType) ;
string strShortType = getTypeShort(iType) ;
strFuncParam = strFuncParam + "\r\n\t\t" + strType + " a" + strShortType + strName + ",";
}
strFuncParam = strFuncParam.TrimEnd(',') ;
string strDef = "public bool ExeSP_" + strTableName + "(" + strFuncParam + ")\r\n";
sb.Append(strDef) ;
sb.Append("{\r\n") ;
sb.Append("\tSqlConnection conn = SqlConn.Instance().Connection ;\r\n") ;
sb.Append("\r\n") ;
sb.Append("\tstring strSPName = \"" + strTableName + "\" ;\r\n") ;
sb.Append("\tSqlCommand command = new SqlCommand(strSPName,conn) ;\r\n");
sb.Append("\tcommand.CommandType = CommandType.StoredProcedure ;\r\n") ;
sb.Append("\r\n") ;

foreach(DataRow dr in ds.Tables[0].Rows)
{
int iType = (byte)dr["xtype"] ;
string strParaName = dr["name"].ToString() ;
string strName = UpFirstChar(strParaName.TrimStart('@')) ;
string strSqlType = getSqlDBType(iType) ;
string strShortType = getTypeShort(iType) ;
string strvalue = " a" + strShortType + strName ;
int iLen = (Int16)dr["prec"] ;

string strLen = "" ;
if (strShortType == "str")
strLen = strLen + "," + iLen ;

string strCommandAdd = "\tcommand.Parameters.Add(\"" + strParaName + "\"," + strSqlType + strLen + ") ;\r\n";
string strCommandvalue = "\tcommand.Parameters[\"" + strParaName + "\"].value=" + strvalue + " ;\r\n";
string strCommandNull = "\tcommand.Parameters[\"" + strParaName + "\"].value= DBNull.value ;\r\n";
sb.Append(strCommandAdd) ;
if (strShortType == "dt")
sb.Append("\tif (" + strvalue + "!= DateTime.Minvalue )\r\n\t") ;
else if (strShortType == "str")
sb.Append("\tif (" + strvalue + "!= null )\r\n\t") ;
else if (strShortType == "img")
sb.Append("\tif (" + strvalue + "!= null )\r\n\t") ;
else
sb.Append("") ;
sb.Append(strCommandvalue) ;
if (strShortType == "dt" strShortType == "str" strShortType == "img")
{
sb.Append("\telse\r\n\t") ;
sb.Append(strCommandNull) ;
}
sb.Append("\r\n") ;
}
sb.Append(AddCatchString()) ;

sb.Append("}\r\n") ;
return sb.ToString() ;
}

對于存儲過程生成結(jié)果集的函數(shù)如下：
private string BuildSPGetData(DataSet ds)
{
StringBuilder sb = new StringBuilder() ;
string strFuncParam = "" ;

foreach(DataRow dr in ds.Tables[0].Rows)
{
int iType = (byte)dr["xtype"] ;
string strName = UpFirstChar(dr["name"].ToString().TrimStart('@')) ;
string strType = getType(iType) ;
string strSqlType = getSqlDBType(iType) ;
string strShortType = getTypeShort(iType) ;
strFuncParam = strFuncParam + "\r\n\t\t" + strType + " a" + strShortType + strName + ",";
}
strFuncParam = strFuncParam.TrimEnd(',') ;
string strDef = "public DataSet QuerySP_" + strTableName + "(" + strFuncParam + ")\r\n";
sb.Append(strDef) ;
sb.Append("{\r\n") ;
sb.Append("\tSqlConnection conn = SqlConn.Instance().Connection ;\r\n") ;
sb.Append("\r\n") ;
sb.Append("\tstring strSPName = \"" + strTableName + "\" ;\r\n") ;
sb.Append("\tSqlCommand command = new SqlCommand(strSPName,conn) ;\r\n");
sb.Append("\tcommand.CommandType = CommandType.StoredProcedure ;\r\n") ;
sb.Append("\r\n") ;

foreach(DataRow dr in ds.Tables[0].Rows)
{
int iType = (byte)dr["xtype"] ;
string strParaName = dr["name"].ToString() ;
string strName = UpFirstChar(strParaName.TrimStart('@')) ;
string strSqlType = getSqlDBType(iType) ;
string strShortType = getTypeShort(iType) ;
string strvalue = " a" + strShortType + strName ;
int iLen = (Int16)dr["prec"] ;

string strLen = "" ;
if (strShortType == "str")
strLen = strLen + "," + iLen ;

string strCommandAdd = "\tcommand.Parameters.Add(\"" + strParaName + "\"," + strSqlType + strLen + ") ;\r\n";
string strCommandvalue = "\tcommand.Parameters[\"" + strParaName + "\"].value=" + strvalue + " ;\r\n";
string strCommandNull = "\tcommand.Parameters[\"" + strParaName + "\"].value= DBNull.value ;\r\n";
sb.Append(strCommandAdd) ;
if (strShortType == "dt")
sb.Append("\tif (" + strvalue + "!= DateTime.Minvalue )\r\n\t") ;
else if (strShortType == "str")
sb.Append("\tif (" + strvalue + "!= null )\r\n\t") ;
else if (strShortType == "img")
sb.Append("\tif (" + strvalue + "!= null )\r\n\t") ;
else
sb.Append("") ;
sb.Append(strCommandvalue) ;
if (strShortType == "dt" strShortType == "str" strShortType == "img")
{
sb.Append("\telse\r\n\t") ;
sb.Append(strCommandNull) ;
}
sb.Append("\r\n") ;
}

sb.Append("\tSqlDataAdapter sdaResult = new SqlDataAdapter(command) ;\r\n") ;
sb.Append("\tDataSet ds = new DataSet() ;\r\n") ;


sb.Append(AddCatchQueryString()) ;

sb.Append("}\r\n") ;
return sb.ToString() ;

}
下面是生成結(jié)果集的執(zhí)行結(jié)果：
public DataSet QuerySP_ddms_GetBank(
int aiPrsn_id)
{
SqlConnection conn = SqlConn.Instance().Connection ;

string strSPName = "ddms_GetBank" ;
SqlCommand command = new SqlCommand(strSPName,conn) ;
command.CommandType = CommandType.StoredProcedure ;

command.Parameters.Add("@prsn_id",SqlDbType.Int ) ;
command.Parameters["@prsn_id"].value= aiPrsn_id ;

SqlDataAdapter sdaResult = new SqlDataAdapter(command) ;
DataSet ds = new DataSet() ;
try
{
sdaResult.Fill(ds) ;
}
catch(Exception e)
{
throw(new Exception("Error in the Database"+e.Message)) ;
}
finally
{
sdaResult.Dispose() ;
}
return ds ;
}

還有兩個函數(shù)在程序中用到了，如下所示：
private string AddCatchString()
{
StringBuilder sb = new StringBuilder() ;
sb.Append("\ttry\r\n") ;
sb.Append("\t{\r\n") ;
sb.Append("\t\tconn.Open() ;\r\n") ;
sb.Append("\t\tcommand.ExecuteNonQuery() ;\r\n") ;
sb.Append("\t\treturn true ;\r\n") ;
sb.Append("\t}\r\n") ;
sb.Append("\tcatch(Exception e)\r\n") ;
sb.Append("\t{\r\n") ;
sb.Append("\t\tthrow(new Exception(\"Error in the Database\"+e.Message)) ;\r\n") ;
sb.Append("\t}\r\n") ;
sb.Append("\tfinally\r\n") ;
sb.Append("\t{\r\n") ;
sb.Append("\t\tconn.Close() ;\r\n") ;
sb.Append("\t}\r\n") ;
return sb.ToString() ;
}

private string AddCatchQueryString()
{
StringBuilder sb = new StringBuilder() ;
sb.Append("\ttry\r\n") ;
sb.Append("\t{\r\n") ;
sb.Append("\t\tsdaResult.Fill(ds) ;\r\n") ;
sb.Append("\t}\r\n") ;
sb.Append("\tcatch(Exception e)\r\n") ;
sb.Append("\t{\r\n") ;
sb.Append("\t\tthrow(new Exception(\"Error in the Database\"+e.Message)) ;\r\n") ;
sb.Append("\t}\r\n") ;
sb.Append("\tfinally\r\n") ;
sb.Append("\t{\r\n") ;
sb.Append("\t\tsdaResult.Dispose() ;\r\n") ;
sb.Append("\t}\r\n") ;
sb.Append("\treturn ds ;\r\n") ;
return sb.ToString() ;
}

順便說一下，文章開頭的一段代碼就是用這個程序生成的，當(dāng)然，這種方法還有待完善的地方，希望大家指正。