笔者近期在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();
}
新闻热点
疑难解答
