SQLHelper

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

namespace CommonClass
{
    static class SqlHelpers
    {
        /// <summary>
        /// 数据库连接串
        /// </summary>
        private static SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ToString());

        /// <summary>
        /// 打开或者关闭数据库连接
        /// </summary>
        private static void ConnStateChange()
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            else
            {
                connection.Close();
            }

        }

        #region SQL语句

        /// <summary>
        /// 查询是否包含某一满足条件的记录
        /// </summary>
        /// <param name="Querystr">SQL语句字符串</param>
        /// <returns>包含返回;true 不包含则返回:false</returns>
        public static bool isHaveRecord(string Querystr)
        {
            return isHaveRecord(Querystr, null);
        }

        /// <summary>
        /// 查询是否包含某一满足条件的记录(带参数)
        /// </summary>
        /// <param name="QueryStr">SQL语句字符串</param>
        /// <param name="ParameterList">参数数组</param>
        /// <returns>包含返回;true 不包含则返回:false</returns>
        public static bool isHaveRecord(string QueryStr, params SqlParameter[] ParameterList)
        {
            try
            {
                SqlCommand cmd = new SqlCommand(QueryStr);
                ConnStateChange();
                if (ParameterList != null)
                {
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = QueryStr;
                    cmd.Parameters.AddRange(ParameterList);
                }
                else
                {
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = QueryStr;
                }
                return cmd.ExecuteReader().Read() == true ? true : false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnStateChange();
            }
        }

        /// <summary>
        /// 获取查询结果集
        /// </summary>
        /// <param name="QueryStr">SQL语句字符串</param>
        /// <returns>返回一个DataTable</returns>
        public static DataTable ExecuteDataTable(string QueryStr)
        {
            return ExecuteDataTable(QueryStr, null);
        }

        /// <summary>
        /// 获取查询结果集
        /// </summary>
        /// <param name="QueryStr">SQL语句字符串</param>
        /// <param name="ParameterList">参数数组</param>
        /// <returns>返回一个DataTable</returns>
        public static DataTable ExecuteDataTable(string QueryStr, params SqlParameter[] ParameterList)
        {
            try
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = new SqlCommand();
                ConnStateChange();
                if (ParameterList != null)
                {
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = QueryStr;
                    cmd.Parameters.AddRange(ParameterList);
                }
                else
                {
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = QueryStr;
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnStateChange();
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="QueryStr">SQL语句</param>
        /// <returns>受影响行数大于0返回true 否则返回 false</returns>
        public static bool ExecuteNonQuery(string QueryStr)
        {
            return ExecuteNonQuery(QueryStr, null);
        }

        /// <summary>
        /// 执行带参数的SQL语句
        /// </summary>
        /// <param name="QueryStr">SQL语句</param>
        /// <param name="ParameterList">SqlParameter参数数组</param>
        /// <returns>受影响行数大于0返回true 否则返回 false</returns>
        public static bool ExecuteNonQuery(string QueryStr, params SqlParameter[] ParameterList)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                ConnStateChange();
                if (ParameterList != null)
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = QueryStr;
                    cmd.Parameters.AddRange(ParameterList);
                }
                else
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = QueryStr;
                }
                return cmd.ExecuteNonQuery() > 0 ? true : false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnStateChange();
            }
        }
        #endregion


        #region 存储过程


        /// <summary>
        /// 查询是否包含某一满足条件的记录
        /// </summary>
        /// <param name="Querystr">存储过程</param>
        /// <returns>包含返回;true 不包含则返回:false</returns>
        public static bool isProHaveRecord(string ProName)
        {
            return isProHaveRecord(ProName, null);
        }

        /// <summary>
        /// 查询是否包含某一满足条件的记录(带参数)
        /// </summary>
        /// <param name="QueryStr">存储过程</param>
        /// <param name="ParameterList">参数数组</param>
        /// <returns>包含返回;true 不包含则返回:false</returns>
        public static bool isProHaveRecord(string ProName, params SqlParameter[] ParameterList)
        {
            try
            {
                SqlCommand cmd = new SqlCommand(ProName);
                ConnStateChange();
                if (ParameterList != null)
                {
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = ProName;
                    cmd.Parameters.AddRange(ParameterList);
                }
                else
                {
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = ProName;
                }
                return cmd.ExecuteReader().Read() == true ? true : false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnStateChange();
            }
        }

        /// <summary>
        /// 获取查询结果集
        /// </summary>
        /// <param name="QueryStr">存储过程</param>
        /// <returns>返回一个DataTable</returns>
        public static DataTable ExecuteProDataTable(string ProName)
        {
            return ExecuteDataTable(ProName, null);
        }

        /// <summary>
        /// 获取查询结果集
        /// </summary>
        /// <param name="QueryStr">存储过程</param>
        /// <param name="ParameterList">SqlParameter参数数组</param>
        /// <returns>返回一个DataTable</returns>
        public static DataTable ExecuteProDataTable(string ProName, params SqlParameter[] ParameterList)
        {
            try
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = new SqlCommand();
                ConnStateChange();
                if (ParameterList != null)
                {
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = ProName;
                    cmd.Parameters.AddRange(ParameterList);
                }
                else
                {
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = ProName;
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnStateChange();
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="QueryStr">存储过程名称</param>
        /// <returns>受影响行数大于0返回true 否则返回 false</returns>
        public static bool ExecuteProNonQuery(string ProName)
        {
            return ExecuteProNonQuery(ProName, null);
        }

        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="QueryStr">存储过程</param>
        /// <param name="ParameterList">SqlParameter参数数组</param>
        /// <returns>受影响行数大于0返回true 否则返回 false</returns>
        public static bool ExecuteProNonQuery(string ProName, params SqlParameter[] ParameterList)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                ConnStateChange();
                if (ParameterList != null)
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = ProName;
                    cmd.Parameters.AddRange(ParameterList);
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = ProName;
                }
                return cmd.ExecuteNonQuery() > 0 ? true : false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnStateChange();
            }
        }
      
        #endregion

        #region  Insert DataTable To DB

        /// <summary>
        /// 把DataTable的数据写入数据库（DataTable的数据列的顺序必须与数据库表的列顺序相同）
        /// </summary>
        /// <param name="dt">需要写入的DataTable</param>
        /// <param name="DestinationTableName">被写入的数据库中的表名称</param>
        public static void InsertDatableToDB(DataTable dt, string DestinationTableName)
        {
            using (SqlBulkCopy sqlBC = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["conn"].ToString(), SqlBulkCopyOptions.UseInternalTransaction))
            {
                sqlBC.BatchSize = 100;
                sqlBC.NotifyAfter = 1;
                sqlBC.DestinationTableName = DestinationTableName;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    sqlBC.ColumnMappings.Add(i,i);//(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                }
                sqlBC.WriteToServer(dt);
            }
        }
           
        #endregion
    }
}