Получение схемы таблицы от запроса
Согласно MSDN, SqlDataReader.GetSchemaTable метаданные столбца возвратов для выполняемого запроса. Я задаюсь вопросом, там похожий метод, который даст метаданные таблицы для данного запроса? Я имею в виду, какие таблицы включены и что искажает его, имеет.
В моем приложении я получаю запрос, и я должен добавить where пункт programically. Используя GetSchemaTable(), Я могу получить метаданные столбца и таблицу, которой они принадлежат. Но даже при том, что таблица имеет псевдонимы, она все еще возвращает реальное имя таблицы. Существует ли способ получить название aliase той таблицы?
Следующий код показывает получение метаданных столбца.
const string connectionString = "your_connection_string";
string sql = "select c.id as s,c.firstname from contact as c";
using(SqlConnection connection = new SqlConnection(connectionString))
using(SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
DataTable schema = reader.GetSchemaTable();
foreach (DataRow row in schema.Rows)
{
foreach (DataColumn column in schema.Columns)
{
Console.WriteLine(column.ColumnName + " = " + row[column]);
}
Console.WriteLine("----------------------------------------");
}
Console.Read();
}
Это предоставит мне подробную информацию столбцов правильно. Но когда я вижу BaseTableName для столбца Id, это дает contact вместо имени псевдонима c. Там какой-либо путь состоит в том, чтобы получить схему таблицы и псевдонимы от запроса как вышеупомянутое?
Любая справка была бы большой!
Править
В то время как я мог использовать план выполнения, предложенный, Грабят, я ценил бы любые альтернативные простые подходы.
Ответ на вопросы tomekszpakowicz
Вы - (или Ваше приложение) источник рассматриваемого запроса? В этом случае необходимо знать псевдонимы.
Я не автор запросов. У нас есть система, где пользователи могут ввести запрос. Мы создаем столбцы из него с помощью метода, который я объяснил выше. Эти детали будут сохранены, и другой пользователь может использовать это как добавление новых критериев и т.д. Таким образом, мы должны создать SQL динамично из информации, мы имеем. Таким образом, когда столбец искажается, и мы не получаем имя псевдонима, затем, где созданный пункт будет недопустим.
Спасибо
4 ответа
Краткий ответ
Это не сработает. По замыслу, вы не можете получить псевдонимы таблиц из схемы результатов. И вы не можете рассчитывать на то, что сможете получить их из плана выполнения запроса.
Длинный ответ
Когда вы получаете результат SQL-запроса, он уже проанализирован, проверен, оптимизирован, скомпилирован во внутреннее представление и выполнен. Псевдонимы являются частью «исходного кода» запроса и обычно теряются где-то на шагах 1 и 2.
После выполнения запроса в виде таблиц можно увидеть только а) реальные физические таблицы и б) возвращаемые данные как единичные. анонимный стол. Все, что находится между ними, можно преобразовать или полностью оптимизировать.
Если бы СУБД должны были сохранять псевдонимы, было бы практически невозможно оптимизировать сложные запросы.
Возможные решения
Я предлагаю повторить проблему:
Являетесь ли вы (или ваше приложение) источником рассматриваемого запроса? В этом случае вам следует знать псевдонимы.
Если вы получаете запросы от кого-то другого ... Что ж ... Это зависит от того, зачем вы добавляете причины.
В худшем случае вам придется разбирать запросы самостоятельно.
В лучшем случае вы могли бы предоставить им доступ к представлениям вместо реальных таблиц и поместить предложения where в представления.
Простое и уродливое решение
Если я правильно понимаю ваши требования:
Пользователь A вводит запрос в вашу программу.
Пользователь B может запускать его (но не может редактировать) и видит возвращенные данные. Кроме того, она может добавлять фильтры на основе возвращаемых столбцов, используя какой-то виджет, предоставленный вами.
Вы не хотите применять фильтры внутри приложения, а вместо этого добавляете их в запрос, чтобы избежать выборки ненужных данных из базы данных.
В этом случае:
Когда запрос редактирования A пытается запустить его и собрать метаданные для возвращенных столбцов. Если
ColumnNameне уникальны, пожаловаться автору. Храните метаданные с запросом.Когда B добавляет фильтр (на основе метаданных запроса), сохраняются имена обоих столбцов. и условия.
При выполнении:
Проверить, действительны ли еще столбцы фильтра (A мог изменить запрос). Если не удалите недопустимые фильтры и / или сообщите B.
Выполните запрос примерно так:
select * from ({запрос введен пользователем A}) x где x.Column1 op1 Value1 и x.Column2 op2 Value2
Если вы хотите корректно обрабатывать изменения схемы базы данных, вам необходимо добавить некоторые дополнительные проверки, чтобы убедиться, что метаданные соответствуют тому, какой запрос действительно возвращает.
Замечание по безопасности
Ваша программа будет передавать запрос, написанный пользователем A, прямо в базу данных. Крайне важно, чтобы вы делали это, используя соединение с базой данных с разрешениями, которые не превышают разрешения базы данных A. В противном случае вы запрашиваете эксплойты на основе SQL-инъекций.
Следствие
Если пользователь A не имеет прямого доступа к базе данных из соображений безопасности, вы не можете использовать вышеуказанное решение.
В этом случае единственный способ обезопасить себя - убедиться, что ваше приложение понимает 100% запроса, что означает его анализ в вашей программе и разрешение только тех операций, которые вы считаете безопасными.
Вы можете получить план выполнения запроса, а затем проанализировать возвращенный XML. Это похоже на использование параметра «Показать предполагаемый план» в Management Studio.
Это похоже на то, что вам нужен парсер для разбора SQL, а затем из разобранного запроса составить символьную таблицу псевдонимов и таблиц, на которые они ссылаются. Затем объединить ее с результатами GetSchemaTable(), чтобы можно было сопоставить столбцы с соответствующим псевдонимом.
В любом случае смотрите вопрос Парсинг SQL кода в C# для некоторых парсеров. Я не рассматривал их подробно, но, возможно, один из них - то, что вам нужно. Если вы делаете только операторы select, посмотрите ссылку ANTLR и грамматику для http://www.antlr.org/grammar/1062280680642/MS_SQL_SELECT.html.
Если ваши запросы просты, вы можете использовать регулярные выражения или собственную грамматику для разбора псевдонимов и имен таблиц из запроса. Это, вероятно, будет самым простым решением.
Самое надежное решение - это, вероятно, заплатить за чей-то парсер, который обрабатывает полный SQL и разбивает его на дерево разбора или что-то еще, по которому можно делать запросы. Я не уверен в достоинствах каждого из них и соотношении цена/надежность. Но некоторые из них очень дорогие. Я бы сказал, если вы не можете сделать это сами, изучите грамматику ANTLR (потому что она бесплатна), если вам нужны только операторы select. В противном случае вам, возможно, придется заплатить....
На самом деле, если ваши пользователи не являются сумасшедшими гениями SQL и используют подзапросы/etc. Я не понимаю, почему вы не можете использовать имена таблиц из представления схемы, которые, как вы сказали, вы получили, чтобы найти их в запросе, а затем найти псевдоним как псевдоним tablename или tablename as alias. Это может сработать для многих случаев..... Но для полного общего случая вам понадобится полный парсер.....
Я думаю, что программа showplan xml Роба Фарли подойдет вам (при условии, что вы используете SQL Server с этой функцией достаточно поздно).
Кажется, в каждом столбце есть для каждого из выбранных столбцов. Предполагая, что у вас есть хотя бы один столбец из каждой таблицы, создание сопоставления между псевдонимом и таблицей должно быть тривиальным.