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

namespace SQLDB
{
public class SqlHelp
{
//链接数据库字符串
private static string connString = ConfigurationManager.ConnectionStrings[“connString”].ToString();
//定义conn
public static SqlConnection conn;
//定义全局变量
public static SqlDataAdapter sda;
//定义全局变量
public static SqlCommandBuilder scb;
//定义全局变量DataSet
public static DataSet ds;
//定义全局变量
public static SqlCommand cmd;
#region 标准化SQL语句的执行，包含连接模式和非链接模式
//===链接连接模型
///
/// 执行增删改
///
/// T-SQL语句
/// 受影响的行数
public static int GetExecuteNonQuery(string sql)
{
//实例化conn
conn = new SqlConnection(connString);
//实例化cmd
SqlCommand cmd = new SqlCommand(sql, conn);
//打开
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{

            throw ex;
        }
        finally
        {
            conn.Close();
        }

    }
    /// <summary>
    /// 查询第一行第一列
    /// </summary>
    /// <param name="sql">T-SQL语句</param>
    /// <returns>返回OBJECT类型</returns>
    public static object GetExecuteScalar(string sql)
    {//实例化conn
        conn = new SqlConnection(connString);
        //实例化cmd
        SqlCommand cmd = new SqlCommand(sql, conn);
        //执行
        try
        {
            conn.Open();
            return cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            conn.Close();
        }

    }
    /// <summary>
    /// 读取所有行数据
    /// </summary>
    /// <param name="sql">T-SQL语句</param>
    /// <returns>返回DataRead</returns>
    public static SqlDataReader GetExecuteReader(string sql)
    {
        //实例化conn
        conn = new SqlConnection(connString);
        //实例化cmd
        SqlCommand cmd = new SqlCommand(sql, conn);
        //执行
        try
        {
            //打开
            conn.Open();
            SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sdr;
        }
        catch (Exception ex)
        {

            throw ex;
        }
    }
    //========================非连接模型下的方法=========================
    /// <summary>
    /// 通过键值对的方式获得DataSet的Table带有表名称和主键
    /// </summary>
    /// <param name="TableKeyValues">Dictionary键值对《表名称,SQL语句》</param>
    /// <param name="TableKey">表的主键</param>
    /// <returns>返回带有主键和表名称的DataSet</returns>
    public static DataSet GetAllInfoToDataSet(Dictionary<string, string> TableKeyValues, string TableKey)
    {
        //实例化dataSet
        ds = new DataSet();
        //实例化conn
        conn = new SqlConnection(connString);
        try
        {
            //循环遍历dic获得键值对
            foreach (KeyValuePair<string, string> item in TableKeyValues)
            {
                //实例化cmd
                cmd = new SqlCommand(item.Value, conn);
                //实例化dataadapter
                sda = new SqlDataAdapter(cmd);
                //定义类型
                sda.SelectCommand = cmd;
                //实例化SqlcommandB
                scb = new SqlCommandBuilder(sda);
                //打开
                conn.Open();
                //填充数据
                sda.Fill(ds, item.Key);
                //为表添加主键
                ds.Tables[item.Key].PrimaryKey = new DataColumn[] { ds.Tables[item.Key].Columns[TableKey] };
            }
            return ds;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            conn.Close();
        }


    }
    //更新DataSet到数据库
    public static void UpdataStudentInfo(string TableName)
    {
        conn.Open();
        sda.Update(ds, TableName);
        conn.Close();
    }
    #endregion
    #region 参数化SQL语句执行
    /// <summary>
    /// 参数化SQL语句执行增删改
    /// </summary>SELECT SNO, SName, Gender, Birthday, Mobile, Email, HomeAddress, PhotoPath  FROM Student where SNO LIKE @SNO AND SName Like @SName  AND  Mobile Like @Mobile"
    /// <param name="sql">T-SQL带参数的语句</param>
    /// <param name="para">调用参数</param>
    /// <returns>返回受影响的行数，</returns>
    public static int GetExecuteNonQuery(string sql,SqlParameter[] para)
    {
        //实例化conn
       conn = new SqlConnection(connString);
        //实例化cmd
         cmd = new SqlCommand(sql, conn);
        //执行
        try
        {
            //打开conn
            conn.Open();
            //执行
            cmd.Parameters.AddRange(para);
            return cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            conn.Close();
        }
    }
/// <summary>
/// 参数化SQL语句执行查询单个结果集，第一行第一列
/// </summary>
/// <param name="sql">SQL带参数的语句</param>
/// <param name="para">调用参数的数组</param>
/// <returns>返回第一行第一列Object类型</returns>
    public static object GetExecuteScalar(string sql,SqlParameter[] para)
    {
        //实例化conn
        conn = new SqlConnection(connString);
        //实例化cmd
        cmd = new SqlCommand(sql, conn);
        //执行
        try
        {
            conn.Open();
            //添加参数化
            cmd.Parameters.AddRange(para);
            //返回单个结果集
            return cmd.ExecuteScalar();
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            conn.Close();
        }
    }
    /// <summary>
    /// 返回多个结果集
    /// </summary>
    /// <param name="sql">带有参数的SQL语句</param>
    /// <param name="para">参数的数组</param>
    /// <returns>返回DataReader多个结果集</returns>
    public static SqlDataReader GetExecuteReader(string sql,SqlParameter[]para)
    {
        //实例化conn
        conn = new SqlConnection(connString);
        //实例化comd
        cmd = new SqlCommand(sql, conn);
        //执行
        try
        {
            //打开
            conn.Open();
            //将参数化赋值给cmd
            cmd.Parameters.AddRange(para);
            //返回
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {

            throw ex;
        }
    }
    #endregion
   
}

}