我想问问如何其他程序员正在生产的动态SQL字符串来执行作为SqlCommand对象的CommandText。

包含用户

我生产参数化查询产生的WHERE子句和选择字段。有时，查询是复杂的，我需要很多的控制权如何在不同的地区都建立。

目前，我使用的许多循环和switch语句产生必要的SQL code片段和创建对象所需的SQL参数。这种方法难以跟随，它使维护一个累赘。

是否有这样做的??一个更清洁，更稳定的方式

任何建议？

编辑： 添加细节到我的previous后：

1。我真的不能模板我的查询，由于要求。它只是改变了太多。

我要允许聚合函数，如COUNT（）。这对本集团通过/ HAVING子句的后果。这也导致嵌套的SELECT语句。这反过来会影响使用的列名 在某些联系人数据存储在一个XML列。用户可以查询这个数据以及和其他关系的列在一起。后果是xmlcolumns不能By子句[SQL语法]出现在组。 在我使用的，使用ROW_NUMBER（）SQL函数一种有效的分页技术。后果是，我不得不使用临时表，然后拿到@@行数，选择我的子集，以避免第二个查询之前。

我会表现出一定的code（恐怖！），这样你们有什么我处理的想法。

  sqlCmd.CommandText =DECLARE @t表（INT的ContactID，ROWRANK INT+声明
      +）INSERT INTO @t（的​​ContactID，ROWRANK+ insertFields +）//插入为几COLS一个可能的
      +选择的ContactID，ROW_NUMBER（）OVER（ORDER BY+ SORTEX pression +
      + sortDirection +）作为ROWRANK//生成rowrank为每一行
      + outerFields
      +FROM（SELECT c.id AS的ContactID
      + coreFields
      +从//有时不同的表是必需的
      +在这里+）T//用户输入到这里。
      + GROUPBY +
      + havingClause //可以是空的
      +;
      +选择@@ ROWCOUNT作为RCOUNT; //返回2的记录，避免了第二次查询
      +选择+田+，FIELD1，FIELD2//加入到其他COLS n中的表
      +FROM @tt INNER JOIN接触C对t.ContactID = c.id
      +WHERE ROWRANK之间+（（*的PageIndex的pageSize）+ 1）+与
      +（（的PageIndex + 1）*的pageSize）; //这里我选择我想要的页面
 

在此例如。我会查询XML数据。对于纯粹的关系数据，查询是简单得多。 每个段中的变量是StringBuilders。 where子句建立像这样：

  //添加参数到SQL命令
AddParamToSQLCmd（SQLCMD，@p+ z.ToString（），SqlDbType.VarChar，50，ParameterDirection.Input，qc.FieldValue）;
//创建SQL code片段
where.AppendFormat（{0} {1} {2} @p {3}，qc.BooleanOperator，qc.FieldName，qc.ComparisonOperator，Z）;
 

解决方案

我有必要做这在我最近的一个项目。下面是我使用的生成SQL的方式：

查询各组成部分是重新由对象（这在我的情况是映射到一个表中的DB一个LINQ到SQL实体）psented $ P $。所以，我有以下类：查询，SelectColumn，加入，WhereCondition，排序，GROUPBY。这些类包含与查询该组件的所有细节。 在最后的五定班列的所有相关的查询对象。所以查询对象本身都有每个类的集合。 在每个类都有，可以生成SQL，它重新presents查询的一部分的方法。因此，创建整个查询结束调用Query.GenerateQuery（），这反过来又通过枚举所有的子集，并调用它们各自的GenerateQuery（）方法

这仍然是一个有点复杂，但最终您知道SQL生成查询始发的每个部分（我不认为有任何大的switch语句）。而且不要忘了使用StringBuilder的。

I want to ask how other programmers are producing Dynamic SQL strings for execution as the CommandText of a SQLCommand object.

I am producing parameterised queries containing user generated "WHERE" clauses and SELECT fields. Sometimes the queries are complex and I need a lot of control over how the different parts are built.

Currently, I am using many loops and switch statements to produce the necessary SQL code fragments and to create the SQL parameters objects needed. This method is difficult to follow and it makes maintenance a real chore.

Is there a cleaner, more stable way of doing this??

Any Suggestions??

EDIT: To add detail to my previous post:

1. I cannot really template my query due to the requirements. It just changes too much.

I have to allow for aggregate functions, like Count(). This has consequences for the Group By/Having clause. it also causes nested SELECT statements. This in turn affects the column name used by Some Contact data is stored in an XML column. Users can query this data AS WELL AS and the other relational columns together. Consequences are that xmlcolumns cannot appear in Group By clauses[sql syntax]. I am using an efficient paging technique that uses Row_Number() SQL Function. Consequences are that I have to use a Temp table and then get the @@rowcount, before selecting my subset, to avoid a second query.

I will show some code(the horror!) so that you guys have an idea of what i'm dealing with.

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

In this Eg. I would be querying XML data. For purely relational data, the query is much more simple. Each of the section variables are StringBuilders. Where clauses are built like so:

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

解决方案

I had the need to do this on one of my recent projects. Here is the scheme that I am using for generating the SQL:

Each component of the query is represented by an Object (which in my case is a Linq-to-Sql entity that maps to a table in the DB). So I have the following classes: Query, SelectColumn, Join, WhereCondition, Sort, GroupBy. Each of these classes contains all details relating to that component of the query. The last five classes are all related to a Query object. So the Query object itself has collections of each class. Each class has a method that can generate the SQL for the part of the query that it represents. So creating the overall query ends up calling Query.GenerateQuery() which in turn enumerates through all of the sub-collections and calls their respective GenerateQuery() methods

It is still a bit complicated, but in the end you know where the SQL generation for each individual part of the query originates (and I don't think that there are any big switch statements). And don't forget to use StringBuilder.