LINQ to SQL:如何直接执行SQL语句

开发 后端
我们将谈到在LINQ to SQL环境下,如何直接执行SQL语句的方法,并发布一张调试图供大家参考。

1、ExecuteQuery方法

看命名,我们很容易联想到ADO.NET里熟悉的Command的ExecuteNonQuery方法,但是VS的智能提示告诉我们这个方法返回的是一个泛型集合,应该“所思非所得”。下面通过一个简单方法,验证我们的猜想(数据库设计可以参考这一篇):

  1. /// <summary>   
  2. /// 直接执行sql语句,获取总人数   
  3. /// </summary>   
  4. /// <returns></returns>   
  5.  public int GetTotalCount()   
  6. {   
  7.   string strSql = "SELECT COUNT(0) FROM Person(NOLOCK)";   
  8.    var query = dataContext.ExecuteQuery<int>(strSql);   
  9. int result = query.First<int>();   
  10. Console.WriteLine();   
  11. Console.WriteLine("total count:{0}", result);   
  12. return result;   

调试的时候,通过IntelliTrace跟踪到:

毫无疑问,上面的图片说明最初的想法是不正确的,”ADO.NET:执行Reader…”云云,让我们更加坚信它实际执行的应该是ExecuteReader方法。当然最简单的方法是直接查看它的方法说明:

  1. // 摘要:   
  2. //     直接对数据库执行 SQL 查询并返回对象。   
  3. //   
  4. // 参数:   
  5. //   query:   
  6. //     要执行的 SQL 查询。   
  7. //   
  8. //   parameters:   
  9. //     要传递给命令的参数数组。注意下面的行为:如果数组中的对象的数目小于命令字符串中已标识的最大数,
  10. 则会引发异常。如果数组包含未在命令字符串中引用的对象,则不会引发异常。如果某参数为   
  11. //     null,则该参数会转换为 DBNull.Value。   
  12. //   
  13. // 类型参数:   
  14. //   TResult:   
  15. //     返回的集合中的元素的类型。   
  16.  //   
  17. // 返回结果:   
  18. //     由查询返回的对象的集合。   
  19. public IEnumerable<TResult> ExecuteQuery<TResult>(string query, params object[] parameters); 

ExecuteQuery方法还有一个非泛型方法:

  1. //   
  2. // 摘要:   
  3. //     直接对数据库执行 SQL 查询。   
  4. //   
  5. // 参数:   
  6. //   elementType:   
  7. //       

要返回的 System.Collections.Generic.IEnumerable<T> 的类型。使查询结果中的列与对象中的字段或属性相匹配的算法如下所示:如果字段或属性映射到特定列名称,则结果集中应包含该列名称。如果未映射字段或属性,则结果集中应包含其名称与该字段或属性相同的列。通过先查找区分大小写的匹配来执行比较。如果未找到匹配项,则会继续搜索不区分大小写的匹配项。如果同时满足下列所有条件,则该查询应当返回(除延迟加载的对象外的)对象的所有跟踪的字段和属性:T 

  1. //     是由 System.Data.Linq.DataContext 显式跟踪的实体。
  2. System.Data.Linq.DataContext.ObjectTrackingEnabled   
  3. //     为 true。实体具有主键。否则会引发异常。   
  4. //   
  5. //   query:   
  6. //     要执行的 SQL 查询。   
  7. //   
  8. //   parameters:   
  9. //     要传递给命令的参数数组。注意下面的行为:如果数组中的对象的数目小于命令字符串中已标识的最大数,
  10. 则会引发异常。如果数组包含未在命令字符串中引用的对象,则不会引发异常。如果某参数为   
  11. //     null,则该参数会转换为 DBNull.Value。   
  12. //   
  13. // 返回结果:   
  14. //     由查询返回的对象的 System.Collections.Generic.IEnumerable<T> 集合。   
  15. public IEnumerable ExecuteQuery(Type elementType, string query, params object[] parameters);  
  16.  

看它的参数需要多传递一个elementType,明显不如泛型方法简洁。

2、ExecuteCommand方法

同样道理,这个方法立刻让我们联想到(世界没有联想,生活将会怎样?),联想到,等等,不知联想到什么。然后我们看一下方法使用说明:

  1. //   
  2. // 摘要:   
  3. //     直接对数据库执行 SQL 命令。   
  4. //   
  5. // 参数:   
  6. //   command:   
  7. //     要执行的 SQL 命令。   
  8. //   
  9. //   parameters:   
  10. //     要传递给命令的参数数组。注意下面的行为:如果数组中的对象的数目小于命令字符串中已标识的最大数,
  11. 则会引发异常。如果数组包含未在命令字符串中引用的对象,则不会引发异常。如果任一参数为   
  12. //     null,则该参数会转换为 DBNull.Value。   
  13. //   
  14. // 返回结果:   
  15. //     一个 int,表示所执行命令修改的行数。   
  16. public int ExecuteCommand(string command, params object[] parameters); 

到这里,看它的返回类型为int,表示执行命令修改的行数,这次很容易想到ExecuteNonQuery方法。对不对呢?通过下面的代码证明我们的设想:

  1. /// <summary>   
  2. /// 直接执行sql语句 根据用户Id更新体重   
  3. /// </summary>   
  4. /// <param name="id">用户Id</param>   
  5. /// <param name="destWeight">更新后的体重</param>   
  6. /// <returns></returns>   
  7. public int ModifyWeightById(int id, double destWeight)   
  8. {   
  9.   string strSql = string.Format("UPDATE Person SET Weight={0} WHERE Id={1}", destWeight, id);   
  10.  int result = dataContext.ExecuteCommand(strSql);   
  11.  Console.WriteLine();   
  12. Console.WriteLine("affect num:{0}", result);   
  13. return result;   

调试过程中,通过IntelliTrace可以很清楚地捕获:

“ADO.NET:执行NonQuery…”基本可以断言我们的设想是正确的。

3、防止sql注入

1和2中,执行sql语句的两个方法都有一个params 类型的参数,我们又会想到ADO.NET非常重要的sql语句的参数化防止sql注入问题。下面通过一个方法,看看linq2sql可不可以防止sql注入。

(1)、直接执行拼接的sql语句(有风险)

  1. /// <summary>   
  2. /// 直接执行sql语句 根据用户Id更新FirstName   
  3. /// </summary>   
  4. /// <param name="id">用户Id</param>   
  5. /// <param name="destName">更新后的FirstName</param>   
  6. /// <returns></returns>   
  7. public int ModifyNameById(int id, string destName)   
  8. {   
  9. string strSql = string.Format("UPDATE Person SET FirstName='{0}' WHERE Id={1}", destName, id);
  10. //这么拼接有风险   
  11.  int result = dataContext.ExecuteCommand(strSql);   
  12. Console.WriteLine();   
  13. Console.WriteLine("affect num:{0}", result);   
  14.  return result;   
  15.  } 

然后,在客户端这样调用这个方法:

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

 

运行的时候,直接抛出异常,提示“Anders”附近有语法错误。毫无疑问,它执行的sql语句:

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

是不能通过的,同时证明了string.format函数不能有效防止sql注入。

(2)、直接通过linq方法的参数传递,结果如何呢?

改进(1)的传参方法:

  1. /// <summary>   
  2. /// 直接执行sql语句 根据用户Id更新FirstName   
  3. /// </summary>   
  4. /// <param name="id">用户Id</param>   
  5. /// <param name="destName">更新后的FirstName</param>   
  6. /// <returns></returns>   
  7. public int ModifyNameById(int id, string destName)   
  8.  {   
  9. //string strSql = string.Format("UPDATE Person SET FirstName='{0}' WHERE Id={1}", destName, id);
  10. //这么拼接有风险   
  11. //int result = dataContext.ExecuteCommand(strSql);   
  12.  string strSql = "UPDATE Person SET FirstName={0} WHERE Id={1}";   
  13. int result = dataContext.ExecuteCommand(strSql, new object[] { destName, id });   
  14. Console.WriteLine();   
  15.  Console.WriteLine("affect num:{0}", result);   
  16.  return result;   

再次通过如下的方式调用:

  1. int result = ServiceFactory.CreatePersonService().ModifyNameById(10, "'Anders'");
  2. //更新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

【编辑推荐】

  1. Linq匿名类型简单概述
  2. Linq随机读取数据浅析
  3. Linq Lambda表达式全面分析
  4. Linq扩展方法简单分析
  5. 初探Linq局部变量类型
责任编辑:彭凡 来源: 博客园
相关推荐

2009-09-07 16:25:14

Linq To SQL

2009-09-10 10:37:15

LINQ to SQL

2009-09-10 18:02:23

LINQ to SQL

2009-09-09 18:03:47

LINQ to SQL

2010-09-25 16:21:41

SQL语句

2009-09-16 17:44:54

LINQ to SQL

2010-09-06 11:24:32

SQL Server语句

2010-09-06 13:17:19

SQL Server语句

2013-10-29 16:27:23

MySQLSQL语句

2009-09-08 14:45:24

Linq to SQL支持SQL Serve

2022-07-28 09:13:30

MySQL数据库

2009-09-18 14:33:37

LINQ to SQLSQL命令

2010-09-25 14:59:54

SQL语句

2010-09-03 14:47:50

SQLSELECT语句

2010-06-09 13:47:31

MySQL直接备份数据

2010-09-06 13:41:42

sql server语句

2010-09-27 10:55:01

SQL事件探查器

2009-09-16 17:33:16

LINQ TO SQL

2009-09-11 12:08:09

Linq to SQL

2009-09-09 14:40:43

Linq to sql
点赞
收藏

51CTO技术栈公众号