Обратите внимание на то, что в функции используется тип данных nvarchar, а не varchar. Строковый тип данных относится к типу данных Unicode, и это должно учитываться в объявлении типа данных функции.

Вслед за завершением описанных действий мы можем приступить к небольшой проверке:

SELECT ContactID, FirstName, LastName, EmailAddress, Phone FROM Person.Contact

WHERE dbo. f CLRExample ( [a-zA-Z0-9 \-]+@ ( [a-zA-Z0-9 \-]+\ .) + (com org edu mil net) ,

EmailAddress) = 1

Если читатель не вносит в рассматриваемую таблицу никаких изменений, то фактически должен быть получен успешный результат проверки каждой строки в таблице, поскольку во всех них содержится адрес adventure-works.com. Проведем еще одну простую проверку, чтобы определить, как действует функция применительно к адресу с допустимым и недопустимым форматом:

DECLARE ©GoodTestMail varchar(100), ©BadTestMail varchar(100)

SET ©GoodTestMail = robv@professionalsql.com SET ©BadTestMail = misc. text

SELECT dbo.fCLRExample( [a-zA-Z0-9 \-]+@([a-zA-Z0-9 \-]+\.)+(comorgedunzau)

©GoodTestMail) AS ShouldBel SELECT dbo.fCLRExample([a-zA-Z0-9 \-]+@([a-zA-Z0-9 \-]+\.)+(comorgedunzau),

©BadTestMail) AS ShouldBeO

В целях сокращения объема книги здесь не приведена полная строка регулярного выражения для проверки адреса электронной почты. Чтобы обеспечить исчерпывающую проверку, потребовалось бы, в частности, включить все допустимые коды государств, применяемые в качестве обозначения доменов верхнего уровня, такие как а и, са, ик и us. Количество таких кодов превышает несколько сотен, но и на этом требования к всесторонней проверке адреса электронной почты не заканчиваются. Несмотря на сказанное, основная приведенная здесь конструкция является вполне приемлемой, и читатель может внести в нее необходимые изменения в соответствии со своими конкретными требованиями.

Вернемся к рассмотрению ожидаемых результатов: ShouldBel

(1 row(s) affected)

ShouldBeO

(1 row(s) affected)

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

ALTER TABLE Person.Contact ADD CONSTRAINT ExampleFunction

CHECK (dbo. f CLRExample ( [a-zA-Z0-9 \-] +© ( [a-zA-Z0-9 \-] +\ .) + (com org edu nz au) ,

EmailAddress) = 1)

Если мы теперь попытаемся обновить строку, вставив в контролируемый столбец недопустимые данные, эта попытка будет отвергнута:

UPDATE Person.Contact

SET EmailAddress = blah blah

WHERE ContactID = 1

В СУБД SQL Server формируется сообщение, суть которого сводится к тому, что данные в операторе обновления конфликтуют с ограничением проверки: Msg 547, Level 16, State О, Line 1

The UPDATE statement conflicted with the CHECK constraint ExampleFunction . The conflict occurred in database AdventureWorks , table Person.Contact , column EmailAddress. The statement has been terminated.

Создание табличных функций

Описанию функций будет посвящено еще несколько следующих разделов настоящей главы. Рассмотрим, с чем это связано. Прежде всего следует отметить, что функции, применяемые в сборках, которые предназначены для использования в СУБД SQL Server, имеют еще некоторые особенности по сравнению с функциями в других сборках.

В этом разделе основное внимание будет уделено функциям, возвращающим значения в виде таблицы, или табличным функциям. Рассматриваемая тема относится к наиболее сложным из тех, которым посвящена настоящая глава, но табличные функции, применяемые в сборках, являются столь же мощными, как и используемые в языке T-SQL. Поэтому табличные функции имеют очень широкую область применения. В частности, табличные функции могут служить и для выполнения столь простых действий, как специальная обработка столбца в каком-то приложении, которую пришлось бы при отсутствии такой возможности проводить с помощью обычной функции на языке T-SQL, и для осуществления настолько сложных операций, как слияние данных из нескольких разрозненных и внешних источников данных.

Приступим к дальнейшим действиям и начнем разработку еще одного проекта Visual Studio, присвоив ему имя ExampleTVF; для этого воспользуемся шаблоном проекта SQL Server, а также добавим новую пользовательскую функцию. На этот раз демонстрируется получение доступа к файловой системе, поэтому добавьте следующие ссылки:

using System;

using System.10;

using System.Collections;

using Microsoft.SqlServer.Server;

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

Функция, применяемая в качестве точки входа, должна реализовывать интерфейс IEnumerable. Это- специальный, широко используемый интерфейс в инфраструктуре .NET, который по существу обеспечивает итерацию по последовательности, заданной в той или иной форме (это может быть массив, коллекция, таблица или что-то другое). При создании табличной функции мы должны также определить свойство FillRowMethodName. Функция, указанная в этом специальном свойстве, неявно вызывается в СУБД SQL Server каждый раз, когда возникает необходимость перейти от одной строки таблицы к другой. Практика показывает, что значительная часть разработчиков предусматривает вызов той функции, которая была реализована с помощью

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

Итак, отложим на время более подробное описание рассматриваемых компонентов и перейдем к изучению вопроса о том, как открыть разрабатываемую функцию. Эта функция предназначена для получения листинга содержимого каталога, но указанный листинг формируется на основе информации, которая должна быть получена путем обработки отдельных файлов. Это означает, что необходимо обработать в цикле содержимое каталога, чтобы получить информацию о каждом файле. Исключительно для того, чтобы сделать функцию более удобной в использовании, предусмотрим также возможность обработки подкаталогов; для этого необходимо обеспечить раскрытие каталогов, содержащихся в других каталогах (подкаталогов).

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

public partial class UserDefinedFunctions {

[SqlFunction(FillRowMethodName = FillRow )]

public static lEnumerable DirectoryList(string sRootDir, string sWildCard, bool bIncludeSubDirs)

Выборка массива записей каталога. Если бы применяемый объект создавался пользователем, то в нем потребовалось бы обеспечить поддержку интерфейса lEnumerable, но эта задача в объекте ArrayList уже решена, поэтому не требуется предпринимать какие-либо дополнительные действия ArrayList aFileArray = new ArrayList();

DirectorySearch(sRootDir, sWildCard, bIncludeSubDirs, aFileArray); return aFileArray;

Решение этой задачи практически сводится к тому, что создается массив, предназначенный для хранения списка файлов, и вызываются внутренние функции для его заполнения. После этого необходимо реализовать функцию, обрабатывающую в цикле листинг каталога, для получения информации о файлах в каждом каталоге:

private static void DirectorySearch(string directory, string sWildCard, bool bIncludeSubDirs, ArrayList aFileArray)

GetFiles(directory, sWildCard, aFileArray); if (bIncludeSubDirs)

foreach (string d in Directory.GetDirectories(directory))

DirectorySearch(d, sWildCard, bIncludeSubDirs, aFileArray);

Применительно к каждому каталогу, предназначенному для получения информации о файлах, выполняется простой вызов метода GetFiles (который реализован в библиотеке System. 10), после чего в цикле обрабатываются результаты, относя-