//1.设置sqlHelper为静态类

  public static class SqlHelper

//2.设置连接字符串的app.config配置文件

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name ="mssqlserver" connectionString="Data source=.;initial catalog=sqldemos;user id=sa;password=123456;"/>
  </connectionStrings>
</configuration>

//3.添加引用 System.Configuration

//sqlHelper.cs

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


namespace _07城市列表和递归Treeview
{
    public static class SqlHelper
    {

        //1.设置sqlHelper为静态类
        //2.设置连接字符串的app.config配置文件
        //3.添加引用 System.Configuration 
        //3.5using System.Configuration;
        //4.配置constr语句(private static readonly 私有字段静态只读类型)
        private static readonly string conStr = ConfigurationManager.ConnectionStrings["mssqlserver"].ConnectionString;

        //5.封装 ExecuteNonQuery 用于增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();
                }
            }          
        }
        //6.封装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();
                }
            }           
        }
        //7.封装ExecuteReader 方法用于返回多个值
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
        {
        //reader方法不要将connect-using 否则在跳出静态类时，会close和dispose conn对象，造成外界无法调用reader对象
            SqlConnection conn = new SqlConnection(conStr);
            
                using (SqlCommand cmd = new SqlCommand(sql,conn))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    try//捕获sql发生错误，防止链接无法正常关闭
                    {
                        conn.Open();
                        //System.Data.CommandBehavior.CloseConnection这个枚举参数，表示将来使用完毕SqlDataReader之后，关闭Reader的同时，将Reader关联的Connection链接，也同时关闭，释放资源。
                        return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    }
                    catch
                    {
                        conn.Close();
                        conn.Dispose();
                        throw;//将异常传递出try-catch
                    }
                }
           
        }
 public static DataTable SqlDataAdapter(string sql, params SqlParameter[] pms)
        {
            DataTable dt = new DataTable();
            
            using (SqlDataAdapter da= new SqlDataAdapter(sql,conStr))
            {
                if (pms != null)
                {
                    da.SelectCommand.Parameters.AddRange(pms);//SqlDataAdapter里面只有一个SelectCommand可以直接用
                }
                da.Fill(dt);
            }
            return dt;
        }
    }//sqlhelper
}