分享一个自己在用的.net 中mysql事务控制类(支持多条sql，参数化，自定义判断条件,错误点返回等)
1)首先看下事务控制器.
using MySql.Data.MySqlClient; using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; namespace ConfigLab.DBUtility.MysqlTrans { /// <summary> /// 功能简介：Mysql的内部类,用于辅助控制批量的sql语句按照事务执行 /// 创建人：黄岛主(taohuadaozhu007@qq.com) /// 联系地址：http://www.cnblogs.com/taohuadaozhu /// 更新日志： /// 2020-4-16(pcw):增加了参数化的支持 /// </summary> public class TransactionController { public class Command { private string commendText = ""; public string CommendText { get { return commendText; } } private MySqlParameter[] _sqlParamsArray = null; /// <summary> /// 参数对象 /// </summary> public MySqlParameter[] SqlParamsArray { get { return this._sqlParamsArray; } } /// <summary> /// 期望的返回值,影响行数 /// </summary> private int expectReturn = 0; /// <summary> /// 实际返回值,影响行数 /// </summary> private int actualReturn = 0; /// <summary> /// true:严格要求实际返回值==期望返回值,false:要求实际返回值>期望返回值 /// </summary> private bool isRequestEqual = true; private string tipsOnError = ""; public string TipsOnError { get { return tipsOnError; } set { tipsOnError = value; } } /// <summary> /// 执行当前的Sql命令之后，实际返回的值，也就是影响行数 /// </summary> public int ActualReturn { get { return actualReturn; } set { actualReturn = value; } } private CommandType _sqlType = CommandType.Text; /// <summary> /// 一个Sql命令处理对象 /// </summary> /// <param name="sCommendText">执行的sql语句</param> /// <param name="iExpectReturn">期望返回值</param> /// <param name="bRequestEqual">true:严格要求实际返回值==期望返回值,false:要求实际返回值>期望返回值</param> public Command(string sCommendText, int iExpectReturn, bool bRequestEqual, string sTipsWhenError, CommandType sqlType) { this.commendText = sCommendText; this.expectReturn = iExpectReturn; this.isRequestEqual = bRequestEqual; this.tipsOnError = sTipsWhenError; this._sqlType = sqlType; } /// <summary> /// 一个Sql命令处理对象 /// </summary> /// <param name="sCommendText">执行的sql语句</param> /// <param name="iExpectReturn">期望返回值</param> /// <param name="bRequestEqual">true:严格要求实际返回值==期望返回值,false:要求实际返回值>期望返回值</param> public Command(string sCommendText, int iExpectReturn, bool bRequestEqual, string sTipsWhenError, MySqlParameter[] paramsArray, CommandType sqlType) { this.commendText = sCommendText; this.expectReturn = iExpectReturn; this.isRequestEqual = bRequestEqual; this.tipsOnError = sTipsWhenError; this._sqlParamsArray = paramsArray; this._sqlType = sqlType; } /// <summary> /// 检查是否执行成功 /// </summary> /// <returns></returns> public bool IsSuccess() { if (this.isRequestEqual) return (actualReturn == expectReturn); return (actualReturn > expectReturn); } public CommandType SqlType { get { return this._sqlType; } } } private List<Command> listCommand = null; public List<Command> ListCommand { get { if (listCommand == null) listCommand = new List<Command>(); return listCommand; } } public void Clear() { if (this.listCommand != null) this.listCommand.Clear(); } public int GetSumActualReturn() { int iSumActualReturn = 0; if (listCommand == null) return 0; for (int i = 0; i < listCommand.Count; i++) { iSumActualReturn += listCommand[i].ActualReturn; } return iSumActualReturn; } public bool IsSuccess() { if (listCommand == null) return false; for (int i = 0; i < listCommand.Count; i++) { if (listCommand[i].IsSuccess() == false) return false; } return true; } /// <summary> /// 获得事务执行失败处的信息 /// </summary> /// <returns></returns> public string GetErrorPointMssage() { if (listCommand == null) return ""; for (int i = 0; i < listCommand.Count; i++) { if (listCommand[i].IsSuccess() == false) return string.Format("{0}(code：{1})", listCommand[i].TipsOnError, listCommand[i].ActualReturn); } return ""; } public string GetErrorPointSql() { if (listCommand == null) return ""; for (int i = 0; i < listCommand.Count; i++) { if (listCommand[i].IsSuccess() == false) { #region//打印 调试语句 string sSqlDebug = ""; try { if (listCommand[i].SqlParamsArray != null && listCommand[i].SqlParamsArray.Length > 0) { sSqlDebug = listCommand[i].CommendText; foreach (MySqlParameter pam in listCommand[i].SqlParamsArray) { sSqlDebug = sSqlDebug.Replace($"?{pam.ParameterName}", $"'{pam.Value}'"); } } else { return listCommand[i].CommendText; } } catch (Exception ex) { } return sSqlDebug; #endregion//打印调试语句 } } return ""; } } }
2)基于事务控制器的事务执行。
/// <summary> /// 功能备注：用事务的方式执行多条SQL语句 /// 创建人：黄岛主(taohuadaozhu007@qq.com) /// 联系地址：http://www.cnblogs.com/taohuadaozhu /// 更新日志： /// 2020-4-16(ww):修复了多条语句中出现相同变量导致重复声明，导致异常的问题。 /// 修复了存储过程类型的sql中没有设置CommondType /// </summary> /// <param name="trans_IsolationLevel">事务隔离级别设置，可以传入:IsolationLevel.ReadCommitted</param> /// <returns></returns> public void RunSQLWithTransaction(ConfigLab.DBUtility.MysqlTrans.TransactionController theTransactionTool, IsolationLevel trans_IsolationLevel, string sSQLConnectstr) { if (theTransactionTool == null || theTransactionTool.ListCommand.Count == 0) return; int iExcuteCount = 0; using (MySqlConnection conn = new MySqlConnection(sSQLConnectstr)) { if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed) conn.Open(); using (MySqlTransaction tran = conn.BeginTransaction(trans_IsolationLevel)) { using (MySqlCommand sqlcm = new MySqlCommand()) { if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken) conn.Open(); sqlcm.Connection = conn; sqlcm.Transaction = tran; try { for (int i = 0; i < theTransactionTool.ListCommand.Count; i++) { //PrepareCommand(sqlcm, conn, null,CommondType.Text, theTransactionTool.ListCommand[i].CommendText, null); PrepareCommand(sqlcm, conn, null, theTransactionTool.ListCommand[i].SqlType, theTransactionTool.ListCommand[i].CommendText, theTransactionTool.ListCommand[i].SqlParamsArray);//debugbypcw@2020-5-17 theTransactionTool.ListCommand[i].ActualReturn = sqlcm.ExecuteNonQuery(); if (theTransactionTool.ListCommand[i].IsSuccess() == false) { tran.Rollback(); sqlcm.Dispose(); if (conn.State == ConnectionState.Open) conn.Close(); break; } else { if (sqlcm.Parameters != null)//addbypcw@2020-5-16 { sqlcm.Parameters.Clear();//否则容易造成多个语句的参数重名就错误.. addbyww@2020-4-16 } } iExcuteCount++; } if (theTransactionTool.IsSuccess()) { tran.Commit(); } } catch (Exception ex) { tran.Rollback(); ConfigLab.Utils.SaveLog(string.Format("执行脚本【{0}】失败，并执行事务回滚:{1}", theTransactionTool.ListCommand[iExcuteCount].CommendText, ex.Message)); } finally { sqlcm.Dispose(); tran.Dispose(); if (conn.State == ConnectionState.Open) conn.Close(); } } } } }
3)附件如下.
https://files.cnblogs.com/files/taohuadaozhu/MySqlHelper.rar
using System;using System.Collections.Generic;using System.Text;using MySql.Data;using MySql.Data.MySqlClient;using System.Data;using System.Collections;using ConfigLab.Comp;using ConfigLab.Comp.DataUtils;
namespace ConfigLab.DBUtility{ /// <summary> /// /// </summary> public class MySQLHelper { //数据库连接字符串 //public string Conn = "Database='wp';Data Source='localhost';User Id='root';Password='root';charset='utf8';pooling=true"; /// <summary> /// 用于mysql的连接字符串 /// </summary> private string _connStr = ""; public MySQLHelper(string sConnStr) { this.Init(sConnStr); } /// <summary> /// 初始化 /// </summary> /// <param name="sConnStr"></param> private void Init(string sConnStr) { this._connStr = sConnStr; } /// <summary> /// 功能备注：用事务的方式执行多条SQL语句 /// 创建人：黄岛主(taohuadaozhu007@qq.com) /// 联系地址：http://www.cnblogs.com/taohuadaozhu /// 更新日志： /// 2020-4-16(ww):修复了多条语句中出现相同变量导致重复声明，导致异常的问题。 /// 修复了存储过程类型的sql中没有设置CommondType /// </summary> /// <param name="trans_IsolationLevel">事务隔离级别设置，可以传入:IsolationLevel.ReadCommitted</param> /// <returns></returns> public void RunSQLWithTransaction(ConfigLab.DBUtility.MysqlTrans.TransactionController theTransactionTool, IsolationLevel trans_IsolationLevel, string sSQLConnectstr) { if (theTransactionTool == null || theTransactionTool.ListCommand.Count == 0) return; int iExcuteCount = 0; using (MySqlConnection conn = new MySqlConnection(sSQLConnectstr)) { if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed) conn.Open(); using (MySqlTransaction tran = conn.BeginTransaction(trans_IsolationLevel)) { using (MySqlCommand sqlcm = new MySqlCommand()) { if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken) conn.Open(); sqlcm.Connection = conn; sqlcm.Transaction = tran; try { for (int i = 0; i < theTransactionTool.ListCommand.Count; i++) { //PrepareCommand(sqlcm, conn, null,CommondType.Text, theTransactionTool.ListCommand[i].CommendText, null); PrepareCommand(sqlcm, conn, null, theTransactionTool.ListCommand[i].SqlType, theTransactionTool.ListCommand[i].CommendText, theTransactionTool.ListCommand[i].SqlParamsArray);//debugbypcw@2020-5-17 theTransactionTool.ListCommand[i].ActualReturn = sqlcm.ExecuteNonQuery(); if (theTransactionTool.ListCommand[i].IsSuccess() == false) { tran.Rollback(); sqlcm.Dispose(); if (conn.State == ConnectionState.Open) conn.Close(); break; } else { if (sqlcm.Parameters != null)//addbypcw@2020-5-16 { sqlcm.Parameters.Clear();//否则容易造成多个语句的参数重名就错误.. addbyww@2020-4-16 } } iExcuteCount++; } if (theTransactionTool.IsSuccess()) { tran.Commit(); } } catch (Exception ex) { tran.Rollback(); ConfigLab.Utils.SaveLog(string.Format("执行脚本【{0}】失败，并执行事务回滚:{1}", theTransactionTool.ListCommand[iExcuteCount].CommendText, ex.Message)); } finally { sqlcm.Dispose(); tran.Dispose(); if (conn.State == ConnectionState.Open) conn.Close(); } } } } } public void RunSQLWithTransaction(ConfigLab.DBUtility.MysqlTrans.TransactionController theTransactionTool) { RunSQLWithTransaction(theTransactionTool, IsolationLevel.ReadCommitted, this._connStr); } /// <summary> /// 给定连接的数据库用假设参数执行一个sql命令（不返回数据集） /// </summary> /// <param name="connectionString">一个有效的连接字符串</param> /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>执行命令所影响的行数</returns> private int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { MySqlCommand cmd = new MySqlCommand(); using (MySqlConnection conn = new MySqlConnection(connectionString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = -1;//-1则是sql异常 try { val = cmd.ExecuteNonQuery(); } catch (Exception e) { //Utils.SaveLog(string.Format("执行【{0}】出错，调试信息：【{1}】", cmdText, e.StackTrace)); Utils.SaveErrorLog(string.Format("[优化资源释放.2019-8-8]执行【{0}】出错，参数【{1}】调试跟踪：【{2}】【{3}】", cmdText, this.GetParamsInfo(commandParameters),e.Message, e.StackTrace)); } finally { if (cmd != null) { cmd.Parameters.Clear(); cmd.Dispose();//addbypcw@2019-8-8 } if (conn != null) { conn.Close(); } } return val; } } /// <summary> /// 执行存储过程，并且 /// </summary> /// <param name="connectionString">一个有效的连接字符串</param> /// <param name="cmdText">存储过程的调用</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>执行命令所影响的行数</returns> private void ExecuteNonQueryProcWithOutputParams(string connectionString, string cmdText, params MySqlParameter[] mysqlParamsArray) { int iRunStatus = 0; this.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, cmdText, mysqlParamsArray); } /// <summary> /// 执行存储过程，并且 /// </summary> /// <param name="connectionString">一个有效的连接字符串</param> /// <param name="cmdText">存储过程的调用</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>执行命令所影响的行数</returns> public void ExecuteNonQueryProcWithOutputParams(string cmdProcCall,params MySqlParameter[] mysqlParamsArray) { this.ExecuteNonQueryProcWithOutputParams(this._connStr, cmdProcCall,mysqlParamsArray); } /// <summary> /// 给定连接的数据库用假设参数执行一个sql命令（不返回数据集） /// </summary> /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>执行命令所影响的行数</returns> public int ExecuteNonQuery(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { return this.ExecuteNonQuery(this._connStr, cmdType, cmdText, commandParameters); }
/// <summary> /// 给定连接的数据库用假设参数执行一个sql命令（不返回数据集） /// </summary> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>执行命令所影响的行数</returns> public int ExecuteNonQuery(string cmdText, params MySqlParameter[] commandParameters) { return this.ExecuteNonQuery(CommandType.Text, cmdText, commandParameters); } /// <summary> /// 用现有的数据库连接执行一个sql命令（不返回数据集） /// </summary> /// <param name="connection">一个现有的数据库连接</param> /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>执行命令所影响的行数</returns> private int ExecuteNonQuery(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) {
MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; }
/// <summary> ///使用现有的SQL事务执行一个sql命令（不返回数据集） /// </summary> /// <remarks> ///举例: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="trans">一个现有的事务</param> /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>执行命令所影响的行数</returns> public int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { int val = -1; using (MySqlCommand cmd = new MySqlCommand()) { try { PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.Dispose();//addbypcw@2019-8-8 return val; } catch(Exception ex) { Utils.SaveErrorLog("ExecuteNonQuery.执行事务异常", ex); } } return val; }
/// <summary> /// 用执行的数据库连接执行一个返回数据集的sql命令 /// </summary> /// <remarks> /// 举例: /// MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">一个有效的连接字符串</param> /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>包含结果的读取器</returns> private MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { //创建一个MySqlCommand对象 MySqlCommand cmd = new MySqlCommand(); //创建一个MySqlConnection对象 MySqlConnection conn = new MySqlConnection(connectionString);
//在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， //因此commandBehaviour.CloseConnection 就不会执行 try { //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); //调用 MySqlCommand 的 ExecuteReader 方法 MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); //清除参数 cmd.Parameters.Clear(); return reader; } catch(Exception e) { //关闭连接，抛出异常 conn.Close(); Utils.SaveLog(string.Format("执行【{0}】出错，调试信息：【{1}】", cmdText, e.StackTrace)); throw e; } }
/// <summary> /// 返回DataSet /// </summary> /// <param name="connectionString">一个有效的连接字符串</param> /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns></returns> private DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { //创建一个MySqlCommand对象 MySqlCommand cmd = new MySqlCommand(); MySqlDataAdapter adapter = new MySqlDataAdapter(); //创建一个MySqlConnection对象 MySqlConnection conn = new MySqlConnection(connectionString); //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， DataSet ds = null; try { //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); //调用 MySqlCommand 的 ExecuteReader 方法 adapter.SelectCommand = cmd; ds = new DataSet(); adapter.Fill(ds); //清除参数 cmd.Parameters.Clear(); //adapter.Dispose();//addbypcw@2019-8-8 //cmd.Dispose();//addbypcw@2019-8-8 } catch (Exception e) { Utils.SaveErrorLog(string.Format("执行【{0}】出错，参数【{1}】调试信息：【{2}】,异常消息：【{3}】", cmdText, this.GetParamsInfo(commandParameters), e.StackTrace,e.Message)); } finally { if(conn!=null) { conn.Close(); } if (adapter != null) { try { adapter.Dispose(); } catch(Exception ex) { Utils.SaveErrorLog("释放adapter资源异常", ex); } } if (cmd != null) { try { cmd.Dispose(); } catch(Exception ex) { Utils.SaveErrorLog("释放cmd资源异常", ex); } } } return ds; } private string GetParamsInfo( MySqlParameter[] commandParameters) { StringBuilder sbParams = new StringBuilder(""); try { if (commandParameters != null && commandParameters.Length > 0) { foreach (MySqlParameter p in commandParameters) { sbParams.AppendFormat("({0}:{1})", p.ParameterName, p.Value.ToString()); } } } catch(Exception error){ Utils.SaveErrorLog(string.Format("获取参数时的异常：{0}", error.Message)); } return sbParams.ToString(); } /// <summary> /// 返回DataTable /// </summary> /// <param name="connectionString">一个有效的连接字符串</param> /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns></returns> private DataTable GetDataTable(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { DataSet dsResult = GetDataSet(connectionString, cmdType, cmdText, commandParameters); if (dsResult == null || dsResult.Tables == null || dsResult.Tables.Count == 0) return null; return dsResult.Tables[0]; } /// <summary> /// 返回DataTable /// </summary> /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns></returns> public DataTable GetDataTable( CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { return this.GetDataTable(this._connStr, cmdType, cmdText, commandParameters); }
/// <summary> /// 返回DataTable /// </summary> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns></returns> public DataTable GetDataTable(string cmdText, params MySqlParameter[] commandParameters) { return this.GetDataTable(CommandType.Text, cmdText, commandParameters); } /// <summary> /// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列 /// </summary> /// <remarks> ///例如: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> ///<param name="connectionString">一个有效的连接字符串</param> /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> private object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { MySqlCommand cmd = new MySqlCommand(); try { using (MySqlConnection connection = new MySqlConnection(connectionString)) { PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); #region//释放连接及命令对象//addbypcw@2019-8-8 if (cmd != null) { cmd.Dispose(); } if (connection != null) { connection.Close(); } #endregion return val; } } catch(Exception ex) { Utils.SaveErrorLog(string.Format("执行sql={0}失败", cmdText),ex); } finally { if (cmd != null) { cmd.Dispose(); } } return null; }
/// <summary> /// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列 /// </summary> /// <remarks> ///例如: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> public object ExecuteScalar(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { return this.ExecuteScalar(this._connStr, cmdType, cmdText, commandParameters); } /// <summary> /// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列 /// </summary> /// <remarks> ///例如: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> public object ExecuteScalar(string cmdText, params MySqlParameter[] commandParameters) { return ExecuteScalar(CommandType.Text,cmdText, commandParameters); } /// <summary> /// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列 /// </summary> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> public int ExecuteScalarWithIntReturn(string cmdText, params MySqlParameter[] commandParameters) { object obj = this.ExecuteScalar(cmdText, commandParameters); if (obj == null || obj.ToString().Trim().Length == 0) return int.MinValue; int iReturn = int.MinValue; if (int.TryParse(obj.ToString(), out iReturn)) return iReturn; return int.MinValue; } /// <summary> /// 用指定的数据库连接执行一个命令并返回一个数据集的第一列 /// </summary> /// <remarks> /// 例如: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">一个存在的数据库连接</param> /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> private object ExecuteScalar(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) {
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; }
public bool ExistTableName(string sTableName) { MySqlParameter[] arrayParams = new MySqlParameter[] { new MySqlParameter(){ DbType=DbType.String, ParameterName="tablename", Value=sTableName} }; DataTable dtResult = this.GetDataTable(string.Format("SELECT table_name FROM information_schema.TABLES WHERE table_name =?tablename"), arrayParams); return (dtResult!=null&&dtResult.Rows.Count>0); } /// <summary> /// 准备执行一个命令 /// </summary> /// <param name="cmd">sql命令</param> /// <param name="conn">OleDb连接</param> /// <param name="trans">OleDb事务</param> /// <param name="cmdType">命令类型例如 存储过程或者文本</param> /// <param name="cmdText">命令文本,例如:Select * from Products</param> /// <param name="cmdParms">执行命令的参数</param> private void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, 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 = cmdType;
if (cmdParms != null&&cmdParms.Length>0) { foreach (MySqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } /// <summary> /// 检查指定表是否存在指定的列 /// </summary> /// <param name="sTableName"></param> /// <param name="sColumnName"></param> /// <returns></returns> public bool ExistColumn(string sDBName,string sTableName, string sColumnName) { string sSql = string.Format("show columns from {0}.`{1}` like '{2}' ", sDBName, sTableName, sColumnName); DataTable resultDT = this.GetDataTable(sSql, null); if (resultDT == null || resultDT.Rows.Count == 0) return false; return true; } /// <summary> /// /// </summary> /// <param name="sDBName"></param> /// <param name="sTableName"></param> /// <param name="sColumnName">例如：QQBlob</param> /// <param name="sColumnTypeAndConstraint">例如：blob not null</param> public void AlterTableColumn(string sDBName,string sTableName,string sColumnName,string sColumnTypeAndConstraint) { //alter table qqgroup.`table_343` add column QQBlob blob not null; string sSql = string.Format("alter table {0}.`{1}` add column {2} {3};",sDBName,sTableName,sColumnName,sColumnTypeAndConstraint); this.ExecuteNonQuery(sSql, null); Utils.SaveLog(string.Format("执行了【{0}】修改列语句",sSql)); } }}
