SqlBulkCopy

參考網站

◎ TABLE SCHEMA

CREATE TABLE [dbo].[Table1](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

◎ Program.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Diagnostics;

namespace SqlBulkCopyDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            Stopwatch sp = new Stopwatch();
            sp.Reset();
            sp.Start();

            //一開始我們先產生一個DataTable來裝我們要寫入的資料
            DataTable dt = new DataTable();
            dt.Columns.Add("Column1", typeof(int));
            dt.Columns.Add("Column2", typeof(string));

            //因為SqlBulkCopy的猛就是大量的一次寫入，所以我們也來跑10萬筆吧
            int i;
            for (i = 0; i < 100000; i++)
            {
                DataRow dr = dt.NewRow();
                dr["Column2"] = "Name" + i.ToString();
                dt.Rows.Add(dr);
            }

            //宣告連結字串
            SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleConnectionString"].ConnectionString);
            sqlConnection.Open();

            //宣告SqlBulkCopy
            using (SqlBulkCopy sqlBC = new SqlBulkCopy(sqlConnection))
            {
                //設定一個批次量寫入多少筆資料
                sqlBC.BatchSize = 1000;
                //設定逾時的秒數
                sqlBC.BulkCopyTimeout = 60;

                //設定 NotifyAfter 屬性，以便在每複製 10000 個資料列至資料表後，呼叫事件處理常式。
                sqlBC.NotifyAfter = 10000;
                sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

                //設定要寫入的 Table
                sqlBC.DestinationTableName = "dbo.Table1";

                //對應資料行
                sqlBC.ColumnMappings.Add("Column1", "Id");//(來源欄位名稱, Table欄位名稱)
                sqlBC.ColumnMappings.Add("Column2", "Name");//(來源欄位名稱, Table欄位名稱)

                //開始寫入
                sqlBC.WriteToServer(dt);
            }

            sqlConnection.Dispose();
            sp.Stop();
            Console.WriteLine("共耗時：" + sp.ElapsedMilliseconds);
            Console.ReadLine();
        }

        static void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine("已處理" + e.RowsCopied + "筆資料！");
        }

    }
}