构建动态SQL查询在C＃/。NET3.5的最佳方法是什么？ [英] Best way of constructing dynamic sql queries in C#/.NET3.5?

问题描述

一个项目我工作的那一刻涉及重构一个C＃的COM对象它作为一个数据库访问层的一些SQL 2005数据库。

A project I'm working on at the moment involves refactoring a C# Com Object which serves as a database access layer to some Sql 2005 databases.

的存在code笔者已建成的所有手动使用一个字符串，并且许多if语句来构造相当复杂的SQL语句（〜10联接，> 10分选择的SQL查询，〜15-25那里的条件和GROUPBY的）。基表始终是相同的，但是结构联接，条件和分组依赖于一组参数传递到我的类/方法。

The author of the existent code has built all the sql queries manually using a string and many if-statements to construct the fairly complex sql statement (~10 joins, >10 sub selects, ~15-25 where conditions and GroupBy's). The base table is always the same one, but the structure of joins, conditions and groupings depend on a set of parameters that are passed into my class/method.

构建这样的SQL查询正常工作，但它显然不是一个很优雅的解决方案（和相当难以阅读/理解和维护以及）...我可以只写一个简单的QueryBuilder的自己，但我pretty的肯定，我不是第一个有这样那样的问题，所以我的问题：

Constructing the sql query like this does work but it obviously isn't a very elegant solution (and rather hard to read/understand and maintain as well)... I could just write a simple "querybuilder" myself but I am pretty sure that I am not the first one with this kind of problem, hence my questions:

• 如何的您的构造数据库查询？
• 请问C＃提供了一种简单的方法来动态构建查询？

• How do you construct your database queries?
• Does C# offer an easy way to dynamically build queries?

推荐答案

我用C＃和LINQ做类似获得日志过滤用户输入词条的东西（见<一href="http://stackoverflow.com/questions/11194/conditional-linq-queries">http://stackoverflow.com/questions/11194/conditional-linq-queries):

I used C# and Linq to do something similar to get log entries filtered on user input (see http://stackoverflow.com/questions/11194/conditional-linq-queries):

IQueryable<Log> matches = m_Locator.Logs;

// Users filter
if (usersFilter)
    matches = matches.Where(l => l.UserName == comboBoxUsers.Text);

 // Severity filter
 if (severityFilter)
     matches = matches.Where(l => l.Severity == comboBoxSeverity.Text);

 Logs = (from log in matches
         orderby log.EventTime descending
         select log).ToList();

编辑：没有在最后一条语句执行，直到.ToList（）查询。

The query isn't performed until .ToList() in the last statement.

这篇关于构建动态SQL查询在C＃/。NET3.5的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！