自己写的数据库基类

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();
        }
    }
}

转载于:https://www.cnblogs.com/liping19851014/archive/2007/06/28/798302.html