使用SqlParameter請記住要宣告SqlDbType

使用SqlParameter請記住要宣告SqlDbType

認識筆者的都知道,目前的工作主要在維護系統的比例比較高,開發佔的時間比較少,
維護比較辛苦的就是,必須承接下廠商與開發團隊全部的程式碼,在系統運作發生問題時,
沒有任何資訊的提供下,又在客戶限時要求修復的狀況下,
常咬著牙一行一行去追,而吐了那麼多苦水,這篇要介紹的範例是今天遇到的Case,
客戶那邊的環境是 SQL 2000 , 而這個系統是 ASP.NET 1.1,
客戶今天反應,他們最近資料都存不進去,但也不是全部都存不進去,而是有些資料存不進去,
有些可以,就測試數筆資料後,慢慢發現內容比較多的偶爾會發生問題,內容少的不會有問題,
在去追裡面的程式碼看到的畫面是
SqlParameterCollection sqlParams = cmdUpdate.Parameters;
sqlParams.Add(new SqlParameter("@content", row["content"]));

這邊可以看到,這樣的寫法是很簡潔,但卻沒有特別宣DbType的資料的型態,
而這段執行後轉變成的TSQL會是什麼呢?
答案是 他會把來轉成 varchar(你內容的字數) ,當你認為在後端資料庫表開 NTEXT (SQL2000),
但不宣告SQLDbType的方式,卻是自動轉換成 varchar 的方式,而在 SQL2000  中 varchar最多只能到8000,
在程式碼中不宣告SqlDbType,一般string變數就會轉成varchar的方式,
而在今天的Case中使用 SqlProfiler  抓出這段問題程式碼轉成TSQL去執行的結果,出現這樣的錯誤訊息:
指定 型別 'varchar' 的大小 (8542) 超出任何資料型別所容許的最大值 (8000)。

所以可以知道是不宣告SqlDbType後,自動轉成varchar(8542)而造成的問題,
所以就修改成以下的程式碼。
//2008/11/10 Fix 因為沒宣告 SqlDbType .NText 導致超過varchar(8000)的問題
SqlParameter txtSqlParameter = new SqlParameter("@content",SqlDbType.NText);
txtSqlParameter.Value = row["content"];
sqlParams.Add(txtSqlParameter);

最後看到這各系統的程式碼都是這樣不宣告SqlDbTypr的方式來做SqlParameter,
算了一下整個系統大概需要補300多個,就只好昧著良心只補了目前的問題SqlParameter 。
修正這個問題後,這個問題就處理完成。
在這個案例中,要表達的是,
請不要認為 .NET 會轉成跟後端資料表一樣的型別屬性,
一定要明確的宣告這個SqlParameter 對應的型別屬性才是正確的方式。

PS(不要catch exception 而不做任何事情,維護的人都只能用外圍的工具來抓錯誤)