Регистрация определяемых пользователем типов в SQL Server
Для использования определяемого пользователем типа в MicrosoftSQL Server необходимо его зарегистрировать. Регистрация определяемого пользователем типа включает регистрацию сборки и создание типа в базе данных, в которой ее нужно использовать. Определяемые пользователем типы находятся в одной базе данных и не могут использоваться в нескольких базах данных, пока идентичная сборка и определяемый пользователем тип не будут зарегистрированы в каждой базе данных. После регистрации сборки определяемого пользователем типа и создания типа, можно использовать этот тип в Transact-SQL и клиентском коде. Дополнительные сведения см. в разделе Определяемые пользователем типы данных CLR.
Использование Visual Studio для развертывания определяемых пользователем типов
Самый простой способ развертывания определяемого пользователем типа является использование Microsoft Visual Studio. Однако, для более сложных сценариев развертывания и наибольшей гибкости используется Transact-SQL, как описано далее в этом разделе.
Для создания и развертывания определяемых пользователем типов с помощью Visual Studio, выполните следующие шаги:
Создайте в разделах языков Visual Basic и Visual C# новый проект База данных.
Добавьте ссылку на базу данных SQL Server, содержащую определяемый пользователем тип.
Добавьте класс Определяемый пользователем тип.
Напишите код для реализации определяемого пользователем типа.
В меню Построить выберите пункт Развернуть. Эта команда регистрирует сборку и создает тип в базе данных SQL Server.
Использование Transact-SQL для развертывания определяемых пользователем типов
Синтаксис CREATE ASSEMBLY Transact-SQL используется для регистрации сборки в базе данных, в которой требуется использование определяемого пользователем типа. Он хранится внутри системных таблиц базы данных, а не внешне в файловой системе. Если определяемый пользователем тип зависит от внешних сборок, их тоже необходимо загрузить в базу данных. Инструкция CREATE TYPE используется для создания определяемого пользователем типа в базе данных, в которой его необходимо использовать. Дополнительные сведения см. в разделах CREATE ASSEMBLY (Transact-SQL) и CREATE TYPE (Transact-SQL).
Использование синтаксиса CREATE ASSEMBLY
Синтаксис CREATE ASSEMBLY регистрирует сборку в базе данных, в которой требуется использование определяемого пользователем типа. После регистрации сборки она не имеет зависимостей.
Создание нескольких версий одной сборки в данной базе данных не допускается. Однако возможно создание нескольких версий одной сборки, в зависимости от культуры данной базы данных. SQL Server разделяет несколько культурных версий сборки по разным именам, зарегистрированным в экземпляре SQL Server. Дополнительные сведения см. в разделе «Создание и использование сборок со строгими именами» в пакете .NET Framework SDK.
Когда синтаксис CREATE ASSEMBLY выполняется с наборами разрешений SAFE или EXTERNAL_ACCESS, сборка проверяется на совместимость и безопасность типа. Если указание набора разрешений пропущено, предполагается использование SAFE. Код с набором разрешений UNSAFE не проверяется. Дополнительные сведения о наборах разрешений сборки см. в разделе Конструирование сборок.
Примеры
Следующая инструкция Transact-SQL регистрирует сборку «Point» с набором разрешений SAFE в SQL Server базе данных AdventureWorks. Если предложение WITH PERMISSION_SET пропускается, сборка регистрируется с набором разрешений SAFE.
USE AdventureWorks;CREATE ASSEMBLY PointFROM '\\ShareName\Projects\Point\bin\Point.dll' WITH PERMISSION_SET = SAFE;
Следующая инструкция Transact-SQL регистрирует сборку с помощью аргумента <assembly_bits> в предложении FROM. Данное значение varbinary представляет файл в виде потока байтов.
USE AdventureWorks;CREATE ASSEMBLY PointFROM 0xfeac4 … 21ac78
Использование синтаксиса CREATE TYPE
После загрузки сборки в базу данных можно создать тип с помощью инструкции CREATE TYPE Transact-SQL. Она добавляет тип в список доступных типов для этой базы данных. Тип имеет область базы данных и может использоваться только в той базе данных, в которой он был создан. Если определяемый пользователем тип уже существует в базе данных, то инструкция CREATE TYPE завершиться с ошибкой.
.gif "Примечание") Примечание |
|---|
Синтаксис CREATE TYPE также используется для создания собственных псевдонимов типов данных SQL Server, и предназначен для замены sp_addtype посредством создания псевдонимов типов данных. Некоторые из дополнительных аргументов в синтаксисе CREATE TYPE служат для создания определяемых пользователем типов, и не применимы для создания псевдонимов типов данных (например, базового типа). |
| Примечание |
|---|
Начиная с версии SQL Server 2005 в базе данных SQL Server с уровнем совместимости 80 нельзя создавать управляемые определяемые пользователем типы, хранимые процедуры, функции, статистические функции и триггеры. Чтобы сделать эти функции интеграции со средой CLR доступными в SQL Server, необходимо при помощи хранимой процедуры sp_dbcmptlevel (Transact-SQL) задать для базы данных уровень совместимости 100. |
Дополнительные сведения см. в разделе CREATE TYPE (Transact-SQL).
Примеры
Следующая инструкция Transact-SQL создает тип Point. EXTERNAL NAME указывается с помощью синтаксиса имен из двух частей AssemblyName.UDTName
CREATE TYPE dbo.Point EXTERNAL NAME Point.[Point];
Удаление определяемого пользователем типа из базы данных
Инструкция DROP TYPE удаляет определяемый пользователем тип из текущей базы данных. После удаления определяемого пользователем типа можно инструкцией DROP ASSEMBLY удалить сборку из базы данных.
Инструкция DROP TYPE не выполняется в следующих ситуациях:
Таблицы в базе данных, которые содержат столбцы, определенные с помощью определяемого пользователем типа.
Функции, хранимые процедуры или триггеры, которые используют переменные или параметры определяемого пользователем типа и созданы в базе данных с помощью предложения WITH SCHEMABINDING.
Примеры
Следующая инструкция Transact-SQL должна выполняться в следующем порядке. Сначала необходимо удалить таблицу, которая обращается к определяемому пользователем типу Point, затем тип, и наконец сборку.
DROP TABLE dbo.Points;DROP TYPE dbo.Point;DROP ASSEMBLY Point;
Поиск зависимостей определяемого пользователем типа.
Если есть зависимые объекты, например, таблицы с определениями столбцов определяемых пользователем типов, то инструкция DROP TYPE завершиться с ошибкой. Также она завершиться с ошибкой, если есть функции, хранимые процедуры, или триггеры, созданные в базе данных с помощью предложения WITH SCHEMABINDING, или эти процедуры используют переменные и параметры определяемого пользователем типа. Сначала необходимо удалить все зависимые объекты, а затем выполнить инструкцию DROP TYPE.
Следующий запрос Transact-SQL определяет все столбцы и параметры в базе данных AdventureWorks, в которых используется определяемый пользователем тип.
USE Adventureworks;SELECT o.name AS major_name, o.type_desc AS major_type_desc , c.name AS minor_name, c.type_desc AS minor_type_desc , at.assembly_class FROM ( SELECT object_id, name, user_type_id, 'SQL_COLUMN' AS type_desc FROM sys.columns UNION ALL SELECT object_id, name, user_type_id, 'SQL_PROCEDURE_PARAMETER' FROM sys.parameters ) AS c JOIN sys.objects AS o ON o.object_id = c.object_id JOIN sys.assembly_types AS at ON at.user_type_id = c.user_type_id;
Обслуживание определяемых пользователем типов
После создания определяемого пользователем типа в базе данных SQL Server его нельзя изменить, хотя можно изменить сборку, на которой основан этот тип. В большинстве случаев необходимо удалить из базы данных определяемый пользователем тип с помощью инструкции DROP TYPE Transact-SQL, внести изменения в базовую сборку, и загрузить ее повторно с помощью инструкции ALTER ASSEMBLY. Затем необходимо повторно создать определяемый пользователем тип и зависимые объекты.
Примеры
Инструкция ALTER ASSEMBLY используется после внесения изменений в исходный код сборки определяемого пользователем типа и ее повторной компиляции. Она копирует DLL-файл на сервер и выполняет повторную привязку к новой сборке. Полный синтаксис см. в разделе ALTER ASSEMBLY (Transact-SQL).
Следующая инструкция ALTER ASSEMBLY Transact-SQL повторно загружает сборку Point.dll из указанного места на диске.
ALTER ASSEMBLY PointFROM '\\Projects\Point\bin\Point.dll'
Использование синтаксиса ALTER ASSEMBLY для добавления исходного кода
Предложение ADD FILE в синтаксисе инструкции ALTER ASSEMBLY отсутствует в инструкции CREATE ASSEMBLY. Оно обеспечивает возможность добавления исходного кода или любых других файлов, связанных со сборкой. Файлы копируются из исходных расположений и сохраняются в системных таблицах базы данных. Это обеспечивает постоянную доступность исходного кода или других файлов на тот случай, если возникнет необходимость повторного создания или документирования текущей версии определяемого пользователем типа.
Следующая инструкция ALTER ASSEMBLY Transact-SQL добавляет исходный код класса Point.cs для определяемого пользователем типа Point. В результате этого текст, содержащийся в файле Point.cs, будет скопирован и сохранен в базе данных с именем PointSource.
ALTER ASSEMBLY PointADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;
Сведения о сборке хранятся в таблице sys.assembly_files базы данных, в которой установлена сборка. Таблица sys.assembly_files содержит следующие столбцы.
assembly_id
Идентификатор, определенный для сборки. Это число назначается всем объектам, относящимся к одной сборке.Имя
Имя объекта.file_id
Числу, идентифицирующему каждый объект, при связи первого объекта со столбцом assembly_id присваивается значение 1. Если с одним столбцом assembly_id связывается несколько объектов, то каждое последующее значение file_id увеличивается на 1.content
Шестнадцатеричное представление сборки или файла.
Для преобразования содержимого столбца content в доступный для чтения текст используется функция CAST или CONVERT. Следующий запрос преобразует содержимое файла Point.cs в доступный для чтения текст, используя для ограничения результирующего набора до одной строки имя в предложении WHERE.
SELECT CAST(content AS varchar(8000)) FROM sys.assembly_files WHERE name='PointSource';
При копировании и вставке результатов в текстовый редактор видно, что разрывы строк и пробелы, существовавшие в исходном тексте, сохранились.
Управление определяемыми пользователем типами и сборками
При планировании реализации определяемых пользователем типов, примите во внимание, какие методы нужны в самой сборке определяемого пользователем типа, а какие нужно создать в отдельных сборках и реализовать в виде определяемых пользователем функций или хранимых процедур. Разделение методов в отдельные сборки позволяет обновлять код без затрагивания данных, которые могут хранится в столбце определяемого пользователем типа таблицы. Сборки определяемого пользователем типа можно изменять без удаления столбцов и других зависимых объектов только когда новое определение может считать бывшие значения, а подпись типа не изменена.
Разделение процедурного кода, который может отличаться от кода, требуемого для реализации определяемого пользователем типа, сильно упрощает обслуживание. Включение кода, необходимого для функционирования определяемого пользователем типа и максимально возможное упрощение определений определяемого пользователем типа снижает риск необходимости удаления этого типа из базы данных для редакции кода или исправления ошибок.
Определяемый пользователем тип «Currency» и функция конвертации валюты
Определяемый пользователем тип Currency в образце базы данных AdventureWorks предоставляет полезный пример рекомендуемого способа структурирования определяемого пользователем типа и связанных с ним функций. Определяемый пользователем тип Currency используется для обработки денег на основе денежной системы определенной культуры, и позволяет хранить различные типы валют, например, доллары, евро и т. п. Класс определяемого пользователем типа представляет культуру в виде строки, а количество денег — в виде типа данных decimal. Все необходимые методы сериализации содержаться внутри сборки, определяющей класс. Функция, реализующая конвертацию валют из одной культуры в другую внедряется в виде внешней функции с именем ConvertCurrency и располагается в отдельной сборке. Функция ConvertCurrency выполняется свою работу путем получения курса перевода из таблицы базы данных AdventureWorks. Если источник коэффициентов конвертации будет изменен, или в существующий код будут внесены какие-либо другие изменения, сборку можно будет легко изменить без затрагивания определяемого пользователем типа Currency.
Листинг кода для определяемого пользователем типа Currency и функции ConvertCurrency можно получить путем установки образцов среды CLR. Дополнительные сведения см. в разделе Вопросы установки образцов кода и образцов баз данных SQL Server.
Использование определяемых пользователем типов в нескольких базах данных
Определяемые пользователем типы по определению находятся в одной базе данных. Таким образом, определяемый пользователем тип, созданный в одной базе данных, нельзя использовать в определении столбца другой базы данных. Для того, чтобы использовать определяемые пользователем типы в нескольких базах данных, в каждой базе данных необходимо выполнить инструкции CREATE ASSEMBLY и CREATE TYPE на идентичных сборках. Сборки считаются идентичными, если имеют одинаковое имя, строгое имя, культуру, версию, набор разрешений и двоичное содержимое.
После того, как определяемый пользователем тип зарегистрирован и доступен в обеих базах данных, можно преобразовать значение данного типа из одной базы данных для использования в другой. Идентичные определяемые пользователем типы могут использоваться в нескольких базах данных в следующих сценариях:
Вызов хранимой процедуры определен в различных базах данных.
Запрашивание таблиц определено в различных базах данных.
Выбор данных определяемого пользователем типа из столбца определяемого пользователем типа одной базы данных и их вставка в идентичный столбец второй базы данных.
В данных ситуациях любое преобразование, требуемое сервером, происходит автоматически. Эти преобразования нельзя выполнить явным образом с помощью функций CAST или CONVERT Transact-SQL.
Обратите внимание, что для использования определяемых пользователем типов при создании SQL Server Database Engine рабочих таблиц в системной базе данных tempdb, не требуется предпринимать каких-либо действий. Это включает обработку курсоров, переменные таблицы и определяемые пользователем возвращающие табличное значение функции, которые включают определяемые пользователем типы и явно использующие системную базу данных tempdb. Однако, в случае явного создания временной таблицы в системной базе данных tempdb, определяющей столбец определяемого пользователем типа, данный тип должен быть зарегистрирован в системной базе данных tempdb тем же способом, что и для пользователя базы данных.