sql helper

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

namespace _02使用md5登录
{
    public static class SqlHelper
    {
        private static readonly string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;

        #region 01执行增删改
        //执行增删改
        public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
        {
            int res = -1;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    con.Open();
                    res = cmd.ExecuteNonQuery();
                }
                return res;
            }
        } 
        #endregion


        #region 02返回单个值的方法
        //返回单个值的方法
        public static object ExecuteScalar(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    con.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }
        
        #endregion
        #region 03封装返回DataReader的方法
        //封装返回DataReader的方法
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
        {
            SqlConnection con = new SqlConnection(constr);
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                con.Open();

                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        } 
        #endregion

        #region 04封装一个执行DataTable的方法
        //封装一个执行DataTable的方法
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)
        {
            DataTable dt = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr))
            {
                if (pms != null)
                {
                    adapter.SelectCommand.Parameters.AddRange(pms);
                }
                adapter.Fill(dt);
            }
            return dt;
        } 
        #endregion
        #region 05执行分页的方法
        public static DataTable GetPageTable(int pageIndex, int pageSize, out int rowCount, out int pageCount, bool isDel)
        {
            DataTable dt = new DataTable();
            rowCount = 0;
            pageCount = 0;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                SqlDataAdapter dat = new SqlDataAdapter("up_GetPagedData2", conn);
                SqlParameter[] paras ={
                                     new SqlParameter("@pageIndex",pageIndex),
                                     new SqlParameter("@pageSize",pageSize),
                                     new SqlParameter("@rowCount",rowCount),
                                     new SqlParameter("@pageCount",pageCount),                                     
                                     new SqlParameter("@isDel",isDel)                                     
                                     };
                //将两个输出参数的输出方向指定
                paras[2].Direction = ParameterDirection.Output;
                paras[3].Direction = ParameterDirection.Output;
                //将参数集合加入到岔村命令对象中
                dat.SelectCommand.Parameters.AddRange(paras);
                //设置查询命令类型为存储过程
                dat.SelectCommand.CommandType = CommandType.StoredProcedure;
                //执行存储过程
                dat.Fill(dt);
                //执行完后，将存储过程中获得的两个输出参数值赋给此方法的两个输出参数
                rowCount = Convert.ToInt32(paras[2].Value);
                pageCount = Convert.ToInt32(paras[3].Value);

            }
            return dt;
        } 
        #endregion
    }
}