欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

asp.net下Oracle,SQL Server,Access万能数据库通用类

程序员文章站 2024-03-07 12:56:09
复制代码 代码如下: using system; using system.collections; using system.collections.specialize...
复制代码 代码如下:

using system;
using system.collections;
using system.collections.specialized;
using system.data;
using system.data.sqlclient;
using system.data.oledb;
using system.data.oracleclient;
using system.configuration;
using system.reflection;

namespace systemframework.dal
{
/**//// <summary>
/// all rights reserved
/// 数据访问基础类
/// 用户可以修改满足自己项目的需要。
/// </summary>
public class databaselayer
{
//数据库连接字符串(web.config来配置)
//<add key="connectionstring" value="server=127.0.0.1;database=database;uid=sa;pwd=" />
private string connectionstring;
public string conntionstring
{
get
{
return connectionstring ;
}
set
{
connectionstring = value;
}
}


public databaselayer(string strconnect,string datatype)
{
this.conntionstring = strconnect;
this.dbtype = datatype;
}


public databaselayer()
{
this.connectionstring = configurationsettings.appsettings["connectionstring"] ;
this.dbtype = configurationsettings.appsettings["datatype"] ;
}

/**//// <summary>
/// 数据库类型
/// </summary>
private string dbtype;
public string dbtype
{
get
{
if ( dbtype == string.empty || dbtype == null )
{
return "access";
}
else
{
return dbtype;
}
}
set
{
if ( value != string.empty && value != null )
{
dbtype = value;
}
if (dbtype ==string.empty || dbtype == null)
{
dbtype = configurationsettings.appsettings["datatype"];
}
if ( dbtype == string.empty || dbtype == null )
{
dbtype = "access";
}
}
}




转换参数#region 转换参数
private system.data.idbdataparameter idbpara(string paraname,string datatype)
{
switch(this.dbtype)
{
case "sqlserver":
return getsqlpara(paraname,datatype);

case "oracle":
return getoledbpara(paraname,datatype);

case "access":
return getoledbpara(paraname,datatype);

default :
return getsqlpara(paraname,datatype);

}
}

private system.data.sqlclient.sqlparameter getsqlpara( string paraname , string datatype)
{
switch(datatype)
{
case "decimal":
return new system.data.sqlclient.sqlparameter ( paraname, system.data.sqldbtype.decimal );
case "varchar":
return new system.data.sqlclient.sqlparameter ( paraname, system.data.sqldbtype.varchar );
case "datetime":
return new system.data.sqlclient.sqlparameter ( paraname, system.data.sqldbtype.datetime );
case "iamge":
return new system.data.sqlclient.sqlparameter ( paraname, system.data.sqldbtype.image );
case "int":
return new system.data.sqlclient.sqlparameter ( paraname, system.data.sqldbtype.int );
case "text":
return new system.data.sqlclient.sqlparameter ( paraname, system.data.sqldbtype.ntext );
default :
return new system.data.sqlclient.sqlparameter ( paraname, system.data.sqldbtype.varchar );
}
}

private system.data.oracleclient.oracleparameter getoraclepara( string paraname , string datatype)
{
switch(datatype)
{
case "decimal":
return new system.data.oracleclient.oracleparameter( paraname, system.data.oracleclient.oracletype.double);

case "varchar":
return new system.data.oracleclient.oracleparameter ( paraname, system.data.oracleclient.oracletype.varchar );

case "datetime":
return new system.data.oracleclient.oracleparameter ( paraname, system.data.oracleclient.oracletype.datetime );

case "iamge":
return new system.data.oracleclient.oracleparameter ( paraname, system.data.oracleclient.oracletype.bfile );

case "int":
return new system.data.oracleclient.oracleparameter ( paraname, system.data.oracleclient.oracletype.int32 );

case "text":
return new system.data.oracleclient.oracleparameter ( paraname, system.data.oracleclient.oracletype.longvarchar );

default:
return new system.data.oracleclient.oracleparameter ( paraname, system.data.oracleclient.oracletype.varchar );

}
}

private system.data.oledb.oledbparameter getoledbpara( string paraname , string datatype)
{
switch(datatype)
{
case "decimal":
return new system.data.oledb.oledbparameter( paraname, system.data.dbtype.decimal);

case "varchar":
return new system.data.oledb.oledbparameter ( paraname, system.data.dbtype.string );

case "datetime":
return new system.data.oledb.oledbparameter ( paraname, system.data.dbtype.datetime );

case "iamge":
return new system.data.oledb.oledbparameter( paraname, system.data.dbtype.binary );

case "int":
return new system.data.oledb.oledbparameter ( paraname, system.data.dbtype.int32 );

case "text":
return new system.data.oledb.oledbparameter ( paraname, system.data.dbtype.string );

default:
return new system.data.oledb.oledbparameter ( paraname, system.data.dbtype.string );

}
}

#endregion

创建 connection 和 command#region 创建 connection 和 command

private idbconnection getconnection()
{
switch(this.dbtype)
{
case "sqlserver":
return new system.data.sqlclient.sqlconnection(this.conntionstring);

case "oracle":
return new system.data.oracleclient.oracleconnection(this.conntionstring);

case "access":
return new system.data.oledb.oledbconnection(this.conntionstring);
default:
return new system.data.sqlclient.sqlconnection(this.conntionstring);
}
}


private idbcommand getcommand(string sql,idbconnection iconn)
{
switch(this.dbtype)
{
case "sqlserver":
return new system.data.sqlclient.sqlcommand(sql,(sqlconnection)iconn);

case "oracle":
return new system.data.oracleclient.oraclecommand(sql,(oracleconnection)iconn);

case "access":
return new system.data.oledb.oledbcommand(sql,(oledbconnection)iconn);
default:
return new system.data.sqlclient.sqlcommand(sql,(sqlconnection)iconn);
}
}

private idbcommand getcommand()
{
switch(this.dbtype)
{
case "sqlserver":
return new system.data.sqlclient.sqlcommand();

case "oracle":
return new system.data.oracleclient.oraclecommand();

case "access":
return new system.data.oledb.oledbcommand();
default:
return new system.data.sqlclient.sqlcommand();
}
}

private idataadapter getadapater(string sql,idbconnection iconn)
{
switch(this.dbtype)
{
case "sqlserver":
return new system.data.sqlclient.sqldataadapter(sql,(sqlconnection)iconn);

case "oracle":
return new system.data.oracleclient.oracledataadapter(sql,(oracleconnection)iconn);

case "access":
return new system.data.oledb.oledbdataadapter(sql,(oledbconnection)iconn);

default:
return new system.data.sqlclient.sqldataadapter(sql,(sqlconnection)iconn);;
}

}

private idataadapter getadapater()
{
switch(this.dbtype)
{
case "sqlserver":
return new system.data.sqlclient.sqldataadapter();

case "oracle":
return new system.data.oracleclient.oracledataadapter();

case "access":
return new system.data.oledb.oledbdataadapter();

default:
return new system.data.sqlclient.sqldataadapter();
}
}

private idataadapter getadapater(idbcommand icmd)
{
switch(this.dbtype)
{
case "sqlserver":
return new system.data.sqlclient.sqldataadapter((sqlcommand)icmd);

case "oracle":
return new system.data.oracleclient.oracledataadapter((oraclecommand)icmd);

case "access":
return new system.data.oledb.oledbdataadapter((oledbcommand)icmd);

default:
return new system.data.sqlclient.sqldataadapter((sqlcommand)icmd);
}
}
#endregion

执行简单sql语句#region 执行简单sql语句
/**//// <summary>
/// 执行sql语句,返回影响的记录数
/// </summary>
/// <param name="sqlstring">sql语句</param>
/// <returns>影响的记录数</returns>
public int executesql(string sqlstring)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using (system.data.idbcommand icmd = getcommand(sqlstring,iconn))
{
iconn.open();
try
{

int rows=icmd.executenonquery();
return rows;
}
catch(system.exception e)
{
throw new exception(e.message);
}
finally
{
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}

/**//// <summary>
/// 执行多条sql语句,实现数据库事务。
/// </summary>
/// <param name="sqlstringlist">多条sql语句</param>
public void executesqltran(arraylist sqlstringlist)
{
using (system.data.idbconnection iconn = this.getconnection())
{
iconn.open();
using(system.data.idbcommand icmd = getcommand())
{
icmd.connection=iconn;
using(system.data.idbtransaction idbtran = iconn.begintransaction())
{
icmd.transaction=idbtran;
try
{
for(int n=0;n<sqlstringlist.count;n++)
{
string strsql = sqlstringlist[n].tostring();
if ( strsql.trim().length>1)
{
icmd.commandtext = strsql;
icmd.executenonquery();
}
}
idbtran.commit();
}
catch(system.exception e)
{
idbtran.rollback();
throw new exception(e.message);
}
finally
{
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}

}

}
}
/**//// <summary>
/// 执行带一个存储过程参数的的sql语句。
/// </summary>
/// <param name="sqlstring">sql语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public int executesql(string sqlstring,string content)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using(system.data.idbcommand icmd = getcommand(sqlstring,iconn))
{
system.data.idataparameter myparameter = this.idbpara( "@content", "text");
myparameter.value = content ;
icmd.parameters.add(myparameter);
iconn.open();
try
{

int rows = icmd.executenonquery();
return rows;
}
catch( system.exception e )
{
throw new exception(e.message);
}
finally
{
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}


/**//// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strsql">sql语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public int executesqlinsertimg(string sqlstring,byte[] fs)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using(system.data.idbcommand icmd = getcommand(sqlstring,iconn))
{
system.data.idataparameter myparameter = this.idbpara( "@content", "image");
myparameter.value = fs ;
icmd.parameters.add(myparameter);
iconn.open();
try
{
int rows = icmd.executenonquery();
return rows;
}
catch( system.exception e )
{
throw new exception(e.message);
}
finally
{
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}

/**//// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="sqlstring">计算查询结果语句</param>
/// <returns>查询结果(object)</returns>
public object getsingle(string sqlstring)
{
using (system.data.idbconnection iconn = getconnection())
{
using (system.data.idbcommand icmd = getcommand(sqlstring,iconn))
{
iconn.open();
try
{
object obj = icmd.executescalar();
if((object.equals(obj,null))||(object.equals(obj,system.dbnull.value)))
{
return null;
}
else
{
return obj;
}
}
catch(system.exception e)
{
throw new exception(e.message);
}
finally
{
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
/**//// <summary>
/// 执行查询语句,返回idataadapter
/// </summary>
/// <param name="strsql">查询语句</param>
/// <returns>idataadapter</returns>
public idataadapter executereader(string strsql)
{
using (system.data.idbconnection iconn = this.getconnection())
{
iconn.open();
try
{
system.data.idataadapter iadapter = this.getadapater(strsql,iconn);
return iadapter;
}
catch(system.exception e)
{
throw new exception(e.message);
}
finally
{
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
/**//// <summary>
/// 执行查询语句,返回dataset
/// </summary>
/// <param name="sqlstring">查询语句</param>
/// <returns>dataset</returns>
public dataset query(string sqlstring)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using(system.data.idbcommand icmd = getcommand(sqlstring,iconn))
{
dataset ds = new dataset();
iconn.open();
try
{
system.data.idataadapter iadapter = this.getadapater(sqlstring,iconn);
iadapter.fill(ds);
return ds;
}
catch(system.exception ex)
{
throw new exception(ex.message);
}
finally
{
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}

/**//// <summary>
/// 执行查询语句,返回dataset
/// </summary>
/// <param name="sqlstring">查询语句</param>
/// <param name="dataset">要填充的dataset</param>
/// <param name="tablename">要填充的表名</param>
/// <returns>dataset</returns>
public dataset query(string sqlstring,dataset dataset,string tablename)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using(system.data.idbcommand icmd = getcommand(sqlstring,iconn))
{
iconn.open();
try
{
system.data.idataadapter iadapter = this.getadapater(sqlstring,iconn);
((oledbdataadapter)iadapter).fill(dataset,tablename);
return dataset;
}
catch(system.exception ex)
{
throw new exception(ex.message);
}
finally
{
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}


/**//// <summary>
/// 执行sql语句 返回存储过程
/// </summary>
/// <param name="sqlstring">sql语句</param>
/// <param name="dataset">要填充的dataset</param>
/// <param name="startindex">开始记录</param>
/// <param name="pagesize">页面记录大小</param>
/// <param name="tablename">表名称</param>
/// <returns>dataset</returns>
public dataset query(string sqlstring , dataset dataset ,int startindex ,int pagesize, string tablename )
{
using (system.data.idbconnection iconn = this.getconnection())
{
iconn.open();
try
{
system.data.idataadapter iadapter = this.getadapater(sqlstring,iconn);

((oledbdataadapter)iadapter).fill(dataset,startindex,pagesize,tablename);

return dataset;
}
catch(exception ex)
{
throw new exception(ex.message);
}
finally
{
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}


/**//// <summary>
/// 执行查询语句,向xml文件写入数据
/// </summary>
/// <param name="sqlstring">查询语句</param>
/// <param name="xmlpath">xml文件路径</param>
public void writetoxml(string sqlstring,string xmlpath)
{
query(sqlstring).writexml(xmlpath);
}

/**//// <summary>
/// 执行查询语句
/// </summary>
/// <param name="sqlstring">查询语句</param>
/// <returns>datatable </returns>
public datatable executequery(string sqlstring)
{
using (system.data.idbconnection iconn = this.getconnection())
{
//system.data.idbcommand icmd = getcommand(sqlstring,iconn);
dataset ds = new dataset();
try
{
system.data.idataadapter iadapter = this.getadapater(sqlstring,iconn);
iadapter.fill(ds);
}
catch(system.exception e)
{
throw new exception(e.message);
}
finally
{
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
return ds.tables[0];
}
}

/**//// <summary>
/// 执行查询语句
/// </summary>
/// <param name="sqlstring">查询语句</param>
/// <returns>datatable </returns>
public datatable executequery(string sqlstring,string proc)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using(system.data.idbcommand icmd = getcommand(sqlstring,iconn))
{
icmd.commandtype = commandtype.storedprocedure;
dataset ds = new dataset();
try
{
system.data.idataadapter idataadapter = this.getadapater(sqlstring,iconn);
idataadapter.fill(ds);
}
catch(system.exception e)
{
throw new exception(e.message);
}
finally
{
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
return ds.tables[0];
}


}
}

/**//// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public dataview execeutedataview(string sql)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using(system.data.idbcommand icmd = getcommand(sql,iconn))
{
dataset ds = new dataset();
try
{
system.data.idataadapter idataadapter = this.getadapater(sql,iconn);
idataadapter.fill(ds);
return ds.tables[0].defaultview;
}
catch(system.exception e)
{
throw new exception(e.message);
}
finally
{
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}

#endregion

执行带参数的sql语句#region 执行带参数的sql语句
/**//// <summary>
/// 执行sql语句,返回影响的记录数
/// </summary>
/// <param name="sqlstring">sql语句</param>
/// <returns>影响的记录数</returns>
public int executesql(string sqlstring,params idataparameter[] iparms)
{
using (system.data.idbconnection iconn = this.getconnection())
{
system.data.idbcommand icmd = getcommand();
{
try
{
preparecommand(out icmd, iconn, null , sqlstring, iparms );
int rows=icmd.executenonquery();
icmd.parameters.clear();
return rows;
}
catch(system.exception e)
{
throw new exception( e.message );
}
finally
{
icmd.dispose();
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}


/**//// <summary>
/// 执行多条sql语句,实现数据库事务。
/// </summary>
/// <param name="sqlstringlist">sql语句的哈希表(key为sql语句,value是该语句的sqlparameter[])</param>
public void executesqltran(hashtable sqlstringlist)
{
using (system.data.idbconnection iconn = this.getconnection())
{
iconn.open();
using (idbtransaction itrans = iconn.begintransaction())
{
system.data.idbcommand icmd = getcommand();
try
{
//循环
foreach ( dictionaryentry myde in sqlstringlist)
{
string cmdtext = myde.key.tostring();
idataparameter[] iparms=( idataparameter[] ) myde.value;
preparecommand( out icmd , iconn , itrans , cmdtext , iparms );
int val = icmd.executenonquery();
icmd.parameters.clear();
}
itrans.commit();
}
catch
{
itrans.rollback();
throw;
}
finally
{
icmd.dispose();
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}

}
}
}


/**//// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="sqlstring">计算查询结果语句</param>
/// <returns>查询结果(object)</returns>
public object getsingle(string sqlstring,params idataparameter[] iparms)
{
using (system.data.idbconnection iconn = this.getconnection())
{
system.data.idbcommand icmd = getcommand();
{
try
{
preparecommand( out icmd, iconn, null , sqlstring, iparms );
object obj = icmd.executescalar();
icmd.parameters.clear();
if((object.equals(obj,null))||(object.equals(obj,system.dbnull.value)))
{
return null;
}
else
{
return obj;
}
}
catch(system.exception e)
{
throw new exception(e.message);
}
finally
{
icmd.dispose();
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}

/**//// <summary>
/// 执行查询语句,返回idatareader
/// </summary>
/// <param name="strsql">查询语句</param>
/// <returns> idatareader </returns>
public idatareader executereader(string sqlstring,params idataparameter[] iparms)
{
system.data.idbconnection iconn = this.getconnection();
{
system.data.idbcommand icmd = getcommand();
{
try
{
preparecommand(out icmd, iconn , null , sqlstring , iparms);
system.data.idatareader ireader = icmd.executereader();
icmd.parameters.clear();
return ireader;
}
catch(system.exception e)
{
throw new exception(e.message);
}
finally
{
icmd.dispose();
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}

/**//// <summary>
/// 执行查询语句,返回dataset
/// </summary>
/// <param name="sqlstring">查询语句</param>
/// <returns>dataset</returns>
public dataset query(string sqlstring,params idataparameter[] iparms)
{
using (system.data.idbconnection iconn = this.getconnection())
{
idbcommand icmd = getcommand();
{
preparecommand(out icmd , iconn , null , sqlstring , iparms );
try
{
idataadapter iadapter = this.getadapater(sqlstring,iconn);
dataset ds = new dataset();
iadapter.fill(ds);
icmd.parameters.clear();
return ds;
}
catch(system.exception ex)
{
throw new exception(ex.message);
}
finally
{
icmd.dispose();
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}


/**//// <summary>
/// 初始化command
/// </summary>
/// <param name="icmd"></param>
/// <param name="iconn"></param>
/// <param name="itrans"></param>
/// <param name="cmdtext"></param>
/// <param name="iparms"></param>
private void preparecommand(out idbcommand icmd,idbconnection iconn,system.data.idbtransaction itrans, string cmdtext, idataparameter[] iparms)
{
if (iconn.state != connectionstate.open)
iconn.open();
icmd = this.getcommand();
icmd.connection = iconn;
icmd.commandtext = cmdtext;
if (itrans != null)
icmd.transaction = itrans;
icmd.commandtype = commandtype.text;//cmdtype;
if (iparms != null)
{
foreach (idataparameter parm in iparms)
icmd.parameters.add(parm);
}
}

#endregion

存储过程操作#region 存储过程操作

/**//// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedprocname">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>sqldatareader</returns>
public sqldatareader runprocedure(string storedprocname, idataparameter[] parameters )
{
system.data.idbconnection iconn = this.getconnection();
{
iconn.open();

using(sqlcommand sqlcmd = buildquerycommand(iconn,storedprocname, parameters))
{
return sqlcmd.executereader(commandbehavior.closeconnection);
}
}
}

/**//// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedprocname">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tablename">dataset结果中的表名</param>
/// <returns>dataset</returns>
public dataset runprocedure(string storedprocname, idataparameter[] parameters ,string tablename)
{

using (system.data.idbconnection iconn = this.getconnection())
{
dataset dataset = new dataset();
iconn.open();
system.data.idataadapter ida = this.getadapater();
ida = this.getadapater( buildquerycommand(iconn, storedprocname, parameters ) );

((sqldataadapter)ida).fill( dataset,tablename);
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
return dataset;
}
}



/**//// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedprocname">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tablename">dataset结果中的表名</param>
/// <param name="startindex">开始记录索引</param>
/// <param name="pagesize">页面记录大小</param>
/// <returns>dataset</returns>
public dataset runprocedure(string storedprocname, idataparameter[] parameters ,int startindex,int pagesize,string tablename)
{

using (system.data.idbconnection iconn = this.getconnection())
{
dataset dataset = new dataset();
iconn.open();
system.data.idataadapter ida = this.getadapater();
ida = this.getadapater( buildquerycommand(iconn, storedprocname, parameters ) );

((sqldataadapter)ida).fill( dataset,startindex,pagesize,tablename);
if(iconn.state != connectionstate.closed)
{
iconn.close();
}
return dataset;
}
}

/**//// <summary>
/// 执行存储过程 填充已经存在的dataset数据集
/// </summary>
/// <param name="storeprocname">存储过程名称</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="dataset">要填充的数据集</param>
/// <param name="tablename">要填充的表名</param>
/// <returns></returns>
public dataset runprocedure(string storeprocname,idataparameter[] parameters,dataset dataset,string tablename)
{
using (system.data.idbconnection iconn = this.getconnection())
{
iconn.open();
system.data.idataadapter ida = this.getadapater();
ida = this.getadapater(buildquerycommand(iconn,storeprocname,parameters));

((sqldataadapter)ida).fill(dataset,tablename);

if(iconn.state != connectionstate.closed)
{
iconn.close();
}

return dataset;
}
}

/**//// <summary>
/// 执行存储过程并返回受影响的行数
/// </summary>
/// <param name="storedprocname"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int runprocedurenoquery(string storedprocname, idataparameter[] parameters )
{

int result = 0;
using (system.data.idbconnection iconn = this.getconnection())
{
iconn.open();
using(sqlcommand scmd = buildquerycommand(iconn,storedprocname,parameters))
{
result = scmd.executenonquery();
}

if(iconn.state != connectionstate.closed)
{
iconn.close();
}
}

return result ;
}

public string runprocedureexecutescalar(string storeprocname,idataparameter[] parameters)
{
string result = string.empty;
using (system.data.idbconnection iconn = this.getconnection())
{

iconn.open();
using(sqlcommand scmd = buildquerycommand(iconn,storeprocname,parameters))
{
object obj = scmd.executescalar();
if(obj == null)
result = null;
else
result = obj.tostring();
}

if(iconn.state != connectionstate.closed)
{
iconn.close();
}

}

return result;
}

/**//// <summary>
/// 构建 sqlcommand 对象(用来返回一个结果集,而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedprocname">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>sqlcommand</returns>
private sqlcommand buildquerycommand(idbconnection iconn,string storedprocname, idataparameter[] parameters)
{

idbcommand icmd = getcommand(storedprocname,iconn);
icmd.commandtype = commandtype.storedprocedure;
if (parameters == null)
{
return (sqlcommand)icmd;
}
foreach (idataparameter parameter in parameters)
{
icmd.parameters.add( parameter );
}
return (sqlcommand)icmd;
}

/**//// <summary>
/// 执行存储过程,返回影响的行数
/// </summary>
/// <param name="storedprocname">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsaffected">影响的行数</param>
/// <returns></returns>
public int runprocedure(string storedprocname, idataparameter[] parameters, out int rowsaffected )
{
using (system.data.idbconnection iconn = this.getconnection())
{
int result;
iconn.open();
using(sqlcommand sqlcmd = buildintcommand(iconn,storedprocname, parameters ))
{
rowsaffected = sqlcmd.executenonquery();
result = (int)sqlcmd.parameters["returnvalue"].value;

if(iconn.state != connectionstate.closed)
{
iconn.close();
}
return result;
}
}
}

/**//// <summary>
/// 创建 sqlcommand 对象实例(用来返回一个整数值)
/// </summary>
/// <param name="storedprocname">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>sqlcommand 对象实例</returns>
private sqlcommand buildintcommand(idbconnection iconn,string storedprocname, idataparameter[] parameters)
{
sqlcommand sqlcmd = buildquerycommand(iconn,storedprocname, parameters );
sqlcmd.parameters.add( new sqlparameter ( "returnvalue",
sqldbtype.int,4,parameterdirection.returnvalue,
false,0,0,string.empty,datarowversion.default,null ));
return sqlcmd;
}
#endregion


}
}