[ADO.NET] 善用 SqlDataAdapter 執行大量指令並傳回查詢資料

今天來介紹一下好用的 DataAdapter

---

I. 前言

很久很久以前，坎尼常遇到一個問題
「新增完一筆資料之後，要回傳該資料的ID」

如果 ID 是自己取 Max + 1 就還能控制
但要是遇到是用 sequence 方式建立的流水號
往往還要拆成兩段語法，建立完資料之後再取出

其實大可不必那麼麻煩，可以將 SQL Statement 串接在一起
(記得用 ; 隔開不同的指令)
再用 SqlCommand 或 SqlDataAdapter 執行即可

II. 實戰

下圖綠色框部分，用 Sql Server 提供的 Temp Tables 語法
分別建立了兩個臨時資料表，並各加入兩筆資料
再用 SELECT 語法將兩個資料表 JOIN 的結果回傳
既然 Create 和 Insert 語法都在 SelectCommand 裡
參數當然也是要加進 SelectCommand 裡 (見上圖藍色框)
記得不要放到 UpdateCommand 或 InsertCommand 去

實際執行情況：確實建立了 Table 並取得 JOIN 成功的資料


另外坎尼又做了小小的測驗，驗證一下臨時資料表有沒有被砍掉
 
事實證明，在上一次 .Fill 結束之後，暫存資料表是會自動清掉
而 Temp Table 的定義是：只存在該使用者連線時
 
這表示 DataAdapter 在 .Fill 之後就會自動把連線關閉
下一次的 .Fill 其實是另外再重新開啟另一個連線

III. 小結

坎尼以前常看到有人用 DataAdapter
卻在執行前後加上 Connection.Open() 及 Connection.Close()
其實這些動作都是不必要的

DataAdapter 在執行時，都會檢查目前和 DB 的連線
執行結束後即會自動關閉與 DB 的連線

希望這些觀念對讀者們有幫助 :)

探索 SqlCommand.Parameters.Add 定義資料值長度

其實這個問題也是老早之前就遇到 (大概一年半前吧...)
所以這兩天坎尼又重新測了一下問題發生的原因

---

先講一下這次的問題所在
SqlCommand.Parameters.Add 有個多載是可以傳入參數值的 size
但在 varchar 的狀態下，傳入中文並不會把值鎖定在 10，以至於系統拋出了例外

先來看範例，首先是資料表定義，有個 varchar(10) 的欄位

接著是可正常運作的範例

相信大家都知道，中文字在 varchar 中會佔 2 單位
所以6個中文字以上的字串，是無法新增至上面所定義的欄位表


當時坎尼就是遇到要把中文字存到 varchar 的欄位中
但很明顯的，size參數完全起不了作用
而且同事還說要不要用 String.SubString 的方法把資料截斷啊?

年幼的坎尼還真的照做了，但問題依舊存在
正確的做法應該是要先轉成 byte[] ，再將資料截斷送入

原本的 "無敵鐵金鋼2" 就被截斷後再送入資料庫中

但這個解法只限於傳入的值皆為 2bytes 的字
一但需截斷的資料中有1byte的字，就可能發生中文字被腰斬的情況



上面談了這麼多
是不是要證明 SqlCommand.Parameters.Add 的第3個 size 參數沒有用呢?
當然不是，它當然有用，只是在上面範例中不適合
其實只要把字串改成全為英文，Parameter就會自動把超過的字串切掉



上述例子中沒有作用的原因為：
中文字才6個，還未達到Length=10的瓶頸
但又因為中文字每個字代表2bytes，所以早就超過資料表裡的限制
中華文化博大精深，一般人尚無法精通，何況電腦

字串切割會以字的數目，而非字所佔的容量
「一二3四五」和「12345」同樣是算 5 個字
用 String.Substring(0,6) 即會拋出 index 不符的例外

但其實上述的範例真正的解決方式為...
應將有可能出現中文字的欄位設為 nvarchar，且前端要針對輸入的字作控管

最後補充一下小小的發現
上面 Table 裡的 ID 值是用遞增方式，但卻少了 2，難道是坎尼自己偷刪嗎?
其實是上述的測試中，坎尼故意傳了大於欄位長度的值進去，因此在 Sql 方面出現例外
不過遞增值的 flag 似乎自動加一了? 所以才會跳過該數字

SQL語法組成，邏輯運算子 vs Between

最近坎尼出差都回不了家
在昏暗的飯店房間裡根本沒心情上來寫文章 真抱歉
Tim最近也忙於公司的事 看來大家都很忙 哈

---

好，今天要講的是 SQL 語法的組成
坎尼看到同事寫的 取某期間內的資料 的 Sql 語法
(起始日 和 結束日 並未強制使用者輸入)

首先 他傳了 date1 和 date2 (date1為起始日 date2為結束日)
接著寫下了下面的組成判斷式

if (date1 != "" && date2 != "")
strSql += " and date between @date1 and @date2 ";
else if (date1 != "" && date2 == "")
strSql += " and date >= @date1 ";
else if (date1 == "" && date2 != "")
strSql += " and date <= @date2 ";

ok 以上還是坎尼幫他簡化過的 Sql 語法
然後坎尼看到了所有跟日期有關的 Sql 部分 都是寫成類似上述的判斷

應該有人會說 這麼寫有錯嗎?
坎尼跟你說：沒錯啊 XDDDD (瞬間被打飛)

不是啦，坎尼是想說，這只要兩行就可以結束的東西，不必寫這麼多啦
以下是坎尼改寫過的 Sql 組裝句
strSql += (date1 != "") ? " and date >= @date1 " : "" ;
strSql += (date2 != "") ? " and date <= @date2 " : "" ;

怎樣？是不是簡潔許多？ (看不懂上面寫法的人 請Google 三元運算式 )
在這種不確定是否有兩個值傳入的比較式
坎尼不會輕易動用 between (平常也沒用between的習慣就是)

當然，這也可以用於其他 數值比較 的語法中
只要條件和上述的一樣：不強制輸入值、比較值需與傳入值(複數)作比較
都可以用上述的寫法
不要再寫成 落落等 的一大堆 if else 啦 不然以後維護的人員會很痛苦 (就是坎尼)

SQL 語法 max、mix、count...取值觀念釐清

還記得坎尼在 自定固定長度流水號的方法 中有提到過
要取得新ID 必需先將目前ID最大值給傳入嗎?
坎尼的作法就是利用 T-SQL 語法中的 select max(id) from table 來取得最大值

而最近坎尼一直在改同事的程式，發現到一個怪現象
他們一樣和坎尼用 max 去抓取目前最大值 id
然後回傳的是 DataTable，接著再用 DataTable.Rows.Count 去判斷

if(DataTable.Rows.Count != 0)
　ID = DataTable.Rows[0][0].ToString();
else
　ID = "0";

有人看出哪不對勁了嗎?

大家先看一下下列美美的抓圖







知道為什麼坎尼要下第一個 select 語法嗎?
第一個 select 不是什麼東西都抓不到嗎?
沒錯! 就是什麼都抓不到!
此時若是把這個查詢塞進一個 DataTable 中，DataTable.Rows.Count = 0

那為什麼要提這個呢?
原因就在於
當我們用 max、min 、count、avg....等語法時
如果資料表內無資料
它仍然會回傳一個值 (count是回傳0，max min avg則是回傳null)

所以，上述的判斷條件式應該改為
if(DataTable.Rows[0][0].ToString() != "")
　ID = DataTable.Rows[0][0].ToString();
else
　ID = "0";

同樣的bug坎尼已經抓過數次 (還是不同的同事寫的 囧>
所以提出此觀念讓還不知道的人了解一下

以上測試環境皆以 MS SQL 2000 為基準

課後補充
坎尼prefer的作法是，用MS SQL裡的 IsNull來判斷 (Oracle是NVL)
這樣抓取資料的人也不用再去判斷來判斷去

語法是 select IsNull(max(ID) , 0) as MaxID from Table