简单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 SqlHelper
{
    /// <summary>
    /// 封装Sql连接和查询方法
    /// </summary>
    public static class SqlHelper
    {
        //1.从app.config中获取连接字符串
        private static readonly string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

        //2.ExecuteNonQuery
        /// <summary>
        /// 执行增、删、改用到的方法
        /// </summary>
        /// <param name="sql">带参数的Sql语句</param>
        /// <param name="cmdType">SqlCommand执行类型，CommandType.Text为SQL语句执行类型， CommandType.StoredProcedure为调用存储过程</param>
        /// <param name="pms">可变的Sql参数数组</param>
        /// <returns>返回影响行数</returns>
        public static int ExecuteNonQuery(string sql,CommandType cmdType, params SqlParameter[] pms)
        {
            //2.1 创建SqlConnection 连接对象
            using (SqlConnection conn = new SqlConnection(constr))
            {
                //2.2创建SqlCommand Sql执行对象
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    //设置执行类型
                    cmd.CommandType = cmdType;
                    //2.3加入参数
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    //2.4打开连接
                    conn.Open();
                    //2.5执行sql语句并返回值--影响行数
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        //3.ExecuteScalar
        /// <summary>
        /// 执行查，返回单个值的方法
        /// </summary>
        /// <param name="sql">带参数的Sql语句</param>
        /// <param name="cmdType">SqlCommand执行类型，CommandType.Text为SQL语句执行类型， CommandType.StoredProcedure为调用存储过程</param>
        /// <param name="pms">可变的Sql参数数组</param>
        /// <returns>返回单个值</returns>
        public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            //3.1创建连接对象SqlConnection
            using (SqlConnection conn = new SqlConnection(constr))
            {
                //3.2创建Sql执行对象SqlCommand
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    //设置执行类型
                    cmd.CommandType = cmdType;
                    //3.3加入参数
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    //3.4打开连接
                    conn.Open();
                    //3.5执行Sql语句并返回单个值
                    return cmd.ExecuteScalar();
                }
            }
        }

        //4.ExecuteReader
        /// <summary>
        /// 执行插，返回SqlDataReader，可以逐条读取数据
        /// </summary>
        /// <param name="sql">带参数的Sql语句</param>
        /// <param name="cmdType">SqlCommand执行类型，CommandType.Text为SQL语句执行类型， CommandType.StoredProcedure为调用存储过程</param>
        /// <param name="pms">可变的Sql参数数组</param>
        /// <returns>返回结果集</returns>
        public static SqlDataReader ExecuteReader(String sql, CommandType cmdType, params SqlParameter[] pms)
        {
            //3.1创建不自动关闭的连接对象
            SqlConnection conn = new SqlConnection(constr);
            //3.2创建自动关闭的SqlCommand
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                //设置执行类型
                cmd.CommandType = cmdType;
                //3.3加入参数
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                //3.4打开连接
                conn.Open();
                //3.5执行Sql语句并返回集合
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//数据库连接随着SqlDataReader的关闭而关闭
            }
        }

        //5.ExecuteAdapter
        /// <summary>
        /// 加载结果集到内存中
        /// </summary>
        /// <param name="sql">带参数的Sql语句</param>
        /// <param name="cmdType">SqlCommand执行类型，CommandType.Text为SQL语句执行类型， CommandType.StoredProcedure为调用存储过程</param>
        /// <param name="pms">可变的Sql参数数组</param>
        /// <returns>返回数据表DataTable</returns>
        public static DataTable ExecuteAdapter(string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            //创建DataTable
            DataTable dt = new DataTable();
            //5.1执行ExecuteAdapter
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr))
            {
                //设置执行类型
                adapter.SelectCommand.CommandType = cmdType;
                //加入参数
                if (pms != null)
                {
                    adapter.SelectCommand.Parameters.AddRange(pms);
                }
                //填充数据表DataTable
                adapter.Fill(dt);
            }
            return dt;
        }
    }
}