有标准的方法可以动态生成sql吗？

Question

我想问一下其他程序员如何生成动态SQL字符串以作为SQLCommand对象的CommandText执行。

我正在生成包含用户生成的WHERE子句和SELECT字段的参数化查询。有时查询很复杂，我需要对不同部分的构建方式进行大量控制。

当前，我正在使用许多循环和switch语句来生成必要的SQL代码片段并创建所需的SQL参数对象。这种方法很难遵循，并且使维护工作变得很繁琐。

有没有更清洁，更稳定的方法？

有什么建议？

编辑：要添加详细信息到我以前的文章：

1. 由于要求，我无法真正为查询模板。它只是改变太多了。
2. 我必须允许聚合函数，例如Count（）。这会对“分组依据/具有”子句产生影响。它还会导致嵌套的SELECT语句。反过来，这会影响
3. 一些联系人数据存储在XML列中。用户可以一起查询AS WELL AS和其他关系列的数据。结果是xmlcolumns不能出现在Group By子句[sql语法]中。
4. 我正在使用使用Row_Number（）SQL函数的高效分页技术。结果是我必须使用一个Temp表，然后在选择我的子集之前获取@@ rowcount，以避免再次查询。

我将展示一些代码（恐怖！），以便你们对我要处理的内容有个了解。

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

在此示例中，我正在查询XML数据。对于纯关系数据，查询要简单得多。每个部分变量都是StringBuilders。where子句的构建方式如下：

// 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);

Answer 1

我需要在我最近的一个项目中执行此操作。这是我用于生成SQL的方案：

• 查询的每个组件都由一个对象表示（在我的情况下，该对象是映射到DB中表的Linq-to-Sql实体）。因此，我有以下类：查询，SelectColumn，联接，WhereCondition，排序，GroupBy。这些类中的每一个都包含与查询的该组件有关的所有详细信息。
• 最后五个类都与Query对象有关。因此，Query对象本身具有每个类的集合。
• 每个类都有一个可以为其表示的查询部分生成SQL的方法。因此，创建整体查询最终将调用Query.GenerateQuery（），后者依次枚举所有子集合并调用其各自的GenerateQuery（）方法

仍然有些复杂，但是最后您知道查询的每个单独部分的SQL生成起源于何处（而且我认为没有任何大的switch语句）。并且不要忘记使用StringBuilder。