Есть ли стандартный подход к динамической генерации sql?

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

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

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

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

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

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

Без фактического образца кода, который можно было бы прокомментировать, трудно предоставить информированную альтернативу циклам и операторам переключения, которые вы используете в настоящее время. Но поскольку вы упомянули, что устанавливаете свойство 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.

ORMs уже решили проблему динамической генерации 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);

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

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

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

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

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

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

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

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

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

У нас хорошо работает ... и сохраняет генерацию SQL в объекте.