Главная » Microsoft SQL Server, Базы данных » Создание типов данных в Visual Studio 2005

0

Хранимые процедуры, функции и триггеры CLR и те же объекты Т-SQL имеют больше сходств, чем отличий. В общем случае эти типы CLR будут состоять из одного метода. В противоположность этому пользовательские типы (далее UDT) и консолидации (далее UDA) вообще не похожи на хранимые процедуры, функции и триггеры CLR.

Объекты UDT и UDA могут быть структурами или классами. Следует заметить, что использование структур в общем случае предпочтительнее, поскольку по определению структуры хранятся в стеке, а типы ссылок — в куче объекта. UDT и UDA, оформленные как класс, вероятнее всего, будут иметь тип ссылки, что сильнее повлияет на производительность, чем на использование структур. Как бы там ни было, UDT и UDA будут содержать множество членов и должны удовлетворять большому числу критериев проектирования. В оставшейся части настоящей главы мы сосредоточим внимание на типах хранимых процедур, функций и триггеров, основанных на методах.

Дополнительная Более подробная информация о пользовательских типах приведена в главе 29.

информация

Создание проекта CLR

Первые шаги в создании компонента интеграции CLR в Visual Studio 2005 практически идентичны, независимо от того, какой тип компонента создается. Например, даже несмотря на то, что по умолчанию проект интеграции CLR не требует наличия подключения к базе данных, поскольку в будущем он будет существовать в самой базе, Visual Studio потребует определить его, поскольку он будет использоваться на этапах разработки и отладки компонента. Определите в проекте то местоположение базы данных, в которой компонент будет тестироваться, уже на этапе создания проекта.

После того как источник данных будет определен, в любой момент можно щелкнуть на нем правой кнопкой мыши в окне Server Explorer, чтобы открыть контекстное меню, содержащее команды отладки приложения, объектов Т-SQL и типов интеграции CLR. Пользователь также должен иметь разрешения к хранимой процедуре sp_enable_sql_debug (находящейся в базе данных master) и разрешения изменения (ALTER) для всех объектов, отладка которых будет производиться в базе данных. Следует отметить, что только администраторы базы данных имеют разрешение на отладку типов CLR, содержащих предложение EXECUTE AS или использующих сертификаты; к тому же на сервере одновременно может отлаживаться только один тип. Во время сессии отладки замораживаются все остальные потоки CLR. Это, естественно, нельзя назвать идеальным вариантом для совместно используемого сервера, если именно на нем выполняется тестирование интеграции. Отладка компонентов CLR больше подходит для обособленной среды тестирования, в которой сервер базы данных является экземпляром, выделенным для монопольного использования одним разработчиком.

А      Развертывание и тестирование интеграции CLR требует установки SQL

Server 2005 и Visual Studio 2005. Отладка и совместная работа во время отлад- Зг         ки не особо хорошо поддерживаются, если используется общая среда SQL

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

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

Рис. 27.1. Создание проекта интеграции CLR в Visual Studio 2005. После того как выберите тип базы данных для используемого языка программирования на левой панели, на правой выберите используемый шаблон проекта

Puc. 27.3. Добавление ссылки в проект SQL Server.

В этом окне отображаются все остальные компоненты сборки в пространстве проекта и доступные сборки системных компонентов

Ссылки на Web-службу XML создают в проекте прокси-сервер для файлов . wsdl и . asmx. Этот сервер позволит выполнять инструкции на стадии разработки проекта, а также стать компоненту интеграции CLR клиентом для указанной Web-службы. Файл определения Web- службы может существовать на локальном сервере, в локальной сети или в Интернете. Как только Web-служба будет добавлена в проект, члены, сгенерированные в прокси-сервере, можно просмотреть в окне Object Explorer. Потенциал использования Web-служб хранимыми процедурами CLR несказанно велик.

Будьте осторожны при использовании Web-служб на стадии разработки. Сгене- Виамание! рированный прокси-сервер может выполнять программный код на локальном компьютере в контексте безопасности пользователя, зарегистрировавшегося в момент открытия IDE программы Visual Studio. В зависимости от того, что делает Web-служба, локальная рабочая станция может быть во время ее работы чрезмерно загружена.

Допсдшиеяичая Дополнительные детали использования Web-служб в SQL Server описываются ^формация в главах 31 и 32.

Программирование хранимой процедуры CLR

Хранимые процедуры являются наиболее гибкой формой типов интеграции CLR. Не нужно и говорить, что они — также самый простой из всех типов. Хранимые процедуры являются наиболее подходящими объектами для использования уровней защиты CAS EXTERNAL_ ACCESS и UNSAFE. Несмотря на то что в некоторых сценариях предпочтительнее задействовать функции или, что менее вероятно, триггеры, хранимые процедуры CLR реализуют более естественный канал интеграции в базу данных гетерогенных хранилищ. Риск использования функций и триггеров заключается в том, что при привлечении их в потенциально “долгоиграющие” сценарии в среде выполнения с привилегиями это станет ограничивающим фактором в их использовании в качестве интерфейса между ядром базы данных и внешним миром. В противоположность этому хранимые процедуры .NET могут легко и безопасно взаимодействовать с файловой системой.

Весь программный код, описываемый в настоящей главе и касающийся про- В ектов Visual Studio 2005, можно загрузить с Web-сайта книги по адресу www. ^ч^/^Сети SQLServerBible.com.

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

И Читает данные, требующие доступа в контексте подключения.

?               Выполняет запись в файловую систему, что влечет за собой использование уровня защиты С AS EXTERNAL ACCESS.

?               Выводит запрашивающему информационные сообщения, реализуя возможность использования класса канала.

Процедура принимает два аргумента.

?               Допустимый запрос Т-SQL, функция или хранимая процедура, которая возвращает один или более результирующих наборов данных.

?               Допустимое имя файла, включая путь к нему.

В приведенном ниже примере продемонстрированы следующие части этой хранимой процедуры. Вначале создается шаблон хранимой процедуры. После добавления шаблона хранимой процедуры в новый проект базы данных и добавления пространства имен System. 10 в сборку методу хранимой процедуры может быть присвоено имя, также могут быть определены аргументы. Затем для доступа к локальным данным будет необходимо использовать контекст внутреннего подключения процесса. Так как внешняя процедура должна иметь возможность получить доступ к данным с помощью вызова хранимой процедуры и/или инструкции SQL, следует реализовать объект, способный выполнить обе эти операции. Далее может оказаться полезным информировать о выполняемом действии вызывающую процедуру. Это легко реализовать с помощью объекта SQLPipe и форматирования сообщения. Например, значения входных аргументов могут быть переданы как информационное сообщение.

Обратите внимание на то, что аргументы, объявленные с “родным” типом SqlString, должны быть преобразованы перед использованием в операциях .NET, в то время как строка .NET (тип string) может использоваться и без преобразования. В последнем случае преобразование выполняется неявно при вызове процедуры. Таким образом, затрат на выполнение преобразования не избежать, хотя трудоемкость программирования можно несколько снизить.

Перед использованием объекта Sql Connect ion он должен быть открыт. К тому же перед записью результатов в файл должен быть открыт поток к этому файлу. Здесь мы впервые используем пространство имен System. 10, ранее добавленное в сборку. Более того, так как процедура использует ресурсы вне контекста SQL Server, в котором запущена, уровень защиты должен быть установлен в EXTERNAL ACCESS, при этом должны быть заданы необходимые права и разрешения для доступа к файловой системе.

Как только соединение будет открыто и файл готов к получению данных, инструкция может выполняться. Существует масса методов выполнения инструкций. Можно наполнять набор данных DataSet, находящийся в памяти, чтобы направить потоком результаты в формате XML. а можно просто получить скалярное значение. Каждая из этих операций требует наличия специального метода. В примере экспорта в файл CSV результат должен быть направлен в поток как односторонний результирующий набор данных или объект DataReader с использованием метода ExecuteReader. Метод ExecuteReader принимает аргумент CommandBehavior, который подстраивает результат к предъявляемым требованиям. В данном примере определен режим работы команды по умолчанию, так что этот аргумент может быть опущен. Например, если требования определяют, что только один результирующий набор данных может быть направлен в файл, а не несколько, следует указать аргумент CommandBehavior. SingleResult.

Когда выполняется DataReader, схема результирующего набора данных может быть легко идентифицирована на лету с помощью создания в памяти объекта DataTable и наполнения его с помощью вызова метода GetSchemaTable существующего объекта DataReader. Этот метод поможет включить в файл CSV имена столбцов.

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

Imports System.10

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub clrusp_ResultToCSVFile(ByVal sTSQL As SqlString, _ ByVal sFile As String)

Dim cnCLR As SqlConnection = New _

SqlConnection("context connection=true")

Dim qryGetResult As SqlCommand = cnCLR.CreateCommand

qryGetResult.CommandText = CStr(sTSQL)

qryGetResult.CommandType = CommandType.Text

Dim plnfo As SqlPipe = SqlContext.Pipe

plnfo.Send("T-SQL batch to execute: " & CStr(sTSQL))

plnfo.Send("Destination file: " & sFile)

cnCLR.Open()

Dim strmResult As StreamWriter = New StreamWriter(sFile)

Dim sqldrResult As SqlDataReader = _

qryGetResult.ExecuteReader(CommandBehavior.Default)

Dim sqldrResultSchema As DataTable sqldrResultSchema = sqldrResult.GetSchemaTable For Each drRow As DataRow In sqldrResultSchema.Rows strmResult.Write(drRow("ColumnName").ToString)

If CInt(drRow("ColumnOrdinal")) < FieldCount – 1 Then strmResult.Write, 0, 1)

Else

strmResult.Write(vbCrLf, 0, 2)

End If Next

If sqldrResult.HasRows Then While sqldrResult.Read()

For i As Int32 = 0 To sqldrResult.FieldCount – 1 strmResult.Write(sqldrResult.GetSqlValue(i).ToString, 0, _

Len(sqldrResult.GetSqlValue(i).ToString))

If i < sqldrResult.FieldCount – 1 Then strmResult.Write(", ", 0, 1)

Else

strmResult.Write(vbCrLf, 0, 2)

End If Next End While End If

strmResult.Flush() strmResult.Close() sqldrResult.Close() cnCLR.Close()

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

Функции CLR

Функция CLR в большинстве случаев не требует такого объема программного кода, как хранимые процедуры. По той же причине, по которой рекомендуется быть в функциях T-SQL предельно кратким, желательно несколько ограничить области применения функций CLR. Это позволит сохранить транзакции максимально краткими и одновременно избежать издержек производительности, которые могут быть скрыты в программном коде функций. Хранимые процедуры CLR способны элегантно модифицировать структуру данных, в то время как функции никогда не смогут изменить состояние базы данных.

Компания Microsoft решила не препятствовать созданию клиентских подключе- Вкимакке! ний ADO.NET 2.0 в теле функций CLR. Это значит, что существует возможность изменения состояния базы данных и нельзя предотвратить такие действия, выполненные из функции. Такая возможность может свести на нет все хорошее, что есть в функциях CLR. Остерегайтесь этих средств!

Теперь посмотрим, как можно использовать скалярную функцию для проверки 1Р-адреса. Такую операцию легко выполнить с помощью функции CLR, использующей обычные выражения .NET. Эта функция является достаточно мощной, поскольку может быть использована в инструкциях DML, хранимых процедурах Т-SQL, ограничениях проверки и вычисляемых столбцах.

Для создания функции добавьте импорт пространства имен System. Text. RegularExpression в шаблон Visual Studio 2005, установите атрибуты функции, определяя, что она детерминированная и точная, введите несколько строк программного кода, после чего разверните функцию. Эта функция может оказаться полезной в ограничениях проверки и индексируемых вычисляемых столбцах, так как она выполняет необходимые манипуляции строками гораздо быстрее, чем эквивалент на языке T-SQL.

Imports System.Text.RegularExpression

<Microsoft.SqlServer.Server.SqlFunction _

(DataAccess:=DataAccessKind.None/ IsDeterministic:=True, _

IsPrecise:=True)> _

Public Shared Function clrfn_IsIP(ByVal Value As String) As Boolean Dim rx As New Regex( _

11 ( (2 [0-4] \d|25 [0-5] | [01] ?\d\d?) \ .) {3} (2 [0-4] \d|25 [0-5] | [01] ?\d\d?) " , RegexOptions.IgnoreCase _

Or RegexOptions.IgnorePatternWhitespace)

Return rx.Match(CType(Value, String)).Success End Function

Если функция в качестве результата возвращает набор данных, а не скалярное значение, результатом станет потоковая табличная функция. Ключевым словом здесь является “потоковая”. Табличные функции Т-SQL должны вернуть всю таблицу до того, как она сможет быть использована. Это задействует большой объем памяти, равно как и пространства в базе tempdb, если результирующий набор данных велик, или если запрос для получения результата обрабатывает большой объем данных. Та же функция, описанная как потоковая функция CLR, может быть использована, как только создана, таким образом, снижая требования к памяти и базе tempdb. К счастью, табличные функции позволяют набору данных быть инициализированным, используя контекст подключения, а не создавая новое клиентское подключение ADO. Использование клиентского подключения ADO.NET сводит на нет роль функции — обеспечение эффективного выполнения не основанных на множествах операций. Следовательно, таких подключений следует избегать.

Для завершения обзора типов CLR, относящихся к базам данных, рассмотрим триггер. В SQL Server 2005 триггеры могут предложить много нового. Триггеры CLR реализуют всю функциональность, доступную в триггерах Т-SQL. Триггер может быть ассоциирован с действием DDL или DML. В триггерах DML доступны таблицы Inserted и Deleted. Триггерами DDL могут быть события или группы событий.

Триггер, предложенный в качестве примера в Books Online, демонстрирует использование стандартных выражений для проверки правильности адреса электронной почты и последующего занесения записи в журнал аудита. Этот пример может показаться интересным, однако логичнее было бы реализовать данную задачу с помощью ограничения проверки и триггера аудита Т-SQL. На самом деле в большинстве задач, для которых предлагается решение с помощью триггера CLR, триггер Т-SQL, делающий запись в очереди брокера служб, был бы удобнее как для разработчика, так и для администратора. Время покажет, но триггер CLR претендует на роль наименее полезного из типов CLR. Давайте рассмотрим следующий триггер CLR DDL, который реализует свойства контекста триггера для операций DDL уровня базы данных. Это позволит продемонстрировать способ формирования триггера CLR.

Триггеры интеграции CLR могут показаться интересными тем, кто заинтересо- Назаштку ван в совместимости с законом Сарбанеса-Оксли, поскольку действия аудита компилируются и потому не так чувствительны к подстановке и непреднамеренной модификации, как триггеры Т-SQL. Контекст XML события триггера также содержит стандартизированный и полный набор информации аудита.

<Microsoft.SqlServer.Server.SqlTrigger _

(Name:="clrtr_ddlchanges", _

Targe t: = "DATABAS E " , _

Event:="DDL_DATABAS E_LEVEL_EVENTS") > _

Public Shared Sub clrtr_ddlchanges()

Dim plnfo As SqlPipe = SqlContext.Pipe

plnfо.Send(SqlContext.TriggerContext.TriggerAction.ToString) plnfo.Send(SqlContext.TriggerContext.EventData.Value.ToString) plnfo.Send(SqlContext.Windowsldentity.Name.ToString)

End Sub

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

Интеграцию CLR можно также использовать для создания пользовательских в функций консолидации (типа min (), max () или sum ()). Подробное описание ти процесса разработки пользовательских функций консолидации данных выходит ^ за рамки настоящей книги, однако вы можете найти примеры таких функций на

сайте книги (www. SQLServerBible. com).

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии