2019-03-27

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

namespace sysSqlHelp
{
   public static class SqlHelp
    {
       /// <summary>
       /// 连接字符
       /// </summary>
       private static string ConStr = ConfigurationManager.ConnectionStrings["StrCon"].ConnectionString;

       /// <summary>
       /// ExecuteReader请使用完后关闭SqlDataReader
       /// </summary>
       /// <param name="Sql">要执行的Sql语句</param>
       /// <param name="CommandType">执行的类型（枚举）</param>
       /// <param name="Sqlp">Sql的所需要的参数（不定参数）</param>
       /// <returns>SqlDataReader</returns>
       public static SqlDataReader ExecuteReader(string Sql, CommandType CommandType,params SqlParameter[] Sqlp) 
       {
           SqlConnection Con = new SqlConnection(ConStr);
           SqlCommand Com = new SqlCommand(Sql,Con);
           Com.CommandType = CommandType;
           if(Sqlp!=null&&Sqlp.Length>0)
           {
               Com.Parameters.AddRange(Sqlp);
           }
           Con.Open();
           return Com.ExecuteReader(CommandBehavior.CloseConnection);
       }
       /// <summary>
       /// ExecuteNonQuery
       /// </summary>
       /// <param name="Sql">要执行的Sql语句</param>
       /// <param name="CommandType">执行的类型（枚举）</param>
       /// <param name="Sqlp">Sql的所需要的参数（不定参数）</param>
       /// <returns>受影响的行数</returns>
       public static int ExecuteNonQuery(string Sql, CommandType CommandType, params SqlParameter[] Sqlp)
       {
           SqlConnection Con = new SqlConnection(ConStr);
           SqlCommand Com = new SqlCommand(Sql, Con);
           Com.CommandType = CommandType;
           if (Sqlp != null && Sqlp.Length > 0)
           {
               Com.Parameters.AddRange(Sqlp);
           }
           Con.Open();
           int Count= Com.ExecuteNonQuery();
           Con.Close();
           return Count;
       }
       /// <summary>
       /// ExecuteScalar
       /// </summary>
       /// <param name="Sql">要执行的Sql语句</param>
       /// <param name="CommandType">执行的类型（枚举）</param>
       /// <param name="Sqlp">Sql的所需要的参数（不定参数）</param>
       /// <returns>首行首列的值</returns>
       public static object ExecuteScalar(string Sql, CommandType CommandType, params SqlParameter[] Sqlp)
       {
           SqlConnection Con = new SqlConnection(ConStr);
           SqlCommand Com = new SqlCommand(Sql, Con);
           Com.CommandType = CommandType;
           if (Sqlp != null && Sqlp.Length > 0)
           {
               Com.Parameters.AddRange(Sqlp);
           }
           Con.Open();
           object Value= Com.ExecuteScalar();
           Con.Close();
           return Value;
       }
       /// <summary>
       /// 取出一张表
       /// </summary>
       /// <param name="Sql"></param>
       /// <param name="CommandType"></param>
       /// <param name="Sqlp"></param>
       /// <returns></returns>
       public static DataTable GetDataTable(string Sql, CommandType CommandType, params SqlParameter[] Sqlp) 
       {
           DataTable dt = new DataTable();
           SqlConnection Con = new SqlConnection(ConStr);
           SqlCommand Com = new SqlCommand(Sql, Con);
           Com.CommandType = CommandType;
           if (Sqlp != null && Sqlp.Length > 0)
           {
               Com.Parameters.AddRange(Sqlp);
           }
           Con.Open();
           SqlDataAdapter da = new SqlDataAdapter(Com);
           da.Fill(dt);
           Con.Close();
           return dt;
       }
     
    }
}