你的SQL語句的參數(shù)化總是個(gè)好想法。使用參數(shù)化SQL語句你不會(huì)污染你的計(jì)劃緩存——錯(cuò)!!!在這篇文章里我想向你展示下用參數(shù)化SQL語句就可以污染你的計(jì)劃緩存，這是非常簡單的!

　　ADO.NET-AddWithValue

　　ADO.NET是實(shí)現(xiàn)像SQL Server關(guān)系數(shù)據(jù)庫數(shù)據(jù)訪問的.NET框架的組成——有一些嚴(yán)重的副作用。不要誤解我——只要你正確使用，ADO.NET一直很棒。你馬上就會(huì)看到，它很容易被錯(cuò)誤使用。我們來看下面實(shí)現(xiàn)SQL語句執(zhí)行的C#代碼。

for (int i = 1; i <= 100; i++) 
{ 
   val += i.ToString(); 
 
   cmd = new SqlCommand( 
      "SELECT * FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = @CarrierTrackingNumber",  
      cnn); 
   cmd.Parameters.AddWithValue("@CarrierTrackingNumber", val); 
   SqlDataReader reader = cmd.ExecuteReader(); 
   reader.Close(); 
} 

　　我們是聰明的開發(fā)者，因此SQL語句本身被參數(shù)化，因?yàn)锳DO.NET框架是地球上最棒的框架，我們使用System.Data.SqlClient.SqlParameterCollection類的AddWithValue方法來提供實(shí)際的參數(shù)值。我在WHLIE循環(huán)里運(yùn)行那個(gè)SQL語句100次，總用不同長度賦予參數(shù)值。在Sales.SalesOrderDetail表里CarrierTrackingNumber列定義為NVARCHAR(25)。因此我們可以在基于我們提供的不同字符長度上有上至25個(gè)不同數(shù)據(jù)類型的參數(shù)。現(xiàn)在讓我們檢查下我們SQL語句執(zhí)行后的計(jì)劃緩存。

1 SELECT 
2     st.text, 
3     cp.* 
4 FROM sys.dm_exec_cached_plans cp 
5 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st 
6 GO 

　　現(xiàn)在事情變得有點(diǎn)瘋狂：在計(jì)劃緩存里我們存儲(chǔ)了100個(gè)不同的執(zhí)行計(jì)劃!

　　對于每個(gè)可能的數(shù)據(jù)類型參數(shù)都有1個(gè)執(zhí)行計(jì)劃——即使當(dāng)數(shù)據(jù)類型是NVACHAR(2***ddWithValue方法非常，非常邪惡：基于你提供的參數(shù)值派生出數(shù)據(jù)類型。永遠(yuǎn)不要使用它!

　　ADO.NET – SqlDbType.VarChar

　　因?yàn)閺奈覀兊腻e(cuò)誤中我們學(xué)到了，現(xiàn)在我們知道ADO.NET的AddWithValue方法的副作用——我們不再用它。現(xiàn)在讓我們重寫我們的C#程序代碼，如下所示定義一個(gè)顯示的參數(shù)數(shù)據(jù)類型：

for (int i = 1; i <= 100; i++) 
{ 
   val += i.ToString(); 
 
   cmd = new SqlCommand( 
      "SELECT * FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = @CarrierTrackingNumber", 
      cnn); 
   cmd.Parameters.Add(new SqlParameter("@CarrierTrackingNumber", SqlDbType.VarChar)); 
   cmd.Parameters["@CarrierTrackingNumber"].Value = val; 
   SqlDataReader reader = cmd.ExecuteReader(); 
   reader.Close(); 
} 

　　從代碼里你可以看到，ADO.NET現(xiàn)在不能派生參數(shù)數(shù)據(jù)類型了，因?yàn)槲覀円呀?jīng)指定了SqlDbType.Varchar數(shù)據(jù)類型。讓我們再次執(zhí)行這個(gè)SQL語句100次并再次檢查下計(jì)劃緩存：

　　沒有啥改變。問題還是一樣：在計(jì)劃緩存里我們還有100個(gè)不一樣的的執(zhí)行計(jì)劃。現(xiàn)在的問題是ADO.NET只強(qiáng)制數(shù)據(jù)類型(SqlDbType.VarChar)，但不是數(shù)據(jù)類型的"長度"。有100個(gè)不同的長度在計(jì)劃緩存里你就有100個(gè)不同的執(zhí)行計(jì)劃。

　　如果你在你的ADO.NET代碼里顯式指定參數(shù)數(shù)據(jù)類型，你也要指定它的長度!現(xiàn)在我們來看下一些修正的C#代碼。

for (int i = 1; i <= 100; i++) 
{ 
   val += i.ToString(); 
 
   cmd = new SqlCommand( 
      "SELECT * FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = @CarrierTrackingNumber", 
      cnn); 
   cmd.Parameters.Add(new SqlParameter("@CarrierTrackingNumber", SqlDbType.VarChar, 100)); 
   cmd.Parameters["@CarrierTrackingNumber"].Value = val; 
   SqlDataReader reader = cmd.ExecuteReader(); 
   reader.Close(); 
} 

　　小結(jié)

　　寓意：ADO.NET是個(gè)很棒的數(shù)據(jù)訪問框架，它提供你有用的功能(例如AddWithValue方法)，當(dāng)從SQL Server角度來說你真的要考慮下你在做什么。當(dāng)你使用參數(shù)化SQL語句時(shí)，你要盡量顯式：你必須地冠以參數(shù)值的實(shí)際數(shù)據(jù)類型，還有你想要的獲得數(shù)據(jù)類型長度。

　　感謝關(guān)注!

　　注：此文章為WoodyTu學(xué)習(xí)MS SQL技術(shù)，收集整理相關(guān)文檔撰寫，歡迎轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁面明顯位置給出此文鏈接!