linq2sql：直接執行sql語句

作者: Jeff Wong 來源: 博客園發布時間: 2010-11-07 20:55 閱讀: 2783 次推薦: 0 原文鏈接 [收藏]

摘要：使用linq很長一段時間了，但是對linq2sql還是不很喜歡，感覺能控制的實在太少，限于實際項目實踐的各種原因，自己再總結一下，備用。

　　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應該盡量避免。

　　update:當我們把（2）中的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)
　　{
　　　　id = 1;
　　　　//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;
　　}

　　雖然運行也正常，但是數據庫中Id為10的那一條記錄的FirstName結果改變成了”@p0”，也就是說使用linq的方法，拼接sql傳參的地方不需要加單引號。

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

　　4、思考

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

　　demo下載：demo

標簽：linq2sql