完美高效的数据库操作类(亲自编写)

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;
using System.Data.Sql;
using System.Collections;
using System.Web.Configuration;
/// <summary>
/// DataBaseOperator 的摘要说明
/// </summary>
public class DataBaseOperator
{
 public DataBaseOperator()
 {
  //
  // TODO: 在此处添加构造函数逻辑
  //
 }
    
    private static readonly string connectionStr =WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
   
   /// <summary>
   /// 返回一个dataSet
   /// </summary>
   /// <param name="sql"></param>
   /// <returns></returns>
    public static DataSet GetDataSet(string sql)
    {
            DataSet ds = new DataSet();
           using(SqlConnection con=new SqlConnection(connectionStr))
           {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlDataAdapter adapter = new SqlDataAdapter(sql,con);
                adapter.Fill(ds);
            }
            catch (SqlException ex)
            {
                if (con.State != ConnectionState.Closed)
                {
                    con.Close();
                    con.Dispose();
                }
                ds = null;
                throw ex;
            }
            finally
            {
                if (con.State != ConnectionState.Closed)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return ds;
           }
    }
    /// <summary>
    /// 返回一个dataTable
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataTable GetDataTable(string sql)
    {
        using(SqlConnection con= new SqlConnection(connectionStr))
        {
            DataTable dt = new DataTable();
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
            SqlDataAdapter adapter = new SqlDataAdapter(sql,con);
                adapter.Fill(dt);
            }
            catch (SqlException ex)
            {
                if (con.State != ConnectionState.Closed)
                {
                    con.Close();
                    con.Dispose();
                }
                dt = null;
                throw ex;
            }
            finally
            {
                if (con.State != ConnectionState.Closed)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return dt;   
    }
    }
    /// <summary>
    /// 返回一个datatable,需传递参数
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="cmdParms"></param>
    /// <returns></returns>
    public static DataTable GetDataTable(string sql, params SqlParameter[] cmdParms)
    {
        using( SqlConnection con=new SqlConnection(connectionStr))
        {
            DataTable dt = new DataTable();
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlDataAdapter adpter = new SqlDataAdapter();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = sql;
                if (cmdParms != null)
                {
                    foreach (SqlParameter para in cmdParms)
                    {
                        cmd.Parameters.Add(para);
                    }
                }
                adpter.SelectCommand = cmd;
                adpter.Fill(dt);
                return dt;
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                if (con.State != ConnectionState.Closed)
                {
                    con.Close();
                    con.Dispose();
                }
            }
        }
   
    }

    /// <summary>
    /// 返回DataReader
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static SqlDataReader GetDataReader(string sql)
    {
        SqlConnection con=new SqlConnection(connectionStr);
       
        SqlCommand cmd = new SqlCommand();
        SqlDataReader reader = null;
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            cmd.CommandText = sql;
            cmd.Connection = con;
            reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }
        catch (SqlException ex)
        {
            if (con.State != ConnectionState.Closed)
            {
                con.Close();
                con.Dispose();
            }
            reader = null;
            throw ex;
        }
       
    }

    private static void PrepareCommand( SqlConnection con, SqlCommand cmd, SqlTransaction trans, string cmdText, SqlParameter[] parameters)
    {
        cmd.Connection = con;
        cmd.CommandText = cmdText;
        if (trans != null)
        { cmd.Transaction = trans; }
        if (parameters != null)
        {
            foreach (SqlParameter parm in parameters)
            {
                cmd.Parameters.Add(parm);
            }
        }
    }
    /// <summary>
    /// 返回DataReader,需传递参数
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="cmdParms"></param>
    /// <returns></returns>
    public static SqlDataReader GetDataReader(string sql, params SqlParameter[] cmdParms)
    {
          SqlConnection con = new SqlConnection(connectionStr);
            SqlDataReader reader = null;
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = sql;
                if (cmdParms != null)
                {
                    foreach (SqlParameter para in cmdParms)
                    {
                        cmd.Parameters.Add(para);
                    }
                }
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch (SqlException e)
            {
                throw e;
            }
    }
    /// <summary>
    ///  返回操作是否成功
    /// </summary>
    /// <param name="SQLString"></param>
    /// <param name="cmdParms"></param>
    /// <returns></returns>
    public static bool ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
    {
        using (SqlConnection con = new SqlConnection(connectionStr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    con.Open();
                    PrepareCommand(con, cmd, null, SQLString, cmdParms);
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    con.Close();
                    con.Dispose();
                    return true;
                }
                catch (SqlException e)
                {
                    if (con.State != ConnectionState.Closed)
                    {
                        con.Close();
                        con.Dispose();
                    }
                    throw new Exception(e.ToString() + SQLString.ToString());
                }
            }
        }
    }
    public static bool GetExecute(string sql, params SqlParameter[] cmdParms)
    {
        bool flag = false;
        using (SqlConnection con = new SqlConnection(connectionStr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    cmd.Connection = con;
                    cmd.CommandText = sql;
                    if (cmdParms != null)
                    {
                        foreach (SqlParameter parm in cmdParms)
                        {
                            cmd.Parameters.Add(parm);
                        }
                    }

                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    flag = true;

                }
                catch (SqlException e)
                {
                    throw e;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }

            }
        }
        return flag;
    }

    public static bool ExecuteSqlFlag(string SQLString)
    {
        bool flag = false;
        using (SqlConnection con = new SqlConnection(connectionStr))
        {
            using (SqlCommand cmd = new SqlCommand(SQLString, con))
            {
                try
                {
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    int rows = cmd.ExecuteNonQuery();
                    flag = true;
                }
                catch (SqlException e)
                {
                    if (con.State != ConnectionState.Closed)
                    {
                        con.Close();
                        con.Dispose();
                    }

                    flag = false;
                    throw e;
                }
                finally
                {
                    if (con.State != ConnectionState.Closed)
                    {
                        con.Close();
                        con.Dispose();
                    }
                }

            }
        }
        return flag;
    }

    /// <summary>
    ///  批量删除，传递一个数组
    /// </summary>
    /// <param name="SQLStringList"></param>
    /// <returns></returns>
    public static bool ExecuteSqlTranFlag(ArrayList SQLStringList)
    {
        using (SqlConnection con = new SqlConnection(connectionStr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlTransaction trans = con.BeginTransaction();
                cmd.Transaction = trans;
                try
                {
                    for (int i = 0; i < SQLStringList.Count; i++)
                    {
                        string sql = SQLStringList[i].ToString();
                        if (sql.Trim().Length > 1)
                        {
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    trans.Commit();
                    return true;
                }
                catch (SqlException e)
                {
                    trans.Rollback();
                    throw e;
                }
                finally
                {
                    if (con.State != ConnectionState.Closed)
                    {
                        con.Close();
                        con.Dispose();
                    }
                }

            }
        }
    }

    /**/
    /// <summary>
    /// 将只返回一条记录的sql语句执行并且返回结果
    /// </summary>
    /// <param name="defaultValue">如果没有记录的话的默认值</param>
    /// <returns>返回结果为string</returns>

    public string GetOneResult(string sqlStr,string defaultValue)
    {
        string rr = "";
        SqlConnection con = new SqlConnection(connectionStr);    
        SqlDataReader dr=null;
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand cmd = new SqlCommand(sqlStr,con);
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            if (dr.HasRows)
            {
                dr.Read();
                rr = dr[0].ToString();
                dr.Close();
                con.Close();
                con.Dispose();
            }
        }
        catch
        {
            rr = "";
        }
        finally
        {
            if (con.State != ConnectionState.Closed)
            {
                con.Close();
                con.Dispose();
            }
        }
        if (rr == "")
            rr = defaultValue;
        return rr;
    }

    /// <summary>
    /// 执行返回bool的存储过程
    /// </summary>
    /// sqlStr 存储过程名
    /// <returns>是否执行成功</returns>
    public static bool ExecuteProcedure(string sqlStr)
    {
        using (SqlConnection con = new SqlConnection(connectionStr))
        {
            using (SqlCommand cmd = new SqlCommand(sqlStr,con))
            {
                try
                {
              
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.ExecuteNonQuery();
                 con.Close();
                 con.Dispose();
                 return true;
                }
                catch (SqlException e)
                {
                    throw e;
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
        }

    }
    /// <summary>
    ///  返回查询结果的第一行第一列
    /// </summary>
    /// <param name="SQLString"></param>
    /// <param name="cmdParms"></param>
    /// <returns></returns>
    public static object ExecuteScalar(string SQLString, params SqlParameter[] cmdParms)
    {
        object str;
        using (SqlConnection con = new SqlConnection(connectionStr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    cmd.Connection = con;
                    cmd.CommandText = SQLString;
                    if (cmdParms != null)
                    {
                        foreach (SqlParameter parm in cmdParms)
                        {
                            cmd.Parameters.Add(parm);
                        }
                    }
                   str= cmd.ExecuteScalar();
                   cmd.Parameters.Clear();
                }
                catch (SqlException e)
                {
                    throw e;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return str;
        }
    }
}