c#经典三层框架中的SqlHelper帮助类

 

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

namespace DAL
{
    class SqlHelper
    { 
        //获取字符串
        static string constr = ConfigurationManager.ConnectionStrings["JDNew"].ConnectionString;
        //创建连接
        static SqlConnection conn = new SqlConnection(constr);
        /// <summary>
        /// //打开连接，关闭连接。
        /// </summary>
        public static void ConnOpen()
        {
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
        }
        public static void ConnClose()
        {
            if (conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql, params object[] param)
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            for (int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.AddWithValue("@" + i, param[i]);
            }
            SqlDataReader sdr = cmd.ExecuteReader();
            conn.Close();
            return sdr;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(string sql, params object[] param)
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            for (int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.AddWithValue("@" + i, param[i]);
            }
            int j = cmd.ExecuteNonQuery();
            if (j > 0)
            {
                conn.Close();
                return true;
            }
            else
            {
                conn.Close();
                return false;
            }
        }
        /// <summary>
        /// 【存储过程】
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery_Pro(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            SqlCommand cmd = new SqlCommand(sql, conn) { CommandType = commandType };
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
            conn.Open();
            int count = cmd.ExecuteNonQuery();
            //conn.Close();
            if (count > 0)
            {
                conn.Close();
                return true;
            }
            else
            {
                conn.Close();
                return false;
            }
        }
        /// <summary>
        /// 数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataTable DataTable(string sql, params object[] param)
        {
            conn.Open();
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            for (int i = 0; i < param.Length; i++)
            {
                sda.SelectCommand.Parameters.AddWithValue("@" + i, param[i]);
            }
            DataTable dt = new DataTable();
            sda.Fill(dt);
            conn.Close();
            return dt;
        }
        /// <summary>
        /// 第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params object[] param)
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            for (int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.AddWithValue("@" + i, param[i]);
            }
            object obj = cmd.ExecuteScalar();
            conn.Close();
            return obj;
        }

        /// <summary>
        /// 事务处理
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="count"></param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static bool Transaction(string sql, int count, params object[] parm)
        {
            ConnOpen();
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (parm != null)
            {
                for (int i = 0; i < parm.Length; i++)
                {
                    cmd.Parameters.AddWithValue("@" + i, parm[i]);
                }
            }

            //开启事务
            cmd.Transaction = conn.BeginTransaction();//利用连接对象 获取开启的事务赋值给命令对象 开启事务
            int result = 0;//定义一个变量来获取 执行成功的个数
            try
            {
                result = cmd.ExecuteNonQuery();//正常执行
            }
            catch
            {
                //如果出现异常代表执行没有成功
                cmd.Transaction.Rollback();//如果没成功，回到原点
                ConnClose();
                return false;
            }
            if (result == count)
            {
                //说明执行成功了 才可以想数据库中提交数据
                cmd.Transaction.Commit();
                ConnClose();
                return true;
            }
            else
            {
                //没有成功 回到原点
                cmd.Transaction.Rollback();
                ConnClose();
                return false;
            }
        }
        /// <summary>
        /// 分页，限制起始索引
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="index"></param>
        /// <param name="MaxCount"></param>
        /// <param name="tablename"></param>
        /// <param name="pm"></param>
        /// <returns></returns>
        public static DataSet DataSet(string sql, int index, int MaxCount, string tablename, params object[] pm)
        {
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            for (int i = 0; i < pm.Length; i++)
            {
                sda.SelectCommand.Parameters.AddWithValue("@" + i, pm[i]);
            }
            DataSet ds = new DataSet();
            sda.Fill(ds, index, MaxCount, tablename);
            conn.Close();
            return ds;
        }

    }
}