using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

using System.Reflection;

using System.Collections;

namespace DataHelp

{

    #region ADO.NET 访问数据库辅助类 +SqlHelp

    //Author:兵兵 +SqlHelp

    public class SqlHelp

    {

        /// <summary>

        /// DB连接字符串

        /// </summary>

        public static readonly string DB= ConfigurationManager.ConnectionStrings["DB"].ConnectionString;

        #region ExecuteDataReader +ExecuteDataReader(string cmdText, List<SqlParameter> parameters,string connString)

        /// <summary>

        /// ExecuteDataReader(执行有参存储过程)

        /// </summary>

        /// <param name="cmdText">存储过程名称</param>

        /// <param name="parameters">参数列表</param>

        /// <param name="connString">连接字符串</param>

        /// <returns>SqlDataReader对象</returns>

        public static SqlDataReader ExecuteDataReader(string cmdText, List<SqlParameter> parameters, string connString)

        {

            SqlConnection conn = new SqlConnection(connString);

            SqlCommand cmd = new SqlCommand();

            CommandBuilder(cmdText, cmd, conn, parameters);

            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            cmd.Parameters.Clear();

            return reader;

        }

        #endregion

        #region ExecuteDataReader +ExecuteDataReader(string cmdText,string connString)

        /// <summary>

        /// ExecuteDataReader(执行无参存储过程)

        /// </summary>

        /// <param name="cmdText">存储过程</param>

        /// <param name="connString">连接字符串</param>

        /// <returns>SqlDataReader对象</returns>

        public static SqlDataReader ExecuteDataReader(string cmdText, string connString)

        {

            SqlConnection conn = new SqlConnection(connString);

            SqlCommand cmd = new SqlCommand();

            CommandBuilder(cmdText, cmd, conn);

            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            cmd.Parameters.Clear();

            return reader;

        }

        #endregion

        #region ExecuteNonQuery +ExecuteNonQuery(string cmdText, List<SqlParameter> parameters, string connString)

        /// <summary>

        /// ExecuteNonQuery(执行有参存储过程)

        /// </summary>

        /// <param name="cmdText">存储过程名称</param>

        /// <param name="parameters">参数列表</param>

        /// <param name="connString">连接字符串</param>

        /// <returns>数据库受影响的行数</returns>

        public static int ExecuteNonQuery(string cmdText, List<SqlParameter> parameters, string connString)

        {

            using (SqlConnection conn = new SqlConnection(connString))

            {

                SqlCommand cmd = new SqlCommand();

                CommandBuilder(cmdText, cmd, conn, parameters);

                int result = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();

                return result;

            }

        }

        #endregion

        #region ExecuteNonQuery +ExecuteNonQuery(string cmdText, string connString)

        /// <summary>

        /// ExecuteNonQuery(执行无参存储过程)

        /// </summary>

        /// <param name="cmdText">存储过程名称</param>

        /// <param name="connString">连接字符串</param>

        /// <returns>数据库受影响的行数</returns>

        public static int ExecuteNonQuery(string cmdText, string connString)

        {

            using (SqlConnection conn = new SqlConnection(connString))

            {

                SqlCommand cmd = new SqlCommand();

                CommandBuilder(cmdText, cmd, conn);

                int result = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();

                return result;

            }

        }

        #endregion

        #region ExecuteScalar +ExecuteScalar(string cmdText, List<SqlParameter> parameters, string connString)

        /// <summary>

        /// ExecuteScalar(执行有参存储过程)

        /// </summary>

        /// <param name="cmdText">存储过程名称</param>

        /// <param name="parameters">参数列表</param>

        /// <param name="connString">连接字符串</param>

        /// <returns>object</returns>

        public static object ExecuteScalar(string cmdText, List<SqlParameter> parameters, string connString)

        {

            using (SqlConnection conn = new SqlConnection(connString))

            {

                SqlCommand cmd = new SqlCommand();

                CommandBuilder(cmdText, cmd, conn, parameters);

                object o = cmd.ExecuteScalar();

                cmd.Parameters.Clear();

                return o;

            }

        }

        #endregion

        #region ExecuteScalar +ExecuteScalar(string cmdText, string connString)

        /// <summary>

        /// ExecuteScalar(执行无参存储过程)

        /// </summary>

        /// <param name="cmdText">存储过程名称</param>

        /// <param name="connString">连接字符串</param>

        /// <returns>object</returns>

        public static object ExecuteScalar(string cmdText, string connString)

        {

            using (SqlConnection conn = new SqlConnection(connString))

            {

                SqlCommand cmd = new SqlCommand();

                CommandBuilder(cmdText, cmd, conn);

                object o = cmd.ExecuteScalar();

                cmd.Parameters.Clear();

                return o;

            }

        }

        #endregion

        #region ExecuteDataTable +ExecuteDataTable(string cmdText, List<SqlParameter> parameters, string connString)

        /// <summary>

        /// ExecuteDataTable(用适配器执行有参存储过程)

        /// </summary>

        /// <param name="cmdText">存储过程名称</param>

        /// <param name="parameters">参数列表</param>

        /// <param name="connString">连接字符串</param>

        /// <returns>DataTable</returns>

        public static DataTable ExecuteDataTable(string cmdText, List<SqlParameter> parameters, string connString)

        {

            using (SqlConnection conn = new SqlConnection(connString))

            {

                SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);

                //命令类型为存储过程

                da.DeleteCommand.CommandType = CommandType.StoredProcedure;

                da.SelectCommand.Parameters.AddRange(parameters.ToArray());

                DataTable dt = new DataTable();

                da.Fill(dt);

                return dt;

            }

        }

        #endregion

        #region ExecuteDataTable +ExecuteDataTable(string cmdText, string connString)

        /// <summary>

        /// ExecuteDataTable(用适配器执行无参存储过程)

        /// </summary>

        /// <param name="cmdText">存储过程名称</param>

        /// <param name="connString">连接字符串</param>

        /// <returns>DataTable</returns>

        public static DataTable ExecuteDataTable(string cmdText, string connString)

        {

            using (SqlConnection conn = new SqlConnection(connString))

            {

                SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);

                //命令类型为存储过程

                da.DeleteCommand.CommandType = CommandType.StoredProcedure;

                DataTable dt = new DataTable();

                da.Fill(dt);

                return dt;

            }

        }

        #endregion

        #region ExecuteDataTableProc(命令+适配器) +ExecuteDataTableProc(string cmdText, List<SqlParameter> parameters, string connString)

        /// <summary>

        /// ExecuteDataTableProc(执行有参存储过程)

        /// </summary>

        /// <param name="cmdText">存储过程名称</param>

        /// <param name="parameters">参数列表</param>

        /// <param name="connString">连接字符串</param>

        /// <returns>DataTable</returns>

        public static DataTable ExecuteDataTableProc(string cmdText, List<SqlParameter> parameters, string connString)

        /// <summary>

        {

            using (SqlConnection conn = new SqlConnection(connString))

            {

                SqlCommand cmd = new SqlCommand();

                CommandBuilder(cmdText, cmd, conn, parameters);

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                DataTable dt = new DataTable();

                adapter.Fill(dt);

                cmd.Parameters.Clear();

                return dt;

            }

        }

        #endregion

        #region ExecuteDataTableProc(命令+适配器) +ExecuteDataTableProc(string cmdText, string connString)

        /// <summary>

        /// ExecuteDataTableProc(执行无参存储过程)

        /// </summary>

        /// <param name="parameters">参数列表</param>

        /// <param name="connString">连接字符串</param>

        /// <returns>DataTable</returns>

        public static DataTable ExecuteDataTableProc(string cmdText, string connString)

        {

            using (SqlConnection conn = new SqlConnection(connString))

            {

                SqlCommand cmd = new SqlCommand();

                CommandBuilder(cmdText, cmd, conn);

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                DataTable dt = new DataTable();

                adapter.Fill(dt);

                cmd.Parameters.Clear();

                return dt;

            }

        }

        #endregion

        #region 准备命令对象 -CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn, List<SqlParameter> parameters)

        /// <summary>

        /// 准备命令对象(执行有参存储过程)

        /// </summary>

        /// <param name="cmdText">存储过程名称</param>

        /// <param name="cmd">命令对象</param>

        /// <param name="conn">连接对象</param>

        /// <param name="parameters">参数列表</param>

        private static void CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn, List<SqlParameter> parameters)

        {

            if (conn.State == System.Data.ConnectionState.Closed)

                conn.Open();

            cmd.Connection = conn;

            cmd.CommandText = cmdText;

            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            if (parameters.Count > )

                cmd.Parameters.AddRange(parameters.ToArray());

        }

        #endregion

        #region 准备命令对象 -CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn)

        /// <summary>

        /// 准备命令对象(执行无参存储过程)

        /// </summary>

        /// <param name="cmdText">存储过程名称</param>

        /// <param name="cmd">命令对象</param>

        /// <param name="conn">连接对象</param>

        private static void CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn)

        {

            if (conn.State == System.Data.ConnectionState.Closed)

                conn.Open();

            cmd.Connection = conn;

            cmd.CommandText = cmdText;

            cmd.CommandType = System.Data.CommandType.StoredProcedure;

        }

        #endregion

        #region 批插入 void BulkInsert( DataTable dt, string tableName, string connStr)

        /// <summary>

        /// 批插入 void BulkInsert( DataTable dt, string tableName, string connStr)

        /// </summary>

        /// <param name="dt">所有数据的表格</param>

        /// <param name="tableName">表名</param>

        public static int BulkInsert(DataTable dt, string tableName, string connStr)

        {

            int result = -;

            if (string.IsNullOrEmpty(tableName))

                throw new Exception("请指定你要插入的表名");

            var count = dt.Rows.Count;

            if (count == )

                return result;

            SqlTransaction sqlBulkTran = null;

            try

            {

                using (SqlConnection conn = new SqlConnection(connStr))

                {

                    if (conn.State == System.Data.ConnectionState.Closed)

                        conn.Open();

                    sqlBulkTran = conn.BeginTransaction();

                    using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlBulkTran))

                    {

                        copy.DestinationTableName = tableName;//指定目标表

                        copy.WriteToServer(dt);//将dt中的所有行复制到SqlBulkCopy对象的DestinationTableName指定的目标表中

                        if (sqlBulkTran != null)

                        {

                            sqlBulkTran.Commit();

                        }

                        result = ;

                    }

                }

            }

            catch (Exception)

            {

                if (sqlBulkTran != null)

                {

                    sqlBulkTran.Rollback();

                }

            }

            finally

            {

                sqlBulkTran = null;

            }

            return result;

        }

        #endregion

    }

    #endregion

}

#region list 扩展方法 Author:高兵兵

public static class IListUtil

{

    /// <summary>

    /// 将集合类转换成DataTable

    /// </summary>

    /// <param name="list">集合</param>

    /// <returns></returns>

    public static DataTable AsDataTable<T>(this IList<T> list)

    {

        DataTable result = new DataTable();

        if (list.Count > )

        {

            PropertyInfo[] propertys = typeof(T).GetProperties();

            foreach (PropertyInfo pi in propertys)

            {

                result.Columns.Add(pi.Name, pi.PropertyType);

            }

            for (int i = ; i < list.Count; i++)

            {

                ArrayList tempList = new ArrayList();

                foreach (var item in propertys)

                {

                    object obj = item.GetValue(list[i], null);

                    tempList.Add(obj);

                }

                object[] array = tempList.ToArray();

                result.LoadDataRow(array, true);

            }

        }

        return result;

    }

}

#endregion