В версиях, предшествующих SQL Server 2005, вы могли использовать для создания хранимых процедур только операторы Transact-SQL. SQL Server 2005 вводит новую возможность Common Language Runtime (CLR), которая позволяет вам разрабатывать различные объекты базы данных (хранимые процедуры, функции, определенные пользователем, триггеры, определенные пользователем агрегаты и определенные пользователем типы) с помощью С# и Visual Basic. CLR также позволяет выполнять эти объекты, используя общую систему выполнения.

В примере 8.9 показано, как вы можете разрешить использование CLR.

 

Для реализации, компиляции и сохранения процедур при использовании CLR вы должны выполнить следующие четыре шага в указанном порядке:

1. Напишите хранимую процедуру на языке С# (или Visual Basic) и скомпилируйте программу, используя соответствующий компилятор.

2. Используйте оператор create assembly для создания соответствующего выполняемого файла.

3. Сохраните процедуру как серверный объект посредством оператора create procedure.

4. Выполните процедуру, используя оператор execute.

На рис. 8.1 показано, как работает CLR. Вы используете среду разработки, подобную Visual Studio, для реализации вашей программы. После ее создания

 

 

запускаете компилятор С# или Visual Basic для генерации объектного кода. Этот код будет сохранен в dll-файле, который является исходным для оператора create assembly. После выполнения этого оператора вы получаете промежуточный код. На следующем шаге вы используете оператор create procedure для сохранения выполняемого файла в качестве объекта базы данных. И, наконец, хранимая процедура может быть выполнена с использованием уже хорошо известного оператора execute.

Примеры 8.10-8.14 демонстрируют только что описанный процесс. В примере 8.10 показана программа на С#, которая будет использована для демонстрации того, как вы применяете CLR для создания и распространения хранимых процедур.

 

 

Эта программа использует запрос для вычисления количества строк в таблице employee. Директивы using в начале программы задают пространства имен, такие как system. Data. Эти директивы позволяют вам задавать имена классов в исходном тексте программы без указания соответствующих имен пространств. Затем определяется класс storedProcedures, который описывается с атрибутом [sqiProcedure]. Этот атрибут сообщает компилятору, что данный класс является хранимой процедурой. Внутри этого класса определяется метод GetEmployeeCount (). Соединение с базой данных устанавливается с использованием экземпляра conn класса sojxonnection. Метод open о применяется к этому экземпляру для открытия соединения. Метод CreateCommand(), примененный к conn,   позволяет  вам   получить  доступ   к  экземпляру

SqlCommand, названному sqlCmd.

Следующие строки кода:

sqlCmd.CommandText = «select count(*)  as  'Employee Count'  « + «from employee»; iRows = (int)sqlCmd.ExecuteScalar ();

используют оператор select для поиска количества строк в таблице employee и для отображения результата. В этой команде указанный текст служит для задания значения свойства CommandText экземпляра sqlCmd, полученного при вызове метода CreateCommand ().

Затем вызывается метод ExecuteScaiar о экземпляра SqlCommand. Он возвращает скалярное значение, которое преобразуется в тип данных int и присваивается переменной iRows.

В примере 8.11 показан первый шаг по развертыванию хранимой процедуры при использовании CLR.

 

 

Пример 8.11 демонстрирует, как нужно скомпилировать в С# метод GetEmployeeCount (см. пример 8.10). Фактически, эта команда может быть использована вообще для компиляции любой программы С#, если вы зададите соответствующее имя для исходной программы, esc- команда, которая используется для вызова компилятора С#. Вы вызываете команду esc в командной строке Windows. Прежде чем вызвать эту команду, вы должны указать размещение компилятора, используя переменную окружения path. На момент написания этой книги компилятор С# (файл csc.exe) можно было найти в каталоге: C:\WlNDOWS\Microsoft.NET\Framework\v2.050727. (Вы должны выбрать подходящую версию компилятора.)

Опция /target задает имя программы С#, а опция /reference определяет dll-файл, который необходим для процесса компиляции.

В примере 8.12 показан следующий шаг в создании хранимой процедуры. (Используйте SQL Server Management Studio для выполнения этого оператора.)

 

 

Оператор create assembly использует управляемый код в качестве исходных данных для создания соответствующего объекта, вместе с которым должны быть созданы CLR хранимые процедуры, UDF и триггеры. Этот оператор имеет следующий синтаксис:

create assembly assembly_name [ authorization owner_name ] from {dll_file}

[with permission_set =  {  safe   |  external_access   |  unsafe  }]

assembiy_name- имя сборки. Необязательное предложение authorization задает имя роли в качестве владельца этой сборки. Предложение from задает путь, где будет размещаться загружаемая сборка. (В примере 8.12 dll-файл, сгенерированный из исходной программы, скопирован из каталога Framework в корневой каталог диска С:.)

Предложение with permission set является очень важным предложением оператора create assembly и должно быть задано всегда. Оно содержит набор кодов доступа, предоставленных сборке. Значение safe определяет наиболее ограниченные полномочия. Код, выполняемый в сборке с этими полномочиями, не может иметь доступ к внешним системным ресурсам, таким как файлы. Значение external_access открывает сборкам доступ к некоторым внешним системным ресурсам, в то время как unsafe предоставляет неограниченный доступ к ресурсам, в том числе и к тем, которые находятся вне системы базы данных.

Database Engine также поддерживает операторы alter assembly и drop assembly. Вы можете использовать оператор alter assembly для обновления системного каталога до последних модулей, поддерживаемых в реализации .NET. Этот оператор также добавляет или удаляет файлы, связанные с данной сборкой. Оператор drop assembly удаляет указанную сборку и все связанные с ней файлы из текущей базы данных.

В примере 8.13 создаются хранимые процедуры, основанные на управляемом коде, реализованном в примере 8.10.

 

 

Оператор create procedure в примере 8.13 отличается от такого же оператора, используемого в примерах 8.5 и 8.7, потому что он содержит опцию external name. Эта опция указывает, что код сгенерирован с использованием CLR. Имя в этом предложении состоит из трех частей:

assembly_name. class_name.method_name

Здесь:

♦ assembly_name-имя сборки (см. пример 8.12);

♦ class_name- имя класса public (см. пример 8.10);

♦ method_name- необязательная часть имени; это имя метода, который задан внутри класса.

Пример 8.14 используется для выполнения процедуры GetEmployeeCount.

 

 

Оператор print возвращает текущее количество строк таблицы employee.

Функции, определенные пользователем

В языках программирования обычно существуют два типа подпрограмм:

♦ хранимые процедуры;

♦ функции, определенные пользователем (User Defined Functions, UDF).

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