在SQL Server資料庫端SELECT * FROM .NET端 SqlBulkCopy大量複製進來的(#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更新指令碼。
TODO: 半自動升級自動化: 前端填入資料表名稱及DataTable引數後,依DataTable欄建#TEMP,依目的資料表PK產生T-SQL更新指令碼。