Работа с Web-службами через CLR

Источник: t-sql

Веб-служба, веб-сервис (англ. web service ) - программная система, идентифицируемая строкой URI, чьи общедоступные интерфейсы определены на языке XML. Описание этой программной системы может быть найдено другими программными системами, которые могут взаимодействовать с ней согласно этому описанию посредством сообщений, основанных на XML, и передаваемых с помощью интернет-протоколов. Веб-служба является единицей модульности при использовании сервисно-ориентированной архитектуры приложения. Для демонстрации работы с Web-службами из SQL Server`a, я воспользовался открытым Web-сервисом Центрального банка Российской Федерации, этот сервис привлекателен тем, что информация всегда актуальна, т.к. обновляется с завидной регулярностью и может быть полезна в реальных проектах. Адрес Веб-сервиса: http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx?WSDL.

Веб сервис для получения ежедневных данных:

  • Сальдо операций ЦБ РФ по предоставлению/абсорбированию ликвидности
  • Получение новостей сервера
  • Операции на открытом рынке
  • Операции Банка России на рынке государственных ценных бумаг по поручению Министерства финансов Российской Федерации
  • Получение основной информации
  • Ставка рефинансирования, золотовалютные резервы, денежная база, денежная масса
  • Валютный своп buy/sell overnight
  • Динамики ставок привлечения средств по депозитным операциям
  • Динамика учетных цен драгоценных металлов и т.д.

Согласитесь, что достаточно ценная информация, а особенно ценна она своей актуальностью. Результат возвращается, как в виде DataSet, так и XMLDocument, но начнём по порядку…

Для начала продемонстрирую на небольшом примере, как работать с этим Web-сервисом из Windows-приложения. Запускаем Microsoft Visual Studio 2008 (но пример прекрасно работает и с 2005ой версией) и создадим Windows Forms Application

.

На форму добавим всего два компонента DataGridView (назовём его dg) и Button (назовём btn). Всё, что будет делать наше приложение - это по кнопке выводить результат(DataSet) в нашу табличку.
Теперь необходимо подключить нашу Веб службу к проекту, для этого в Solution Explorer`e правой кнопкой мыши по References и Add Service Reference…

В поле Address добавляем адресс нашей Веб службы(http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx?WSDL), а в поле Namespace имя, под которым у нас в проекте будет фигурировать служба (WS)

Далее всё более, чем тривиально, перед нами весь набор возможностей данной Веб службы

В данном примере я прочитаю все новости за последние 5 дней, для этого на кнопку вешаю код:

1.WS.DailyInfoSoap i = new WS.DailyInfoSoapClient();
2.dg.DataSource = i.NewsInfo(DateTime.Now.AddDays(-5), DateTime.Now).Tables[0].DefaultView;

Результат вывожу в виде таблицы:

Как сами видите нет ничего сложного, далее можно оперативно получить информацию по всем операциям ЦБРФ. Но согласитесь гораздо удобнее считывать информацию фоново, например по расписанию, и сохранять информацию в БД. Для этого мы создадим сборку CLR (Common Language Runtime)

Но…сперва все подводные камни, с которыми и сам столкнулся.
Подготовим наш сервер БД, для того, чтобы можно было подключать сборки:

 
1.SP_CONFIGURE 'clr enabled', 1
2.GO
3.RECONFIGURE
4.GO

Создадим тестовую БД и выставим Свойство базы данных TRUSTWORTHY:

 
1.CREATE DATABASE TestDB
2.GO
3.ALTER DATABASE TestDB SET TRUSTWORTHY ON
4.GO

Затем на подобии с вариантом для Windows-приложения напишем код для нашей сборки и скомпилируем dll-ку:

 
01.using System;
02.using Microsoft.SqlServer.Server;
03.using System.Data;
04.using System.Data.SqlTypes;
05.using System.Collections;
06.using CLRWS.WS;
07.  
08.public class WSClass
09.{
10.    [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read,
11.FillRowMethodName = "GetListInfo")]
12.  
13.    public static IEnumerable GetNews(DateTime From)
14.    {
15.        CLRWS.WS.DailyInfoSoap i = new CLRWS.WS.DailyInfoSoapClient();
16.        DataTable t = new DataTable();
17.        t = i.NewsInfo(From, DateTime.Now).Tables[0];
18.        return t.Rows;
19.    }
20.  
21.    public static void GetListInfo(
22.                    object obj,
23.                    out SqlInt32 Doc_id,
24.                    out SqlDateTime DocDate,
25.                    out SqlString Title,
26.                    out SqlString Url)
27.    {
28.        DataRow r = (DataRow)obj;
29.        Doc_id = new SqlInt32(Convert.ToInt32(r["Doc_id"].ToString()));
30.        DocDate = new SqlDateTime(Convert.ToDateTime(r["DocDate"].ToString()));
31.        Title = new SqlString(r["Title"].ToString());
32.        Url = new SqlString(r["Url"].ToString());
33.    }
34.  
35.}

Код компилируется без проблем, и теперь попытаемся подключить нашу сборку, предварительно перенесём её в отдельную папку C:\MyCLR:

 
1.CREATE ASSEMBLY ClrWebServices
2.FROM 'C:\MyCLR\CLRWS.dll'
3.WITH PERMISSION_SET = UNSAFE;
4.GO

Но тут же получаем ошибку:

Messages
Msg 10301, Level 16, State 1, Line 1
Assembly "CLRWS" references assembly "system.servicemodel, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.", which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(failed to retrieve text for this error. Reason: 1815)). Please load the referenced assembly into the current database and retry your request.

Для нашей сборки не хватает ряда библиотек и начиная с system.servicemodel, расположенной в папке C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0 , начинаем копировать библиотеки в папку C:\MyCLR. Кромя того, ряд библиотек придётся взять из папки C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 , общее кол-во библиотек можно увидеть ниже:

И теперь вуаля…скрипт отрабатывает без ошибок, создадим функцию для работы с нашей сборкой:

 
01.CREATE FUNCTION [dbo].[GetNews]
02.(
03.@From datetime
04.)
05.RETURNS TABLE
06.(
07.Doc_id INT,
08.DocDate DATETIME,
09.Title NVARCHAR(max),
10.Url NVARCHAR(max)
11.)
12.AS
13.EXTERNAL NAME [ClrWebServices].[WSClass].[GetNews]
14.GO

Ну и пробуем запустить эту функцию, в качестве входного параметра указывается дата, с которой мы берём новости и по сегодняшний день:

 
1.SELECT * from [dbo].[GetNews] ('20090101')

Но тут же получаем ошибку:

Messages
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "GetNews":
System.InvalidOperationException: Could not find default endpoint element that references contract "WS.DailyInfoSoap" in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this contract could be found in the client element.
System.InvalidOperationException:
at System.ServiceModel.Description.ConfigLoader.LoadChannelBehaviors(ServiceEndpoint serviceEndpoint, String configurationName)
at System.ServiceModel.ChannelFactory.ApplyConfiguration(String configurationName)
at System.ServiceModel.ChannelFactory.InitializeEndpoint(String configurationName, EndpointAddress address)
at System.ServiceModel.ChannelFactory`1..ctor(String endpointConfigurationName, EndpointAddress remoteAddress)
at System.ServiceModel.EndpointTrait`1.CreateSimplexFactory()
at System.ServiceModel.EndpointTrait`1.CreateChannelFactory()
at System.ServiceModel.ClientBase`1.CreateChannelFactoryRef(EndpointTrait`1 endpointTrait)
at System.ServiceModel.ClientBase`1.InitializeChannelFactoryRef()
at System.ServiceModel.ClientBase`1..ctor()
at CLRWS.WS.DailyInfoSoapClient..ctor()
at WSClass.GetNews(DateTime From)

Всё дело в том, что нам нужна информация, которая находится в файле app.config:

а именно

Для этого изменяем немного нашу сборку, компилируем и пересоздаём её и нашу функцию, вот так выглядит новая версия:

 
01.using System;
02.using Microsoft.SqlServer.Server;
03.using System.Data;
04.using System.Data.SqlTypes;
05.using System.Collections;
06.using CLRWS.WS;
07.using System.ServiceModel;
08.  
09.public class WSClass
10.{
11.    [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read,
12.FillRowMethodName = "GetListInfo")]
13.  
14.    public static IEnumerable GetNews(DateTime From)
15.    {
16.        EndpointAddress ea = new EndpointAddress(http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx?WSDL);
17.        BasicHttpBinding bin = new BasicHttpBinding();
18.  
19.        CLRWS.WS.DailyInfoSoap i = new CLRWS.WS.DailyInfoSoapClient(bin, ea);
20.        DataTable t = new DataTable();
21.        t = i.NewsInfo(From, DateTime.Now).Tables[0];
22.        return t.Rows;
23.    }
24.  
25.    public static void GetListInfo(
26.                    object obj,
27.                    out SqlInt32 Doc_id,
28.                    out SqlDateTime DocDate,
29.                    out SqlString Title,
30.                    out SqlString Url)
31.    {
32.        DataRow r = (DataRow)obj;
33.        Doc_id = new SqlInt32(Convert.ToInt32(r["Doc_id"].ToString()));
34.        DocDate = new SqlDateTime(Convert.ToDateTime(r["DocDate"].ToString()));
35.        Title = new SqlString(r["Title"].ToString());
36.        Url = new SqlString(r["Url"].ToString());
37.    }
38.  
39.}

После запуска нашей функции с уже новой сборкой

 
1.SELECT * from [dbo].[GetNews] ('20090101')

получаем новую ошибку

Messages
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "GetNews":
System.Configuration.ConfigurationErrorsException: The type "Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior, Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" registered for extension "Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior" could not be loaded. (c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 189)
System.Configuration.ConfigurationErrorsException:
at System.Configuration.BaseConfigurationRecord.EvaluateOne(String[] keys, SectionInput input, Boolean isTrusted, FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult)
at System.Configuration.BaseConfigurationRecord.Evaluate(FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult, Boolean getLkg, Boolean getRuntimeObject, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSection(String configKey, Boolean getLkg, Boolean checkPermission)
at System.C…

Ну эту ошибку легко убрать, достаточно отключить дебагинг:
[Program Files]\Microsoft Visual Studio 9.0\Common7\IDE\vsdiag_regwcf.exe -u
но и после этого ошибка, но уже новая:

Messages
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "GetNews":
System.ServiceModel.CommunicationException: There was an error in serializing body of message : "Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information.". Please see InnerException for more details. -> System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. -> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.ServiceModel.CommunicationException:

Server stack trace:
at System.ServiceModel.Dispatcher.XmlSerializerOperationFormatter.SerializeBody(XmlDictionaryWriter writer, MessageVersion version, String action, MessageDescription messageDescription, Object returnValue, Object[] parameters, Boolean isRequest)
at System.ServiceModel.Dispatcher.OperationFormatter.SerializeBodyContents(XmlDictionaryWriter writer, MessageVersion version, Object[] parameters, Object returnValue, Boolean isRequest)
at System.ServiceModel.Dispatcher.OperationFormatter.OperationFormatterMessage.OperationFormatterBodyWriter.OnWriteBodyContents(XmlDictionaryWriter writer)
at System.ServiceModel.Channels.BodyWriter.WriteBodyContents(XmlDictionaryWriter writer)
at System.ServiceModel.Channels.BodyWriterMessage.OnWriteBodyContents(XmlDictionaryWriter writer)
at System.ServiceModel.Channels.Message.OnWriteMessage(XmlDictionaryWriter writer)
at System.ServiceModel.Channels.Message.WriteMessage(XmlDictionaryWriter writer)
at System.ServiceModel.Channels.BufferedMessageWriter.WriteMessage(Message message, BufferManager bufferManager, Int32 initialOffset, Int32 maxSizeQuota)
at…


Сериализация XML из объектов базы данных CLR, кромя того о подобной ошибке можно прочитать в базе знаний Майкрософт http://support.microsoft.com/kb/913668.

После всех "заморочек", продемонстрирую, как же всё-таки подключить сборку для работы с Веб-сервисами. Для начала удалим нашу БД TestDB, т.к. всё, что было сделано до этого было лишним. Ну и создадим её заново. Для создания "правильной" сборки нам потребуются несколько утилит:
1) Web Services Description Language Tool (Wsdl.exe) для создания прокси-сборки (C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\wsdl.exe)
2) csc.exe для компилирования прокси-сборки (C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CSC.exe)
3) XML Serializer Generator Tool (Sgen.exe) - Создает сборку сериализации XML для типов в указанной сборке (C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\sgen.exe)

Теперь создаём прокси-сбоку:
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\wsdl.exe" /o:CBRF.cs /n:WS http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx?WSDL
на выходе получаем нашу сборку CBRF.cs
Теперь если немного изучить её, то наш финальный вариант CLR-сборки выглядит так:

01.using System;
02.using Microsoft.SqlServer.Server;
03.using System.Data;
04.using System.Data.SqlTypes;
05.using System.Collections;
06.using WS;
07.  
08.public class WSClass
09.{
10.            [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read,
11.        FillRowMethodName = "GetListInfo")]
12.  
13.    public static IEnumerable GetNews(DateTime From)
14.    {
15.    WS.DailyInfo i=new WS.DailyInfo();
16.    DataTable t = new DataTable();
17.    t=i.NewsInfo(From, DateTime.Now).Tables[0];
18.        return t.Rows;
19.    }
20.  
21.        public static void GetListInfo(
22.                        object obj,
23.                        out SqlInt32 Doc_id,
24.                        out SqlDateTime DocDate,
25.                        out SqlString Title,
26.                        out SqlString Url)
27.        {
28.            DataRow r = (DataRow)obj;
29.            Doc_id = new SqlInt32(Convert.ToInt32(r["Doc_id"].ToString()));
30.            DocDate = new SqlDateTime(Convert.ToDateTime(r["DocDate"].ToString()));
31.            Title = new SqlString(r["Title"].ToString());
32.            Url = new SqlString(r["Url"].ToString());
33.        }
34.  
35.}

Сохраним этот код в отдельный файл, например MyCLR.cs
Теперь поместим в одну папку два наших файла: CBRF.cs и MyCLR.cs и скомпилируем их в одну dll-ку, для этого из директории с этими файлами запустим "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CSC" /target:library /out:WS.dll *.cs
После запуска появится наша библиотека WS.dll
Последним шагом создадим сборку сериализации "C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\sgen.exe" /a:WS.dll на выходе ещё одна библиотека WS.XmlSerializers.dll

ВСЁ!!! Теперь подключим наши библиотеки:

 
01.CREATE ASSEMBLY ClrWebServices
02.FROM 'C:\MyCLR\WS.dll'
03.WITH PERMISSION_SET = UNSAFE;
04.GO
05.  
06.CREATE ASSEMBLY [ClrWebServices.XmlSerializers]
07.FROM 'C:\MyCLR\WS.XmlSerializers.dll'
08.WITH PERMISSION_SET = SAFE;
09.GO

Создадим функцию:

01.CREATE FUNCTION [dbo].[GetNews]
02.(
03.@From datetime
04.)
05.RETURNS TABLE
06.(
07.Doc_id INT,
08.DocDate DATETIME,
09.Title NVARCHAR(max),
10.Url NVARCHAR(max)
11.)
12.AS
13.EXTERNAL NAME [ClrWebServices].[WSClass].[GetNews]
14.GO

Ну и получим результат:

 
1.SELECT * from [dbo].[GetNews] ('20090101')

Скачать dll-ки: WS.rar

 

Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=23340