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
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值