SqlBulkCopy 批量復制數據到數據表

使用 SqlBulkCopy 類只能向 SQL Server 表寫入數據。但是，數據源不限于 SQL Server；可以使用任何數據源，只要數據可加載到 DataTable 實例或可使用 IDataReader 實例讀取數據

1. 使用Datatable作為數據源的方式：

下面的代碼使用到了ColumnMappings，因為目標表和數據源Datatable的結構不一致，需要這么一個映射來指定對應關系

  public string SaveJHCData(LzShopBasicData[] datas)        {            var result = new AResult();            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["**"].ConnectionString);            con.Open();            foreach (var item in datas)            {                Logger.Info("數據更新處理，店鋪名稱：" + item.ShopName + "數據日期" + item.SellDate);                try                {                    using (TransactionScope scope = new TransactionScope())                    {                        DataTable JHCOrderItemsdt = SaveJHCOrderItemsData(item);                        SqlBulkCopy JHCOrderItemscopy = new SqlBulkCopy(con);                        JHCOrderItemscopy.ColumnMappings.Add("orderId", "orderId");                        JHCOrderItemscopy.ColumnMappings.Add("auctionId", "auctionId");                        JHCOrderItemscopy.ColumnMappings.Add("itemTitle", "itemTitle");                        JHCOrderItemscopy.ColumnMappings.Add("tradeAmt", "tradeAmt");                        JHCOrderItemscopy.ColumnMappings.Add("al
2.使用IDataReader作為數據源的方式，這種方式個人認為用的很少，首先目標表和來源表兩個數據庫連接你都需要拿到，如果兩個都可以拿到，一般直接操作sql就可以解決：
這里是直接拷貝的MSDN的代碼，
用到的AdventureWorks 數據庫可以直接在網上下載到，
using System.Data.SqlClient;class PRogram{    static void Main()    {        string connectionString = GetConnectionString();        // Open a sourceConnection to the AdventureWorks database.        using (SqlConnection sourceConnection =                   new SqlConnection(connectionString))        {            sourceConnection.Open();            // Perform an initial count on the destination table.            SqlCommand commandRowCount = new SqlCommand(                "SELECT COUNT(*) FROM " +                "dbo.BulkCopyDemoMatchingColumns;",                sourceConnection);            long countStart = System.Convert.ToInt32(                commandRowCount.ExecuteScalar());            Console.WriteLine("Starting row count = {0}", countStart);            // Get data from the source table as a SqlDataReader.            SqlCommand commandSourceData = new SqlCommand(                "SELECT ProductID, Name, " +                "ProductNumber " +                "FROM Production.Product;", sourceConnection);            SqlDataReader reader =                commandSourceData.ExecuteReader();            // Open the destination connection. In the real world you would             // not use SqlBulkCopy to move data from one table to the other             // in the same database. This is for demonstration purposes only.            using (SqlConnection destinationConnection =                       new SqlConnection(connectionString))            {                destinationConnection.Open();                // Set up the bulk copy object.                 // Note that the column positions in the source                // data reader match the column positions in                 // the destination table so there is no need to                // map columns.                using (SqlBulkCopy bulkCopy =                           new SqlBulkCopy(destinationConnection))                {                    bulkCopy.DestinationTableName =                        "dbo.BulkCopyDemoMatchingColumns";                    try                    {                        // Write from the source to the destination.                        bulkCopy.WriteToServer(reader);                    }                    catch (Exception ex)                    {                        Console.WriteLine(ex.Message);                    }                    finally                    {                        // Close the SqlDataReader. The SqlBulkCopy                        // object is automatically closed at the end                        // of the using block.                        reader.Close();                    }                }                // Perform a final count on the destination                 // table to see how many rows were added.                long countEnd = System.Convert.ToInt32(                    commandRowCount.ExecuteScalar());                Console.WriteLine("Ending row count = {0}", countEnd);                Console.WriteLine("{0} rows were added.", countEnd - countStart);                Console.WriteLine("Press Enter to finish.");                Console.ReadLine();            }        }    }    private static string GetConnectionString()        // To avoid storing the sourceConnection string in your code,         // you can retrieve it from a configuration file.     {        return "Data Source=(local); " +            " Integrated Security=true;" +            "Initial Catalog=AdventureWorks;";    }}
View Code
實戰：借助類型反射動態構建Datatable數據源,通過SqlBulkCopy批量保存入庫
1.獲取一張空的Datatable
var dt = bisdal.From<TopBrand>(TopBrand._.ID == -1, OrderByClip.Default).ToDataTable();
2.填充DataTable，這里是通過遍歷外部的集合，把屬性屬性逐一賦值填充到目標Datatable
         foreach (var item in brandselldataitems)                    {                        try                        {                            TopBrand topbrand = new TopBrand                            {                                BrandIndex = item.mk,                                BrandName = item.c58,                                Date = date,                                WinnerAmt = item.c60,                                WinnerPeople = item.c62,                                WinnerProNum = item.c61,                                HotTaobaoCategoryID = cid                            };                            CreateDtByItem<TopBrand>(topbrand, dt);                        }                        catch (Exception ex)                        {                            Logger.Error(ex.ToString());                            continue;                        }                    }
這里借助反射，遍歷實體屬性集合，動態構建DataTableRow對象
       private void CreateDtByItem<T>(T item, DataTable dt)        {            System.Reflection.PropertyInfo[] properties = item.GetType().GetProperties(System.Reflec