建立动态SQL查询的最佳做法 [英] Best practice to build dynamic SQL queries

问题描述

我正在寻找一些有关构建动态查询的技巧.我有一个应用程序，允许用户搜索数据库表中的10个字段.根据用户界面中哪些字段填充了值，查询应在数据库的其他字段中进行搜索.

I am looking for some tips and tricks how to build dynamic queries. I am having an application which lets the user search 10 fields in the database table. Depending on which fields in the UI are filled with a value the query should search in an additional field in the DB.

当前，我正在尝试使用StringBuilder构建查询并添加where子句，但我真的不喜欢这样，我想知道是否有更好的方法可以做到这一点，例如，如果可能的话，可以使用LINQ.

Currently I am trying to build the query using StringBuilder and adding the where clause but I really don't like this and I am wondering if there is a better way to do this, for example with LINQ if possible.

也许有人可以提出想法，或者更好地编写一些示例代码.谢谢，祝你有美好的一天！

Maybe someone can bring up ideas or better some example code. Thanks and have a nice day!

推荐答案

对于LINQ来说，它是微不足道的:

With LINQ it is pretty trivial:

IQueryable<User> users = db.Users;

if(name != null) users = users.Where(u => u.Name == name);
if(dept != null) users = users.Where(u => u.Dept == dept);
...

var page = users.OrderBy(u => u.Name).Take(100).ToList();

每个连续的Where 组成带有更多过滤器的查询；正是您想要的.

Each successive Where composes the query with more filters; exactly what you want.

使用原始TSQL，StringBuilder并非没有道理.只需确保将其完全参数化即可.这可能意味着在每个术语中添加参数.例如:

With raw TSQL, StringBuilder isn't unreasonable; just make sure that you fully parameterize it. This might mean adding parameters in each term; for example:

...
if(name != null) {
    sql.Append(" and u.Name = @name");
    cmd.Parameters.AddWithValue("name", name);
}
if(dept != null) {
    sql.Append(" and u.Dept = @dept");
    cmd.Parameters.AddWithValue("dept", dept);
}
...

这篇关于建立动态SQL查询的最佳做法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！