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

namespace Reservation.DAL
{
    public class SqlHelper
    {
        /// <summary>
        /// 自定义数据库连接字符串
        /// </summary>
        public static string ConnString = @"Data Source=.;Initial Catalog=DB_Date_Plan; User ID=sa;Pwd=111111";
        /// <summary>
        /// 应用程序下获取连接字符串
        /// </summary>
        //public static string ConnString
        //{
        //    get {

        //        return System.Configuration.ConfigurationManager.ConnectionStrings["connstring"].ToString();
        //    }
        //}

        /// <summary>
        /// 执行一个Sql语句返回受影响的行数
        /// </summary>
        /// <param name="sql">insert,update,delete或相关的存储过程</param>
        /// <param name="type">命令类型：SQL语句还是存储过程</param>
        /// <param name="pars">SQL语句所需要的参数</param>
        public static int ExcuteSQLReturnInt(string sql, CommandType type, params SqlParameter[] pars)
        {
            //定义连接对象
            SqlConnection conn = new SqlConnection(ConnString);
            //判断连接对象的状态，并且打开
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {
                conn.Open();
            }
            try
            {
                //实例化命令对象
                SqlCommand cmd = new SqlCommand(sql, conn);
                //判断CommandType类型是否是SQL语句还是存储过程
                cmd.CommandType = type;
                if (pars != null && pars.Length > 0)
                {
                    foreach (SqlParameter p in pars)
                    {
                        cmd.Parameters.Add(p);
                    }
                }
                //调用方法执行SQL语句或存储过程
                int count = cmd.ExecuteNonQuery();
                return count;
            }
            catch (Exception ex)
            {
                return 0;
            }
            finally
            {
                //记得要关闭连接
                conn.Close();
            }
        }

        /// <summary>
        /// 执行一个Sql语句或存储过程，返回一条记录，sqldataReader
        /// </summary>
        /// <param name="sql">select语句，或相关的存储过程</param>
        /// <param name="type">指定命令类型，sql语句还是存储过程，默认的是sql语句</param>
        /// <param name="pars">参数的集合</param>
        /// <returns></returns>
        public static SqlDataReader ExcuteSqlReturnReader(string sql, CommandType type, SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (pars != null && pars.Length > 0)
            {
                foreach (SqlParameter p in pars)
                {
                    cmd.Parameters.Add(p);
                }
            }
            cmd.CommandType = type;
            //当reader.close(),也就是关闭了datareader时，CommandBehavior.CloseConnection 也会关闭连接！
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }

        /// <summary>
        /// 执行一个sql语句或存储过程,不带参数的!返回一天记录 sqldataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static SqlDataReader ExcuteSqlReturnReader(string sql, CommandType type)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = type;
            //当reader.close(),也就是关闭了datareader时，CommandBehavior.CloseConnection 也会关闭连接！
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }

        /// <summary>
        ///  执行一个Sql语句或存储过程，返回一个数据集，dataset
        /// </summary>
        /// <param name="sql">select语句或相关的存储过程</param>
        /// <param name="type">命令类型</param>
        /// <param name="pars">命令类型</param>
        /// <returns>DataSet</returns>
        public static DataSet SelectSqlReturnDataset(string sql, CommandType type, SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            //数据集识别器对象SqlDataAdapter 会自动打开数据库链接！
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            if (pars != null && pars.Length > 0)
            {
                foreach (SqlParameter p in pars)
                {
                    sda.SelectCommand.Parameters.Add(p);
                }
            }
            sda.SelectCommand.CommandType = type;
            DataSet ds = new DataSet();
            sda.Fill(ds);
            return ds;
        }

        /// <summary>
        /// 执行一个sql语句返回一个数据表对象
        /// </summary>
        /// <param name="sql">select</param>
        /// <param name="type">命令类型</param>
        /// <param name="pars">参数集合</param>
        /// <returns>DataTable</returns>
        public static DataTable SelectSqlReturnDataTable(string sql, CommandType type, SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            if (pars != null && pars.Length > 0)
            {
                foreach (SqlParameter p in pars)
                {
                    sda.SelectCommand.Parameters.Add(p);
                }
            }
            sda.SelectCommand.CommandType = type;
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }

        //*******************************************************************
        //SelectSqlReturnDataTable方法的重载，不传参数的情况下
        /// <summary>
        /// 执行一个sql语句返回一个数据表对象,不传参数
        /// </summary>
        /// <param name="sql">select</param>
        /// <param name="type">命令类型</param>
        /// <returns></returns>
        public static DataTable SelectSqlReturnDataTable(string sql, CommandType type)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            sda.SelectCommand.CommandType = type;
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }

        /// <summary>
        /// 执行一个sql语句或相关的存储过程，返回一个值
        /// </summary>
        /// <param name="sql">select count(*) from tablename where ....</param>
        /// <param name="type">命令类型</param>
        /// <param name="pars">所需的参数</param>
        /// <returns>object</returns>
        public static object selectSqlReturnObject(string sql, CommandType type, SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {
                conn.Open();
            }
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = type;
                if (pars != null && pars.Length > 0)
                {
                    foreach (SqlParameter p in pars)
                    {
                        cmd.Parameters.Add(p);
                    }
                }
                object obj = cmd.ExecuteScalar();
                return obj;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 执行一个sql语句或相关的存储过程，返回一个值，不传参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static object selectSqlReturnObject(string sql, CommandType type)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {
                conn.Open();
            }
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = type;
                object obj = cmd.ExecuteScalar();
                return obj;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

    }
}