[C#][SQL SERVER]使用SP執行批次作業
很久以前我大概有寫過類似的文章，執行批次作業所帶來的效能改善我在這就不多說，
以前文章中的方法在Entity Framework中執行是沒問題的([C#][EF]呼叫SP並輸出參數)，
當然你要使用EF來執行批次作業也OK，如Entity Framework Extensions (Multiple entity updates) or EntityFramework.Extended
只是我個人使用起來總覺得要處理的小細節太多，
所以我不太建議使用EF來處理資料批次作業，
而且以前的方法就曾有朋友問如沒SQL2008不就沒轍了，
拜G大神後讓我找到另一種方法來達到相同目的，使用起來我個人覺得比較方便。
1.建立測試資料表和資料
create table mytest
(
c1 int identity(1,1) not null primary key,
c2 varchar(10),
c3 nvarchar(20),
c4 datetime
)
insert into mytest select 'rico',N'批次作業',getdate()
insert into mytest select 'rico',N'批次作業',getdate()
insert into mytest select 'rico',N'批次作業',getdate()
insert into mytest select 'rico2',N'批次作業2',getdate()
insert into mytest select 'rico2',N'批次作業2',getdate()
下面我會針對mytest資料表建立三個批次作業類型的SP(Update、Delete、Insert)
2.建立USP_mytestBulk_Update(批次更新)
create proc USP_mytestBulk_Update
(@UpdatedData nvarchar(max),@cols varchar(max),@message nvarchar(4000) output)
as
set nocount on;
begin try
DECLARE @hDoc int
DECLARE @mysql nvarchar(4000)
exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedData ;
set @mysql='UPDATE mytest set '+@cols+' FROM OPENXML( @hDoc , ''ArrayOfMytest/mytest'', 2) WITH ( c1 int , '+char(13)+char(10)
+'c2 varchar(10), c3 nvarchar(20),c4 datetime ) XMLTable WHERE mytest.c1 = XMLTable.c1 '
-- UPDATE mytest
-- SET
-- mytest.c3 = XMLTable.c3
-- FROM OPENXML(@hDoc, 'ArrayOfMytest/mytest', 2)
-- WITH (
-- c1 int ,
--c2 varchar(10),
--c3 nvarchar(20),
--c4 datetime
-- ) XMLTable
--WHERE mytest.c1 = XMLTable.c1
exec sp_executesql @mysql, N'@hDoc int', @hDoc = @hDoc;
--釋放記憶體
EXEC sp_xml_removedocument @hDoc
set @message='Update Success';
end try
begin catch
set @message=ERROR_MESSAGE();
end catch
C# Code
//先找出需被批次更新資料
var myupdates = db.mytest.Where(o => o.c2 == "rico2");
//找出c2==rico2，把c3資料更新為 批次更新操作
foreach (mytest t in myupdates)
{ t.c3=string.Format("批次更新操作{0}",t.c1.ToString()); }
//序列化
XmlSerializer serializer = new XmlSerializer(typeof(mytest[]));
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
serializer.Serialize(sw, myupdates.ToArray<mytest>());
string cols="mytest.c3 = XMLTable.c3";
ObjectParameter messagepara = new ObjectParameter("message","");
db.USP_mytestBulk_Update(sb.ToString(), cols, messagepara);
string result = (string)messagepara.Value;
結果
建立USP_mytestBulk_Delete(批次刪除)
create proc USP_mytestBulk_Delete
(@DeleteData nvarchar(max),@message nvarchar(4000) output)
as
set nocount on;
begin try
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@DeleteData ;
DELETE FROM mytest
WHERE c1 IN
(
SELECT XMLTable.c1
FROM OPENXML(@hDoc, 'ArrayOfMytest/mytest', 2)
WITH (
c1 int ,
c2 varchar(10),
c3 nvarchar(20),
c4 datetime
) XMLTable
)
--釋放記憶體
EXEC sp_xml_removedocument @hDoc
set @message='Delete Success';
end try
begin catch
set @message=ERROR_MESSAGE();
end catch
C# Code
//先找出需被批次刪除資料
var mydels = db.mytest.Where(o => o.c3 == "批次作業");
//序列化
XmlSerializer serializer = new XmlSerializer(typeof(mytest[]));
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
serializer.Serialize(sw, mydels.ToArray<mytest>());
ObjectParameter messagepara = new ObjectParameter("message","");
db.USP_mytestBulk_Delete(sb.ToString(), messagepara);
string result = (string)messagepara.Value;
結果
建立USP_mytestBulk_Insert(批次新增)
create proc USP_mytestBulk_Insert
(@InsertData nvarchar(max),@message nvarchar(4000) output)
as
set nocount on;
begin try
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@InsertData ;
INSERT INTO mytest(c2,c3,c4)
SELECT XMLTable.c2,XMLTable.c3,XMLTable.c4
FROM OPENXML(@hDoc, 'ArrayOfMytest/mytest', 2)
WITH (
c1 int ,
c2 varchar(10),
c3 nvarchar(20),
c4 datetime
) XMLTable
--釋放記憶體
EXEC sp_xml_removedocument @hDoc
set @message='Insert Success';
end try
begin catch
set @message=ERROR_MESSAGE();
end catch
C# Code
//先建立需被批次新增資料
List<mytest> newdatas = new List<mytest>();
for (int i = 0; i < 10; i++)
{
mytest data = new mytest();
data.c1 = i;
data.c2 = string.Format("rico{0}", i.ToString());
data.c3 = "批次新增作業";
data.c4 = DateTime.Now;
newdatas.Add(data);
}
//序列化
XmlSerializer serializer = new XmlSerializer(typeof(mytest[]));
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
serializer.Serialize(sw, newdatas.ToArray<mytest>());
ObjectParameter messagepara = new ObjectParameter("message","");
//db.USP_mytestBulk_Update(sb.ToString(), cols, messagepara);
//db.USP_mytestBulk_Delete(sb.ToString(), messagepara);
db.USP_mytestBulk_Insert(sb.ToString(), messagepara);
string result = (string)messagepara.Value;
結果
參考
Bulk INSERT / UPDATE / DELETE in LINQ to SQL