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

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


}
}