dotnet代码自动生成机的实现（2）

在syscolumns 里有一个字段xtype，该字段表示列的数据类型，俺定义了两个函数，能根据xtype的值得到对应的dotnet中的数据类型和sqldb数据类型： 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 + " "; } 当然，还有一个函数先介绍一下，后面有它的具体用法： 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; } 现在有了上面的介绍和函数基础，我们可以逐步实现我们的目标了： 1.生成数据实体类的数据成员 大家看一下，对于一个字段id，整形，我们这样定义： private int iId; public int Id { get { return iId; } set { iId = value; } } 其中iId表示数据类型+字段名称，其中字段名称的第一个字母大写，代码实现如下： 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(); } 参数DataSet ds中的ds是通过上一页的sa.Fill(ds)得到的，dr["name"]表示字段名称，dr["xtype"] 是字段类型。 UpFirstChar函数负责把字符串的第一个字母大写： private string UpFirstChar(string strvalue) { return strvalue.Substring(0, 1).ToUpper() + strvalue.Substring(1, strvalue.Length - 1); } 这样就生成了数据实体的数据成员。 2。生成Add方法 Add方法生成起来有点难度，可以参考第一页的程序，我们这里是通过参数的方法来实现add的。有一点要注意，就是在对参数赋值时要考虑参数值为空的情况。代码实现如下： 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的实现方法类似与add，就不详细说了，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。生成存储过程的执行方法和返回结果方法。存储过程的参数也在syscolumns表里有详细的说明，里面还记录了每个参数是不是传出参数，不过在这个函数里我没有考虑参数的方向，当然，要考虑进去也不是很麻烦。 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(); } 对于存储过程生成结果集的函数如下： 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(); } 下面是生成结果集的执行结果： 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; } 还有两个函数在程序中用到了，如下所示： 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(); } 顺便说一下，文章开头的一段代码就是用这个程序生成的，当然，这种方法还有待完善的地方，希望大家指正。 原文连接：http://www.host01.com/article/Net/00020006/0561213181730059.htm 相关资源：敏捷开发V1.0.pptx