[.NET] 半自動SqlBulkUpdate SQL Server(方案二)

在SQL Server資料庫端SELECT * FROM  .NETSqlBulkCopy大量複製進來的(#TEMP)Temporary Table

  • (方案1)Table variable (user type)
  • (方案2)Temporary table

為了符合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萬筆資料。
--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

從AP端建立來源資料內容(1,000筆),將符合名單內的帳戶點數更新為100點
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;
}
依序在AP端建立連接、產生資料表結構、BulkCopy寫入#TEMP資料、執行指令更新
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

TODO: 探討比較兩個方案效能及操作差異
TODO: 半自動升級自動化: 前端填入資料表名稱及DataTable引數後,依DataTable欄建#TEMP,依目的資料表PK產生T-SQL更新指令碼。