SMSReport/SynchronizationServer/ConsoleDataSync/DataProvider.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Web;

/// <summary>
/// DataProvider 的摘要说明
/// </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);
}