Существует ли стандартный подход к генерации sql динамически?

Я хочу спросить, как другие программисты производят динамические строки SQL для выполнения в качестве CommandText объекта SQLCommand.

Я создаю параметризованные запросы, содержащие пользовательские предложения WHERE и поля SELECT. Иногда запросы сложны, и мне нужен большой контроль над тем, как создаются разные части.

В настоящее время я использую множество циклов и операторов switch для создания необходимых фрагментов кода SQL и для создания необходимых параметров параметров SQL. Этот метод трудно выполнить, и это делает техническое обслуживание реальной задачей.

Есть ли более чистый, более стабильный способ сделать это?

Любые предложения

EDIT: Чтобы добавить деталь к моему предыдущему сообщению:

1. Я не могу настроить свой запрос из-за требований. Это просто слишком сильно меняется.

Я должен разрешить агрегатные функции, такие как Count(). Это имеет последствия для предложения Group By/Have. он также вызывает вложенные операторы SELECT. Это, в свою очередь, влияет на имя столбца, используемое
Некоторые данные контакта хранятся в столбце XML. Пользователи могут запрашивать эти данные AS WELL AS и другие реляционные столбцы вместе. Следствием является то, что xmlcolumns не могут появляться в предложениях Group By [синтаксис sql].
Я использую эффективную технику подкачки, которая использует функцию 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. Где кладки строятся так:

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

Ответ 1

Мне нужно было сделать это в одном из моих недавних проектов. Вот схема, которую я использую для генерации SQL:

Каждый компонент запроса представлен объектом (который в моем случае представляет собой объект Linq-to-Sql, который сопоставляется с таблицей в БД). Поэтому у меня есть следующие классы: Query, SelectColumn, Join, WhereCondition, Sort, GroupBy. Каждый из этих классов содержит все сведения, относящиеся к этому компоненту запроса.
Последние пять классов связаны с объектом Query. Таким образом, у объекта Query есть коллекции каждого класса.
Каждый класс имеет метод, который может генерировать SQL для части запроса, который он представляет. Таким образом, создание общего запроса заканчивается вызовом Query.GenerateQuery(), который, в свою очередь, перечисляет все подкатегории и вызывает их соответствующие методы GenerateQuery()

Это все еще немного сложно, но в конце вы знаете, откуда генерируется генерация SQL для каждой отдельной части запроса (и я не думаю, что есть какие-либо большие операторы switch). И не забудьте использовать StringBuilder.

Ответ 2

Мы создали собственный объект FilterCriteria, который является видом динамического построителя запросов черного ящика. Он имеет свойства коллекции для SelectClause, WhereClause, GroupByClause и OrderByClause. Он также содержит свойства для CommandText, CommandType и MaximumRecords.

Затем мы передаем наш объект FilterCriteria в нашу логику данных и выполняем его против сервера базы данных и передаем значения параметров хранимой процедуре, которая выполняет динамический код.

Хорошо работает для нас... и сохраняет генерация SQL, содержащуюся в объекте.

Ответ 3

Вы можете попробовать подход, используемый средствами генерации кода, такими как CodeSmith. Создайте шаблон SQL с заполнителями. Во время выполнения прочитайте шаблон в строке и замените заполнители фактическими значениями. Это полезно, только если весь код SQL соответствует шаблону.

Ответ 4

Гульзар и Райан Лансио делают хорошие замечания в отношении CodeSmith и ORM. Любой из них может уменьшить или устранить вашу текущую нагрузку, когда дело доходит до создания динамического SQL. Ваш текущий подход к использованию параметризованного SQL является разумным, просто потому, что он хорошо защищает от атак SQL-инъекций.

Без фактического примера кода для комментариев трудно представить обоснованную альтернативу циклам и операторам switch, которые вы используете в настоящее время. Но поскольку вы упоминаете, что задаете свойство CommandText, я бы рекомендовал использовать string.Format в вашей реализации (если вы еще не используете его). Я думаю, что это может сделать ваш код более легким для реструктуризации и, следовательно, улучшить читаемость и понимание.

Ответ 5

Обычно это что-то вроде этого:

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.

Ответ 6

Если вам действительно нужно сделать это из кода, ORM - это, вероятно, способ, чтобы попытаться сохранить его.

Но я хотел бы предложить альтернативу, которая хорошо работает и может избежать проблем с производительностью, которые сопровождают динамические запросы, из-за изменения SQL, требующего создания новых планов запросов, с различными требованиями к индексам.

Создайте хранимую процедуру, которая принимает все возможные параметры, а затем используйте что-то вроде этого в предложении where:

where...
and (@MyParam5 is null or @MyParam5 = Col5)

то из кода гораздо проще установить значение параметра в DBNull.Value, когда оно неприменимо, вместо того, чтобы изменять строку SQL, которую вы генерируете.

Ваши администраторы баз данных будут намного счастливее с вами, потому что у них будет одно место для настройки запросов, SQL будет легко читаться, и им не придется прорываться сквозь трассировки профилировщика, чтобы найти множество различных запросов, созданный вашим кодом.

Ответ 7

Из любопытства вы рассмотрели использование ORM для управления доступом к данным. Уже существует много функций, которые вы пытаетесь реализовать. Это может быть чем-то, на что можно смотреть, потому что лучше не изобретать колесо.

Ответ 8

ORM уже решили проблему динамического генерации SQL (я предпочитаю NHibernate/ActiveRecord). Используя эти инструменты, вы можете создать запрос с неизвестным количеством условий, перейдя через пользовательский ввод и создав массив объектов Expression. Затем выполните встроенные методы запросов с помощью этого настраиваемого набора выражений.

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

Есть больше опций "Выражение": неравномерность, больше/меньше, null/not-null и т.д. Тип "Условие", который я только что составил, вы, вероятно, можете ввести свой пользовательский ввод в полезный класс.