using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; /// <summary> /// MySqlHelper 的摘要说明 /// </summary> public class MySqlHelper { //private SqlConnection Conn; private SqlCommand Comm; private SqlDataAdapter da; private DataSet ds; private DataTable dt; private SqlDataReader dr; private SqlTransaction trans; public static string ConnString = System.Configuration.ConfigurationSettings.AppSettings["ConnString"]; public static SqlConnection Conn() { SqlConnection Conn = new SqlConnection(ConnString); return Conn; } // // TODO: 在此处添加构造函数逻辑 // /// <summary>无参数命令或存储过程构造MySqlCommand对象</summary> /// <param name="commandTextOrProcedureName">Sql命令或存储过程名</param> /// <param name="commandType">类型(Sql命令或存储过程)</param> /// private SqlCommand buildSqlCommand(string commandTextOrProcedureName, CommandType commandType) { try { Comm = new SqlCommand(commandTextOrProcedureName, Conn()); Comm.CommandType = commandType; return Comm; } catch (Exception error) { throw error; } } /// <summary>在新的MySqlConnection中以无参数命令或存储过程构造MySqlCommand对象</summary> /// <param name="commandTextOrProcedureName">Sql命令或存储过程名</param> /// <param name="commandType">类型(Sql命令或存储过程)</param> /// <param name="newConn">新的MySqlConnection对象</param> private SqlCommand buildSqlCommand(string commandTextOrProcedureName, CommandType commandType, SqlConnection newConn) { try { Comm = new SqlCommand(commandTextOrProcedureName, newConn); Comm.CommandType =commandType; return Comm; } catch (Exception error) { throw error; } } / <summary>有参数命令或存储过程构造MySqlCommand对象</summary> / <param name="commandTextOrProcedureName">Sql命令或存储过程名</param> / <param name="parameters">SqlParameter参数数组</param> / <param name="commandType">类型(Sql命令或存储过程)</param> private SqlCommand buildSqlCommand(string commandTextOrProcedureName, SqlParameter[] parameters, CommandType commandType) { try { Comm = new SqlCommand(commandTextOrProcedureName,Conn()); Comm.CommandType = commandType; foreach (SqlParameter parameter in parameters) { Comm.Parameters.Add(parameter); } return Comm; } catch (Exception error) { throw error; } } / <summary>在新的MySqlConnection中以有参数命令或存储过程构造MySqlCommand对象</summary> / <param name="commandTextOrProcedureName">Sql命令或存储过程名</param> / <param name="parameters">MySqlParameter参数数组</param> / <param name="commandType">类型(Sql命令或存储过程)</param> / <param name="newConn">新的MySqlConnection对象</param> private SqlCommand buildSqlCommand(string commandTextOrProcedureName, SqlParameter[] parameters, CommandType commandType, SqlConnection newConn) { try { Comm = new SqlCommand(commandTextOrProcedureName, newConn); Comm.CommandType = commandType; foreach (SqlParameter parameter in parameters) { Comm.Parameters.Add(parameter); } return Comm; } catch (Exception error) { throw error; } } ///**/ / <summary>执行无参数查询命令或存储过程,返回第一行第一列的值</summary> / <param name="commandTextOrProcedureName">Sql命令或存储过程名</param> / <param name="commandType">类型(Sql命令或存储过程)</param> / <param name="isNewConnection">是否使用新的连接执行</param> / <returns>返回第一行第一列的值,如果结果为null则返回空字符串</returns> private string ExecuteScalar(string commandTextOrProcedureName, CommandType commandType, bool isNewConnection) { string result = ""; if (isNewConnection) { using (SqlConnection newConn = new SqlConnection(MySqlHelper.ConnString)) { try { Comm = new SqlCommand(commandTextOrProcedureName, newConn); newConn.Open(); if (Comm.ExecuteScalar() != null) result = Comm.ExecuteScalar().ToString(); } catch (Exception error) { throw error; } finally { newConn.Close(); } } } else { try { if (Conn().State == ConnectionState.Closed) { Conn().Open(); } Comm = this.buildSqlCommand(commandTextOrProcedureName, commandType); if (Comm.ExecuteScalar() != null) result = Comm.ExecuteScalar().ToString(); } catch (Exception error) { throw error; } finally { Conn().Close(); } return result; } return result; } ///**/ / <summary>执行有参数查询命令或存储过程,返回第一行第一列的值</summary> / <param name="commandTextOrProcedureName">Sql命令或存储过程名</param> / <param name="parameters">SqlParameter参数数组</param> / <param name="commandType">类型(Sql命令或存储过程)</param> / <param name="isNewConnection">是否使用新的连接执行</param> / <returns>返回第一行第一列的值,如果结果为null则返回空字符串</returns> protected string ExecuteScalar(string commandTextOrProcedureName, SqlParameter[] parameters, CommandType commandType, bool isNewConnection) { string result = ""; if (isNewConnection) { using (SqlConnection newConn = new SqlConnection(MySqlHelper.ConnString)) { try { Comm = this.buildSqlCommand(commandTextOrProcedureName, parameters, commandType, newConn); newConn.Open(); if (Comm.ExecuteScalar() != null) result = Comm.ExecuteScalar().ToString(); } catch (Exception error) { throw error; } finally { newConn.Close(); } } } else { try { if (Conn().State == ConnectionState.Closed) { Conn().Open(); } Comm = this.buildSqlCommand(commandTextOrProcedureName, parameters, commandType); if (Comm.ExecuteScalar() != null) result = Comm.ExecuteScalar().ToString(); } catch (Exception error) { throw error; } finally { Conn().Close(); } } return result; } // /// <summary>执行无参数命令或存储过程,返回受影响行数</summary> // /// <param name="commandTextOrProcedureName">Sql命令或存储过程名</param> // /// <param name="commandType">类型(Sql命令或存储过程)</param> // /// <param name="isNewConnection">是否使用新的连接执行</param> // /// <returns>返回受影响的行数</returns> protected int ExecuteNonQuery(string commandTextOrProcedureName, CommandType commandType, bool isNewConnection) { int result = 0; if (isNewConnection) { using (SqlConnection newConn = new SqlConnection(MySqlHelper.ConnString)) { try { Comm = this.buildSqlCommand(commandTextOrProcedureName, commandType, newConn); newConn.Open(); result = Comm.ExecuteNonQuery(); } catch (Exception error) { throw error; } finally { newConn.Close(); } } } else { try { if (Conn().State == ConnectionState.Closed) { Conn().Open(); } Comm = this.buildSqlCommand(commandTextOrProcedureName, commandType); result = Comm.ExecuteNonQuery(); } catch (Exception error) { throw error; } finally { Conn().Close(); } } return result; } ///**/ / <summary>执行有参数命令或存储过程,返回受影响行数</summary> / <param name="commandTextOrProcedureName">Sql命令或存储过程名</param> / <param name="parameters">SqlParameter参数数组</param> / <param name="commandType">类型(Sql命令或存储过程)</param> / <param name="isNewConnection">是否使用新的连接执行</param> / <returns>返回受影响的行数</returns> protected int ExecuteNonQuery(string commandTextOrProcedureName, SqlParameter[] parameters, CommandType commandType, bool isNewConnection) { int result = 0; if (isNewConnection) { using (SqlConnection newConn = new SqlConnection(MySqlHelper.ConnString)) { try { Comm = this.buildSqlCommand(commandTextOrProcedureName, parameters, commandType, newConn); newConn.Open(); result = Comm.ExecuteNonQuery(); } catch (Exception error) { throw error; } finally { newConn.Close(); } } } else { try { if (Conn().State == ConnectionState.Closed) { Conn().Open(); } Comm = this.buildSqlCommand(commandTextOrProcedureName, parameters, commandType); result = Comm.ExecuteNonQuery(); } catch (Exception error) { throw error; } finally { Conn().Close(); } } return result; } ///**/ / <summary>执行无参数查询命令或存储过程,返回SqlDataReader对象(注意用完后及时关闭MySqlDataReader对象)</summary> / <param name="commandTextOrProcedureName">Sql命令或存储过程名</param> / <param name="commandType">类型(Sql命令或存储过程)</param> / <param name="isNewConnection">是否使用新的连接执行</param> / <returns>MySqlDataReader(当MySqlDataReader对象关闭时对应的MySqlConnection对象将关闭)</returns> protected SqlDataReader ExecuteReader(string commandTextOrProcedureName, CommandType commandType, bool isNewConnection) { if (isNewConnection) { try { SqlConnection newConn = new SqlConnection(MySqlHelper.ConnString); Comm = this.buildSqlCommand(commandTextOrProcedureName, commandType, newConn); newConn.Open(); return Comm.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception error) { throw error; } } else { try { if (Conn().State == ConnectionState.Closed) { Conn().Open(); } Comm = this.buildSqlCommand(commandTextOrProcedureName, commandType); return Comm.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception error) { throw error; } } } / <summary>执行有参数查询命令或存储过程,返回SqlDataReader对象(注意用完后及时关闭MySqlDataReader对象)</summary> / <param name="commandTextOrProcedureName">Sql命令或存储过程名</param> / <param name="parameters">MySqlParameter参数数组</param> / <param name="commandType">类型(Sql命令或存储过程)</param> / <param name="isNewConnection">是否使用新的连接执行</param> / <returns>MySqlDataReader(当MySqlDataReader对象关闭时对应的MySqlConnection对象将关闭)</returns> protected SqlDataReader ExecuteReader(string commandTextOrProcedureName, SqlParameter[] parameters, CommandType commandType, bool isNewConnection) { if (isNewConnection) { try { SqlConnection newConn = new SqlConnection(MySqlHelper.ConnString); Comm = this.buildSqlCommand(commandTextOrProcedureName, parameters, commandType, newConn); newConn.Open(); return Comm.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception error) { throw error; } } else { try { if (Conn().State == ConnectionState.Closed) { Conn().Open(); } Comm = this.buildSqlCommand(commandTextOrProcedureName, parameters, commandType); return Comm.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception error) { throw error; } } } / <summary>执行无参数查询命令或存储过程,返回DataSet</summary> / <param name="commandTextOrProcedureName">Sql命令或存储过程名</param> / <param name="commandType">类型(Sql命令或存储过程)</param> / <param name="isNewConnection">是否用新的连接执行</param> / <returns></returns> public DataSet ExecuteDataSet(string commandTextOrProcedureName, CommandType commandType, bool isNewConnection) { if (isNewConnection) { using (SqlConnection newConn = new SqlConnection(MySqlHelper.ConnString)) { try { newConn.Open(); Comm = this.buildSqlCommand(commandTextOrProcedureName, commandType, newConn); da = new SqlDataAdapter(Comm); ds = new DataSet(); da.Fill(ds); return ds; } catch (Exception error) { throw error; } finally { newConn.Close(); } } } else { try { if (Conn().State == ConnectionState.Closed) { Conn().Open(); } Comm = this.buildSqlCommand(commandTextOrProcedureName, commandType); da = new SqlDataAdapter(Comm); ds = new DataSet(); da.Fill(ds); return ds; } catch (Exception error) { throw error; } finally { Conn().Close(); } } } / <summary>执行有参数查询命令或存储过程,返回DataSet</summary> / <param name="commandTextOrProcedureName">Sql命令或存储过程名</param> / <param name="parameters">SqlParameter参数数组</param> / <param name="commandType">类型(Sql命令或存储过程)</param> / <param name="isNewConnection">是否用新的连接执行</param> / <returns></returns> protected DataSet ExecuteDataSet(string commandTextOrProcedureName, SqlParameter[] parameters, CommandType commandType, bool isNewConnection) { if (isNewConnection) { using (SqlConnection newConn = new SqlConnection(MySqlHelper.ConnString)) { try { newConn.Open(); Comm = this.buildSqlCommand(commandTextOrProcedureName, parameters, commandType, newConn); da = new SqlDataAdapter(Comm); ds = new DataSet(); da.Fill(ds); return ds; } catch (Exception error) { throw error; } finally { newConn.Close(); } } } else { try { if (Conn().State == ConnectionState.Closed) { Conn().Open(); } Comm = this.buildSqlCommand(commandTextOrProcedureName, parameters, commandType); da = new SqlDataAdapter(Comm); ds = new DataSet(); da.Fill(ds); return ds; } catch (Exception error) { throw error; } finally { Conn().Close(); } } } ///**/ / <summary> / 事务处理 / </summary> / <param name="cmdArray">MySqlCommand数组</param> / <param name="isNewConnection">是否使用新的连接执行</param> protected void ExecuteTransaction(SqlCommand[] cmdArray, bool isNewConnection) { if (isNewConnection) { using (SqlConnection newConn = new SqlConnection(MySqlHelper.ConnString)) { try { newConn.Open(); trans = newConn.BeginTransaction(); foreach (SqlCommand c in cmdArray) { c.ExecuteNonQuery(); } trans.Commit(); } catch (Exception error) { trans.Rollback(); throw error; } finally { newConn.Close(); } } } else { try { if (Conn().State == ConnectionState.Closed) { Conn().Open(); } trans = Conn().BeginTransaction(); foreach (SqlCommand c in cmdArray) { c.ExecuteNonQuery(); } trans.Commit(); } catch (Exception error) { trans.Rollback(); throw error; } finally { Conn().Close(); } } } / <summary>执行有参数且返回值为Int型的存储过程,获取返回结果</summary> / <param name="commandTextOrProcedureName">存储过程名</param> / <param name="parameters">SqlParameter参数数组</param> / <returns>Int</returns> protected int ExecuteProcedure(string procedureName, SqlParameter[] parameters) { try { if (Conn().State == ConnectionState.Closed) { Conn().Open(); } Comm = this.buildSqlCommand(procedureName, parameters, CommandType.StoredProcedure); Comm.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, String.Empty, DataRowVersion.Default, null)); Comm.ExecuteNonQuery(); return (int)Comm.Parameters["ReturnValue"].Value; } catch (Exception error) { throw error; } finally { Conn().Close(); } } / 执行分页存储过程,存储过程的第一个参数必须为out用于输出记录总数 / </summary> / <remarks>parameters中勿使用表别名,SQL语法尽量标准</remarks> / <param name="rowsTotal">返回的记录总数</param> / <param name="procedureName">分页存储过程名称</param> / <param name="parameters">分页存储过程参数</param> / <returns></returns> protected DataSet ExecuteProcedure(out int rowsTotal, string procedureName, SqlParameter[] parameters) { try { if (Conn().State == ConnectionState.Closed) { Conn().Open(); } Comm = this.buildSqlCommand(procedureName, parameters, CommandType.StoredProcedure); ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(Comm); da.Fill(ds); rowsTotal = int.Parse(parameters[0].Value.ToString()); return ds; } catch (Exception error) { throw error; } finally { Conn().Close(); } } }