C#.SqlHelper类库

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

namespace sqlHelper
{
    public static  class SqlHelper
    {
        //index-----选择执行配置文件中的需要连接的数据库连接字段;
        //sqlText-----需要执行的sql语句;
        //sqlPar------需要的参数列表;
        //type-------解释命令字符串的格式枚举;
        //commandType---解释命令字符串的格式：
        //                      1-------CommandType.StoredProcedure;          
        //                      2-------CommandType.TableDirect;
        //                      3-------CommandType.Text.
        #region ExecuteNonQuery 返回受影响的的行数
        public static int ExecuteNonQuery(int index,string sqlText,params SqlParameter[] sqlPar)
        {           
            return ExecuteNonQuery(index, sqlText, CommandType.Text, sqlPar);
        }
        public static int ExecuteNonQuery(string sqlText)
        {
            return ExecuteNonQuery(1, sqlText,CommandType.Text);
        }
        public static int ExecuteNonQuery(string sqlText,Dictionary<string,string> sqlPar)
        {
            int count = sqlPar.Count;
            SqlParameter[] sqlPars = new SqlParameter[count];
            foreach (var item in sqlPar)
            {
                count--;
                sqlPars[count] = new SqlParameter(item.Key, item.Value);
            }           
            return ExecuteNonQuery(1, sqlText,CommandType.Text, sqlPars);
        }
        public static int ExecuteNonQuery(int index,string sqlText,CommandType type,params SqlParameter[] sqlPar)
        {
            string sql = System.Configuration.ConfigurationManager.ConnectionStrings[index].ConnectionString;
            SqlConnection con = new SqlConnection(sql);
            SqlCommand com = new SqlCommand(sqlText, con);
            com.CommandType = type;
            if (sqlPar.Count() > 0)
                com.Parameters.AddRange(sqlPar);
            try
            {
                con.Open();
                return com.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Close();
                con.Dispose();
                com.Dispose();
            }    
        }
        public static int ExecuteNonQuery(string sqlText ,int commandType,params SqlParameter[] sqlPar)
        {
            CommandType type;
            switch(commandType)
            {
                case 1:
                    type = CommandType.StoredProcedure;
                    break;
                case 2:
                    type = CommandType.TableDirect;
                    break;
                case 3:
                    type = CommandType.Text;
                    break;
                default:
                    throw new Exception("参数commandType值只能为1,2,3");
            }
            return ExecuteNonQuery(1, sqlText, type, sqlPar);
        }
        #endregion

        #region ExecuteScalar 返回查询结果的第一行第一列
        public static object ExecuteScalar(int index, string sqlText, CommandType type, params SqlParameter[] sqlPar)
        {
            string sql = System.Configuration.ConfigurationManager.ConnectionStrings[index].ConnectionString;
            SqlConnection con = new SqlConnection(sql);
            SqlCommand com = new SqlCommand(sqlText, con);
            com.CommandType = type;
            if (sqlPar.Count() > 0)
                com.Parameters.AddRange(sqlPar);
            try
            {
                con.Open();
                return com.ExecuteScalar();              
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Close();
                con.Dispose();
                com.Dispose();
            }
        }
        public static object  ExecuteScalar(int index, string sqlText, params SqlParameter[] sqlPar)
        {
            return ExecuteScalar(index, sqlText, CommandType.Text, sqlPar);
        }
        public static object  ExecuteScalar(string sqlText)
        {
            return ExecuteScalar(1, sqlText, CommandType.Text);
        }
        public static object  ExecuteScalar(string sqlText, Dictionary<string, string> sqlPar)
        {
            int count = sqlPar.Count;
            SqlParameter[] sqlPars = new SqlParameter[count];
            foreach (var item in sqlPar)
            {
                count--;
                sqlPars[count] = new SqlParameter(item.Key, item.Value);
            }

            return ExecuteScalar(1, sqlText, CommandType.Text, sqlPars);
        }
        public static object  ExecuteScalar(string sqlText, int commandType, params SqlParameter[] sqlPar)
        {
            CommandType type;
            switch (commandType)
            {
                case 1:
                    type = CommandType.StoredProcedure;
                    break;
                case 2:
                    type = CommandType.TableDirect;
                    break;
                case 3:
                    type = CommandType.Text;
                    break;
                default:
                    throw new Exception("参数commandType值只能为1,2,3");
            }
            return ExecuteScalar(1, sqlText, type, sqlPar);
        }
        #endregion

        #region GetTable 返回查询结果集，并将结果集封装为DataTable返回
        public static DataTable GetTable(int index,string sqlText,CommandType type,params SqlParameter[] sqlPar)
        {
            DataTable table=new DataTable();
            string sql = System.Configuration.ConfigurationManager.ConnectionStrings[index].ConnectionString;
            SqlConnection con = new SqlConnection(sql);
            SqlDataAdapter adapter = new SqlDataAdapter(sqlText, con);
            adapter.SelectCommand.CommandType = type;
            if (sqlPar.Count() > 0)
                adapter.SelectCommand.Parameters.AddRange(sqlPar);
            try
            {
                adapter.Fill(table);
                return table;
            }
            catch(Exception e)
            {
                throw e;
            }
            finally
            {
                con.Close();
                con.Dispose();
                adapter.Dispose();
            }
        }
        public static DataTable GetTable(int index,string sqlText,params SqlParameter[] sqlPar)
        {
            return GetTable(index, sqlText, CommandType.Text, sqlPar);
        }
        public static DataTable GetTable(string sqlText)
        {
            return GetTable(1, sqlText, CommandType.Text);
        }
        public static DataTable GetTable(string sqlText,Dictionary<string, string> sqlPar)
        {
            int count = sqlPar.Count;
            SqlParameter[] sqlPars = new SqlParameter[count];
            foreach (var item in sqlPar)
            {
                count--;
                sqlPars[count] = new SqlParameter(item.Key, item.Value);
            }
            return GetTable(1, sqlText, CommandType.Text, sqlPars);
        }
        public static DataTable GetTable(string sqlText, int commandType, params SqlParameter[] sqlPar)
        {
            CommandType type;
            switch (commandType)
            {
                case 1:
                    type = CommandType.StoredProcedure;
                    break;
                case 2:
                    type = CommandType.TableDirect;
                    break;
                case 3:
                    type = CommandType.Text;
                    break;
                default:
                    throw new Exception("参数commandType值只能为1,2,3");
            }
            return GetTable(1, sqlText, type, sqlPar);
        }
        #endregion

        #region 验证是否在数据库中有重复，有则返回True，没有则返回False
        //TableName-----所要验证表的名称;
        //ColumnsName----所要验证列的名称;
        //RowName--------所要验证行的名称.
        public static bool IsRepeat(int index,string TableName, string ColumnName,string RowName)
        {
            int count;
            string sql = System.Configuration.ConfigurationManager.ConnectionStrings[index].ConnectionString;
            SqlConnection con = new SqlConnection(sql);
            string sqlText = "select count(*) from "+TableName+" where "+ColumnName+"=@rowName";
            SqlCommand com = new SqlCommand(sqlText, con);
            com.Parameters.Add("@rowName", RowName);
            try
            {
                con.Open();
                count = Convert.ToInt32(com.ExecuteScalar());
            }
            catch(Exception e)
            {
                throw e;
            }
            finally
            {
                con.Close();
                con.Dispose();
                com.Dispose();
            }
            if (count == 0)
                return false;
            else
                return true;
        }
        #endregion
    }
}