iT邦幫忙

4

[筆記][MSSQL]執行多段指令時(1) - 發生錯誤就停止執行

昨天在幫客戶處理匯入資料的問題,因為資料量實在很龐大,加上匯到一半時客戶又通知可能還會有更多資料需要匯入,小弟我就默默的關掉客戶提供的EXCEL表,打算幫他寫個匯入功能。

選擇的方式是用NPOI讀EXCLE檔案(關於NPOI等假日比較有時間會寫一篇簡單的實作教學),再用迴圈把T-SQL串起來執行,這邊相信各位大大都有一套自己的方式,而我昨天組合好的T-SQL需要新增的資料表有五個以上,指令也有上千行,資料量一大要發現錯誤就很困難,加上就算給客戶範例格式,可能也還是會有一堆問題。

所以我在思考的是假設今天客戶自行準備了一千多筆資料要匯入,但是匯入時在某一筆發生錯誤SQL還是會繼續執行下一個INSERT指令一直到結束,這麼一來在整份匯入資料中就會有廖廖幾筆資料沒有成功,而客戶必須要把那些資料修正後抓出來,另存一份EXCEL檔才能再次匯入,但是但是以上看起來很正常的流程其實並不是客戶想要的,因為他們大多都會想要在原EXCEL修正後直接匯入(還是我遇到的客戶都比較懶?XD),而這種方式也比較符合他們的作業方式。

以上說了那麼多,接下來要開始進入正文了,針對這個方式我們會需要用到交易和Error Handle這兩個語法,以下使用北風資料庫的[Order Details]做測試:

首先第一段是比較一般的SQL:

  INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
  VALUES('11078','11','14.00','10','0')

  INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
  VALUES('11078','1111111111111111111111111111111111111','14.00','10','0')

  INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
  VALUES('11078','12','14.00','10','0')

執行結果:
https://ithelp.ithome.com.tw/upload/images/20171017/20106935d7Fx5EIKUR.jpg
查詢剛剛新增的資料,發現雖然第二筆資料錯誤了,但第三筆還是會執行
https://ithelp.ithome.com.tw/upload/images/20171017/20106935GrWnLxomBw.jpg

那假如我們把交易和Error Handle加進剛剛的語法裡面,SQL會變成:

--在一開始先用TRY把主要的新增SQL包起來
BEGIN TRY

--之後開啟交易
BEGIN TRANSACTION

  --執行新增
  INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
  VALUES('11078','11','14.00','10','0')

  INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
  VALUES('11078','1111111111111111111111111111111111111','14.00','10','0')

  INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
  VALUES('11078','12','14.00','10','0')

  --這邊是利用TRY的特性,如果能跑到這一行的話代表以上的指令執行都沒有錯誤,既然沒有錯誤,就可以直接「確認交易」。
  COMMIT TRANSACTION

END TRY

BEGIN CATCH

--這邊也是利用TRY在出錯時會跑到CATCH的特性,讓他一出錯就到這CATCH裡面執行「回復交易」的動作。
ROLLBACK TRANSACTION

END CATCH

執行結果,他不會執行第三段T-SQL,因為在執行第二段出錯時就跳到CATCH了。
https://ithelp.ithome.com.tw/upload/images/20171017/2010693528QLzl160J.jpg
查詢剛剛新增的資料,會發現什麼都沒有,因為他在第二筆報錯時就回復了交易內所有的更動
https://ithelp.ithome.com.tw/upload/images/20171017/20106935JujjBiry5V.jpg

以上是如何在發生錯誤時停止執行SQL,因為昨天查了一下資料似乎沒有相關的資料,所以才拼出這個方式,如果各位大大有更好的做法也可以留言告訴我,我都會去嘗試的!!

Function:
1.開啟交易(BEGIN TRANSACTION):
[筆記][MSSQL]關於SQL的交易概念
2.Error Handle:

BEGIN TRY
--監控在TRY裡的指令,如果出錯就會跳到CATCH中繼續執行
END TRY

BEGIN CATCH
--當TRY裡執行的指令錯誤,就會執行CATCH內的指令
END CATCH

因為這一篇還沒有講到發生錯誤時除錯的處理,打在一起感覺又偏離主題,就下一篇在講吧!應該明天就會打上來了,應該...XD

第二篇:
[筆記][MSSQL]執行多段指令時(2) - 發生錯誤後的除錯方式

謝謝各位!!


圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

0
小碼農米爾
iT邦高手 1 級 ‧ 2017-10-18 00:04:20

Excel匯入我都用C#做,但用C#效能會比較差一點,不過可以做更細緻的錯誤提示,我會在進SQL之前先把該驗證的都驗一驗,全部通過才執行SQL,驗不過會返回詳細的錯誤提示,然後執行SQL也是出錯全部打回,不過這時的錯誤提示就不是整理過的,會直接出系統的錯誤訊息,一開始的驗證會盡量讓提示訊息好懂,方便使用者修改Excel,以上小弟經驗,不過用C#效能真的會和SQL差一大截,資料量大會需要搭配進度條。

看更多先前的回應...收起先前的回應...
神Q超人 iT邦研究生 5 級 ‧ 2017-10-18 08:33:36 檢舉

我也是用C#組SQL的,不過我在C#裡面就沒有做其它判斷,錯誤處理的提示也沒辦法像在C#中一行一行檢查還來的仔細,只能看情況做事了!
那你SQL是用什麼樣的方式把資料全部打回的0_0?

我是用 C# 不過意思一樣,也是用 try catch 做XD,錯誤訊息可以在 Exception 取到。

var tran = cn.BeginTransaction();

try
{
    ---執行SQL語法---
    ExecuteSQL();
    ExecuteSQL();
    ExecuteSQL();
    ---執行SQL語法---
    
    tran.Commit();
}
catch(Exception ex)
{
    tran.Rollback();
}
小魚 iT邦大師 1 級 ‧ 2017-10-18 12:00:32 檢舉

Try Catch +1

神Q超人 iT邦研究生 5 級 ‧ 2017-10-18 12:43:36 檢舉

原來在C#內也有提供交易的處理哦!
又學到一課惹!

OMG所以我才是異類嗎?哈哈

哈哈,不會啊,我的同事也有喜歡用SQL處理的,只是我不喜歡串SQL,依個人喜好選用即可XD

我要留言

立即登入留言