DBHelper类连接数据库
程序员文章站
2023-03-27 18:14:55
1、执行多条SQL语句,实现数据库事务 2、执行一条计算查询结果语句,返回查询结果(object) 3、执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close ) 4、执行查询语句,返回DataTable 5、执行查询语句,返回Data ......
1、执行多条sql语句,实现数据库事务
2、执行一条计算查询结果语句,返回查询结果(object)
3、执行查询语句,返回sqldatareader ( 注意:调用该方法后,一定要对sqldatareader进行close )
4、执行查询语句,返回datatable
5、执行查询语句,返回dataset
6、执行存储过程,返回sqldatareader ( 注意:调用该方法后,一定要对sqldatareader进行close )
7、执行存储过程,带参数
8、构建 sqlcommand 对象(用来返回一个结果集,而不是一个整数值)
9、执行存储过程,返回影响的行数
10、创建 sqlcommand 对象实例(用来返回一个整数值)
using system; using system.collections; using system.collections.specialized; using system.data; using system.data.sqlclient; using system.configuration; using system.data.common; using system.collections.generic; namespace dal { /// <summary> /// 数据访问抽象基础类 /// copyright (c) 2004-2008 by litianping /// </summary> public abstract class dbhelpersql { //数据库连接字符串(web.config来配置),可以动态更改connectionstring支持多数据库. //private const string connectionstring = "data source=.;initial catalog=数据库名称;user id=sa;password=数据库密码"; public static string connectionstring = configurationmanager.connectionstrings["data"].connectionstring; public dbhelpersql() { } #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 getmaxid(string fieldname, string tablename) { string strsql = "select max(" + fieldname + ")+1 from " + tablename; object obj = getsingle(strsql); if (obj == null) { return 1; } else { return int.parse(obj.tostring()); } } public static bool exists(string strsql) { object obj = 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 = 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 = 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; } } } } 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语句,实现数据库事务。 /// </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 (system.data.sqlclient.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 (system.data.sqlclient.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; } } /// <summary> /// 执行查询语句,返回pagedataset /// </summary> /// <param name="sqlstring">查询语句</param> /// <returns>dataset</returns> public static dataset pagequery(string sqlstring, int pageindex, int pagesize) { using (sqlconnection connection = new sqlconnection(connectionstring)) { dataset ds = new dataset(); try { connection.open(); sqldataadapter command = new sqldataadapter(sqlstring, connection); command.fill(ds, pageindex, pagesize, "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; } } #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 (system.data.sqlclient.sqlexception e) { 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 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> /// 执行查询语句,返回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> /// 执行查询语句,返回datatable /// </summary> /// <param name="sqlstring">查询语句</param> /// <returns>datatable</returns> public static datatable getdatatable(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.tables[0]; } } } /// <summary> /// 执行查询语句,返回dataset /// </summary> /// <param name="sqlstring">查询语句</param> /// <returns>dataset</returns> public static dataset pagequery(string sqlstring, int pageindex, int pagesize, 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,pageindex, pagesize, "ds"); cmd.parameters.clear(); } catch (system.data.sqlclient.sqlexception ex) { 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 } }