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 = ; 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 = ; i < param.Length; i++)

            {

                cmd.Parameters.AddWithValue("@" + i, param[i]);

            }

            int j = cmd.ExecuteNonQuery();

            if (j > )

            {

                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 > )

            {

                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 = ; 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 = ; 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 = ; i < parm.Length; i++)

                {

                    cmd.Parameters.AddWithValue("@" + i, parm[i]);

                }

            }

            //开启事务

            cmd.Transaction = conn.BeginTransaction();//利用连接对象 获取开启的事务赋值给命令对象 开启事务

            int result = ;//定义一个变量来获取 执行成功的个数

            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 = ; 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;

        }

    }

}