一起谈.NET技术，linq2sql：直接执行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应该尽量避免。

　　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

一起谈.NET技术，linq2sql：直接执行sql语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景