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



namespace hr_DAL
{
    public class SqlHelper
    {
        //连接对象和命令对象
        private static SqlConnection conn;
        private static SqlCommand cmd;
        private static string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        /// <summary>
        /// 构造函数,实例化一个空的SqlHelper对象
        /// </summary>
        public SqlHelper()
        { 
        }
        #region 共有方法
        /// <summary>
        ///方法:实例化连接对象
        /// </summary>
        private static void con()
        {
            conn = new SqlConnection(constr);
        }
        /// <summary>
        /// 方法:打开连接,实例化命令对象
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static SqlCommand ExtCommand(string sql)
        {
            con();
            //创建一个connection对象打开时,
            //就会创建一个连接池,连接池和连接字符串完全匹配.
            //如果一个连接对象的连接字符串和连接池完全匹配,就会把该连接对象放到匹配的池中,
            //如果不完全匹配,则再创建一个连接池
            conn.Open();
            
            cmd = conn.CreateCommand();//将连接给命令对象
            cmd.CommandText = sql;
            return cmd;
 
        }

        /// <summary>
        /// 方法:关闭连接,释放资源
        /// </summary>
        private static void CloseConn()
        {
            if(conn!=null)//连接池是否为null,是否占用连接
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();//关闭了连接,
                    cmd.Dispose();//清空了连接字符串,即连接池
                    conn.Dispose();
                }
            }


        }
        #endregion
        /// <summary>
        /// 方法:得到执行结果的首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string GetOne(string sql)
        {
            ExtCommand(sql);
           string result= (string) cmd.ExecuteScalar();
           CloseConn();
           return result;
        }

        /// <summary>
        /// 方法:用SqlDataAdapter得到一个表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetTable(string sql)
        {
            ExtCommand(sql);
            SqlDataAdapter sda = new SqlDataAdapter();
            //将cmd命令对象交给适配器对象
            sda.SelectCommand = ExtCommand(sql);
            DataSet ds = new DataSet();
            sda.Fill(ds,"table");
            CloseConn();
            return ds.Tables["table"];


            #region SqlDataAdapter的使用方法
            //SqlConnection conn = new SqlConnection(constr);
            //SqlCommand cmd = new SqlCommand("select * from product", conn);

            //SqlDataAdapter sdat = new SqlDataAdapter(cmd);
            //DataSet ds = new DataSet();

            //sdat.Fill(ds, "p");
            //dataGridView1.DataSource = ds.Tables["p"];
            #endregion

        }
        /// <summary>
        /// 方法:根据条件查询是否存在
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool Exists(string sql)
        {
            int i = int.Parse( GetOne(sql));
            if (i > 0)
                return true;
            else
                return false;

        }
        /// <summary>
        /// 方法:根据条件返回一个数据流
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql)
        {
            ExtCommand(sql);
            //CommandBehavior.CloseConnection表示数据流关闭,连接也关闭
            SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sdr;

        }
    }
}