linq2sql:直接執行sql語句

前言：使用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