using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace SQL
{
  public static class SqlHelper
  {
    /// <summary>
    /// 創(chuàng)建連接的字符串
    /// </summary>
    static readonly string connStr=ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

 #region 1.0 執(zhí)行查詢語(yǔ)句,，返回一個(gè)表 + static DataTable ExcuteTable(string sql, params SqlParameter[] ps)
    /// <summary>
    /// 1.0 執(zhí)行查詢語(yǔ)句，返回一個(gè)表
    /// </summary>
    /// <param name="sql">sql語(yǔ)句</param>
    /// <param name="ps">參數(shù)數(shù)組</param>
    /// <returns>返回一張表</returns>
    public static DataTable ExcuteTable(string sql, params SqlParameter[] ps)
    {
      SqlDataAdapter da = new SqlDataAdapter(sql, connStr);
      da.SelectCommand.Parameters.AddRange(ps);
      DataTable dt = new DataTable();
      da.Fill(dt);
      return dt;
    } 
    #endregion

    #region 2.0 執(zhí)行增刪改的方法 + static int ExcuteNoQuery(string sql, params SqlParameter[] ps)
    /// <summary>
    /// 2.0 執(zhí)行增刪改的方法
    /// </summary>
    /// <param name="sql">sql語(yǔ)句</param>
    /// <param name="ps">參數(shù)數(shù)組</param>
    /// <returns>返回一條記錄</returns>
    public static int ExcuteNoQuery(string sql, params SqlParameter[] ps)
    {
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        SqlCommand command = new SqlCommand(sql, conn);
        command.Parameters.AddRange(ps);
        return command.ExecuteNonQuery();
      }
    } 
    #endregion

    #region 3.0 執(zhí)行存儲(chǔ)過(guò)程的方法 + static int ExcuteProc(string procName, params SqlParameter[] ps)
    /// <summary>
    /// 3.0 執(zhí)行存儲(chǔ)過(guò)程的方法
    /// </summary>
    /// <param name="procName">存儲(chǔ)過(guò)程名</param>
    /// <param name="ps">參數(shù)數(shù)組</param>
    /// <returns></returns>
    public static int ExcuteProc(string procName, params SqlParameter[] ps)
    {
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        SqlCommand command = new SqlCommand(procName, conn);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddRange(ps);
        return command.ExecuteNonQuery();
      }
    } 
    #endregion

    #region 4.0 查詢結(jié)果集,，返回的是首行首列 + static int ExecScalar(string sql, params SqlParameter[] ps)
    /// <summary>
    /// 4.0 查詢結(jié)果集,，返回的是首行首列
    /// </summary>
    /// <param name="sql">sql語(yǔ)句</param>
    /// <param name="ps">參數(shù)數(shù)組</param>
    /// <returns></returns>
    public static object ExecScalar(string sql, params SqlParameter[] ps) //調(diào)用的時(shí)候才判斷是什么類(lèi)型
    {
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        SqlCommand command = new SqlCommand(sql, conn);
        command.Parameters.AddRange(ps);
        return command.ExecuteScalar();
      }
    } 
    #endregion
  }
}