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

    }

}