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

by Alexey Knyazev 14. января 2010 00:32

Веб-служба, веб-сервис (англ. 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 дней, для этого на кнопку вешаю код:

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

 

 

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

 

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

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

SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO

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

CREATE DATABASE TestDB
GO
ALTER DATABASE TestDB SET TRUSTWORTHY ON
GO

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

using System;
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlTypes;
using System.Collections;
using CLRWS.WS;

public class WSClass
{
    [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read,
FillRowMethodName = "GetListInfo")]

    public static IEnumerable GetNews(DateTime From)
    {
        CLRWS.WS.DailyInfoSoap i = new CLRWS.WS.DailyInfoSoapClient();
        DataTable t = new DataTable();
        t = i.NewsInfo(From, DateTime.Now).Tables[0];
        return t.Rows;
    }

    public static void GetListInfo(
                    object obj,
                    out SqlInt32 Doc_id,
                    out SqlDateTime DocDate,
                    out SqlString Title,
                    out SqlString Url)
    {
        DataRow r = (DataRow)obj;
        Doc_id = new SqlInt32(Convert.ToInt32(r["Doc_id"].ToString()));
        DocDate = new SqlDateTime(Convert.ToDateTime(r["DocDate"].ToString()));
        Title = new SqlString(r["Title"].ToString());
        Url = new SqlString(r["Url"].ToString());
    }

}

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

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

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

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, общее кол-во библиотек можно увидеть ниже:

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

CREATE FUNCTION [dbo].[GetNews]
(
@From datetime
)
RETURNS TABLE
(
Doc_id INT,
DocDate DATETIME,
Title NVARCHAR(max),
Url NVARCHAR(max)
)
AS
EXTERNAL NAME [ClrWebServices].[WSClass].[GetNews]
GO

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

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

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

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:

 а именно

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

using System;
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlTypes;
using System.Collections;
using CLRWS.WS;
using System.ServiceModel;

public class WSClass
{
    [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read,
FillRowMethodName = "GetListInfo")]

    public static IEnumerable GetNews(DateTime From)
    {
        EndpointAddress ea = new EndpointAddress("http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx?WSDL");
        BasicHttpBinding bin = new BasicHttpBinding();

        CLRWS.WS.DailyInfoSoap i = new CLRWS.WS.DailyInfoSoapClient(bin, ea);
        DataTable t = new DataTable();
        t = i.NewsInfo(From, DateTime.Now).Tables[0];
        return t.Rows;
    }

    public static void GetListInfo(
                    object obj,
                    out SqlInt32 Doc_id,
                    out SqlDateTime DocDate,
                    out SqlString Title,
                    out SqlString Url)
    {
        DataRow r = (DataRow)obj;
        Doc_id = new SqlInt32(Convert.ToInt32(r["Doc_id"].ToString()));
        DocDate = new SqlDateTime(Convert.ToDateTime(r["DocDate"].ToString()));
        Title = new SqlString(r["Title"].ToString());
        Url = new SqlString(r["Url"].ToString());
    }

}

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

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

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

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

но и после этого ошибка, но уже новая:

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-сборки выглядит так:

using System;
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlTypes;
using System.Collections;
using WS;

public class WSClass
{
            [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read,
		FillRowMethodName = "GetListInfo")]

    public static IEnumerable GetNews(DateTime From)
    {
	WS.DailyInfo i=new WS.DailyInfo();
	DataTable t = new DataTable();
	t=i.NewsInfo(From, DateTime.Now).Tables[0];
        return t.Rows;
    }

        public static void GetListInfo(
                        object obj,
                        out SqlInt32 Doc_id,
                        out SqlDateTime DocDate,
                        out SqlString Title,
                        out SqlString Url)
        {
            DataRow r = (DataRow)obj;
            Doc_id = new SqlInt32(Convert.ToInt32(r["Doc_id"].ToString()));
            DocDate = new SqlDateTime(Convert.ToDateTime(r["DocDate"].ToString()));
            Title = new SqlString(r["Title"].ToString());
            Url = new SqlString(r["Url"].ToString());
        }

}

Сохраним этот код в отдельный файл, например 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

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

CREATE ASSEMBLY ClrWebServices
FROM 'C:\MyCLR\WS.dll'
WITH PERMISSION_SET = UNSAFE;
GO

CREATE ASSEMBLY [ClrWebServices.XmlSerializers]
FROM 'C:\MyCLR\WS.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO

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

CREATE FUNCTION [dbo].[GetNews]
(
@From datetime
)
RETURNS TABLE
(
Doc_id INT,
DocDate DATETIME,
Title NVARCHAR(max),
Url NVARCHAR(max)
)
AS
EXTERNAL NAME [ClrWebServices].[WSClass].[GetNews]
GO

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

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

 

 

Ниже эти две библиотеки в архиве:

WS.rar

Tags: , , , , ,

SQL Server

Комментарии (6) -

guest
guest Russia
14.05.2012 15:41:37 #

Добрый день!
А Вы не подскажете, как бороться со следующей проблемой при добавлении Service Reference?

Метаданные содержат неразрешимую ссылку: "www.cbr.ru/.../DailyInfo.asmx?WSDL";.
Удаленный сервер вернул неожиданный ответ: (407) Proxy Authentication Required ( The ISA Server requires authorization to fulfill the request. Access to the Web Proxy filter is denied.  ).
Удаленный сервер возвратил ошибку: (407) Требуется проверка подлинности посредника.
If the service is defined in the current solution, try building the solution and adding the service reference again.

Заранее спасибо!

Reply

Alexey Knyazev
Alexey Knyazev Russia
04.07.2012 13:33:23 #

Доступ в сеть через прокси?

Reply

Андрей
Андрей Russia
07.09.2012 21:17:14 #

Алексей, да, через прокси. Isa-клиент установлен

Reply

Петроченко Сергей
Петроченко Сергей Belarus
17.07.2012 22:58:50 #

А можно обновить статью для NET 4.0 и SQL2012
А то сборка регистрироваться не хочет ругаясь на System.ServiceModel

Reply

verte
verte Russia
06.08.2012 17:36:00 #

Алексей, добры день!
Если есть возможность, приведите пример подключения через прокси-сервер.
Очень актуально
Заранее спасибо.

Reply

er77
er77 Russia
21.12.2013 22:48:29 #

Для тех кто юзает прокси
   подключите к проекту сетевого админа, он поможет завернуть трафик наружу.


ЕР

Reply

Добавить комментарий

  Country flag

biuquote
  • Комментарий
  • Предпросмотр
Loading