Динамическое формирование SQL-запросов из C# кода - .NET

System.Data.Common хороша для создания соединений, команд и вызова хранимых процедур.

А вот как динамически формировать SQL запросы к произвольной базе?

Десять лет назад надо было все делать вручную, применять шаблон "Factory",
чтобы создать один из нескольких драйверов БД,
потом надо было при помощи драйвера насоздавать примитивы,
объединить их в нужный запрос и
запустить обработку этого запроса.

Какая готовая библиотека Microsoft существует (или какие классы я просмотрел)
для решения этой задачи сейчас, в дополнение к System.Data.Common?

Здравствуйте, Arsen.Shnurkov, Вы писали:

AS>А вот как динамически формировать SQL запросы к произвольной базе?

Сегодня для этого вполне подходит Linq.

Здравствуйте, IT, Вы писали:

IT>Здравствуйте, Arsen.Shnurkov, Вы писали:

AS>>А вот как динамически формировать SQL запросы к произвольной базе?

IT>Сегодня для этого вполне подходит Linq.

Совсем не подходит! Ни что из базовых библиотек не позволяет генерировать запросы для произвольного сервера. Это и теоретически не возможно, т.к., к примеру, в OLE DB не заложено требование к драйверу о предоставлении метаданных и предоставлении методов формирования запросов для управления изменениями таблицы. Драйвер может вполне приспокойно выполнять некие базовые команды и произвольные запросы, но при этом ни чего не говорить об хранилище данных и его исполнительном механизме.

Здравствуйте, Arsen.Shnurkov, Вы писали:

AS>System.Data.Common хороша для создания соединений, команд и вызова хранимых процедур.

AS>А вот как динамически формировать SQL запросы к произвольной базе?

AS>Десять лет назад надо было все делать вручную, применять шаблон "Factory",
AS>чтобы создать один из нескольких драйверов БД,
AS>потом надо было при помощи драйвера насоздавать примитивы,
AS>объединить их в нужный запрос и
AS>запустить обработку этого запроса.

AS>Какая готовая библиотека Microsoft существует (или какие классы я просмотрел)
AS>для решения этой задачи сейчас, в дополнение к System.Data.Common?

Задачу точнее опишите. SQL запросы на основе чего генерить? Что на входе?

Здравствуйте, shakm, Вы писали:

AS>>>А вот как динамически формировать SQL запросы к произвольной базе?
IT>>Сегодня для этого вполне подходит Linq.

S>Совсем не подходит! Ни что из базовых библиотек не позволяет генерировать запросы для произвольного сервера. Это и теоретически не возможно, т.к., к примеру, в OLE DB не заложено требование к драйверу о предоставлении метаданных и предоставлении методов формирования запросов для управления изменениями таблицы. Драйвер может вполне приспокойно выполнять некие базовые команды и произвольные запросы, но при этом ни чего не говорить об хранилище данных и его исполнительном механизме.

Согласен, теоретически это, конечно, невозможно. Но практически сегодня тот же Janus используют один и тот же код для доступа к 4-м (если не ошибаюсь) различным серверам БД. Можно ещё легко добавить пяток, но там проблема упирается немного в другое — в поддержку DDL.

AS>>А вот как динамически формировать SQL запросы к произвольной базе?
IT>Сегодня для этого вполне подходит Linq.

не согласен, цитата с msdn:

LINQ предложение join всегда работает с коллекциями объектов, а не непосредственно с таблицами базы данных.

Здравствуйте, Arsen.Shnurkov, Вы писали:

AS>не согласен, цитата с msdn:
AS>

LINQ предложение join всегда работает с коллекциями объектов, а не непосредственно с таблицами базы данных.

Можно ссылку на цитату? Что-то мне не верится в то, что она относится к обсуждаемой теме.

Здравствуйте, IT, Вы писали:

IT>Здравствуйте, Arsen.Shnurkov, Вы писали:

AS>>не согласен, цитата с msdn:
AS>>

LINQ предложение join всегда работает с коллекциями объектов, а не непосредственно с таблицами базы данных.

IT>Можно ссылку на цитату? Что-то мне не верится в то, что она относится к обсуждаемой теме.

Вообще весь спор запутан! Какая связь между LINQ и генерацией специфических текстовых строк SQL запросов под конкретную реализацию СУБД????
Человек спросил о возможности именно генерации текстовых запросов!!!

Тема ранее обсуждалась, и в принципе, в OLE DB есть функции, которые позволяют прочитать метаданные и многое другое, чтобы позволить сформировать простейшие запросы. Но все же, драйвер конкретной СУБД не обязан реализовывать эти методы, а уж о конкретном синтаксисе SQL конкретной СУБД вообще речи не может идти. Я не знаю подробно нутра OLE DB, но может быть там поддерживается какая-то стандартная спецификация SQL.. наврядли... ведь СУБДшки то не полностью поддерживают (ранее точно не полностью поддерживали) спецификации SQL (например SQL 92), вводили свои ограничения и расширения.
Производители моделлеров, как пример, затачивают свои программы под конкретные СУБД. Я вот пользуюсь и ErWin'ом и PowerDesigner'ом. Они отлично генерят скрипты под конкретные СУБД.

Кстати, из PowerDesigner можно смело наворовать шаблонов, на основании которых он генерит скрипты.

AS>>>не согласен, цитата с msdn:
AS>>>

LINQ предложение join всегда работает с коллекциями объектов, а не непосредственно с таблицами базы данных.

IT>>Можно ссылку на цитату? Что-то мне не верится в то, что она относится к обсуждаемой теме.

http://msdn.microsoft.com/ru-ru/library/bb397927.aspx

S>Вообще весь спор запутан! Какая связь между LINQ и генерацией специфических текстовых строк SQL запросов под конкретную реализацию СУБД????

Судя по тому, что я прочитал про Linq — мне тоже не ясна эта связь.

S>Человек спросил о возможности именно генерации текстовых запросов!!!

S>Производители моделлеров, как пример, затачивают свои программы под конкретные СУБД.

Драйвер как раз и нужен для учета особенностей синтаксиса СУБД.
Когда я писал о "загрузке драйвера под СУБД", я предполагал, что его можно написать, но очень лень.

> а уж о конкретном синтаксисе SQL конкретной СУБД вообще речи не может идти.

Вообще я имел в виду работу:
"The Design of a Robust Persistence Layer For Relational Databases: An AmbySoft Inc. White Paper."
Scott W. Ambler,
SIGS Books/Cambridge University Press, 1998.
http://www.ambysoft.com/downloads/persistenceLayer.pdf
рисунок 7 на 11-ой странице

S>Кстати, из PowerDesigner можно смело наворовать шаблонов

Хочу готовое и под .Net. А за воровство и посадить могут

Здравствуйте, Arsen.Shnurkov, Вы писали:

AS>Хочу готовое и под .Net. А за воровство и посадить могут

BLToolkit?

Здравствуйте, Arsen.Shnurkov, Вы писали:

AS>Драйвер как раз и нужен для учета особенностей синтаксиса СУБД.
AS>Когда я писал о "загрузке драйвера под СУБД", я предполагал, что его можно написать, но очень лень.

Ну вообще-то понятно, что драйвер OLE DB и есть, так сказать трансформатор команд текстовых запросов с его параметрами. Ты передаешь текст запроса, передаешь параметры, а драйвер уже сам там как-то все разруливает. И тебе это может быть недоступно. Вот к примеру мелкософтовский драфвер может выдать инфу по метаданым, а какой-то другой, может не дать.

AS>Вообще я имел в виду работу:
AS>"The Design of a Robust Persistence Layer For Relational Databases: An AmbySoft Inc. White Paper."
AS>Scott W. Ambler,
AS>SIGS Books/Cambridge University Press, 1998.
AS>http://www.ambysoft.com/downloads/persistenceLayer.pdf
AS>рисунок 7 на 11-ой странице

Я долго не вчитывался. Но прослойку с объектами и с акссесорами мы сделали — вещь получилась обалденная. Приложения пишуться на сервере, а на клиенте только дизайнятся в дизайнере, без перекомпиляции. Формы отображаются автоматически и ведут себя согласно задизайненым правилам. Мы, конечно же, немного стандартизировали для себя возможности win форм и web форм, и правила поведения, но получилось круто. Сел, на сервере написал код и методы (аксессоры), залез в админку клинетского приложения, добавил объекты, повязал их связями и поведением, ввел скрипты аксессоров и замапил их параметры на атрибуты объектов, связей и процессов, сохранил натсройки, сгенерил новый номер версии, и все, у пользоователей появляется инфа об обновлении и они жмут кнопку и у них новые интерфейсы, поведение и бизнес процессы. Мы не мудрили с теориями как в приведенной книге. Я сел, нарисовал, прог всязл да нарограммил (кстати за 3 месяца) и мы теперь развиваем эту штуковину

).

S>>Кстати, из PowerDesigner можно смело наворовать шаблонов

AS>Хочу готовое и под .Net. А за воровство и посадить могут

Я шутя применил слово "своровать". ТЬам шаблоны открыты — бери и юзай сколько хочешь.

Здравствуйте, shakm, Вы писали:

S>Вообще весь спор запутан!

Какой вопрос, такой и спор. Я так понимаю, что все понимают вопрос по своему и соответственно спорят о чём-то своём.

S>Какая связь между LINQ и генерацией специфических текстовых строк SQL запросов под конкретную реализацию СУБД????

Самая прямая. Например, вот Linq запрос:

var q =
    from p in db.Person
    orderby p.FirstName
    select (p.LastName ?? "").Length;

var list = q.Skip(2).Take(5).ToList();

А вот реализация специфических текстовых строк SQL запросов под конкретные реализации разных СУБД для этого запроса:

-- MsSql2008
SELECT *
FROM
(
    SELECT
        t.*,
        ROW_NUMBER() OVER
        (
            ORDER BY
                oby
        ) as rn
    FROM
    (
        SELECT
            Len(Coalesce([p].[LastName], '')) as [c1],
            [p].[FirstName] as [oby]
        FROM
            [Person] [p]
    ) t
) t1
WHERE
    t1.rn BETWEEN 3 AND 7


-- MsSql2005
SELECT *
FROM
(
    SELECT
        t.*,
        ROW_NUMBER() OVER
        (
            ORDER BY
                oby
        ) as rn
    FROM
    (
        SELECT
            Len(Coalesce([p].[LastName], '')) as [c1],
            [p].[FirstName] as [oby]
        FROM
            [Person] [p]
    ) t
) t1
WHERE
    t1.rn BETWEEN 3 AND 7


-- SqlCe
SELECT
    Len(Coalesce([p].[LastName], '')) as [c1]
FROM
    [Person] [p]
ORDER BY
    [p].[FirstName]


-- DB2
SELECT *
FROM
(
    SELECT
        t.*,
        ROW_NUMBER() OVER() as rn
    FROM
    (
        SELECT
            Length(Coalesce("p"."LastName", '')) as "c1"
        FROM
            "Person" "p"
        ORDER BY
            "p"."FirstName"
    ) t
) t1
WHERE
    t1.rn BETWEEN 3 AND 7


-- Informix
SELECT SKIP 2 FIRST 5
    Length(Nvl(p.LastName, '')) as c1
FROM
    Person p
ORDER BY
    p.FirstName


-- Firebird
SELECT FIRST 5 SKIP 2
    Char_Length(Coalesce(p.LastName, '')) as c1
FROM
    Person p
ORDER BY
    p.FirstName


-- Oracle
SELECT t1.*
FROM
(
    SELECT t.*, ROWNUM as rn
    FROM
    (
        SELECT
            Length(Nvl(p.LastName, '')) as c1
        FROM
            Person p
        ORDER BY
            p.FirstName
    ) t
    WHERE
        ROWNUM <= 7
) t1
WHERE
    t1.rn > 2


-- PostgreSQL
SELECT
    Length(Coalesce("p"."LastName", '')) as "c1"
FROM
    "Person" "p"
ORDER BY
    "p"."FirstName"
LIMIT 5 OFFSET 2 


-- MySql
SELECT
    Length(Coalesce(p.LastName, '')) as c1
FROM
    Person p
ORDER BY
    p.FirstName
LIMIT 2,5


-- SQLite
SELECT
    Length(Coalesce([p].[LastName], '')) as [c1]
FROM
    [Person] [p]
ORDER BY
    [p].[FirstName]
LIMIT 5 OFFSET 2


-- Sybase
SELECT TOP 7
    Len(Coalesce([p].[LastName], '')) as [c1]
FROM
    [Person] [p]
ORDER BY
    [p].[FirstName]


-- Access
SELECT TOP 7
    Len(Iif([p].[LastName] IS NULL, '', [p].[LastName])) as [c1]
FROM
    [Person] [p]
ORDER BY
    [p].[FirstName]

S>Кстати, из PowerDesigner можно смело наворовать шаблонов, на основании которых он генерит скрипты.

Интересно. Надо будет посмотреть.

Здравствуйте, IT, Вы писали:

IT>Самая прямая. Например, вот Linq запрос:

Так вы же сами написали запрос на Linq...
А автору вроде бы надо было просто имея имя таблицы получить аксессоры.
Может быть он о другом....
Не подскажите, как под DataTable, созданный рантайме (не важно как), динамически создать вот тот код, что вы привели?
Ну например, такие условия, на входе:
— DataTable, или просто имя таблицы,
— строка коннекта к базе
— тип базы (енум или что-то еще)

А такие вещи делают некоторые системки. Но в дотнете этого нет, так?
Я работал с такой системой, обмен информации, обширненькая системка. Когда добавляешь в нее entity, она под нее генерит все необходимые аксессоры, а ты уже можешь ихз донастроить, подтачить.

Здравствуйте, shakm, Вы писали:

S>А автору вроде бы надо было просто имея имя таблицы получить аксессоры.
S>Может быть он о другом....

Думаю, сначала надо бы прояснить именно этот момент

Здравствуйте, IT, Вы писали:

IT>Здравствуйте, shakm, Вы писали:

IT>Самая прямая. Например, вот Linq запрос:

читаю форум и удивляюсь почему все не в тему?
и все ради похвастать знаниями

Здравствуйте, Аноним, Вы писали:

А>читаю форум и удивляюсь почему все не в тему?
А>и все ради похвастать знаниями

Не похвастаться, а поделиться. Задай автор свой вопрос более внятно, он бы уже давно получил ответ.

Здравствуйте, Arsen.Shnurkov, Вы писали:

AS>Вообще я имел в виду работу:
AS>"The Design of a Robust Persistence Layer For Relational Databases: An AmbySoft Inc. White Paper."
AS>Scott W. Ambler,
AS>SIGS Books/Cambridge University Press, 1998.
AS>http://www.ambysoft.com/downloads/persistenceLayer.pdf
AS>рисунок 7 на 11-ой странице

Структуры базы, к которой потом будут применяться все эти критерии заранее известна?

Здравствуйте, Arsen.Shnurkov, Вы писали:

IT>>>Можно ссылку на цитату? Что-то мне не верится в то, что она относится к обсуждаемой теме.
AS>http://msdn.microsoft.com/ru-ru/library/bb397927.aspx

Это статья о Linq вообще, а не о Linq 2 SQL или любом другом Linq провайдере для БД. Цитата из английского MSDN (таже статья):

Join operations create associations between sequences that are not explicitly modeled in the data sources. For example you can perform a join to find all the customers and distributors who have the same location. In LINQ the join clause always works against object collections instead of database tables directly.

и пример оттуда же:

var innerJoinQuery =
    from cust in customers
    join dist in distributors on cust.City equals dist.City
    select new { CustomerName = cust.Name, DistributorName = dist.Name };

Здесь скорее всего имеется ввиду, что join в Linq шире, чем просто возможность делать джоины к таблицам. Хотя формулировка всё равно кривая. В общем, с join в Linq нет никаких проблем, он нормально транслируется в SQL JOIN. Можешь сам проверить на том же Linq2SQL.

IT> Структуры базы, к которой потом будут применяться все эти критерии заранее известна?

В некотором смысле. Точная структура неизвестна, но известно, по каким правилам она формируется.

IT>Самая прямая. Например, вот Linq запрос:
IT>...
IT>А вот реализация специфических текстовых строк SQL запросов под конкретные реализации разных СУБД для этого запроса:
IT>...

Вот, суперответ, именно то, что нужно.

Нашел, как генерировать команды при помощи Linq:
http://msdn.microsoft.com/en-us/library/bb882652.aspx

// using System.Data.Common;
Northwnd db = new Northwnd(@"c:\northwnd.mdf");

var q =
    from cust in db.Customers
    where cust.City == "London"
    select cust;

DbCommand dc = db.GetCommand(q);
Console.WriteLine("\nCommand Text: \n{0}",dc.CommandText);

Только не ясно, как именно это генерирование команд происходит,
через какие вспомогательные классы выясняются детали того SQL, который надо сформировать

Здравствуйте, Arsen.Shnurkov, Вы писали:

IT>> Структуры базы, к которой потом будут применяться все эти критерии заранее известна?

AS>В некотором смысле. Точная структура неизвестна, но известно, по каким правилам она формируется.

Если точная структура не известна, то Linq не поможет. Для Linq модель данных должна быть известна во время компиляции, т.к. генерация SQL делается по объектной модели.

Если модель не известна, но нужно применять что-то вроде SqlBuilder, т.е. создавать запрос в виде некоего SQL AST, а затем с помощью SQL провайдеров создавать SQL под конкретную СУБД. Соответствующие средства есть в BLToolkit, в нём Linq-провайдер как раз организован по схеме Linq-provider -> SqlQuery -> SqlProvider.

	От:	Arsen.Shnurkov
	Дата:	20.09.10 19:32
	Оценка:

От:	IT	linq2db.com
Дата:	21.09.10 04:16
Оценка:

	От:	shakm
	Дата:	21.09.10 06:13
	Оценка:

	От:	barn_czn
	Дата:	21.09.10 08:05
	Оценка:

От:	IT	linq2db.com
Дата:	21.09.10 14:03
Оценка:

От:	hardcase	http://nemerle.org
Дата:	24.09.10 05:48
Оценка: