标准化SQLHelp数据库通用访问类(全)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace SQLDB
{
public class SqlHelp
{ //定义链接数据库字符串
private static string connString = ConfigurationManager.ConnectionStrings[“connString”].ToString();
//定义conn
private static SqlConnection conn;
//定义cmd
private static SqlCommand cmd;
//实例化DataSet
private static DataSet ds = new DataSet();
#region 链接模型下标准化的数据通用访问类的三个方法
///
/// 返回第一行第一列数据
///
/// SQL语句
/// 返回第一行第一列数据
public static object GetExecuteScalar(string sql)
{
//1.实例化conn
conn = new SqlConnection(connString);
//2.实例化cmd
cmd = new SqlCommand(sql, conn);
//3执行
try
{
//4.打开
conn.Open();
//5.返回
return cmd.ExecuteScalar();

        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            //6.关闭
            conn.Close();
        }
    }
    /// <summary>
    /// 读取所有数据到DataReader
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <returns>返回所有数据到DataReader</returns>
    public static SqlDataReader GetExecuteReader(string sql)
    {
        //实例化conn
        conn = new SqlConnection(connString);
        //实例化cmd
        cmd = new SqlCommand(sql, conn);
        //执行
        try
        {
            conn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);

        }
        catch (Exception ex)
        {

            throw ex;
        }
    }
    /// <summary>
    /// 执行增删改
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <returns>返回增删改受影响的行数整数类型</returns>
    public static int GetExecuteNonQuery(string sql)
    {
        //实例化conn
        conn = new SqlConnection(connString);
        //实例化cmd
        cmd = new SqlCommand(sql, conn);
        //执行
        try
        {
            conn.Open();
            return cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            conn.Close();
        }
    }

    #endregion
    #region 非链接模型下的数据通用访问类的两种方法
    /// <summary>
    /// 获取DataSet的Table中没有名称
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <returns>返回没有表名称的DataSet</returns>
    public static DataSet GetDataSetNotTableName(string sql)
    {
        //实例化conn
        conn = new SqlConnection(connString);
        //实例化CMD
        cmd = new SqlCommand(sql, conn);
        //执行
        try
        {
            conn.Open();
            //实例化dataReader
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            //填充到DataSet
            sda.Fill(ds);

        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            conn.Close();
        }
        return ds;
    }
    /// <summary>
    /// 获得所有信息到DataSet内存数据库
    /// </summary>
    /// <param name="dic">键值对《键{表名称}，值{查询语句}>》</param>
    /// <param name="KeyName">主键的列</param>
    /// <returns>返回带有主键和Table表名称的内存数据库</returns>
    public static DataSet GetDataSet(Dictionary<string, string> dic, string KeyName)
    {
        //实例化conn
        conn = new SqlConnection(connString);
        //执行
        try
        {
            foreach (KeyValuePair<string, string> item in dic)
            {
                cmd = new SqlCommand(item.Value, conn);
                //实例化DataAdapter
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                //定义sda的类型
                sda.SelectCommand = cmd;
                //实例化commandB 记录DataSet的操作过程，包括增删改
                SqlCommandBuilder scb = new SqlCommandBuilder(sda);
                //打开
                conn.Open();
                //填充DataSet
                sda.Fill(ds, item.Key);
                //定义表的主键
                ds.Tables[item.Key].PrimaryKey = new DataColumn[] { ds.Tables[item.Key].Columns[KeyName] };
            }
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            conn.Close();
        }
        //返回
        return ds;
    }
    #endregion
    #region 链接模型下标准化的带有参数数组的数据通用访问类(重载)
    /// <summary>
    /// 参数化SQL语句的获得获得第一行第一列的数据
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="para">SQL参数数组</param>
    /// <returns>返回object类型</returns>
    public static object GetExecuteScalar(string sql, SqlParameter[] para)
    {
        //实例化conn
        conn = new SqlConnection(connString);
        //实例化cmd
        cmd = new SqlCommand(sql, conn);
        //执行
        try
        {
            //打开
            conn.Open();
            if (para != null)//如果参数数组不为空的话
            {
                //执行参数数组
                cmd.Parameters.AddRange(para);
            }
            return cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            conn.Close();
        }
    }
    /// <summary>
    /// 参数化SQL语句的获取所有数据的链接模型
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="para">参数数组</param>
    /// <returns>返回DataReader</returns>
    public static SqlDataReader GetExecuteReader(string sql, SqlParameter[] para)
    {
        //实例化conn
        conn = new SqlConnection(connString);
        //实例化cmd
        cmd = new SqlCommand(sql, conn);
        //执行
        try
        {
            conn.Open();
            if (para != null)//如果参数数组不为空的话
            {
                //执行参数数组
                cmd.Parameters.AddRange(para);
            }
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {

            throw ex;
        }
    }
    /// <summary>
    /// 参数化SQL语句的执行增删改
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="para">参数数组</param>
    /// <returns>返回受影响的行数</returns>
    public static int GetExecuteNonQuery(string sql, SqlParameter[] para)
    {
        //实例化conn
        conn = new SqlConnection(connString);
        //实例化cmd
        cmd = new SqlCommand(sql, conn);
        //执行
        try
        {
            //打开
            conn.Open();
            if (para != null)//如果参数数组不为空的话
            {
                //执行参数数组
                cmd.Parameters.AddRange(para);
            }
            //返回所有数据
            return cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            conn.Close();
        }
    }
    #endregion
    #region 使用存储过程执行的数据通用访问类的方法
    /// <summary>
    /// 调用存储过程执行增删改
    /// </summary>
    /// <param name="procedureName">SQL中存储过程的名称</param>
    /// <param name="para">参数数组</param>
    /// <returns>返回受影响的行数，整数类型</returns>
    public static int GetExecuteNonQueryByProcedure(string procedureName, SqlParameter[] para)
    {
        //实例化conn
        conn = new SqlConnection(connString);
        //实例化cmd
        cmd = new SqlCommand();
        //指定cmd的链接对象
        cmd.Connection = conn;
        //指定cmd的类型为存储过程
        cmd.CommandType = CommandType.StoredProcedure;
        //指定SQL中存储过程的名称
        cmd.CommandText = procedureName;
        //执行
        try
        {
            //打开链接
            conn.Open();
            //判断参数数组是否为空
            if (para != null)
            {
                //读取添加存储过程的数组
                cmd.Parameters.AddRange(para);
            }
            return cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            //关闭链接
            conn.Close();
        }
    }
    /// <summary>
    /// 调用存储过程获取单个结果集
    /// </summary>
    /// <param name="procedureName">SQL中存储过程的名称</param>
    /// <param name="para">参数的数组</param>
    /// <returns>返回object的类型</returns>
    public static Object GetExecuteScalarByProcedure(string procedureName, SqlParameter[] para)
    {
        //实例化conn
        conn = new SqlConnection(connString);
        //实例化cmd
        cmd = new SqlCommand();
        //指定cmd的链接对象
        cmd.Connection = conn;
        //指定cmd的类型为存储过程
        cmd.CommandType = CommandType.StoredProcedure;
        //指定SQL中存储过程的名称
        cmd.CommandText = procedureName;
        //执行
        try
        {
            //打开链接
            conn.Open();
            //判断参数数组是否为空
            if (para != null)
            {
                //读取添加存储过程的数组
                cmd.Parameters.AddRange(para);
            }
            return cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            //关闭链接
            conn.Close();
        }
    }
    /// <summary>
    /// 调用存储过程获取多个结果集
    /// </summary>
    /// <param name="procedureName">SQL中存储过程的名称</param>
    /// <param name="para">参数数组</param>
    /// <returns>返回多个结果集SqlDataReader</returns>
    public static SqlDataReader GetExecuteReaderByProcedure(string procedureName, SqlParameter[] para)
    {
        //实例化conn
        conn = new SqlConnection(connString);
        //实例化cmd
        cmd = new SqlCommand();
        //指定cmd的链接对象
        cmd.Connection = conn;
        //指定cmd的类型为存储过程
        cmd.CommandType = CommandType.StoredProcedure;
        //指定SQL中存储过程的名称
        cmd.CommandText = procedureName;
        //执行
        try
        {
            //打开链接
            conn.Open();
            //判断参数数组是否为空
            if (para != null)
            {
                //读取添加存储过程的数组
                cmd.Parameters.AddRange(para);
            }
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {

            throw ex;
        }

    }
    #endregion
    #region 执行SQL事务的通用访问的方法
    /// <summary>
    /// 执行SQL事务，将多条SQL语句同时更新数据库
    /// </summary>
    /// <param name="SqlList">SQL语句的集合</param>
    /// <returns>如果事务执行成功返回True,否则为fales</returns>
    public static bool UpdataToSQLByTran(List<string> SqlList)
    {
        //实例化conn
        SqlConnection conn = new SqlConnection(connString);
        //实例化cmd
        SqlCommand cmd = new SqlCommand();
        //执行cmd的链接
        cmd.Connection = conn;
        //异常处理
        try
        {
            //打开链接
            conn.Open();
            //开始事务
            cmd.Transaction = conn.BeginTransaction();
            //循环遍历所有的SQL语句，并一次执行
            foreach (string item in SqlList)
            {
                //执行的cmd的执行类型
                cmd.CommandText = item;
                //执行cmd
                cmd.ExecuteNonQuery();
            }
            //如果全部执行完毕，提交事务
            cmd.Transaction.Commit();
            //执行成功返回True
            return true;

        }
        //异常
        catch (Exception ex)
        {
            //判断是不是由于事务运行中导致的，如果不是Null,表示事务运行中的错误，就需要回滚
            if (cmd.Transaction == null)
            {
                //发生错误回滚事务
                cmd.Transaction.Rollback();
            }
            //抛出错误
            throw ex;
        }
        //无论是否执行成功，都将释放事务资源，关闭conn的链接
        finally
        {
            //如果事务资源没有被释放，那么释放资源
            if (cmd.Transaction != null)
            {
                cmd.Transaction = null;
            }
            //关闭链接
            conn.Close();
        }

    }
    #endregion

}

}