• Merry   2014/10/11 11:16:00
  • Linq查询中直接执行sql语句的各种情况总结
  • 关键字: Linq 执行SQL 返回空值
  • 1、ExecuteQuery方法

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    /// <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跟踪到:

    executereader

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

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

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    //
     // 摘要:
     //     直接对数据库执行 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方法

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

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

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    /// <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可以很清楚地捕获:

    executenoquery

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

     

    3、防止sql注入

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

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    /// <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;
    }

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

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

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

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

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

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

    改进(1)的传参方法:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    /// <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;
    }

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

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

    这一次运行正常吗?经测试,真的真的是真的正常,终于可以长松一口气了。查看数据库,Id为10的那一条记录的FirstName结果改变成了“'Anders'”(带单引号),明白ado.net工作原理的都知道这个一点也不神奇,不是吗?

    对比(1)和(2),我们发现还是用linq2sql的源生方法传参比较好,通过拼接sql应该尽量避免。

    update:当我们把(2)中的sql语句第一个参数加上单引号,写成下面的形式:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    /// <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应该怎么实现呢?您尝试使用过了吗?