Интеграция SQL с .NET

#net #sql_server #sqlclr

Необходимо реализовать некоторый функционал обработки данных SQL сервера при помощи .NET.

По ключевым словам "Интеграция .NET и SQL" находятся, в первую очередь, описания
"SQL CLR". Попробовал сие, и столкнулся с рядом серьезных проблем, которые не позволяют
его использовать:


Сложности в регистрации .NET сборок на SQL сервере. А именно: я использую сторонние
библиотеки. Во время их регистрации указывается об необходимости регистрировать system.servicemodel.
Если пытаюсь регистрировать system.servicemodel, необходима регистрация сборки system.servicemodel.internals,
а этой сборки я не нахожу. 
Зависимость версии сервера от версии .NET: Sql сервер версии 2008 использует .NET
до версии 3.5, соответсвенно сторонние сборки тоже должны быть скомпилированы в версиях
не старше 3.5. Мною используемые библиотеки доступны только в версии 4.0. Таким образом
отпадает возможность их использования в версиях SQL сервера до 2008. Для меня слишком
критично.


Итак, меня интересуют другие доступные способы интерации .NET и SQL.

Во время проб SQLCRL возникла идея использовать WCF сервис из пользовательских SQL
функций. Таким образом я бы мог всю "грязную" работу (и зависимости от сторонних библиотек)
переложить на сторону сервиса, а в контракте использовать лишь простые структуры данных.
Сервис должен тогда работать локально и испльзовать самое быстрое бинарное соединение
(возможно pipe).

Как вам такая идея? Может быть существуют другие способы, попроще?

Спасибо за советы.
    

Ответы

Ответ 1

Идея использовать SQLCLR для описанного сценария может казаться привлекательной,
но имеет свои минусы, и для описанной задачи есть другие решения.

Основные минусы SQLCLR

Основной минус это maintainability проекта, использующего подобную интеграцию, и
здесь Вы уже столкнулись с некоторыми трудностями.

Плюс SQLCLR

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

Способ проще

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

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

В случае вызова SQL для получения структурированных данных, и отправки для сохранения
обратно - точно такие же по смыслу данные, в тех же самых направлениях. То есть, в
терминах обмена сообщениями в межпроцессном взаимодействии, будет следующее:

(Запрос из SQL в .NET):  SQL -> Cтруктура    -> NET (1)
(Ответ  из .NET в SQL):  NET -> PDF          -> SQL (2)

Либо:
(Запрос из .NET в SQL):  NET -> id документа -> SQL (дешево)
(Ответ  из SQL в .NET):  SQL -> Cтруктура    -> NET (1)
(Запрос из .NET в SQL):  NET -> PDF          -> SQL (2)
(ОК от SQL)           :  SQL -> OK           -> NET (дешево)


Видим, что основной обмен сообщениями (1, 2) остается таким же в обоих случаях.

Компромисс

Есть и компромиссный вариант, где SQLCLR все же будет использоваться, но только в
ограниченном варианте - только для сериализации контракта. for xml может оказаться
неудобным по каким то причинам, например - если уже есть оговоренный формат сериализации,
который удобнее реализовать средствами .NET.

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

Молоток побольше

Судя по комментариям, для автора важно, чтобы SQL Server  стал точкой интеграции
нескольких систем, поэтому стоит посмотреть в сторону очередей Server Broker.

Service Broker в частности поддерживает активацию внешних процессов.

Типовые варианты использования компонента Service Broker 
https://technet.microsoft.com/ru-ru/library/ms166071(v=sql.105).aspx


Ответ 2

После некоторых экспериментов хотел бы поделиться своим опытом. 

Я продолжил развивать идею обработки данных при помощи локального сервиса WCF. Сделал
небольшое тестовое приложение.
Сразу скажу, что использовать WCF в смысле "Service Reference" (соврменный .NET клиент
для WCF служб) не получилось: основные проблемы возникли при регистрации различных
сборок, которые не входят в число поддерживаемых. Кроме того, необходимо четко определять
и разделять версии сборок .NET и SQL Servera, битность системы, битность сервера. Кроме
того смешанные сборки (которые содержат чистый управляемый код и неуправляемый) не
могут быть зарегестрированы на сервере. Долго бился - очень сложно.

Зато испльзовать WCF как "Web Refernece" (устаревший, но еще доступный вариант, который
основывается на .NET 2.0) вполне получилось.
В этом случае привязка "NamedPipes", как я хотел, недоступна - можно задействовать
лишь HttpBasic. Но тем не менее - работает! Кроме того, не понадобилось регистрировать
какие-то дополнительные сборки .NET. Но потребовалось создание дополнительной сборки
сериализации для моей сборки (в интрене легко нагуглить, что это, как и почему) - содержит
инструкции относительно сериализции, которые в нормальной обстановке генерируются налету,
но в ограниченной SQL.NET среде должны быть представленны явно. Ну и, ясно, что никаких
app.config SQL Server не понимет, поэтому вся конфигурация у меня прописана жестко
в коде (для хранения настроек можно было бы использовать сам SQL server). Также были
проблемы при передачи на WCF сервис больших байтовых массивов - WebReference-Клиент
не имеет настроек типа MaxMessageSize. Такое решил с помощью чанкинга - передачи данных
небольшими порциями.

SQL Service Database Project содан для целевой платформы 3.5, без проблем выполняется
и на SQL Server 2008 R2 и на SQL Server 2012.


Ответ 3

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

По сути, решение не отличается от того, которое использеут локальную Web-службу.
Только ей на смену приходит COM+ сервис. COM+ сервис пишется на .NET, использует необходимые
сторонние библиотеки, наружу торчит интерфейс, оперирующий простыми типами. 

[JustInTimeActivation]
public class Class1 : ServicedComponent, Interface1
{
    [AutoComplete]
    public byte[] MyMethodName(byte[] inputData, string ext ) {
        // ...
    }
}


SqlCLR-функция обращаятся к COM+ службе при помощи рефликции:

Type customType = Type.GetTypeFromProgID("ClassLibraryCom1.Class1"); 
Object customObj = Activator.CreateInstance(customType);
Object objValue = customType.InvokeMember("MyMethodName", BindingFlags.InvokeMethod,
null, customObj, new Object[] { param1, param2 } );


Данный подход я испробовал на небольшом теством проекте - выполнение ощутимо быстрее
чем веб-сервис, хоть и рефлексия (насколько быстрее - пока неясно, нужно замерять).
Расход памяти - примерно такой же.