c#封装DBHelper类
程序员文章站
2022-05-25 14:36:20
public enum EffentNextType { /// /// 对其他语句无任何影响 /// None, /// /// 当前语句必须为"select count(1) from .."格式,如果存在则继续执行,不存在回滚事务 /// ... ......
public enum effentnexttype { /// <summary> /// 对其他语句无任何影响 /// </summary> none, /// <summary> /// 当前语句必须为"select count(1) from .."格式,如果存在则继续执行,不存在回滚事务 /// </summary> whenhavecontine, /// <summary> /// 当前语句必须为"select count(1) from .."格式,如果不存在则继续执行,存在回滚事务 /// </summary> whennohavecontine, /// <summary> /// 当前语句影响到的行数必须大于0,否则回滚事务 /// </summary> excuteeffectrows, /// <summary> /// 引发事件-当前语句必须为"select count(1) from .."格式,如果不存在则继续执行,存在回滚事务 /// </summary> solicitationevent } public class commandinfo { public object shareobject = null; public object originaldata = null; event eventhandler _solicitationevent; public event eventhandler solicitationevent { add { _solicitationevent += value; } remove { _solicitationevent -= value; } } public void onsolicitationevent() { if (_solicitationevent != null) { _solicitationevent(this, new eventargs()); } } public string commandtext; public system.data.common.dbparameter[] parameters; public effentnexttype effentnexttype = effentnexttype.none; public commandinfo() { } public commandinfo(string sqltext, sqlparameter[] para) { this.commandtext = sqltext; this.parameters = para; } public commandinfo(string sqltext, sqlparameter[] para, effentnexttype type) { this.commandtext = sqltext; this.parameters = para; this.effentnexttype = type; } }
public abstract class dbhelper { public static string connectionstring =configurationmanager.connectionstrings["entity"].connectionstring; #region 公用方法 /// <summary> /// 判断是否存在某表的某个字段 /// </summary> /// <param name="tablename">表名称</param> /// <param name="columnname">列名称</param> /// <returns>是否存在</returns> public static bool columnexists(string tablename, string columnname) { string sql = "select count(1) from syscolumns where [id]=object_id('" + tablename + "') and [name]='" + columnname + "'"; object res = getsingle(sql); if (res == null) { return false; } return convert.toint32(res) > 0; } public static int getminid(string fieldname, string tablename) { string strsql = "select min(" + fieldname + ") from " + tablename; object obj = dbhelper.getsingle(strsql); if (obj == null) { return 0; } else { return int.parse(obj.tostring()); } } public static int getmaxid(string fieldname, string tablename) { string strsql = "select max(" + fieldname + ")+1 from " + tablename; object obj = dbhelper.getsingle(strsql); if (obj == null) { return 1; } else { return int.parse(obj.tostring()); } } public static bool exists(string strsql) { object obj = dbhelper.getsingle(strsql); int cmdresult; if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value))) { cmdresult = 0; } else { cmdresult = int.parse(obj.tostring()); } if (cmdresult == 0) { return false; } else { return true; } } /// <summary> /// 表是否存在 /// </summary> /// <param name="tablename"></param> /// <returns></returns> public static bool tabexists(string tablename) { string strsql = "select count(*) from sysobjects where id = object_id(n'[" + tablename + "]') and objectproperty(id, n'isusertable') = 1"; //string strsql = "select count(*) from sys.objects where object_id = object_id(n'[dbo].[" + tablename + "]') and type in (n'u')"; object obj = dbhelper.getsingle(strsql); int cmdresult; if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value))) { cmdresult = 0; } else { cmdresult = int.parse(obj.tostring()); } if (cmdresult == 0) { return false; } else { return true; } } public static bool exists(string strsql, params sqlparameter[] cmdparms) { object obj = dbhelper.getsingle(strsql, cmdparms); int cmdresult; if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value))) { cmdresult = 0; } else { cmdresult = int.parse(obj.tostring()); } if (cmdresult == 0) { return false; } else { return true; } } #endregion #region 执行简单sql语句 /// <summary> /// 执行sql语句,返回影响的记录数 /// </summary> /// <param name="sqlstring">sql语句</param> /// <returns>影响的记录数</returns> public static int executesql(string sqlstring) { using (sqlconnection connection = new sqlconnection(connectionstring)) { using (sqlcommand cmd = new sqlcommand(sqlstring, connection)) { try { connection.open(); int rows = cmd.executenonquery(); return rows; } catch (system.data.sqlclient.sqlexception e) { connection.close(); throw e; } } } } /// <summary> /// 2012-2-21新增重载,执行sql语句,返回影响的记录数 /// </summary> /// <param name="connection">sqlconnection对象</param> /// <param name="trans">sqltransaction事件</param> /// <param name="sqlstring">sql语句</param> /// <returns>影响的记录数</returns> public static int executesql(sqlconnection connection, sqltransaction trans, string sqlstring) { using (sqlcommand cmd = new sqlcommand(sqlstring, connection)) { try { cmd.connection = connection; cmd.transaction = trans; int rows = cmd.executenonquery(); return rows; } catch (system.data.sqlclient.sqlexception e) { //trans.rollback(); throw e; } } } public static int executesqlbytime(string sqlstring, int times) { using (sqlconnection connection = new sqlconnection(connectionstring)) { using (sqlcommand cmd = new sqlcommand(sqlstring, connection)) { try { connection.open(); cmd.commandtimeout = times; int rows = cmd.executenonquery(); return rows; } catch (system.data.sqlclient.sqlexception e) { connection.close(); throw e; } } } } /// <summary> /// 执行sql和oracle滴混合事务 /// </summary> /// <param name="list">sql命令行列表</param> /// <param name="oraclecmdsqllist">oracle命令行列表</param> /// <returns>执行结果 0-由于sql造成事务失败 -1 由于oracle造成事务失败 1-整体事务执行成功</returns> public static int executesqltran(list<commandinfo> list, list<commandinfo> oraclecmdsqllist) { using (sqlconnection conn = new sqlconnection(connectionstring)) { conn.open(); sqlcommand cmd = new sqlcommand(); cmd.connection = conn; sqltransaction tx = conn.begintransaction(); cmd.transaction = tx; try { foreach (commandinfo myde in list) { string cmdtext = myde.commandtext; sqlparameter[] cmdparms = (sqlparameter[])myde.parameters; preparecommand(cmd, conn, tx, cmdtext, cmdparms); if (myde.effentnexttype == effentnexttype.solicitationevent) { if (myde.commandtext.tolower().indexof("count(") == -1) { tx.rollback(); throw new exception("违背要求" + myde.commandtext + "必须符合select count(..的格式"); //return 0; } object obj = cmd.executescalar(); bool ishave = false; if (obj == null && obj == dbnull.value) { ishave = false; } ishave = convert.toint32(obj) > 0; if (ishave) { //引发事件 myde.onsolicitationevent(); } } if (myde.effentnexttype == effentnexttype.whenhavecontine || myde.effentnexttype == effentnexttype.whennohavecontine) { if (myde.commandtext.tolower().indexof("count(") == -1) { tx.rollback(); throw new exception("sql:违背要求" + myde.commandtext + "必须符合select count(..的格式"); //return 0; } object obj = cmd.executescalar(); bool ishave = false; if (obj == null && obj == dbnull.value) { ishave = false; } ishave = convert.toint32(obj) > 0; if (myde.effentnexttype == effentnexttype.whenhavecontine && !ishave) { tx.rollback(); throw new exception("sql:违背要求" + myde.commandtext + "返回值必须大于0"); //return 0; } if (myde.effentnexttype == effentnexttype.whennohavecontine && ishave) { tx.rollback(); throw new exception("sql:违背要求" + myde.commandtext + "返回值必须等于0"); //return 0; } continue; } int val = cmd.executenonquery(); if (myde.effentnexttype == effentnexttype.excuteeffectrows && val == 0) { tx.rollback(); throw new exception("sql:违背要求" + myde.commandtext + "必须有影响行"); //return 0; } cmd.parameters.clear(); } tx.commit(); return 1; } catch (sqlexception e) { tx.rollback(); throw e; } catch (exception e) { tx.rollback(); throw e; } } } /// <summary> /// 执行多条sql语句,实现数据库事务。 /// </summary> /// <param name="sqlstringlist">多条sql语句</param> public static int executesqltran(list<string> sqlstringlist) { using (sqlconnection conn = new sqlconnection(connectionstring)) { conn.open(); sqlcommand cmd = new sqlcommand(); cmd.connection = conn; sqltransaction tx = conn.begintransaction(); cmd.transaction = tx; try { int count = 0; for (int n = 0; n < sqlstringlist.count; n++) { string strsql = sqlstringlist[n]; if (strsql.trim().length > 1) { cmd.commandtext = strsql; count += cmd.executenonquery(); } } tx.commit(); return count; } catch { tx.rollback(); return 0; } } } /// <summary> /// 执行带一个存储过程参数的的sql语句。 /// </summary> /// <param name="sqlstring">sql语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static int executesql(string sqlstring, string content) { using (sqlconnection connection = new sqlconnection(connectionstring)) { sqlcommand cmd = new sqlcommand(sqlstring, connection); system.data.sqlclient.sqlparameter myparameter = new system.data.sqlclient.sqlparameter("@content", sqldbtype.ntext); myparameter.value = content; cmd.parameters.add(myparameter); try { connection.open(); int rows = cmd.executenonquery(); return rows; } catch (sqlexception e) { throw e; } finally { cmd.dispose(); connection.close(); } } } /// <summary> /// 执行带一个存储过程参数的的sql语句。 /// </summary> /// <param name="sqlstring">sql语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static object executesqlget(string sqlstring, string content) { using (sqlconnection connection = new sqlconnection(connectionstring)) { sqlcommand cmd = new sqlcommand(sqlstring, connection); system.data.sqlclient.sqlparameter myparameter = new system.data.sqlclient.sqlparameter("@content", sqldbtype.ntext); myparameter.value = content; cmd.parameters.add(myparameter); try { connection.open(); object obj = cmd.executescalar(); if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value))) { return null; } else { return obj; } } catch (system.data.sqlclient.sqlexception e) { throw e; } finally { cmd.dispose(); connection.close(); } } } /// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strsql">sql语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> public static int executesqlinsertimg(string strsql, byte[] fs) { using (sqlconnection connection = new sqlconnection(connectionstring)) { sqlcommand cmd = new sqlcommand(strsql, connection); system.data.sqlclient.sqlparameter myparameter = new system.data.sqlclient.sqlparameter("@fs", sqldbtype.image); myparameter.value = fs; cmd.parameters.add(myparameter); try { connection.open(); int rows = cmd.executenonquery(); return rows; } catch (sqlexception e) { throw e; } finally { cmd.dispose(); connection.close(); } } } /// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="sqlstring">计算查询结果语句</param> /// <returns>查询结果(object)</returns> public static object getsingle(string sqlstring) { using (sqlconnection connection = new sqlconnection(connectionstring)) { using (sqlcommand cmd = new sqlcommand(sqlstring, connection)) { try { connection.open(); object obj = cmd.executescalar(); if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value))) { return null; } else { return obj; } } catch (system.data.sqlclient.sqlexception e) { connection.close(); throw e; } } } } public static object getsingle(string sqlstring, int times) { using (sqlconnection connection = new sqlconnection(connectionstring)) { using (sqlcommand cmd = new sqlcommand(sqlstring, connection)) { try { connection.open(); cmd.commandtimeout = times; object obj = cmd.executescalar(); if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value))) { return null; } else { return obj; } } catch (system.data.sqlclient.sqlexception e) { connection.close(); throw e; } } } } /// <summary> /// 执行查询语句,返回sqldatareader ( 注意:调用该方法后,一定要对sqldatareader进行close ) /// </summary> /// <param name="strsql">查询语句</param> /// <returns>sqldatareader</returns> public static sqldatareader executereader(string strsql) { sqlconnection connection = new sqlconnection(connectionstring); sqlcommand cmd = new sqlcommand(strsql, connection); try { connection.open(); sqldatareader myreader = cmd.executereader(commandbehavior.closeconnection); return myreader; } catch (system.data.sqlclient.sqlexception e) { throw e; } } /// <summary> /// 执行查询语句,返回dataset /// </summary> /// <param name="sqlstring">查询语句</param> /// <returns>dataset</returns> public static dataset query(string sqlstring) { using (sqlconnection connection = new sqlconnection(connectionstring)) { dataset ds = new dataset(); try { connection.open(); sqldataadapter command = new sqldataadapter(sqlstring, connection); command.fill(ds, "ds"); } catch (system.data.sqlclient.sqlexception ex) { throw new exception(ex.message); } return ds; } } public static dataset query(string sqlstring, int times) { using (sqlconnection connection = new sqlconnection(connectionstring)) { dataset ds = new dataset(); try { connection.open(); sqldataadapter command = new sqldataadapter(sqlstring, connection); command.selectcommand.commandtimeout = times; command.fill(ds, "ds"); } catch (system.data.sqlclient.sqlexception ex) { throw new exception(ex.message); } return ds; } } /// <summary> ///新增重载,执行查询语句,返回dataset /// </summary> /// <param name="connection">sqlconnection对象</param> /// <param name="trans">sqltransaction事务</param> /// <param name="sqlstring">sql语句</param> /// <returns>dataset</returns> public static dataset query(sqlconnection connection, sqltransaction trans, string sqlstring) { dataset ds = new dataset(); try { sqldataadapter command = new sqldataadapter(sqlstring, connection); command.selectcommand.transaction = trans; command.fill(ds, "ds"); } catch (sqlexception ex) { throw new exception(ex.message); } return ds; } #endregion #region 执行带参数的sql语句 /// <summary> /// 执行sql语句,返回影响的记录数 /// </summary> /// <param name="sqlstring">sql语句</param> /// <returns>影响的记录数</returns> public static int executesql(string sqlstring, params sqlparameter[] cmdparms) { using (sqlconnection connection = new sqlconnection(connectionstring)) { using (sqlcommand cmd = new sqlcommand()) { try { preparecommand(cmd, connection, null, sqlstring, cmdparms); int rows = cmd.executenonquery(); cmd.parameters.clear(); return rows; } catch (sqlexception e) { throw e; } } } } /// <summary> /// 新增重载,执行sql语句,返回影响的记录数 /// </summary> /// <param name="connection">sqlconnection对象</param> /// <param name="trans">sqltransaction对象</param> /// <param name="sqlstring">sql语句</param> /// <returns>影响的记录数</returns> public static int executesql(sqlconnection connection, sqltransaction trans, string sqlstring, params sqlparameter[] cmdparms) { using (sqlcommand cmd = new sqlcommand()) { try { preparecommand(cmd, connection, trans, sqlstring, cmdparms); int rows = cmd.executenonquery(); cmd.parameters.clear(); return rows; } catch (system.data.sqlclient.sqlexception e) { //trans.rollback(); throw e; } } } /// <summary> /// 执行多条sql语句,实现数据库事务。 /// </summary> /// <param name="sqlstringlist">sql语句的哈希表(key为sql语句,value是该语句的sqlparameter[])</param> public static void executesqltran(hashtable sqlstringlist) { using (sqlconnection conn = new sqlconnection(connectionstring)) { conn.open(); using (sqltransaction trans = conn.begintransaction()) { sqlcommand cmd = new sqlcommand(); try { //循环 foreach (dictionaryentry myde in sqlstringlist) { string cmdtext = myde.key.tostring(); sqlparameter[] cmdparms = (sqlparameter[])myde.value; preparecommand(cmd, conn, trans, cmdtext, cmdparms); int val = cmd.executenonquery(); cmd.parameters.clear(); } trans.commit(); } catch { trans.rollback(); throw; } } } } /// <summary> /// 执行多条sql语句,实现数据库事务。 /// </summary> /// <param name="sqlstringlist">sql语句的哈希表(key为sql语句,value是该语句的sqlparameter[])</param> public static int executesqltran(system.collections.generic.list<commandinfo> cmdlist) { using (sqlconnection conn = new sqlconnection(connectionstring)) { conn.open(); using (sqltransaction trans = conn.begintransaction()) { sqlcommand cmd = new sqlcommand(); try { int count = 0; //循环 foreach (commandinfo myde in cmdlist) { string cmdtext = myde.commandtext; sqlparameter[] cmdparms = (sqlparameter[])myde.parameters; preparecommand(cmd, conn, trans, cmdtext, cmdparms); if (myde.effentnexttype == effentnexttype.whenhavecontine || myde.effentnexttype == effentnexttype.whennohavecontine) { if (myde.commandtext.tolower().indexof("count(") == -1) { trans.rollback(); return 0; } object obj = cmd.executescalar(); bool ishave = false; if (obj == null && obj == dbnull.value) { ishave = false; } ishave = convert.toint32(obj) > 0; if (myde.effentnexttype == effentnexttype.whenhavecontine && !ishave) { trans.rollback(); return 0; } if (myde.effentnexttype == effentnexttype.whennohavecontine && ishave) { trans.rollback(); return 0; } continue; } int val = cmd.executenonquery(); count += val; if (myde.effentnexttype == effentnexttype.excuteeffectrows && val == 0) { trans.rollback(); return 0; } cmd.parameters.clear(); } trans.commit(); return count; } catch { trans.rollback(); throw; } } } } /// <summary> /// 执行多条sql语句,实现数据库事务。 /// </summary> /// <param name="sqlstringlist">sql语句的哈希表(key为sql语句,value是该语句的sqlparameter[])</param> public static void executesqltranwithindentity(system.collections.generic.list<commandinfo> sqlstringlist) { using (sqlconnection conn = new sqlconnection(connectionstring)) { conn.open(); using (sqltransaction trans = conn.begintransaction()) { sqlcommand cmd = new sqlcommand(); try { int indentity = 0; //循环 foreach (commandinfo myde in sqlstringlist) { string cmdtext = myde.commandtext; sqlparameter[] cmdparms = (sqlparameter[])myde.parameters; foreach (sqlparameter q in cmdparms) { if (q.direction == parameterdirection.inputoutput) { q.value = indentity; } } preparecommand(cmd, conn, trans, cmdtext, cmdparms); int val = cmd.executenonquery(); foreach (sqlparameter q in cmdparms) { if (q.direction == parameterdirection.output) { indentity = convert.toint32(q.value); } } cmd.parameters.clear(); } trans.commit(); } catch { trans.rollback(); throw; } } } } /// <summary> /// 执行多条sql语句,实现数据库事务。 /// </summary> /// <param name="sqlstringlist">sql语句的哈希表(key为sql语句,value是该语句的sqlparameter[])</param> public static void executesqltranwithindentity(hashtable sqlstringlist) { using (sqlconnection conn = new sqlconnection(connectionstring)) { conn.open(); using (sqltransaction trans = conn.begintransaction()) { sqlcommand cmd = new sqlcommand(); try { int indentity = 0; //循环 foreach (dictionaryentry myde in sqlstringlist) { string cmdtext = myde.key.tostring(); sqlparameter[] cmdparms = (sqlparameter[])myde.value; foreach (sqlparameter q in cmdparms) { if (q.direction == parameterdirection.inputoutput) { q.value = indentity; } } preparecommand(cmd, conn, trans, cmdtext, cmdparms); int val = cmd.executenonquery(); foreach (sqlparameter q in cmdparms) { if (q.direction == parameterdirection.output) { indentity = convert.toint32(q.value); } } cmd.parameters.clear(); } trans.commit(); } catch { trans.rollback(); throw; } } } } /// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="sqlstring">计算查询结果语句</param> /// <returns>查询结果(object)</returns> public static object getsingle(string sqlstring, params sqlparameter[] cmdparms) { using (sqlconnection connection = new sqlconnection(connectionstring)) { using (sqlcommand cmd = new sqlcommand()) { try { preparecommand(cmd, connection, null, sqlstring, cmdparms); object obj = cmd.executescalar(); cmd.parameters.clear(); if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value))) { return null; } else { return obj; } } catch (system.data.sqlclient.sqlexception e) { throw e; } } } } /// <summary> /// 新增重载,执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="connection">sqlconnection对象</param> /// <param name="trans">sqltransaction事务</param> /// <param name="sqlstring">计算查询结果语句</param> /// <returns>查询结果(object)</returns> public static object getsingle(sqlconnection connection, sqltransaction trans, string sqlstring, params sqlparameter[] cmdparms) { using (sqlcommand cmd = new sqlcommand()) { try { preparecommand(cmd, connection, trans, sqlstring, cmdparms); object obj = cmd.executescalar(); cmd.parameters.clear(); if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value))) { return null; } else { return obj; } } catch (system.data.sqlclient.sqlexception e) { trans.rollback(); throw e; } } } /// <summary> /// 执行查询语句,返回sqldatareader ( 注意:调用该方法后,一定要对sqldatareader进行close ) /// </summary> /// <param name="strsql">查询语句</param> /// <returns>sqldatareader</returns> public static sqldatareader executereader(string sqlstring, params sqlparameter[] cmdparms) { sqlconnection connection = new sqlconnection(connectionstring); sqlcommand cmd = new sqlcommand(); try { preparecommand(cmd, connection, null, sqlstring, cmdparms); sqldatareader myreader = cmd.executereader(commandbehavior.closeconnection); cmd.parameters.clear(); return myreader; } catch (system.data.sqlclient.sqlexception e) { throw e; } // finally // { // cmd.dispose(); // connection.close(); // } } /// <summary> /// 执行查询语句,返回dataset /// </summary> /// <param name="sqlstring">查询语句</param> /// <returns>dataset</returns> public static dataset query(string sqlstring, params sqlparameter[] cmdparms) { using (sqlconnection connection = new sqlconnection(connectionstring)) { sqlcommand cmd = new sqlcommand(); preparecommand(cmd, connection, null, sqlstring, cmdparms); using (sqldataadapter da = new sqldataadapter(cmd)) { dataset ds = new dataset(); try { da.fill(ds, "ds"); cmd.parameters.clear(); } catch (system.data.sqlclient.sqlexception ex) { throw new exception(ex.message); } return ds; } } } /// <summary> /// 新增重载,执行查询语句,返回dataset /// </summary> /// <param name="connection">sqlconnection对象</param> /// <param name="trans">sqltransaction事务</param> /// <param name="sqlstring">查询语句</param> /// <returns>dataset</returns> public static dataset query(sqlconnection connection, sqltransaction trans, string sqlstring, params sqlparameter[] cmdparms) { sqlcommand cmd = new sqlcommand(); preparecommand(cmd, connection, trans, sqlstring, cmdparms); using (sqldataadapter da = new sqldataadapter(cmd)) { dataset ds = new dataset(); try { da.fill(ds, "ds"); cmd.parameters.clear(); } catch (system.data.sqlclient.sqlexception ex) { trans.rollback(); throw new exception(ex.message); } return ds; } } private static void preparecommand(sqlcommand cmd, sqlconnection conn, sqltransaction trans, string cmdtext, sqlparameter[] cmdparms) { if (conn.state != connectionstate.open) conn.open(); cmd.connection = conn; cmd.commandtext = cmdtext; if (trans != null) cmd.transaction = trans; cmd.commandtype = commandtype.text;//cmdtype; if (cmdparms != null) { foreach (sqlparameter parameter in cmdparms) { if ((parameter.direction == parameterdirection.inputoutput || parameter.direction == parameterdirection.input) && (parameter.value == null)) { parameter.value = dbnull.value; } cmd.parameters.add(parameter); } } } #endregion #region 存储过程操作 /// <summary> /// 执行存储过程,返回sqldatareader ( 注意:调用该方法后,一定要对sqldatareader进行close ) /// </summary> /// <param name="storedprocname">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>sqldatareader</returns> public static sqldatareader runprocedure(string storedprocname, idataparameter[] parameters) { sqlconnection connection = new sqlconnection(connectionstring); sqldatareader returnreader; connection.open(); sqlcommand command = buildquerycommand(connection, storedprocname, parameters); command.commandtype = commandtype.storedprocedure; returnreader = command.executereader(commandbehavior.closeconnection); return returnreader; } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedprocname">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="tablename">dataset结果中的表名</param> /// <returns>dataset</returns> public static dataset runprocedure(string storedprocname, idataparameter[] parameters, string tablename) { using (sqlconnection connection = new sqlconnection(connectionstring)) { dataset dataset = new dataset(); connection.open(); sqldataadapter sqlda = new sqldataadapter(); sqlda.selectcommand = buildquerycommand(connection, storedprocname, parameters); sqlda.fill(dataset, tablename); connection.close(); return dataset; } } public static dataset runprocedure(string storedprocname, idataparameter[] parameters, string tablename, int times) { using (sqlconnection connection = new sqlconnection(connectionstring)) { dataset dataset = new dataset(); connection.open(); sqldataadapter sqlda = new sqldataadapter(); sqlda.selectcommand = buildquerycommand(connection, storedprocname, parameters); sqlda.selectcommand.commandtimeout = times; sqlda.fill(dataset, tablename); connection.close(); return dataset; } } /// <summary> /// 构建 sqlcommand 对象(用来返回一个结果集,而不是一个整数值) /// </summary> /// <param name="connection">数据库连接</param> /// <param name="storedprocname">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>sqlcommand</returns> private static sqlcommand buildquerycommand(sqlconnection connection, string storedprocname, idataparameter[] parameters) { sqlcommand command = new sqlcommand(storedprocname, connection); command.commandtype = commandtype.storedprocedure; foreach (sqlparameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以dbnull.value. if ((parameter.direction == parameterdirection.inputoutput || parameter.direction == parameterdirection.input) && (parameter.value == null)) { parameter.value = dbnull.value; } command.parameters.add(parameter); } } return command; } /// <summary> /// 执行存储过程,返回影响的行数 /// </summary> /// <param name="storedprocname">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="rowsaffected">影响的行数</param> /// <returns></returns> public static int runprocedure(string storedprocname, idataparameter[] parameters, out int rowsaffected) { using (sqlconnection connection = new sqlconnection(connectionstring)) { int result; connection.open(); sqlcommand command = buildintcommand(connection, storedprocname, parameters); rowsaffected = command.executenonquery(); result = (int)command.parameters["returnvalue"].value; //connection.close(); return result; } } /// <summary> /// 创建 sqlcommand 对象实例(用来返回一个整数值) /// </summary> /// <param name="storedprocname">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>sqlcommand 对象实例</returns> private static sqlcommand buildintcommand(sqlconnection connection, string storedprocname, idataparameter[] parameters) { sqlcommand command = buildquerycommand(connection, storedprocname, parameters); command.parameters.add(new sqlparameter("returnvalue", sqldbtype.int, 4, parameterdirection.returnvalue, false, 0, 0, string.empty, datarowversion.default, null)); return command; } #endregion }
上一篇: SrervletContext和文件下载