LINQ to SQL:如何直接執行SQL語句

作者：Jeff Wong 2010-11-04 09:43:46

我們將談到在LINQ to SQL環境下，如何直接執行SQL語句的方法，并發布一張調試圖供大家參考。

1、ExecuteQuery方法

看命名，我們很容易聯想到ADO.NET里熟悉的Command的ExecuteNonQuery方法，但是VS的智能提示告訴我們這個方法返回的是一個泛型集合，應該“所思非所得”。下面通過一個簡單方法，驗證我們的猜想（數據庫設計可以參考這一篇）：

/// <summary>   
/// 直接執行sql語句，獲取總人數   
/// </summary>   
/// <returns></returns>   
 public int GetTotalCount()   
{   
  string strSql = "SELECT COUNT(0) FROM Person(NOLOCK)";   
   var query = dataContext.ExecuteQuery<int>(strSql);   
int result = query.First<int>();   
Console.WriteLine();   
Console.WriteLine("total count:{0}", result);   
return result;   
}

調試的時候，通過IntelliTrace跟蹤到：

毫無疑問，上面的圖片說明最初的想法是不正確的,”ADO.NET:執行Reader…”云云，讓我們更加堅信它實際執行的應該是ExecuteReader方法。當然最簡單的方法是直接查看它的方法說明：

// 摘要:   
//     直接對數據庫執行 SQL 查詢并返回對象。   
//   
// 參數:   
//   query:   
//     要執行的 SQL 查詢。   
//   
//   parameters:   
//     要傳遞給命令的參數數組。注意下面的行為：如果數組中的對象的數目小于命令字符串中已標識的最大數，
則會引發異常。如果數組包含未在命令字符串中引用的對象，則不會引發異常。如果某參數為   
//     null，則該參數會轉換為 DBNull.Value。   
//   
// 類型參數:   
//   TResult:   
//     返回的集合中的元素的類型。   
 //   
// 返回結果:   
//     由查詢返回的對象的集合。   
public IEnumerable<TResult> ExecuteQuery<TResult>(string query, params object[] parameters);

ExecuteQuery方法還有一個非泛型方法：

//   
// 摘要:   
//     直接對數據庫執行 SQL 查詢。   
//   
// 參數:   
//   elementType:   
//

要返回的 System.Collections.Generic.IEnumerable<T> 的類型。使查詢結果中的列與對象中的字段或屬性相匹配的算法如下所示：如果字段或屬性映射到特定列名稱，則結果集中應包含該列名稱。如果未映射字段或屬性，則結果集中應包含其名稱與該字段或屬性相同的列。通過先查找區分大小寫的匹配來執行比較。如果未找到匹配項，則會繼續搜索不區分大小寫的匹配項。如果同時滿足下列所有條件，則該查詢應當返回（除延遲加載的對象外的）對象的所有跟蹤的字段和屬性：T

//     是由 System.Data.Linq.DataContext 顯式跟蹤的實體。
System.Data.Linq.DataContext.ObjectTrackingEnabled   
//     為 true。實體具有主鍵。否則會引發異常。   
//   
//   query:   
//     要執行的 SQL 查詢。   
//   
//   parameters:   
//     要傳遞給命令的參數數組。注意下面的行為：如果數組中的對象的數目小于命令字符串中已標識的最大數，
則會引發異常。如果數組包含未在命令字符串中引用的對象，則不會引發異常。如果某參數為   
//     null，則該參數會轉換為 DBNull.Value。   
//   
// 返回結果:   
//     由查詢返回的對象的 System.Collections.Generic.IEnumerable<T> 集合。   
public IEnumerable ExecuteQuery(Type elementType, string query, params object[] parameters);

看它的參數需要多傳遞一個elementType，明顯不如泛型方法簡潔。

2、ExecuteCommand方法

同樣道理，這個方法立刻讓我們聯想到（世界沒有聯想，生活將會怎樣？），聯想到，等等，不知聯想到什么。然后我們看一下方法使用說明：

//   
// 摘要:   
//     直接對數據庫執行 SQL 命令。   
//   
// 參數:   
//   command:   
//     要執行的 SQL 命令。   
//   
//   parameters:   
//     要傳遞給命令的參數數組。注意下面的行為：如果數組中的對象的數目小于命令字符串中已標識的最大數，
則會引發異常。如果數組包含未在命令字符串中引用的對象，則不會引發異常。如果任一參數為   
//     null，則該參數會轉換為 DBNull.Value。   
//   
// 返回結果:   
//     一個 int，表示所執行命令修改的行數。   
public int ExecuteCommand(string command, params object[] parameters);

到這里，看它的返回類型為int，表示執行命令修改的行數，這次很容易想到ExecuteNonQuery方法。對不對呢？通過下面的代碼證明我們的設想：

/// <summary>   
/// 直接執行sql語句 根據用戶Id更新體重   
/// </summary>   
/// <param name="id">用戶Id</param>   
/// <param name="destWeight">更新后的體重</param>   
/// <returns></returns>   
public int ModifyWeightById(int id, double destWeight)   
{   
  string strSql = string.Format("UPDATE Person SET Weight={0} WHERE Id={1}", destWeight, id);   
 int result = dataContext.ExecuteCommand(strSql);   
 Console.WriteLine();   
Console.WriteLine("affect num:{0}", result);   
return result;   
}

調試過程中，通過IntelliTrace可以很清楚地捕獲：

“ADO.NET:執行NonQuery…”基本可以斷言我們的設想是正確的。

3、防止sql注入

1和2中，執行sql語句的兩個方法都有一個params 類型的參數，我們又會想到ADO.NET非常重要的sql語句的參數化防止sql注入問題。下面通過一個方法，看看linq2sql可不可以防止sql注入。

（1）、直接執行拼接的sql語句（有風險）

/// <summary>   
/// 直接執行sql語句 根據用戶Id更新FirstName   
/// </summary>   
/// <param name="id">用戶Id</param>   
/// <param name="destName">更新后的FirstName</param>   
/// <returns></returns>   
public int ModifyNameById(int id, string destName)   
{   
string strSql = string.Format("UPDATE Person SET FirstName='{0}' WHERE Id={1}", destName, id);
//這么拼接有風險   
 int result = dataContext.ExecuteCommand(strSql);   
Console.WriteLine();   
Console.WriteLine("affect num:{0}", result);   
 return result;   
 }

然后，在客戶端這樣調用這個方法：

int result = ServiceFactory.CreatePersonService().ModifyNameById(10, "'Anders'");
//更新id為10的人的FirstName

運行的時候，直接拋出異常，提示“Anders”附近有語法錯誤。毫無疑問，它執行的sql語句：

UPDATE Person SET FirstName=''Anders'' WHERE Id=10

是不能通過的，同時證明了string.format函數不能有效防止sql注入。

（2）、直接通過linq方法的參數傳遞，結果如何呢？

改進（1）的傳參方法：

/// <summary>   
/// 直接執行sql語句 根據用戶Id更新FirstName   
/// </summary>   
/// <param name="id">用戶Id</param>   
/// <param name="destName">更新后的FirstName</param>   
/// <returns></returns>   
public int ModifyNameById(int id, string destName)   
 {   
//string strSql = string.Format("UPDATE Person SET FirstName='{0}' WHERE Id={1}", destName, id);
//這么拼接有風險   
//int result = dataContext.ExecuteCommand(strSql);   
 string strSql = "UPDATE Person SET FirstName={0} WHERE Id={1}";   
int result = dataContext.ExecuteCommand(strSql, new object[] { destName, id });   
Console.WriteLine();   
 Console.WriteLine("affect num:{0}", result);   
 return result;   
}

再次通過如下的方式調用：

int result = ServiceFactory.CreatePersonService().ModifyNameById(10, "'Anders'");
//更新id為10的人的FirstName

這一次運行正常嗎？經測試，真的真的是真的正常，終于可以長松一口氣了。查看數據庫，Id為10的那一條記錄的FirstName結果改變成了“'Anders'”（帶單引號），明白ADO.NET工作原理的都知道這個一點也不神奇，不是嗎？

對比（1）和（2），我們發現還是用linq2sql的源生方法傳參比較好，通過拼接sql應該盡量避免。

到這里，可能你會認為上面1、2、3全是廢話，知道怎么用不就行了嘛？！其實很長一部分時間我也是這么想這么做的。工作久了，早就習慣了不求甚解，現在看書，看園子里高手的博客，多多少少會有點反思。

4、思考

ADO.NET中比較常用的ExecuteScalar方法，在linq2sql應該怎么實現呢？您嘗試使用過了嗎？

原文鏈接：http://www.cnblogs.com/jeffwongishandsome/archive/2010/11/03/1868438.html

【編輯推薦】

Linq匿名類型簡單概述
Linq隨機讀取數據淺析
Linq Lambda表達式全面分析
Linq擴展方法簡單分析
初探Linq局部變量類型

責任編輯：彭凡來源：博客園

LINQ to SQL