張鬼東的實作與學習

SqlBulkCopy這個物件可以讓我們大量複製資料, 執行速度非常的快, 我自己實測寫入了一百多萬筆的資料, 原本要花費至少要40分鐘, 但最後只用了一分鐘又十秒, 對於大量批次寫入真的很實用, 不過目的地的資料庫必須是SQL Server, 因為 SqlBulkCopy只能使用 SqlConnection 來作業, 

        static void Main(string[] args)
        {
            DateTime CopyStartTime;

            //連線到來源資料庫
            using (SqlConnection cnSource = new SqlConnection(“Data Source=SourceDB;Initial Catalog=SourceDBName;Persist Security Info=True;User ID=uid;Password=pwd”))
            {
                cnSource.Open();
                using (SqlCommand cmdSource = new SqlCommand(““, cnSource))
                {
                    //連線到目的資料庫
                    using (SqlConnection cnDestination = new SqlConnection(“Data Source=DestinationDB;Initial Catalog=DestinationDBName;Persist Security Info=True;User ID=uid;Password=pwd”))
                    {
                        cnDestination.Open()
                        //取得來要複製的資料表中, 有幾筆資料
                        cmdSource.CommandText = “SELECT COUNT(*) FROM SampleData”;
                        Console.WriteLine(“Total Rows:{0}”, cmdSource.ExecuteScalar().ToString());
                        //建立 SqlBulkCopy 物件
                        SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(cnDestination);
                        //設定每個批次要複製的筆數
                        sqlBulkCopy.BatchSize = 3000;
                        //time out
                        sqlBulkCopy.BulkCopyTimeout = 100;
                        //目的地的資料表
                        sqlBulkCopy.DestinationTableName = “dbo.SampleData”;
                        //設定當SqlBulkCopy複製多少筆資料後, 觸發通知事件
                        sqlBulkCopy.NotifyAfter = 300000;
                        sqlBulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(sqlBulkCopy_SqlRowsCopied);
                        //欄位對應
                        //sqlBulkCopy.ColumnMappings.Add(“ID”, “ID”);
                        //sqlBulkCopy.ColumnMappings.Add(“Name”, “Name”);
                        //sqlBulkCopy.ColumnMappings.Add(“PurchaseDate”, “PurchaseDate”);

                        CopyStartTime = DateTime.Now;
                        cmdSource.CommandText = “SELECT * FROM SampleData”;
                        using (SqlDataReader drd = cmdSource.ExecuteReader())
                        {
                            sqlBulkCopy.WriteToServer(drd);
                        }
                        Console.WriteLine(“All Rows Copied. It spend {0} seconds.”, new TimeSpan(DateTime.Now.Ticks - CopyStartTime.Ticks).TotalSeconds);
                        Console.ReadLine();
                    }
                }
            }
        }

        private static void sqlBulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine(“{0} Rows Copied”, e.RowsCopied);
        }

執行結果如下完整的程式碼, 放在 Gist

https://gist.github.com/ed0507/edbb5a5e7d97ebf6c2d6e791049548e7