1. Вступ
2. Розширення служб Reporting Services
3. Використання функцій, які повертають табличне значення
4. Використання табличних функцій як альтернативи модулів обробки даних
5. переваги
6. недоліки
7. Використання табличних функцій в службах SQL Server Reporting Services
8. Налаштування SQL Server для інтеграції з CLR
9. Розробка функції, що повертає табличне значення
10. Розгортання функції, що повертає табличне значення
11. Дозволи, необхідні для розгортання
12. Звіт по журналу подій
13. Веб-служби і функції, які повертають табличне значення
14. Використання веб-служби MapPoint Web Service
15. Таблична функція, яка працює з MapPoint Web Service
16. Розгортання та налагодження функції GetProximity
17. Створення звіту за допомогою веб-служби MapPoint Web Service
18. Висновок
19. про автора

У цій статті описано, як використовувати функції 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

1. Натисніть кнопку Пуск, послідовно виберіть пункти Усі програми, Microsoft SQL Server 2005, Засоби налаштування, а потім клацніть Налаштування контактної зони.
2. В "Засобах настройки контактної зони SQL Server 2005" клацніть Налаштування контактної зони для функціональних можливостей.
3. Виберіть необхідний екземпляр сервера, розгорніть параметри СУБД, а потім клацніть Інтеграція CLR.
4. Виберіть Дозволити інтеграцію 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 для виконання наступних завдань:

1. використання служби Find Service для визначення широти і довготи магазину компанії AdventureWorks, в якому продаються велосипеди;
2. використання служби Find Service для пошуку найближчого до цього місця банкомату;
3. використання служби Routing Service для складання маршруту від магазину до банкомату;
4. використання служби 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

Розгортання повертає табличне значення функції, що використовує веб-служби, складніше, ніж в попередньому прикладі. Нижче приведена повна послідовність кроків по такому розгортання:

1. Налаштуйте проект, що включає функцію 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.

2. Додайте в збірку атрибут System.Security.AllowPartiallyTrustedCallers. Щоб це зробити, додайте в файл проекту AssemblyInfo.cs наступний рядок:

   [Assembly: System.Security.AllowPartiallyTrustedCallers]

   Це дозволяє збірці XML-сериализации обмінюватися даними з основною збіркою, що містить функцію GetProximity.

3. Зареєструйте в SQL Server бібліотеку XML-сериализации, створену на кроці 1. Буде досить задати їй набір дозволів SAFE.
4. Створіть асиметричний ключ для бібліотеки, що містить повертає табличне значення функцію GetProximity.
5. З асиметричного ключа створіть ім'я входу і призначте йому набір дозволів EXTERNAL ACCESS.
6. Зареєструйте збірку, яка містить функцію GetProximity, з набором дозволів EXTERNAL ACCESS.
7. Зареєструйте функцію 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.

ПОСИЛАННЯ по темі