有标准的方法可以动态生成sql吗？
我想问一下其他程序员如何生成动态SQL字符串以作为SQLCommand对象的CommandText执行。
我正在生成包含用户生成的" WHERE"子句和SELECT字段的参数化查询。有时查询很复杂，我需要对不同部分的构建方式进行大量控制。
当前，我正在使用许多循环和switch语句来生成必要的SQL代码片段并创建所需的SQL参数对象。这种方法很难遵循，并且使维护工作变得很繁琐。
有没有更清洁，更稳定的方法？
有什么建议？？
编辑：
要在我以前的文章中添加细节：
- 我必须允许聚合函数，例如Count()。这会对"分组依据/具有"子句产生影响。它还会导致嵌套的SELECT语句。反过来，这会影响
- 一些联系人数据存储在XML列中。用户可以一起查询AS WELL AS和其他关系列的数据。结果是xmlcolumns不能出现在Group By子句[sql语法]中。
- 我正在使用使用Row_Number()SQL函数的有效分页技术。结果是我必须使用一个Temp表，然后在选择我的子集之前获取@@ rowcount以避免再次查询。
1.由于要求，我无法真正为查询模板。只是变化太多了。
sqlCmd.CommandText = "DECLARE @t Table(ContactId int, ROWRANK int" + declare
+ ")INSERT INTO @t(ContactId, ROWRANK" + insertFields + ")"//Insert as few cols a possible
+ "Select ContactID, ROW_NUMBER() OVER (ORDER BY " + sortExpression + " "
+ sortDirection + ") as ROWRANK" // generates a rowrank for each row
+ outerFields
+ " FROM ( SELECT c.id AS ContactID"
+ coreFields
+ from // sometimes different tables are required
+ where + ") T " // user input goes here.
+ groupBy+ " "
+ havingClause //can be empty
+ ";"
+ "select @@rowcount as rCount;" // return 2 recordsets, avoids second query
+ " SELECT " + fields + ",field1,field2" // join onto the other cols n the table
+" FROM @t t INNER JOIN contacts c on t.ContactID = c.id"
+" WHERE ROWRANK BETWEEN " + ((pageIndex * pageSize) + 1) + " AND "
+ ( (pageIndex + 1) * pageSize); // here I select the pages I want
我将显示一些代码(恐怖！)，以便你们对我正在处理的内容有所了解。
//Add Parameter to SQL Command
AddParamToSQLCmd(sqlCmd, "@p" + z.ToString(), SqlDbType.VarChar, 50, ParameterDirection.Input, qc.FieldValue);
// Create SQL code Fragment
where.AppendFormat(" {0} {1} {2} @p{3}", qc.BooleanOperator, qc.FieldName, qc.ComparisonOperator, z);
解决方案
回答
在这个例子中。我将查询XML数据。对于纯关系数据，查询要简单得多。
每个部分变量都是StringBuilders。 where子句的构建方式如下：
回答
出于好奇，我们是否考虑过使用ORM来管理数据访问。我们尝试实现的许多功能可能已经存在。可能要看一眼，因为最好不要重新发明轮子。
回答
我们可以尝试使用CodeSmith之类的代码生成工具使用的方法。使用占位符创建一个SQL模板。在运行时，将模板读入字符串并将占位符替换为实际值。仅当所有SQL代码都遵循模式时，此功能才有用。
- 查询的每个组件都由一个对象表示(在我的情况下，该对象是映射到DB中表的Linq-to-Sql实体)。因此，我有以下类：查询，SelectColumn，联接，WhereCondition，排序，GroupBy。这些类中的每一个都包含与查询的该组件有关的所有详细信息。
- 最后五个类都与Query对象有关。因此，Query对象本身具有每个类的集合。
- 每个类都有一个可以为其表示的查询部分生成SQL的方法。因此，创建整体查询最终将调用Query.GenerateQuery()，后者依次枚举所有子集合并调用它们各自的GenerateQuery()方法
我需要在我最近的一个项目中执行此操作。这是我用于生成SQL的方案：
回答
仍然有些复杂，但是最后我们知道查询的每个单独部分的SQL生成是从哪里来的(而且我认为没有大的switch语句)。并且不要忘记使用StringBuilder。
Gulzar和Ryan Lanciaux在提到CodeSmith和ORM时很好说。在生成动态SQL时，这两种方法都可以减轻或者消除我们当前的负担。我们当前使用参数化SQL的方法是明智的，仅因为它很好地防止了SQL注入攻击。
回答
如果没有要评论的实际代码示例，很难为我们当前使用的循环和switch语句提供一个知情的替代方案。但是由于我们提到要设置CommandText属性，因此我建议在实现中使用string.Format(如果尚未使用它)。我认为这可以使代码更易于重组，从而提高可读性和理解力。
string query= "SELECT {0} FROM .... WHERE {1}"
StringBuilder selectclause = new StringBuilder();
StringBuilder wherecaluse = new StringBuilder();
// .... the logic here will vary greatly depending on what your system looks like
MySqlcommand.CommandText = String.Format(query, selectclause.ToString(), whereclause.ToString());
通常是这样的：
回答
我也刚开始使用ORM。我们可能想看看其中之一。 ActiveRecord / Hibernate是Google的一些很好的关键字。
List<Expression> expressions = new List<Expression>(userConditions.Count);
foreach(Condition c in userConditions)
{
expressions.Add(Expression.Eq(c.Field, c.Value));
}
SomeTable[] records = SomeTable.Find(expressions);
ORM已经解决了动态SQL生成的问题(我更喜欢NHibernate / ActiveRecord)。使用这些工具，我们可以通过遍历用户输入并生成Expression对象数组来创建条件数量未知的查询。然后使用该自定义表达式集执行内置查询方法。
回答
还有更多的"表达式"选项：非相等，大于/小于，空/非空等。我刚刚编写的"条件"类型，我们可能会将用户输入填充到有用的类中。
如果我们确实需要通过代码执行此操作，那么ORM可能是尝试保持其清洁的方法。
但是我想提供一种替代方案，该替代方案运行良好并且可以避免动态查询所伴随的性能问题，因为更改SQL需要创建新的查询计划，并且对索引有不同的要求。
where... and (@MyParam5 is null or @MyParam5 = Col5)
创建一个接受所有可能参数的存储过程，然后在where子句中使用类似以下的内容：
然后，通过代码，在不适用时将参数值设置为DBNull.Value更为简单，而不是更改生成的SQL字符串。
回答
DBA会比我们更快乐，因为他们将有一个地方可以进行查询调优，SQL易于阅读，而且他们不必通过探查器跟踪来查找由我们生成的许多不同查询代码。
我们创建了自己的FilterCriteria对象，该对象有点像黑盒动态查询构建器。它具有SelectClause，WhereClause，GroupByClause和OrderByClause的集合属性。它还包含CommandText，CommandType和MaximumRecords的属性。
然后，我们将FilterCriteria对象传递给我们的数据逻辑，然后对数据库服务器执行该对象，并将参数值传递给执行动态代码的存储过程。
段落数量不匹配
