using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace _001

{

    public static class SqlHelper

    {

        //定义一个连接字符串,

        //readonly，只能在初始化和构造函数中赋值

        private static readonly string conStr=ConfigurationManager.ConnectionStrings["mssqlserver"].ConnectionString;

        //1.执行增insert 删delete 改update的方法;

        public static int ExecuteNonQuery(string sql,params SqlParameter[] pms)

        {

            using (SqlConnection conn = new SqlConnection(conStr))

            {

                using (SqlCommand cmd = new SqlCommand(sql, conn))

                {

                    if (pms != null)

                    {

                        cmd.Parameters.AddRange(pms);

                    }

                    conn.Open();

                    return cmd.ExecuteNonQuery();

                }

            }

        }

        //2.执行查询，返回单个值的方法

        //ExecuteScalar()

        public static object ExecuteScalar(string sql, params SqlParameter[] pms)

        {

            using (SqlConnection conn = new SqlConnection(conStr))

            {

                using (SqlCommand cmd = new SqlCommand(sql, conn))

                {

                    if (pms != null)

                    {

                        cmd.Parameters.AddRange(pms);

                    }

                    conn.Open();

                    return cmd.ExecuteScalar();

                }

            }

        }

        //3.执行查询，返回多行，多列的方法

        //ExecuteReader

        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)

        {

            SqlConnection conn = new SqlConnection(conStr);

                using (SqlCommand cmd = new SqlCommand(sql, conn))

                {

                    if (pms != null)

                    {

                        cmd.Parameters.AddRange(pms);

                    }

                    try

                    {

                        conn.Open();

                        return cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    }

                    catch

                    {

                        conn.Close();

                        conn.Dispose();

                        throw;

                    }

                }

        }

    }

}