В этой статье описано, как использовать функции SQL, возвращающих табличное значение, и исполняемых CLR, для объединения различных типов источников данных при создании отчетов SQL Server Reporting Services.
(EN)
Версия статьи в формате Word:SSRSandTableValuedFunctions.doc (EN)
Программные примеры: MSDNReportingServices_TVF.exe
Введение
В Microsoft SQL Server 2005 появился новый функционал - он интегрирован со средой CLR платформы Microsoft.NET Framework. Это позволяет использовать функции и классы .NET Framework в инструкциях и запросах Transact-SQL.
Существует несколько механизмов интеграции с CLR. Это:
- CLR-функции , создаваемые пользователем (в том числе функции, возвращающие табличные значения);
- определяемые пользователем CLR-типы;
- хранимые процедуры, исполняемые CLR;
- триггеры, исполняемые CLR.
В этом документе показано, как использовать CLR-функции, возвращающие табличные значения, чтобы получать данные для отчетов из различных источников (в дополнение к базам данных) и создавать эффективные отчеты средствами Reporting Services.
Расширение служб Reporting Services
За счет интеграции со средой CLR возможности служб Reporting Services можно расширить. Например, можно создавать:
- модули доставки - предоставляющие отчеты в ответ на событие;
- модули просмотра отчетов - отображающие отчет в формате, отличном от того, который поддерживается службами Reporting Services;
- модули безопасности - представляют разработанный пользователем механизм проверки подлинности и авторизации для просмотра и управления отчетами;
- модули обработки данных - могут разрабатываться для обработки данных из источников, которые не поддерживаются службами Reporting Services;
- пользовательские элементы отчетов - настраиваемые серверные элементы управления, которые можно вставлять в отчеты и предоставлять дополнительный функционал в добавок к встроенным элементам управления.
В этой статье обсуждается реализация функций, возвращающих табличное значение (далее также - табличных функций), для обработки данных как альтернатива использованию модулей обработки данных. Подробнее о расширении служб Reporting Services см. Расширения служб Reporting Servicesв электронной документации по SQL Server 2005.
Использование функций, возвращающих табличное значение
Эти функции используются для программного создания таблицы во время выполнения приложений. Полученные таблицы можно использовать в инструкциях запросов Transact-SQL аналогично любым другим таблицам базы данных. Когда функции, возвращающие табличное значение, впервые появились в SQL Server 2000, их можно было создавать только с помощью Transact-SQL. Ниже приведен пример реализации:
CREATE function EmployeeNames() returns @employeeNames table (id int, name nvarchar(20), ) as begin INSERT @employeeNames values(1, 'Ryan'); INSERT @employeeNames values(2, 'John'); INSERT @employeeNames values(3, 'Bob'); return end
Впоследствии можно ссылаться на эту функцию в инструкции SELECT как на таблицу:
SELECT name from EmployeeNames() where id = 1
Запрос возвращает следующее:
name -------------------- Ryan
Хотя эта возможность полезна, она ограничена рамками языка Transact-SQL, разработанного специально для реляционных данных. При попытке выйти за их пределы Transact-SQL оказывается довольно неудобен. В SQL Server 2005 для создания табличных функций, можно использовать любой из языков .NET Framework, что открывает новые удивительные возможности. Теперь программисты могут представить любую необходимую информацию как таблицу реляционной базы данных.
В примере ниже приведен пример на языке Microsoft Visual C#, реализующий табличную функцию для SQL Server 2005. Код возвращает таблицу, созданную из журналов событий системы.
using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;
public class TabularEventLog
{
[SqlFunction(TableDefinition="logTime datetime,Message" +
"nvarchar(4000),Category nvarchar(4000),InstanceId bigint",
Name="ReadEventLog", FillRowMethodName = "FillRow")]
public static IEnumerable InitMethod(String logname)
{
return new EventLog(logname, Environment.MachineName).Entries;
}
public static void FillRow(Object obj, out SqlDateTime timeWritten,
out SqlChars message, out SqlChars category,
out long instanceId)
{
EventLogEntry eventLogEntry = (EventLogEntry)obj;
timeWritten = new SqlDateTime(eventLogEntry.TimeWritten);
message = new SqlChars(eventLogEntry.Message);
category = new SqlChars(eventLogEntry.Category);
instanceId = eventLogEntry.InstanceId;
}
}Функция, возвращающая табличное значение, реализована в виде двух статических методов классаTabularEventLog. Первому методу - InitMethod - присвоен атрибут SqlFunction, определяющий этот метод в качестве точки входа для функции. Метод должен возвращать объект IEnumerable илиIEnumerator. Этот объект содержит данные, которые будут использоваться для заполнения возвращаемой таблицы. При выполнении функции SQL Server перечисляет каждый объект, содержащийся в IEnumerator, и использует его для заполнения строки данных. Это достигается путем передачи объекта второму методу класса - FillRow. Этот метод преобразует объект в строку возвращаемой таблицы. Его имя указано в параметре FillRowMethodName атрибута SqlFunction.
Дополнительные метаданные определяются в аргументах атрибута SqlFunction. В предыдущем примере в атрибуте описаны имена и типы столбцов, а также имя возвращаемой таблицы.
После развертывания этой функции на сервере SQL Server можно запустить следующий запрос, который выведет 10 последних записей из журнала приложения:
SELECT TOP 10 T.logTime, T.Message, T.InstanceId FROM dbo.ReadEventLog(N'Application') as T
Результаты показаны на рис. 1.
Подробнее см. в электронной документации по SQL Server 2005 в разделеФункции CLR, возвращающие табличное значение электронной документации по SQL Server 2005.
Использование табличных функций как альтернативы модулям обработки данных
Модули обработки данных служб Reporting Services позволяют эмулировать источник данных, используя для этого ADO.NET. По принципу работы это похоже на то, как в этих службах используются табличные функции. Последние имеют важные преимущества по сравнению с модулями обработки данных.
Преимущества
Во-первых, функции, возвращающие табличное значение, намного проще реализовать, чем модули обработки данных. Для реализации такой функции, необходимо создать только два метода, а для модуля обработки данных - целый ряд интерфейсов. Кроме того, функции гораздо проще развертывать. Microsoft Visual Studio 2005 способна автоматически установить табличную .NET-функцию на сервере SQL Server, после чего она немедленно становится доступной для использования службами Reporting Services. Для развертывания модуля обработки данных необходимо скопировать его сборку на клиент и на сервер отчетов, а затем изменить XML-файлы конфигурации на обоих компьютерах.
Другим важным преимуществом табличных функции является то, что она может быть частью объединения JOIN в базе данных, к которой они подключены. Это позволяет обрабатывать и отфильтровывать реляционные данные SQL Server одновременно с пользовательскими данными, определенными в функции, перед их добавлением в отчет. Это было бы невозможно при использовании модуля обработки данных, поскольку службы Reporting Services не поддерживают запросы с объединением различных источников данных.
Недостатки
Модули обработки данных значительно мощнее и гибче табличных функций, возвращающих табличное значение. Функция может моделировать только одну таблицу БД, в то время как модуль обработки данных может смоделировать эквивалент целой базы данных. Кроме того, модуль функционирует как нестандартный источник данных и может иметь свой собственный язык запросов и синтаксис подключения. Для некоторых типов данных использование языка SQL не всегда целесообразно. Например, службы Reporting Services для XML-данных включают модуль обработки данных, использующий язык запросов, сходный с языком XPath. Использование модулей обработки полезно в тех случаях, когда разработчику необходимо иметь полный контроль над кодом доступа к данным.
Использование табличных функций в службах SQL Server Reporting Services
Перед использованием табличных функций в службах Reporting Services необходимо выполнить три действия. Во-первых, необходимо настроить SQL Server для разрешения интеграции с CLR. Во-вторых, в Visual Studio нужно разработать саму функцию. Наконец, ее необходимо развернуть на экземпляре SQL Server.
Чтобы в SQL Server включить интеграцию с CLR, необходимо установить соответствующий флаг. Это можно сделать либо с помощью средства настройки контактной зоны SQL Server, либо запустив запрос.
Настройка SQL Server для интеграции с CLR
- Нажмите кнопку Пуск, последовательно выберите Все программы, Microsoft SQL Server 2005, Средства настройки, а затем щелкните Настройка контактной зоны.
- В "Средствах настройки контактной зоны SQL Server 2005" щелкните Настройка контактной зоны для функциональных возможностей.
- Выберите требуемый экземпляр сервера, разверните параметры СУБД, а затем щелкните Интеграция CLR.
- Выберите Разрешить интеграцию CLR.
Альтернативный способ - запуск запроса к SQL Server, приведенного ниже (для запуска требуется разрешение ALTER SETTINGS)
USE master sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'clr enabled', 1; GO RECONFIGURE; GO
Разработка функции, возвращающей табличное значение
Для разработки табличной функции, возвращающей табличное значение, создайте в Visual Studio новый проект SQL Server. Чтобы создать такой проект, откройте диалоговое окно New Project (Новый проект), разверните Visual C#, а затем выберите Database (База данных). Будет предложено ввести информацию о подключении к базе данных. Подробнее см. в электронной документации по SQL Server 2005 в разделеСоздание проекта SQL Server. После установки подключения к базе данных можно писать код функции. Сначала в данном проекте создайте пустой CS-файл с именем EventLog.cs, затем скопируйте текст функции из предыдущего раздела статьи и вставьте его в этот файл.
Развертывание функции, возвращающей табличное значение
Для развертывания функции необходимо зарегистрировать её и содержащую ее сборку на экземпляре сервера SQL Server. Это можно сделать с помощью команд Transact-SQL. Ниже приведен сценарий, позволяющий зарегистрировать сборку tvfEventLogs и функцию ReadEventLog:
CREATE ASSEMBLY tvfEventLog FROM'D:\assemblies\tvfEventLog\tvfeventlog.dll' WITH PERMISSION_SET = SAFE GO CREATE FUNCTION ReadEventLog(@logname nvarchar(100)) RETURNS TABLE (logTime datetime,Message nvarchar(4000), Category nvarchar(4000),InstanceId bigint) AS EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod GO
Альтернативный способ - развернуть эту сборку непосредственно из Visual Studio: для этого в обозревателе решений на названии проекта нужно щелкнуть правой кнопкой мыши и выбрать командуDeploy (Развернуть). Сигнатура функции и другие необходимые метаданные будут взяты из атрибутаSqlFunction.
Разрешения, необходимые для развертывания
Для безопасного запуска кода в среде под управлением SQL Server необходимо использовать наборы разрешений. При создании проекта базы данных в Visual Studio по умолчанию установлен набор разрешений SAFE. Это единственный набор разрешений, позволяющий разворачивать проект базы данных непосредственно из Visual Studio без выполнения других настроек. Чтобы задать сборке набор разрешений, отличный от SAFE, необходимо перед развертыванием сборки в базе данных присвоить ей строгое имя и выполнить другие шаги по настройке.
При регистрации сборки на сервере SQL Server допускаются три возможных набора разрешений: SAFE, EXTERNAL ACCESS и UNSAFE.
- SAFE разрешает только внутренние вычисления и локальный доступ к данным из кода сборки.
- EXTERNAL ACCESS разрешает доступ к внешним системным ресурсам, например файлам, сетевым ресурсам и реестру.
- UNSAFE не накладывает никаких ограничений на выполнение кода сборки.
Чтобы развернуть сборку с помощью набора разрешений, отличного от SAFE, необходимо выполнить несколько дополнительных шагов. Из сборки, которую необходимо зарегистрировать на SQL Server, нужно создать асимметричный ключ. С помощью этого ключа следует создать учетное имя. Наконец, этому имени должен быть назначен соответствующий набор разрешений. Ниже приведен пример на Transact-SQL, в котором сборке, созданной в tvfEventLogs в предыдущем разделе предоставляется набор разрешений UNSAFE:
USE master GO CREATE ASYMMETRIC KEY EventLogKey FROM EXECUTABLE FILE = 'D:\assemblies\tvfEventLog\tvfeventlog.dll' CREATE LOGIN EventLogLogin FROM ASYMMETRIC KEY EventLogKey GRANT UNSAFE ASSEMBLY TO EventLogLogin GO
Это необходимо сделать только один раз, а не каждый раз при развертывании сборки. Более подробную информацию об использовании различных уровней разрешений и регистрации сборок на сервере SQL Server можно найти в разделах Контроль доступа для кода при интеграции CLR и электронной документации по SQL Server 2005.
Отчет по журналу событий
После развертывания функции, возвращающей табличное значение, к базе данных добавляется виртуальная таблица записей событий системы для данного компьютера . Поскольку SQL Server обрабатывает функцию как таблицу, она может без проблем использоваться в службах Reporting Services.
После развертывания сборки создайте в Visual Studio новый проект Reporting Services. (если вы не знаете, как создавать отчеты с помощью конструктора отчетов служб Reporting Services, см. раздел документации по SQL Server 2005 Учебники по службам Reporting Services). Создайте отчет с источником данных SQL Server, который подключается к той же базе данных, в которой установлена эта функция. Затем создайте набор данных, использующий следующий запрос:
SELECT TOP 10 T.logTime, T.Message, T.InstanceId FROM dbo.ReadEventLog(N'Security') as T WHERE T.Category = N'Logon/Logoff'
После того, как набор данных определен, добавьте таблицу в макет отчета и выведите данные туда. Наконец, запустите отчет. Будут выведены последние 10 событий безопасности локального компьютера с категорией "Вход/выход". На рис. 2 приведен пример отчета.
Этот простой пример можно расширить другими задачами по контролю и управлению компьютером. Например, можно создать табличную функцию для синтаксического анализа журналов Microsoft Internet Information Services (IIS). Затем можно использовать службы Reporting Services для создания приложения по наблюдению за веб-трафиком.
Веб-службы и функции, возвращающие табличное значение
Одна из интересных способностей, которыми можно наделить функции, возвращающие табличное значение, - извлечение данных из веб-служб. Это можно использовать для создания нестандартных отчетов. Покажем, как использовать веб-службу Microsoft MapPoint Web Service в табличной функции и присоединим ее к данным из базы данных AdventureWorks, чтобы для наглядно представить пространственные данные на карте и в отчете.
Использование веб-службы MapPoint Web Service
Перед началом работы с веб-службой MapPoint Web Service необходимо получить от Майкрософт бесплатную учетную запись разработчика. Для этого зайдите на веб-узел отдела обслуживания MapPoint Web Service. Подробнее о том, как начать разработку в этой веб-службе см. Приступая к работе с MapPoint Web Service SDK.
Необходимо добавить веб-ссылку на данный проект с помощью Visual Studio, указывающую на WSDL-файл, находящийся на промежуточном сервере MapPoint. Подробнее о настройке Web Reference для веб-службы MapPoint Web Service см. Работа с API-интерфейсом SOAP службы MapPoint Web Service.
Веб-служба MapPoint Web Service предоставляет фактически 4 службы, у каждой из которых собственный адрес SOAP:
- Служба Common Service предоставляет функциональность, которая может использоваться другими службами. Она применяется для извлечения метаданных и для служебных задач.
- Служба Find Service может использоваться для поиска местоположения, широты и долготы адреса, а также интересующих объектов в заданном районе.
- Служба Routing Service позволяет составить маршрут поездки из одной точки в другую.
- Служба Render Service используется для создания изображения карты на основе информации о местоположении и маршруте движения.
Табличная функция, работающая с MapPoint Web Service
Мы, в конечном счете, хотим, чтобы функция, возвращающая табличное значение, обратилась к веб-службе MapPoint Web Service для выполнения следующих задач:
- использование службы Find Service для определения широты и долготы магазина компании AdventureWorks, в котором продаются велосипеды;
- использование службы Find Service для поиска ближайшего к этому месту банкомата;
- использование службы Routing Service для составления маршрута от магазина до банкомата;
- использование службы Render Service для вывода этого маршрута на карту.
Во-первых, определим табличную функцию и дадим ей имя GetProximity. Приведенный ниже код Transact-SQL содержит сигнатуру этой функции:
CREATE FUNCTION GetProximity(@city nvarchar(200), @state nvarchar(2), @count int, @entityTypeName nvarchar(200)) RETURNS TABLE (HitName nvarchar(200), HitAddress nvarchar(200), MapImage varbinary(max))
Функция GetProximity получает название города и состоящий из двух знаков код штата, и задает начальную точку маршрута, количество записей, которые требуется вернуть, и вид объектов, которые нужно искать. Функция ищет n ближайших объектов, где n определяется параметром count, а тип объектов - параметром entityTypeName. Она возвращает таблицу, содержащую имя, адрес и карту (двоичное изображение), на которой нанесены направления движения к каждому объекту.
Сигнатуры методов C# выглядят следующим образом:
public static IEnumerable InitMap(string city, string state, int count, string entityTypeName) public static void FillRow(Object obj, out SqlChars name, out SqlChars address, out SqlBinary map)
Обратите внимание, что тип данных Transact-SQL nvarchar сопоставляется с типом данных .NET Framework SqlChars, а тип данных Transact-SQL varbinary - с типом данных .NET Framework SqlBinary. Полный список сопоставлений типов данных см. в документации пространства имен System.Data.SqlTypes.
В методе InitMap мы определяем широту и долготу для города и штата. Затем ищем все объекты рядом с этими координатами. Наконец, находим направления движения от исходной точки до искомого объекта. Возвращаемое значение - массив объектов типа Route, инкапсулирующий маршруты движения.
public static IEnumerable InitMap(string city, string state, int count, string entityTypeName)
{
FindServiceSoap find = new FindServiceSoap();
find.PreAuthenticate = true;
find.Credentials = new NetworkCredential(username, passwd);
// Geocode the initial city and state
FindAddressSpecification findSpec = new FindAddressSpecification();
Address findAddr = new Address();
findAddr.CountryRegion = "US";
findAddr.Subdivision = state;
findAddr.PrimaryCity = city;
findSpec.InputAddress = findAddr;
findSpec.DataSourceName = "MapPoint.NA";
findSpec.Options = new FindOptions();
findSpec.Options.ThresholdScore = 0.45;
FindResults results = find.FindAddress(findSpec);
if (results.NumberFound > 0)
{
// If the city and state exist, get the latitude and longitude
Location startLocation = results.Results[0].FoundLocation;
LatLong startPoint = startLocation.LatLong;
// Find the nearby entities
FindNearbySpecification findNearby = new
FindNearbySpecification();
FindFilter filter = new FindFilter();
filter.EntityTypeName = entityTypeName;
findNearby.Filter = filter;
FindOptions options = new FindOptions();
options.Range = new FindRange();
// Set the count limit
options.Range.Count = count;
findNearby.Options = options;
findNearby.DataSourceName = "NavTech.NA";
findNearby.LatLong = startPoint;
findNearby.Distance = 10.0;
results = find.FindNearby(findNearby);
Route[] routes = new Route[results.Results.Length];
RouteServiceSoap routeService = new RouteServiceSoap();
routeService.PreAuthenticate = true;
routeService.Credentials = new NetworkCredential(username,passwd);
RouteSpecification spec = new RouteSpecification();
spec.DataSourceName = "MapPoint.NA";
// Create the route to each entity
spec.Segments = new SegmentSpecification[2];
spec.Segments[0] = new SegmentSpecification();
spec.Segments[0].Waypoint = new Waypoint();
spec.Segments[0].Waypoint.Location = startLocation;
spec.Segments[0].Waypoint.Name = "start";
for (int x = 0; x < results.Results.Length; x++)
{
spec.Segments[1] = new SegmentSpecification();
spec.Segments[1].Waypoint = new Waypoint();
spec.Segments[1].Waypoint.Location =
results.Results[x].FoundLocation;
spec.Segments[1].Waypoint.Name = "end";
routes[x] = routeService.CalculateRoute(spec);
}
return routes;
}
return null;
}В методе FillRow служба Render используется для преобразования каждого объекта Route в изображение карты. Затем это изображение и данные о местоположении объекта заносятся в строку.
public static void FillRow(Object obj, out SqlChars name, out SqlChars
address, out SqlBinary map)
{
Route route = (Route)obj;
// build the address string
Address endAddress =
route.Specification.Segments[1].Waypoint.Location.Address;
string entityAddress = endAddress.AddressLine;
string enitityCity = endAddress.PrimaryCity;
string entityState = endAddress.Subdivision;
string entityName = route.Specification.Segments[1].Waypoint.Location.Entity.DisplayName;
// Assign the values of two of the columns
name = new SqlChars(entityName);
address = new SqlChars(entityAddress + ' ' + enitityCity + ' ' +
entityState);
// Get the view of the route
ViewByHeightWidth view = route.Itinerary.View.ByHeightWidth;
RenderServiceSoap renderService = new RenderServiceSoap();
renderService.PreAuthenticate = true;
renderService.Credentials = new NetworkCredential(username, passwd);
// Render the map with the route
MapSpecification mapSpec = new MapSpecification();
mapSpec.DataSourceName = "MapPoint.NA";
mapSpec.Views = new MapView[]{view};
mapSpec.Route = route;
// Assign the map image to the map column
MapImage[] image = renderService.GetMap(mapSpec);
map = new SqlBinary(image[0].MimeData.Bits);
}Развертывание и отладка функции GetProximity
Развертывание возвращающей табличное значение функции, использующей веб-службы, сложнее, чем в предыдущем примере. Ниже приведена полная последовательность шагов по такому развертыванию:
- Настройте проект, включающий функцию GetProximity, на предварительное создание сборки XML-сериализации. При вызове веб-службы платформа .NET Framework динамически формирует сборку для выполнения сериализации и десериализации SOAP XML. Это вызывает трудности, поскольку управляющее приложение среды CLR сервера SQL Server не разрешает динамическую загрузку сборок во время выполнения. Поэтому сборка XML-сериализации для вызовов веб-служб должна создаваться во время компиляции и регистрироваться в SQL Server. Для предварительного создания этой сборки из Visual Studio в меню Project (Проект) щелкните Properties (Свойства) и выберите Build (Построение). Для опции Generate serialization assembly (Создание сборки сериализации) задайте вариант On (Включено). DLL-библиотека XML-сериализации будет создана при построении проекта и помещена в каталог bin. Ей присваивается имя [ProjectName].XmlSerializers.dll.
-
Добавьте в сборку атрибут System.Security.AllowPartiallyTrustedCallers. Чтобы это сделать, добавьте в файл проекта AssemblyInfo.cs следующую строку:
[assembly: System.Security.AllowPartiallyTrustedCallers]
Это позволяет сборке XML-сериализации обмениваться данными с основной сборкой, содержащей функцию GetProximity.
- Зарегистрируйте в SQL Server библиотеку XML-сериализации, созданную на шаге 1. Будет достаточно задать ей набор разрешений SAFE.
- Создайте асимметричный ключ для библиотеки, содержащей возвращающую табличное значение функциюGetProximity.
- Из асимметричного ключа создайте имя входа и назначьте ему набор разрешений EXTERNAL ACCESS.
- Зарегистрируйте сборку, содержащую функцию GetProximity, с набором разрешений EXTERNAL ACCESS.
- Зарегистрируйте функцию GetProximity, возвращающую табличное значение.
Ввиду относительно долгой и сложной цепочки развертывания , мы отказались от соответствующего сервиса в Visual Studio в пользу сценария Transact-SQL, который запускается на этапе после построения проекта и выполняет шаги 3-7.
Отладка табличных функций очень проста. Проекты базы данных содержат каталог Test Scripts. Сценарии могут добавляться в этот каталог и запускаться непосредственно из Visual Studio. После завершения развертывания функции можно не выходя из Visual Studio создать запрос Transact-SQL для вызова функции и ее пошаговой проверки.
Для тестирования функции GetProximity создайте в каталоге Test Scripts файл со сценарием теста под именем "Test.sql" и поместите в него следующий запрос:
SELECT * FROM GetProximity('Redmond', 'WA', 5, 'SIC3578')Обратите внимание на аргументы функции. Центром запроса выбран город Редмонд в штате Вашингтон, поэтому в качестве аргумента @city указан "Redmond", а в качестве аргумента @state - "WA". Параметру @count задано значение 5, определяющее количество объектов, которые необходимо вернуть. А в качестве аргумента @entityTypeName указано значение "SIC3578", означающее банкоматы в том источнике данных MapPoint, которым мы пользуемся. Более подробную информацию об источнике данных MapPoint и типах объектов можно найти в разделе Источники данных MapPoint.
Для запуска запроса в Visual Studio щелкните правой кнопкой мыши файл Test.sql в обозревателе решений и выберите команду Debug Script (Отладить сценарий). В окне вывода Visual Studio отобразятся результаты, подобные этим:
HitName HitAddress MapImage ---------------------------------------------------------------------- Woodgrove Bank 8502 160th Ave NE Redmond WA <BINARY> Woodgrove Bank 16025 NE 85th St Redmond WA <BINARY> Woodgrove Bank 16150 NE 85th St Redmond WA <BINARY> Woodgrove Bank 8867 161st Ave NE Redmond WA <BINARY> Woodgrove Bank 15600 Redmond Way Redmond WA <BINARY> No rows affected. (5 row(s) returned)
Для отладки функции GetProximity задайте в коде на C# точку останова, и запустите сценарий снова. Выполнение кода в заданной точке будет прервано, и его можно будет отладить таким же образом, как любой другой управляемый процесс.
Создание отчета с помощью веб-службы MapPoint Web Service
Образец базы данных AdventureWorks, входящий в комплект поставки SQL Server 2005, представляет собой базу данных вымышленного изготовителя велосипедов и принадлежностей к ним, поставляющего свои изделия магазинам розничной торговли по всей территории США. В данном примере компания Adventure Works Cycles принимает решение прекратить прием кредитных карт и чеков. В дальнейшем компания хотела бы принимать оплату всех счетов только наличными. Для удобства клиентов компания в течение периода перехода к оплате наличными будет создавать отчеты, содержащие адреса и карту с расположением пяти ближайших к магазину банкоматов. Этот сценарий далек от реального, однако он показывает, как с помощью функций, возвращающих табличное значение, можно объединить традиционный источник данных (базу данных SQL) с нетрадиционным (веб-службой MapPoint Web Service).
На первом шаге создания такого отчета необходимо в Visual Studio создать новый проект Report Server и указать источник данных. В нашем случае это пример базы данных SQL Server 2005 AdventureWorks. В ней уже установлена созданная ранее табличная функция MapPoint. В отчете используется один набор данных с полями: название магазина, название банкомата, адрес банкомата и карту с местоположением банкомата.
Для каждого магазина мы вызываем функцию GetProximity и получаем данные о пяти ближайших банкоматах. Для реализации этой задачи воспользуемся новым выражением APPLY. Оно немного отличается от JOIN тем, что объединение происходит на основе аргументов функции, а не результатов. Это означает, что функция, возвращающая табличное значение, вызывается для каждой строки, возвращаемой левой частью предложения APPLY. Затем объединение результатов функции присоединяется к остальной части запроса. Ниже приведен запрос Transact-SQL формирующих набор данных для отчета.
SELECT TOP(40) Sales.Store.Name, Person.Address.City, Person.StateProvince.StateProvinceCode, GetProximity_1.HitName, GetProximity_1.HitAddress, GetProximity_1.MapImage FROM Sales.CustomerAddress INNER JOIN Person.Address ON Sales.CustomerAddress.AddressID = Person.Address.AddressID AND Sales.CustomerAddress.AddressID = Person.Address.AddressID INNER JOIN Sales.Store INNER JOIN Sales.StoreContact ON Sales.Store.CustomerID = Sales.StoreContact.CustomerID ON Sales.CustomerAddress.CustomerID = Sales.StoreContact.CustomerID INNER JOIN Person.StateProvince ON Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID AND Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID CROSS APPLY dbo.GetProximity(Person.Address.City, Person.StateProvince.StateProvinceCode, 5, 'SIC3578') AS GetProximity_1
Обратите внимание на использование CROSS APPLY для связи аргументов функции GetProximity с другими данными запроса: Person.Address.City и Person.StateProvince.StateProvinceCode.
 |
|
 | ||
| ||||
 |
|
 | ||
В нашем отчете используются два вложенных списка. Внутренний список содержит текстовое поле для имени и адреса банкомата, а также его изображения на карте. Для изображения в отчете задано свойство AutoSize, чтобы оно автоматически увеличивалось в зависимости от размера, полученного от веб-службы. Внешний список содержит текстовые поля для имени и местоположения магазина. Он сгруппирован по названиям магазинов. Изображение отчета в режиме разметки приведено на рис. 3.
На рис. 4 приведен готовый для просмотра отчет с картами, на которых отмечены банкоматы.
Заключение
В этой статье описано, как возвращающие табличное значение функции SQL Server можно использовать для расширения возможностей доступа к данным служб SQL Server Reporting Services. Такие функции предоставляют программистам и разработчикам отчетов дополнительные средства в ситуациях, когда данные не хранятся непосредственно в таблицах базы данных.
Об авторе
Райан Экли является инженером по разработке программного обеспечения в группе бизнес-аналитики Microsoft SQL Server Business Intelligence. Его основной специализацией являются механизмы обработки данных и отчетов в службах SQL Server Reporting Services.