SMSReport/SynchronizationServer/WindowsService/DataProvider.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Web;
namespace WindowsService
{
    /// <summary>
    /// 数据访问基类
    /// </summary>
    public static class DataProvider
    {
        public static IDbConnection GetDbConnection()
        {
            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Target"].ConnectionString;
            IDbConnection connection = new SqlConnection(connectionString);
            connection.Open();
            return connection;
        }

        /// <summary>
        /// 将 <see cref="DataTable"/> 的数据批量插入到数据库中。
        /// </summary>
        /// <param name="varlist">要批量插入的list。</param>
        /// <param name="batchSize">每批次写入的数据量。</param>
        public static void Insert<T>(string tableName, IEnumerable<T> varlist, int batchSize = 10000)
        {

            var dataTable = varlist.ToDataTable2<T>(null);

            if (dataTable.Rows.Count == 0)
            {
                return;
            }
            using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Target"].ConnectionString))
            {
                try
                {
                    connection.Open();
                    //给表名加上前后导符 
                    using (var bulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, null)
                    {
                        DestinationTableName = tableName,
                        BatchSize = batchSize
                    })
                    {
                        //循环所有列，为bulk添加映射
                        foreach (DataColumn c in dataTable.Columns)
                        {
                            if (c.ColumnName == "id" || c.ColumnName == "ID" || c.ColumnName == "_id")
                            { }
                            else
                                bulk.ColumnMappings.Add(c.ColumnName, c.ColumnName);
                        }
                        bulk.WriteToServer(dataTable);
                        bulk.Close();
                    }
                }
                catch (Exception exp)
                {
                    throw exp;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        public static void Clear()
        {
            using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Target"].ConnectionString))
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "Delete From Users";
                cmd.ExecuteNonQuery();
            }
        }

        public static void E(Action<IDbCommand> act)
        {
            using (var connection = GetDbConnection())
            {
                act(connection.CreateCommand());
            }
        }

        /// <summary>
        /// 将 <see cref="DataTable"/> 的数据批量插入到数据库中。
        /// </summary>
        /// <param name="dataTable">要批量插入的 <see cref="DataTable"/>。</param>
        /// <param name="batchSize">每批次写入的数据量。</param>
        public static void Insert(string tableName, DataTable dataTable, int batchSize = 10000)
        {
            if (dataTable.Rows.Count == 0)
            {
                return;
            }
            using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["target"].ConnectionString))
            {
                try
                {
                    connection.Open();
                    //给表名加上前后导符 
                    using (var bulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, null)
                    {
                        DestinationTableName = tableName,
                        BatchSize = batchSize
                    })
                    {
                        //循环所有列，为bulk添加映射
                        foreach (DataColumn c in dataTable.Columns)
                        {
                            bulk.ColumnMappings.Add(c.ColumnName, c.ColumnName.Trim());
                        }
                        bulk.WriteToServer(dataTable);
                        bulk.Close();
                    }
                }
                catch (Exception exp)
                {
                    throw exp;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// List<T> 转DataTable
        /// 使用方法： DataTable tb = infos.ToDataTable2<NationalInfo>(rec => new object[] { infos });
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="varlist"></param>
        /// <param name="fn"></param>
        /// <returns></returns>
        public static DataTable ToDataTable2<T>(this IEnumerable<T> varlist, CreateRowDelegate<T> fn)
        {

            DataTable dtReturn = new DataTable();
            PropertyInfo[] oProps = null;

            if (oProps == null)
            {
                oProps = (typeof(T)).GetProperties();
                foreach (PropertyInfo pi in oProps)
                {
                    Type colType = pi.PropertyType;
                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                    {
                        colType = colType.GetGenericArguments()[0];
                    }

                    dtReturn.Columns.Add(new DataColumn(pi.Name, colType));

                }
            }
            foreach (T rec in varlist)
            {
                DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps)
                {

                    dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);

                }

                dtReturn.Rows.Add(dr);

            }

            return (dtReturn);

        }
        public delegate object[] CreateRowDelegate<T>(T t);
    }
}