史丹利好熱

為了符合AP/DB分層及效能需求，現有系統流程是AP端完成批次商業運算，再將大量資料(GB級)整批寫入DB:
(1)早期系統作法是將運算後的資料序列化並壓縮後傳送到資料庫，資料庫端再呼叫SQL CLR 反序列化寫入資料表。
(2)自從.NET 2.0推出SqlbulkCopy功能，若沒有加密需要，底層資料存取元件則直接使用SqlbulkCopy。

一直以來，除了資料新增，運算後有時也進行更新或刪除動作，現有系統流程處理這部分資料操作的流程:

• 1.先BulkCopy複製到實體資料表 
• 2.再呼叫預存程序或執行T-SQL更新或刪除

要先建立實體資料表有點小麻煩，而且當schema變更時又要同步修改，另外交付文件:資料庫清單、文件等...

 

先前重構底層元件時偶然發現幾種新作法，趕緊筆記下來BulkUpdate方案二:
using connection
{

•      1.建立暫存資料表定義。
•      2.BulkCopy寫入#TEMP資料(tempdb)。
•      3.利用T-SQL 操作目的資料表 JOIN #TEMP 進行資料更新或刪除欄位，完成作業需求!

}  

--1.建立
CREATE TABLE TEST_POINT
(                     
       [ID] [varchar](10) ,          --ID           
       [CurrentPoint] [INT] ,        --目前點數          
       [UpdateUser] [VARCHAR](10) ,  --維護人員
       [UpdateTime] [datetime]       --維護時間 
)
--2.新增1萬筆
DECLARE @rows INT = 0;
WHILE @rows < 10000
BEGIN
	SET @rows +=1;
	INSERT INTO TEST_POINT VALUES(CONVERT(VARCHAR(10), @rows),@rows,'initial',GETDATE())
END
--3.確認資料初始內容
SELECT TOP 100 * FROM TEST_POINT

public DataTable BuildDataTable()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("ID", typeof(string));
    dt.Columns.Add("CurrentPoint", typeof(decimal));
    dt.Columns.Add("UpdateUser", typeof(string));
    dt.Columns.Add("UpdateTime", typeof(DateTime));

    for (int i = 0; i < 1000; i++)
    {
        int j = i + 1;
        dt.Rows.Add(new object[] { j.ToString(), 100, "ADO.NET", DateTime.Now });
    }

    return dt;
}

DataTable dt = BuildDataTable();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CN"].ConnectionString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("", conn))
    {
        //(1)產生資料表結構
        cmd.CommandText = @" 
           SELECT * INTO #TEMP FROM TEST_POINT 
           WHERE ID = 'NO ONE' ";
        cmd.ExecuteNonQuery();
        //(2)BulkCopy寫入#TEMP資料
        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
        {
            bulkcopy.DestinationTableName = "#TEMP";
            bulkcopy.WriteToServer(dt);
            bulkcopy.Close();
        }
        //(3)執行指令更新
        cmd.CommandText = @"
        UPDATE TEST_POINT 
           SET CurrentPoint = B.CurrentPoint,
               UpdateUser   = '#TEMP',
               UpdateTime   = B.UpdateTime
        FROM TEST_POINT A JOIN #TEMP B
          ON A.ID = B.ID 
          ";
        int UpdateCount = cmd.ExecuteNonQuery();
        //顯示更新筆數
        Console.WriteLine("Update:{0}",UpdateCount);
    }
}

確認執行結果:資料更新完畢，更新前1,000筆。

另外，從黑暗大的分享聯想ADO.NET直接傳遞Table Vaiable到SP的方案，暫時命名方案一：Sending a DataTable(TVP) To T-SQL/Stored Procedure
http://blog.darkthread.net/post-2011-05-26-import-with-sql-tvp.aspx