C#工具:Ado.Net SqlServer数据库 MySql数据库
程序员文章站
2022-10-06 16:19:17
数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库. SqlServer调用数据库 using System; using System.Collections.Generic; using System.Linq; using System.T ......
数据库连接字符串(web.config来配置),可以动态更改connectionstring支持多数据库.
sqlserver调用数据库
using system; using system.collections.generic; using system.linq; using system.text; using system.threading.tasks; using system.configuration; using system.data; using system.data.sqlclient; namespace dal { public static class dbhelper { public static string strcon = "data source=.;initial catalog=week3;integrated security=true"; /// <summary> /// 增删改 /// </summary> /// <param name="sql">sql语句</param> /// <param name="str">连接字符串</param> /// <returns></returns> public static int executenonquery(string sql) { //实例化连接对象 sqlconnection conn = new sqlconnection(strcon); int result = 0; try { conn.open(); //实例化命令对象 sqlcommand cmd = new sqlcommand(sql, conn); //执行命令 result = cmd.executenonquery(); } catch (exception) { throw; } finally { conn.close(); } return result; } /// <summary> /// 获取表格 /// </summary> /// <param name="sql">sql语句</param> /// <param name="str">连接字符串</param> /// <returns></returns> public static datatable getdatatable(string sql) { //实例化连接对象 sqlconnection conn = new sqlconnection(strcon); datatable dt = new datatable(); try { //实例化适配器 sqldataadapter sda = new sqldataadapter(sql, conn); sda.fill(dt); } catch (exception) { throw; } return dt; } /// <summary> /// /// </summary> /// <param name="sql">sql语句</param> /// <param name="str">连接字符串</param> /// <returns></returns> public static sqldatareader getdatareader(string sql) { //实例化连接对象 sqlconnection conn = new sqlconnection(strcon); sqldatareader sdr; try { conn.open(); //实例化命令对象 sqlcommand cmd = new sqlcommand(sql, conn); sdr = cmd.executereader(); } catch (exception) { throw; } return sdr; } /// <summary> /// 返回单行单列 /// </summary> /// <param name="sql">sql语句</param> /// <param name="str">连接字符串</param> /// <returns></returns> public static int executescalar(string sql) { //实例化连接对象 sqlconnection conn = new sqlconnection(strcon); int result = 0; try { conn.open(); //实例化命令对象 sqlcommand cmd = new sqlcommand(sql, conn); result = convert.toint32(cmd.executescalar()); } catch (exception) { throw; } finally { conn.close(); } return result; } /// <summary> /// 信息分页显示 /// </summary> /// <returns></returns> public static datatable getinfobypage(int pageindex, int pagesize) { string sql = string.format(@"select top {0}* from( select xinxi.*,row_number() over(order by xinxi.xinxiid) pid ,type.name from type inner join xinxi on type.id= xinxi.typeid ) as temp where temp.pid>{1}", pagesize, (pageindex - 1) * pagesize); return dbhelper.getdatatable(sql); } /// <summary> /// 获取个数 /// </summary> /// <returns></returns> public static int getcount() { string sql = "select count(xinxi.xinxiid) from type inner join xinxi on type.id= xinxi.typeid"; return dbhelper.executescalar(sql); } } }
sqlserver调用数据库存储过程
using system; using system.collections.generic; using system.data; using system.data.sqlclient; using system.linq; using system.web; namespace unit07_service { public class dbhelper { public static string connstr = "data source=.;initial catalog=db_news;integrated security=true"; public static sqlconnection cnn = new sqlconnection(connstr); /// <summary> /// 执行增删改的操作 /// </summary> /// <param name="sql">sql命令</param> /// <returns>受影响的行数</returns> public static int executenonquery(string sql) { open(); sqlcommand command = new sqlcommand(sql, cnn); int result = command.executenonquery(); cnn.close(); return result; } /// <summary> /// 查询单个值 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static object executescalar(string sql) { open(); sqlcommand command = new sqlcommand(sql, cnn); object result = command.executescalar(); cnn.close(); return result; } /// <summary> /// 返回数据表 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static datatable getdatatable(string sql) { sqldataadapter adapter = new sqldataadapter(sql, cnn); dataset ds = new dataset(); adapter.fill(ds); return ds.tables[0]; } /// <summary> /// 返回datareader对象,使用结束后,勿忘关闭datareader与数据库 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static sqldatareader getdatareader(string sql) { open(); sqlcommand command = new sqlcommand(sql, cnn); return command.executereader(); } /// <summary> /// 打开数据库 /// </summary> public static void open() { if (cnn.state == connectionstate.broken || cnn.state == connectionstate.open) { cnn.close(); } cnn.open(); } /// <summary> /// 打开数据库 /// </summary> public static void close() { cnn.close(); } /// <summary> /// 使用存储过程查询数据结果 /// </summary> /// <param name="sql"></param> /// <param name="paras"></param> /// <returns></returns> public static datatable getdatatable(string procname, sqlparameter[] paras = null) { open(); sqlcommand command = new sqlcommand(procname, cnn); command.commandtype = commandtype.storedprocedure; if (paras != null) { command.parameters.addrange(paras); } sqldataadapter adapter = new sqldataadapter(command); dataset ds = new dataset(); adapter.fill(ds); close(); return ds.tables[0]; } /// <summary> /// 使用存储过程执行增删改 /// </summary> /// <param name="sql"></param> /// <param name="paras"></param> /// <returns></returns> public static int executenonquery(string procname, sqlparameter[] paras) { open(); sqlcommand command = new sqlcommand(procname, cnn); command.commandtype = commandtype.storedprocedure; command.parameters.addrange(paras); int result = command.executenonquery(); close(); return result; } } }
mysql调用数据库
using system; using system.collections; using system.collections.specialized; using system.data; using mysql.data.mysqlclient; using system.configuration; using system.data.common; using system.collections.generic; namespace student_api.controllers { /// <summary> /// 数据访问抽象基础类 /// copyright (c) 2004-2008 by litianping /// </summary> public abstract class dbhelpermysql { //数据库连接字符串(web.config来配置),可以动态更改connectionstring支持多数据库. public static string connectionstring = "server=127.0.0.1;user id=root;pwd=root;database=06a_exam"; public dbhelpermysql() { } #region 公用方法 /// <summary> /// 得到最大值 /// </summary> /// <param name="fieldname"></param> /// <param name="tablename"></param> /// <returns></returns> 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()); } } /// <summary> /// 是否存在 /// </summary> /// <param name="strsql"></param> /// <returns></returns> 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> /// 是否存在(基于mysqlparameter) /// </summary> /// <param name="strsql"></param> /// <param name="cmdparms"></param> /// <returns></returns> public static bool exists(string strsql, params mysqlparameter[] 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 (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand(sqlstring, connection)) { try { connection.open(); int rows = cmd.executenonquery(); return rows; } catch (mysql.data.mysqlclient.mysqlexception e) { connection.close(); throw e; } } } } public static int executesqlbytime(string sqlstring, int times) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand(sqlstring, connection)) { try { connection.open(); cmd.commandtimeout = times; int rows = cmd.executenonquery(); return rows; } catch (mysql.data.mysqlclient.mysqlexception e) { connection.close(); throw e; } } } } /// <summary> /// 执行多条sql语句,实现数据库事务。 /// </summary> /// <param name="sqlstringlist">多条sql语句</param> public static int executesqltran(list<string> sqlstringlist) { using (mysqlconnection conn = new mysqlconnection(connectionstring)) { conn.open(); mysqlcommand cmd = new mysqlcommand(); cmd.connection = conn; mysqltransaction 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 (mysqlconnection connection = new mysqlconnection(connectionstring)) { mysqlcommand cmd = new mysqlcommand(sqlstring, connection); mysql.data.mysqlclient.mysqlparameter myparameter = new mysql.data.mysqlclient.mysqlparameter("@content", sqldbtype.ntext); myparameter.value = content; cmd.parameters.add(myparameter); try { connection.open(); int rows = cmd.executenonquery(); return rows; } catch (mysql.data.mysqlclient.mysqlexception 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 (mysqlconnection connection = new mysqlconnection(connectionstring)) { mysqlcommand cmd = new mysqlcommand(sqlstring, connection); mysql.data.mysqlclient.mysqlparameter myparameter = new mysql.data.mysqlclient.mysqlparameter("@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 (mysql.data.mysqlclient.mysqlexception 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 (mysqlconnection connection = new mysqlconnection(connectionstring)) { mysqlcommand cmd = new mysqlcommand(strsql, connection); mysql.data.mysqlclient.mysqlparameter myparameter = new mysql.data.mysqlclient.mysqlparameter("@fs", sqldbtype.image); myparameter.value = fs; cmd.parameters.add(myparameter); try { connection.open(); int rows = cmd.executenonquery(); return rows; } catch (mysql.data.mysqlclient.mysqlexception 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 (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand(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 (mysql.data.mysqlclient.mysqlexception e) { connection.close(); throw e; } } } } public static object getsingle(string sqlstring, int times) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand(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 (mysql.data.mysqlclient.mysqlexception e) { connection.close(); throw e; } } } } /// <summary> /// 执行查询语句,返回mysqldatareader ( 注意:调用该方法后,一定要对mysqldatareader进行close ) /// </summary> /// <param name="strsql">查询语句</param> /// <returns>mysqldatareader</returns> public static mysqldatareader executereader(string strsql) { mysqlconnection connection = new mysqlconnection(connectionstring); mysqlcommand cmd = new mysqlcommand(strsql, connection); try { connection.open(); mysqldatareader myreader = cmd.executereader(commandbehavior.closeconnection); return myreader; } catch (mysql.data.mysqlclient.mysqlexception e) { throw e; } } /// <summary> /// 执行查询语句,返回dataset /// </summary> /// <param name="sqlstring">查询语句</param> /// <returns>dataset</returns> public static dataset query(string sqlstring) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { dataset ds = new dataset(); try { connection.open(); mysqldataadapter command = new mysqldataadapter(sqlstring, connection); command.fill(ds, "ds"); } catch (mysql.data.mysqlclient.mysqlexception ex) { throw new exception(ex.message); } return ds; } } public static dataset query(string sqlstring, int times) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { dataset ds = new dataset(); try { connection.open(); mysqldataadapter command = new mysqldataadapter(sqlstring, connection); command.selectcommand.commandtimeout = times; command.fill(ds, "ds"); } catch (mysql.data.mysqlclient.mysqlexception 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 mysqlparameter[] cmdparms) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand()) { try { preparecommand(cmd, connection, null, sqlstring, cmdparms); int rows = cmd.executenonquery(); cmd.parameters.clear(); return rows; } catch (mysql.data.mysqlclient.mysqlexception e) { throw e; } } } } /// <summary> /// 执行多条sql语句,实现数据库事务。 /// </summary> /// <param name="sqlstringlist">sql语句的哈希表(key为sql语句,value是该语句的mysqlparameter[])</param> public static void executesqltran(hashtable sqlstringlist) { using (mysqlconnection conn = new mysqlconnection(connectionstring)) { conn.open(); using (mysqltransaction trans = conn.begintransaction()) { mysqlcommand cmd = new mysqlcommand(); try { //循环 foreach (dictionaryentry myde in sqlstringlist) { string cmdtext = myde.key.tostring(); mysqlparameter[] cmdparms = (mysqlparameter[])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是该语句的mysqlparameter[])</param> public static void executesqltranwithindentity(hashtable sqlstringlist) { using (mysqlconnection conn = new mysqlconnection(connectionstring)) { conn.open(); using (mysqltransaction trans = conn.begintransaction()) { mysqlcommand cmd = new mysqlcommand(); try { int indentity = 0; //循环 foreach (dictionaryentry myde in sqlstringlist) { string cmdtext = myde.key.tostring(); mysqlparameter[] cmdparms = (mysqlparameter[])myde.value; foreach (mysqlparameter q in cmdparms) { if (q.direction == parameterdirection.inputoutput) { q.value = indentity; } } preparecommand(cmd, conn, trans, cmdtext, cmdparms); int val = cmd.executenonquery(); foreach (mysqlparameter 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 mysqlparameter[] cmdparms) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand()) { 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 (mysql.data.mysqlclient.mysqlexception e) { throw e; } } } } /// <summary> /// 执行查询语句,返回mysqldatareader ( 注意:调用该方法后,一定要对mysqldatareader进行close ) /// </summary> /// <param name="strsql">查询语句</param> /// <returns>mysqldatareader</returns> public static mysqldatareader executereader(string sqlstring, params mysqlparameter[] cmdparms) { mysqlconnection connection = new mysqlconnection(connectionstring); mysqlcommand cmd = new mysqlcommand(); try { preparecommand(cmd, connection, null, sqlstring, cmdparms); mysqldatareader myreader = cmd.executereader(commandbehavior.closeconnection); cmd.parameters.clear(); return myreader; } catch (mysql.data.mysqlclient.mysqlexception 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 mysqlparameter[] cmdparms) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { mysqlcommand cmd = new mysqlcommand(); preparecommand(cmd, connection, null, sqlstring, cmdparms); using (mysqldataadapter da = new mysqldataadapter(cmd)) { dataset ds = new dataset(); try { da.fill(ds, "ds"); cmd.parameters.clear(); } catch (mysql.data.mysqlclient.mysqlexception ex) { throw new exception(ex.message); } return ds; } } } private static void preparecommand(mysqlcommand cmd, mysqlconnection conn, mysqltransaction trans, string cmdtext, mysqlparameter[] 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 (mysqlparameter parameter in cmdparms) { if ((parameter.direction == parameterdirection.inputoutput || parameter.direction == parameterdirection.input) && (parameter.value == null)) { parameter.value = dbnull.value; } cmd.parameters.add(parameter); } } } #endregion } }
mysql调用数据库存储过程
using system; using system.collections; using system.collections.specialized; using system.data; using mysql.data.mysqlclient; using system.configuration; using system.data.common; using system.collections.generic; namespace student_client { /// <summary> /// 数据访问抽象基础类 /// copyright (c) 2004-2008 by litianping /// </summary> public abstract class dbhelpermysql { //数据库连接字符串(web.config来配置),可以动态更改connectionstring支持多数据库. public static string connectionstring = "server=127.0.0.1;user id=root;pwd=root;database=06a_exam"; public dbhelpermysql() { } #region 公用方法 /// <summary> /// 得到最大值 /// </summary> /// <param name="fieldname"></param> /// <param name="tablename"></param> /// <returns></returns> 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()); } } /// <summary> /// 是否存在 /// </summary> /// <param name="strsql"></param> /// <returns></returns> 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> /// 是否存在(基于mysqlparameter) /// </summary> /// <param name="strsql"></param> /// <param name="cmdparms"></param> /// <returns></returns> public static bool exists(string strsql, params mysqlparameter[] 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 (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand(sqlstring, connection)) { try { connection.open(); int rows = cmd.executenonquery(); return rows; } catch (mysql.data.mysqlclient.mysqlexception e) { connection.close(); throw e; } } } } public static int executesqlbytime(string sqlstring, int times) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand(sqlstring, connection)) { try { connection.open(); cmd.commandtimeout = times; int rows = cmd.executenonquery(); return rows; } catch (mysql.data.mysqlclient.mysqlexception e) { connection.close(); throw e; } } } } /// <summary> /// 执行多条sql语句,实现数据库事务。 /// </summary> /// <param name="sqlstringlist">多条sql语句</param> public static int executesqltran(list<string> sqlstringlist) { using (mysqlconnection conn = new mysqlconnection(connectionstring)) { conn.open(); mysqlcommand cmd = new mysqlcommand(); cmd.connection = conn; mysqltransaction 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 (mysqlconnection connection = new mysqlconnection(connectionstring)) { mysqlcommand cmd = new mysqlcommand(sqlstring, connection); mysql.data.mysqlclient.mysqlparameter myparameter = new mysql.data.mysqlclient.mysqlparameter("@content", sqldbtype.ntext); myparameter.value = content; cmd.parameters.add(myparameter); try { connection.open(); int rows = cmd.executenonquery(); return rows; } catch (mysql.data.mysqlclient.mysqlexception 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 (mysqlconnection connection = new mysqlconnection(connectionstring)) { mysqlcommand cmd = new mysqlcommand(sqlstring, connection); mysql.data.mysqlclient.mysqlparameter myparameter = new mysql.data.mysqlclient.mysqlparameter("@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 (mysql.data.mysqlclient.mysqlexception 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 (mysqlconnection connection = new mysqlconnection(connectionstring)) { mysqlcommand cmd = new mysqlcommand(strsql, connection); mysql.data.mysqlclient.mysqlparameter myparameter = new mysql.data.mysqlclient.mysqlparameter("@fs", sqldbtype.image); myparameter.value = fs; cmd.parameters.add(myparameter); try { connection.open(); int rows = cmd.executenonquery(); return rows; } catch (mysql.data.mysqlclient.mysqlexception 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 (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand(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 (mysql.data.mysqlclient.mysqlexception e) { connection.close(); throw e; } } } } public static object getsingle(string sqlstring, int times) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand(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 (mysql.data.mysqlclient.mysqlexception e) { connection.close(); throw e; } } } } /// <summary> /// 执行查询语句,返回mysqldatareader ( 注意:调用该方法后,一定要对mysqldatareader进行close ) /// </summary> /// <param name="strsql">查询语句</param> /// <returns>mysqldatareader</returns> public static mysqldatareader executereader(string strsql) { mysqlconnection connection = new mysqlconnection(connectionstring); mysqlcommand cmd = new mysqlcommand(strsql, connection); try { connection.open(); mysqldatareader myreader = cmd.executereader(commandbehavior.closeconnection); return myreader; } catch (mysql.data.mysqlclient.mysqlexception e) { throw e; } } /// <summary> /// 执行查询语句,返回dataset /// </summary> /// <param name="sqlstring">查询语句</param> /// <returns>dataset</returns> public static dataset query(string sqlstring) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { dataset ds = new dataset(); try { connection.open(); mysqldataadapter command = new mysqldataadapter(sqlstring, connection); command.fill(ds, "ds"); } catch (mysql.data.mysqlclient.mysqlexception ex) { throw new exception(ex.message); } return ds; } } public static dataset query(string sqlstring, int times) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { dataset ds = new dataset(); try { connection.open(); mysqldataadapter command = new mysqldataadapter(sqlstring, connection); command.selectcommand.commandtimeout = times; command.fill(ds, "ds"); } catch (mysql.data.mysqlclient.mysqlexception 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 mysqlparameter[] cmdparms) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand()) { try { preparecommand(cmd, connection, null, sqlstring, cmdparms); int rows = cmd.executenonquery(); cmd.parameters.clear(); return rows; } catch (mysql.data.mysqlclient.mysqlexception e) { throw e; } } } } /// <summary> /// 执行多条sql语句,实现数据库事务。 /// </summary> /// <param name="sqlstringlist">sql语句的哈希表(key为sql语句,value是该语句的mysqlparameter[])</param> public static void executesqltran(hashtable sqlstringlist) { using (mysqlconnection conn = new mysqlconnection(connectionstring)) { conn.open(); using (mysqltransaction trans = conn.begintransaction()) { mysqlcommand cmd = new mysqlcommand(); try { //循环 foreach (dictionaryentry myde in sqlstringlist) { string cmdtext = myde.key.tostring(); mysqlparameter[] cmdparms = (mysqlparameter[])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是该语句的mysqlparameter[])</param> public static void executesqltranwithindentity(hashtable sqlstringlist) { using (mysqlconnection conn = new mysqlconnection(connectionstring)) { conn.open(); using (mysqltransaction trans = conn.begintransaction()) { mysqlcommand cmd = new mysqlcommand(); try { int indentity = 0; //循环 foreach (dictionaryentry myde in sqlstringlist) { string cmdtext = myde.key.tostring(); mysqlparameter[] cmdparms = (mysqlparameter[])myde.value; foreach (mysqlparameter q in cmdparms) { if (q.direction == parameterdirection.inputoutput) { q.value = indentity; } } preparecommand(cmd, conn, trans, cmdtext, cmdparms); int val = cmd.executenonquery(); foreach (mysqlparameter 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 mysqlparameter[] cmdparms) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand()) { 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 (mysql.data.mysqlclient.mysqlexception e) { throw e; } } } } /// <summary> /// 执行查询语句,返回mysqldatareader ( 注意:调用该方法后,一定要对mysqldatareader进行close ) /// </summary> /// <param name="strsql">查询语句</param> /// <returns>mysqldatareader</returns> public static mysqldatareader executereader(string sqlstring, params mysqlparameter[] cmdparms) { mysqlconnection connection = new mysqlconnection(connectionstring); mysqlcommand cmd = new mysqlcommand(); try { preparecommand(cmd, connection, null, sqlstring, cmdparms); mysqldatareader myreader = cmd.executereader(commandbehavior.closeconnection); cmd.parameters.clear(); return myreader; } catch (mysql.data.mysqlclient.mysqlexception 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 mysqlparameter[] cmdparms) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { mysqlcommand cmd = new mysqlcommand(); preparecommand(cmd, connection, null, sqlstring, cmdparms); using (mysqldataadapter da = new mysqldataadapter(cmd)) { dataset ds = new dataset(); try { da.fill(ds, "ds"); cmd.parameters.clear(); } catch (mysql.data.mysqlclient.mysqlexception ex) { throw new exception(ex.message); } return ds; } } } private static void preparecommand(mysqlcommand cmd, mysqlconnection conn, mysqltransaction trans, string cmdtext, mysqlparameter[] 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 (mysqlparameter 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> /// 执行带参数的存储过程 /// </summary> /// <param name="sqlstring"></param> /// <param name="cmdparms"></param> /// <returns></returns> public static object executeproc(string sqlstring, params mysqlparameter[] cmdparms) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand()) { try { preparecommand(cmd, connection, null, sqlstring, cmdparms); cmd.commandtype = commandtype.storedprocedure; int rows = cmd.executenonquery(); console.writeline("返回值为 " + cmdparms[1].value); cmd.parameters.clear(); return rows; } catch (mysql.data.mysqlclient.mysqlexception e) { connection.close(); throw e; } } } } /// <summary> /// 存储过程查询datatable /// </summary> /// <param name="sqlstring"></param> /// <param name="cmdparms"></param> /// <returns></returns> public static datatable queryproc(string sqlstring, params mysqlparameter[] cmdparms) { using (mysqlconnection connection = new mysqlconnection(connectionstring)) { using (mysqlcommand cmd = new mysqlcommand()) { try { preparecommand(cmd, connection, null, sqlstring, cmdparms); cmd.commandtype = commandtype.storedprocedure; mysqldataadapter adapter = new mysqldataadapter(cmd); datatable dt = new datatable(); adapter.fill(dt); cmd.parameters.clear(); return dt; } catch (mysql.data.mysqlclient.mysqlexception e) { connection.close(); throw e; } } } } #endregion } }
上一篇: AS3 程序延迟执行的方法分享
下一篇: php 读取shell管道传输过来的内容
推荐阅读
-
用C#对ADO.NET数据库完成简单操作的方法
-
.net(C#数据库访问) Mysql,Sql server,Sqlite,Access四种数据库的连接方式
-
使用数据库客户端工具Oracle SQL Developer加载第三方驱动连接mysql的方法
-
ADO.NET ORM数据库增删改查封装(工具一)
-
Oracle、mysql和sqlserver数据库中对于事务的不同理解
-
mysql数据库转移至sqlserver数据库中详解(mysql和sqlserver数据转移)
-
HeidiSQl使用方法(mysql数据库管理工具)
-
找到一种不错的从SQLServer转成Mysql数据库的方法
-
在项目中迁移MS SQLServer到Mysql数据库,实现MySQL数据库的快速整合
-
C#工具:Ado.Net SqlServer数据库 MySql数据库