--- ADO.NET --- Хранимые процедуры

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

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

На данном этапе в базе данных AutoLot определена одна хранимая процедура с именем GetPetName, имеющая следующий формат:

Давайте добавим вызов хранимой процедуры в сборку AutoLotDAL.dll, которая рассматривалась в предыдущей статье:

Public string LookUpPetName(int carId) { string carPetName = string.Empty; // Задание имени хранимой процедуры using (SqlCommand cmd = new SqlCommand("GetPetName", this.connect)) { cmd.CommandType = CommandType.StoredProcedure; // Входной параметр. SqlParameter param = new SqlParameter(); param.ParameterName = "@carID"; param.SqlDbType = SqlDbType.Int; param.Value = carId; //По умолчанию параметры считаются входными, но все же для ясности: param.Direction = ParameterDirection.Input; cmd.Parameters.Add(param); // Выходной параметр. param = new SqlParameter(); param.ParameterName = "@petName"; param.SqlDbType = SqlDbType.Char; param.Size = 10; param.Direction = ParameterDirection.Output; cmd.Parameters.Add(param); // Выполнение хранимой процедуры. cmd.ExecuteNonQuery(); // Возврат выходного параметра. carPetName = ((string)cmd.Parameters["@petName"].Value).Trim(); } return carPetName; }

Один важный аспект, касающийся вызова хранимых процедур: вспомните, что объект команды может представлять оператор SQL (по умолчанию) или имя хранимой процедуры. Если необходимо сообщить объекту команды, что он должен вызывать хранимую процедуру, то нужно передать имя этой процедуры (через аргумент конструктора или с помощью свойства CommandText) и установить в свойстве CommandType значение CommandType.StoredProcedure (иначе вы получите исключение времени выполнения, т.к. по умолчанию объект команды ожидает оператор SQL).

Далее обратите внимание, что свойство Direction объекта параметра позволяет указать направление действия каждого параметра, передаваемого хранимой процедуре (например, входной параметр, выходной параметр, входной/выходной параметр или возвращаемое значение). Как и ранее, все объекты параметров добавляются в коллекцию параметров для данного объекта команды.

После завершения работы хранимой процедуры с помощью вызова ExecuteNonQuery() значение выходного параметра можно получить с помощью просмотра коллекции параметров для данного объекта команды и соответствующего приведения типа.

Последнее обновление: 31.10.2015

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

Объект SqlCommand имеет встроенную поддержку хранимых процедур. В частности у него определено свойство CommandType , которое в качестве значения принимает значение из перечисления System.Data.CommandType . И значение System.Data.CommandType.StoredProcedure как раз указывает, что будет использоваться хранимая процедура.

Но чтобы использовать хранимые процедуры, нам надо их вначале создать. Для этого перейдем в SQL Server Management Studio к нашей базе данных usersdb, раскроем ее узел и далее выберем Programmability->Stored Procedures. Нажмем на этот узел правой кнопкой мыши и в контекстном меню выберем пункт Stored Procedure... :

В центральной части программы открывает код процедуры, который генерируется по умолчанию. Заменим этот код следующим:

CREATE PROCEDURE . @name nvarchar(50), @age int AS INSERT INTO Users (Name, Age) VALUES (@name, @age) SELECT SCOPE_IDENTITY() GO

Эта процедура выполняет добавление данных. После выражения CREATE PROCEDURE идет название процедуры. Процедура называется "sp_InsertUser", и по этому названию мы ее будем вызывать в коде C#. Название процедуры может быть любое.

Процедура имеет два входных параметра: @name и @age. Через эти параметры будут передаваться значения для имени и возраста пользователя. В самом теле процедуры после выражения AS идет стандартное sql-выражение INSERT, которое выполняет добавление данных. И в конце с помощью выражения SELECT возвращается результат. Выражение SCOPE_IDENTITY() возвращает id добавленной записи, поэтому на выходе из процедуры мы получим id новой записи. И завершается процедура ключевым словом GO.

И затем нажмем на кнопку Execute. После этого в базу данных добавляется хранимая процедура.

Подобным образом добавим еще одну процедуру, которая будет возвращать объекты:

CREATE PROCEDURE . AS SELECT * FROM Users GO

И также для ее добавления нажмем на кнопку Execute.

Теперь перейдем к коду C# и определим следующую программу:

Class Program { static string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"; static void Main(string args) { Console.Write("Введите имя пользователя:"); string name = Console.ReadLine(); Console.Write("Введите возраст пользователя:"); int age = Int32.Parse(Console.ReadLine()); AddUser(name, age); Console.WriteLine(); GetUsers(); Console.Read(); } // добавление пользователя private static void AddUser(string name, int age) { // название процедуры string sqlExpression = "sp_InsertUser"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = new SqlCommand(sqlExpression, connection); // указываем, что команда представляет хранимую процедуру command.CommandType = System.Data.CommandType.StoredProcedure; // параметр для ввода имени SqlParameter nameParam = new SqlParameter { ParameterName = "@name", Value = name }; // добавляем параметр command.Parameters.Add(nameParam); // параметр для ввода возраста SqlParameter ageParam = new SqlParameter { ParameterName = "@age", Value = age }; command.Parameters.Add(ageParam); var result = command.ExecuteScalar(); // если нам не надо возвращать id //var result = command.ExecuteNonQuery(); Console.WriteLine("Id добавленного объекта: {0}", result); } } // вывод всех пользователей private static void GetUsers() { // название процедуры string sqlExpression = "sp_GetUsers"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = new SqlCommand(sqlExpression, connection); // указываем, что команда представляет хранимую процедуру command.CommandType = System.Data.CommandType.StoredProcedure; var reader = command.ExecuteReader(); if (reader.HasRows) { Console.WriteLine("{0}\t{1}\t{2}", reader.GetName(0), reader.GetName(1), reader.GetName(2)); while (reader.Read()) { int id = reader.GetInt32(0); string name = reader.GetString(1); int age = reader.GetInt32(2); Console.WriteLine("{0} \t{1} \t{2}", id, name, age); } } reader.Close(); } } }

Для упрощения кода обращения к процедурам здесь вынесены в отдельные методы. В методе AddUser вызывается процедура sp_InsertUser. Ее название передается в конструктор объекта SqlCommand также, как и обычное sql-выражение. И с помощью выражения command.CommandType = System.Data.CommandType.StoredProcedure устанавливается, что это выражение система будет рассматривать как хранимую процедуру.

Поскольку процедура получает данные через параметры, то соответственно нам надо определить эти параметры с помощью объектов SqlParameter . Ему передается название параметра и значение. Названия параметров должны соответствовать тем названиям, которые мы определили в коде процедуры.

С помощью метода command.Parameters.Add() параметры добавляются к процедуре. И затем происходит выполнение.

Так как в коде процедуры добавления мы определили возвращение id новой записи, то есть возвращение скалярного значения, то для выполнения команды и его получения мы можем использовать метод ExecuteScalar() . Но мы также можем использовать и метод ExecuteNonOuery() , только он вернет количество добавленных записей, а не id.

В случае второго метода все еще проще: объекту команды просто передается название процедуры, и так как процедура фактически выполняет выражение SELECT и возвращает набор данных, то для выполнения команды мы можем использовать метод ExecuteReader() . И с помощью ридера получить все данные.

Запустим программу и введем какие-либо данные на добавление.

Предыстория

Для создания бизнес приложений, основанных на SQL Server 2000, необходимо программировать бизнес логику, используя внутренний язык работы с запросами T-SQL. Но в силу его ограниченной функциональности часто создание такой логики отнимает много времени и усилий на отладку. C появлением SQL Server 2005 ситуация в корне меняется: в этой версии декларирована возможность применять при программировании хранимых процедур и различных пользовательских функций языки более высокого уровня, а именно CLR-языков (Visual Basic.NET и C#).

Поискав в Интернете материалы по интеграции SQL Server 2005 и CLR на русском языке, я нашел лишь одну статью, в которой приводились примеры по использованию CLR в SQL Server 2005 (http://www.osp.ru/win2000/2005/08/034.htm). После ее прочтения стало понятно, что автор использовал довольно специфичную версию SQL Server 2005, и поэтому корректно запустить некоторые из промеров не удалось.

Изучив часть примеров, поставляемых с MS SQL Server 2005, и почитав MSDN и SQL Server Books Online, я убедился, что некоторые примеры тоже неработоспособны.

После этого я решил попробовать сам разобраться в этом вопросе, и результатом моей работы служит эта статья.

Подготовка к работе

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

Для этого откроем SQL Server Management Studio и выполним следующий скрипт:

Sp_configure "show advanced options", 1; GO RECONFIGURE; GO sp_configure "clr enabled", 1; GO RECONFIGURE; GO

Результат работы скрипта:

Configuration option "show advanced options" changed from 1 to 1. Run the RECONFIGURE statement to install. Configuration option "clr enabled" changed from 1 to 1. Run the RECONFIGURE statement to install.

Данное действие необходимо, чтобы разрешить SQL Server 2005 выполнять пользовательские сборки.

Создание тестовой базы данных

Для того, чтобы иметь возможность выполнять тестирование создаваемых сборок, необходимо с начала создать тестовую базы данных (пусть она имеет название Test2005).

В этой базе данных будет всего одна таблица с именем Users, содержащая 4 столбца:

Скрипт для создания таблицы:

CREATE TABLE .( IDENTITY(1,1) NOT NULL, (50) COLLATE Cyrillic_General_CI_AS NOT NULL, NOT NULL, NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC)WITH (IGNORE_DUP_KEY = OFF) ON ) ON

Пусть созданная таблица содержит следующие данные:

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

Создание проекта для работы с базой данных

Для того, чтобы начать создание сборки необходимо создать проект, для этого выполним следующие действия:

Запустим Microsoft Visual Studio 2005

Создадим новый проект с названием TestingCLR , выбрав заготовку SQL Server Project в разделе Database нужного языка (Рис. 1).

Рисунок 1

В появившемся окне нам предлагают создать связь с базой данных (Рис. 2)

Рисунок 2

Нажмем кнопку Add New Reference и в появившемся окне выберем используемый для тестирования SQL Server, учетную запись, созданную базу данных и нажмем кнопку ОК.

Созданный проект имеет следующую структуру (Рис. 3):

Рисунок 3

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

После создания проекта можно перейти к созданию первой хранимой процедуры при помощи CLR.

Создание хранимой процедуры на языке CLR

Для создания хранимой процедуры необходимо нажать правой кнопкой мыши на проекты и выбрать подпункт New Item, пункта Add. После этого на экране появится диалог создания файла (Рис. 4)

Рисунок 4

Выберем в этом окне Store Procedure, введем название CreateHTMLLog и нажмем OK.

Создаваемая хранимая процедура будет создавать HTML документ с данными о пользователях, дата отключения которых больше либо равна дате, переданной ей как параметр.

Исходный код класса:

Using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { //Специальный тег которым помечаются Хранимые процедуры public static void CreateLog(SqlDateTime Value) //Процедура создания HTML файла { DataTable tbl = new DataTable(); //Здесь будут храниться результаты запроса SqlPipe Pipe = SqlContext.Pipe; //Получаем объект SQLPipe из SQLContext //Объект Pipe позволит нам работать с результатами, которые возвращает хранимая процедура SqlDataRecord record = new SqlDataRecord(new SqlMetaData("HTML", SqlDbType.Text));//Создаем Запись //Для отправки данных необходимо задать метаданные, которые представляют собой Имя колонки и ее тип System.Text.StringBuilder st = new System.Text.StringBuilder();//Создаем объект StringBuilder using (SqlConnection connection = new SqlConnection("context connection=true")) //Создаем соединение с базой данных //context connection=true показывает, что будет использоваться текущее соединение { connection.Open(); //Открываем соединение SqlCommand command = new SqlCommand("SELECT ID,Name,DateBegin,DateEnd FROM Users WHERE isnull(DateEnd,"20500101") >= @Date", connection);// Выполняем запрос к базе данных command.Parameters.AddWithValue("@Date", Value);//Определяем параметр @Date tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection)); //Загружаем результат в DataTable } //Создаем документ HTML st.Append(""); st.Append("

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class StoredProcedures _ Public Shared Sub CreateLog(ByVal Value As SqlDateTime) Dim tbl As New DataTable() "Здесь будут храниться результаты запроса Dim Pipe As SqlPipe = SqlContext.Pipe "Получаем объект SQLPipe из SQLContext "Объект Pipe позволит нам работать с результатами, которые возвращает хранимая процедура Dim record As New SqlDataRecord(New SqlMetaData("HTML", SqlDbType.Text)) "Создаем Запись "Для отправки данных необходимо задать метаданные, которые представляют собой Имя колонки и ее тип Dim st As New System.Text.StringBuilder() "Создаем объект StringBuilder Using connection As New SqlConnection("context connection=true") "Создаем соединение с базой данных "context connection=true показывает, что будет использоваться текущее соединение connection.Open() "Открываем соединение Dim command As New SqlCommand("SELECT ID,Name,DateBegin,DateEnd FROM Users WHERE isnull(DateEnd,"20500101") >= @Date", connection) "Выполняем запрос к базе данных command.Parameters.AddWithValue("@Date", Value) "Определяем параметр @Date tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection)) "Загружаем результат в DataTable End Using "Создаем документ HTML st.Append("") st.Append("

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

Запустим проект на выполнение, и в окне Output можно будет увидеть результаты работы созданной хранимой процедуры.

Теперь выполним в SQL Server Management Studio ту же строчку, что мы добавили в Test.sql, и результат выполнения будет примерно такой (Рис. 5):

Рисунок 5

Рисунок 6

Пояснения по исходному коду хранимой процедуры

SQLContext – это абстрактный объект, представляющий собой вызывающий контекст, который позволяет получить доступ к таким объектам, как SQLPipe , SQLTriggerContext и WindowsIdentity .

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

SQLTriggerContext – объект, содержащий информацию о том, что вызвало срабатывание триггера.

WindowsIdentity – объект, представляющий пользователя в системе Windows

Создание пользовательской функции, возвращающей скалярное значение

Добавим в проект новый файл, для этого в диалоге создания файла (Рис. 4) выберем User-Defined Function и назовем функцию QuantityWord .

Данная функция будет подсчитывать количество определенных символов в тексте. Исходный текст функции:

Using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { //Тег пометки что это функция //Данной функции передается 2 параметра: первый - где ищем, второй - что ищем //Результатом работы функции будет: Число вхождений word в text, либо NULL, если text или word - NULL public static SqlInt32 QuantityWord(SqlString text, SqlString word) { bool flag = false; //Определяем флаг выхода из цикла int i = -1; //Определяем смещение для поиска int count = 0; //Количество вхождений if (!text.IsNull && !word.IsNull) //Если что то NULL тогда возвращаем NULL { string t = text.Value; //Получаем строку из параметра text string w = word.Value; //Получаем строку из параметра word while (flag == false) //Запускаем цикл подсчета кол-ва вхождений { i = t.IndexOf(w, i + 1); //Ищем вхождение if (i >= 0) count++; else flag = true; } return new SqlInt32(count); //Возвращаем кол-во вхождений } else { return new SqlInt32(); //Возвращаем NULL } } }

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class UserDefinedFunctions "Данной функции передается 2 параметра: первый - где ищем, второй - что ищем "Результатом работы функции будет: Число вхождений word в text, либо NULL, если text или word - NULL _ Public Shared Function QuantityWord(ByVal text As SqlString, ByVal word As SqlString) As SqlInt32 Dim flag As Boolean = False "Определяем флаг выхода из цикла Dim i As Integer = -1 "Определяем смещение для поиска Dim count As Integer = 0 "Количество вхождений If (Not text.IsNull And Not word.IsNull) Then "Если что то NULL тогда возвращаем NULL Dim t As String = text.Value "Получаем строку из параметра text Dim w As String = word.Value "Получаем строку из параметра word While (flag = False) "Запускаем цикл подсчета кол-ва вхождений i = t.IndexOf(w, i + 1) "Ищем вхождение If (i >= 0) Then count += 1 Else flag = True End If End While Return New SqlInt32(count) "Возвращаем кол-во вхождений Else Return New SqlInt32() "Возвращаем NULL End If End Function End Class

Для проверки необходимо изменить файл Test.sql, теперь он должен содержать следующую строчку:

SELECT Name, dbo.QuantityWord(Name,"ро") AS Count FROM Users --dbo.QuantityWord("Где ищем", "Что ищем")

Если установить в любом месте исходного кода функции точку останова, и запустить проект в режиме «Start Debugging», то при выполнении запроса можно будет пошагово выполнить созданную нами функцию.

Результат работы данного запроса, если его запустить в SQL Server Management Studio, будет иметь следующий вид (Рис. 7):

Рисунок 7

Создание пользовательской функции, возвращающей таблицу

Отдельно я решил рассмотреть пользовательские функции, возвращающие как результат своей работы таблицу, так как в примерах поставляемых с SQL Server 2005 они не работают.

Добавим в проект новый файл, для этого в диалоге создания файла (Рис. 4) выберем User-Defined Function и назовем ее ReturnLetter .

Данная функция будет возвращать таблицу, в которой посимвольно разбито передаваемое функции слово.

Исходный код функции:

Using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { //Тег, определяющий, что данная функция возвращает таблицу public static System.Collections.IEnumerable ReturnLetter(SqlString text) //Функции передается 1 параметр - строка { string value; //Переменная для хранения строки if (text.IsNull) //Если функции передано значение NULL, то возвращаем пустую строку value = ""; else value = text.Value; return value.ToCharArray(); //Возвращаем массив байтов } //Функция заполнения таблицы public static void FillRow(Object obj, out string stringElement) { stringElement = obj.ToString();//Возвращает в таблицу строку } }

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class UserDefinedFunctions "Тег, определяющий, что данная функция возвращает таблицу _ Public Shared Function ReturnLetter(ByVal text As SqlString) As System.Collections.IEnumerable "Функции передается 1 параметр - строка Dim value As String "Переменная для хранения строки If text.IsNull Then "Если функции передано значение NULL, то возвращаем пустую строку value = "" Else value = text.Value End If Return value.ToCharArray() "Возвращаем массив байтов End Function "Функция заполнения таблицы Public Shared Sub FillRow(ByVal obj As Object, ByRef stringElement As String) stringElement = obj.ToString() "Возвращает в таблицу строку End Sub End Class

Для проверки созданной функции необходимо изменить файл Test.sql, он должен содержать следующую строчку:

После запуска проекта на выполнение в окне Output будут результаты работы функции.

Результат работы запроса, если его запустить в SQL Server Management Studio, будет иметь следующий вид (Рис. 8):

Рисунок 8

Пояснения по исходному коду функций

Хочу обратить внимание на тег, которым помечаются функции:

FillRowMethodName – В этом параметре указывается имя функции для заполнении таблицы.

TableDefinition – Параметр, отвечающий за метаданные, возвращаемые пользователю (странным образом данные параметр отсутствует в примерах C#, но в SQL Server Books Online (BOL) мне удалось найти, как его использовать).

Значение параметра TableDefinition равное " Testid int, Testname nvarchar(4000)" показывает, что данная функция возвращает таблицу, состоящую из 2-х столбцов. Первый столбец с именем Testid и типом int , а второй – Testname с типом nvarchar(4000) .

Хотелось бы обратить внимание на определение функции FillRow на языке VB.NET

Public Shared Sub FillRow(ByVal obj As Object, ByRef stringElement As String)

В примерах, которые поставляются с SQL Server 2005, выходные параметры не помечены как Out() , хотя BOL настоятельно рекомендует это делать (пробовал запускать как с ним, так и без него, никаких изменений не заметил). Но думаю, что лучше следовать тому, что написано в BOL.

Если ваша функция возвращает таблицу с несколькими столбцами, то тогда Out параметры перечисляются через запятую:

Imports System.Runtime.InteropServices Public Shared Sub FillRow(ByVal obj As Object, ByRef TestID As String, ByRef TestName As String)

Создание пользовательской функции агрегирования

Функции агрегирования или агрегатные функции – это функции, которые работают с подмножеством значений и возвращают скалярное значение. Ярчайшим примеров таких функция являются функции подсчета сумм Sum и количества строк Count . В этом примере мы построим такую функцию, которая будет объединять строки и как результат выдаст строку, где через запятую перечислены все входящие имена пользователей за месяц.

Исходный код функции:

Using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; //Данный класс будет сериализоваться //Формат сериализации будет UserDefined //Имя агрегатной функции будет SumText //Максимальный размер возвращаемого значения 8000 байт //Данный класс поддерживает интерфейс IBinarySerialize public struct MonthSum: IBinarySerialize { private string Names; //Локальная переменная, в которой накапливаются значения public void Init() //Данный метод вызывается, когда начинается процесс агрегирования { Names = ""; //Обнуляем накопленные значения } public void Accumulate(SqlString Value) //Данный метод вызывается для каждого значения из группы { Names += Value.Value + ","; //Накапливаем имена, разделяя их запятой } public void Merge(MonthSum Group) //Этот метод используется для слияния сложных частичных вычислений, он используется для группировки группировок { Names += Group.Names; //Получаем имена из переданного значения } public SqlString Terminate() // Возвращаем результат группировки { if (Names.Length > 0) //Если ничего не группировалось, то возвращаем NULL return new SqlString(Names.Substring(0, Names.Length - 1)); //Убираем последнюю запятую else return new SqlString(); //Возвращаем NULL } #region Методы интерфейса IBinarySerialize public void Read(System.IO.BinaryReader r) { Names = r.ReadString(); //Читаем значения Names } public void Write(System.IO.BinaryWriter w) { w.Write(Names); //Записываем значения Names } #endregion }

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server "Данный класс будет сериализоваться "Формат сериализации будет UserDefined "Имя агрегатной функции будет SumText "Максимальный размер возвращаемого значения 8000 байт "Данный класс поддерживает интерфейс IBinarySerialize _ _ Public Structure MonthSum Implements IBinarySerialize Private Names As String "Локальная переменная, в которой накапливаются значения Public Sub Init() "Данный метод вызывается, когда начинается процесс агрегирования Names = "" "Обнуляем накопленные значения End Sub Public Sub Accumulate(ByVal value As SqlString) "Данный метод вызывается для каждого значения из группы Names += value.Value + "," "Накапливаем имена, разделяя их запятой End Sub Public Sub Merge(ByVal Group As MonthSum) "Этот метод используется для слияния сложных частичных вычислений, он используется для группировки группировок Names += Group.Names "Получаем имена из переданной группы End Sub Public Function Terminate() As SqlString "Возвращаем результат группировки If (Names.Length > 0) Then "Если ничего не группировалось, то возвращаем NULL Return New SqlString(Names.Substring(0, Names.Length - 1)) "Убираем последнюю запятую Else Return New SqlString() "Возвращаем NULL End If End Function #Region "Методы интерфейса IBinarySerialize" Public Sub Read(ByVal r As System.IO.BinaryReader) Implements Microsoft.SqlServer.Server.IBinarySerialize.Read Names = r.ReadString() "Читаем значения Names End Sub Public Sub Write(ByVal w As System.IO.BinaryWriter) Implements Microsoft.SqlServer.Server.IBinarySerialize.Write w.Write(Names) "Записываем значения Names End Sub #End Region End Structure

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

После запуска проекта на выполнения результаты работы можно увидеть в окне Output.

Если же запрос из файла Test.sql выполнить в SQL Server Management Studio, то результат будет примерно такой (Рис. 9):

Рисунок 9

Пояснения по исходному коду агрегатных функций

Обратите внимание, что для создания функции агрегирования используется не класс, а структура, помеченная тегом сериализации и специальным тегом SqlUserDefinedAggregate .

Дополнительные (необязательные) атрибуты SqlUserDefinedAggregate :

IsInvariantToDuplicates – Устанавливается true, если функция агрегирования нечувствительна к повторениям (например, MAX и MIN нечувствительны, а SUM чувствительна).

IsInvariantToNulls – Устанавливается true, если функция инварианта к NULL (например, MIN и SUM используют данный атрибут, а COUNT не использует)

IsInvariantToOrder – Атрибут зарезервирован для будущего использования.

IsNullIfEmpty – Атрибут используется для отметки, что агрегат может возвращать NULL , если не одного значения не было накоплено.

Создание триггеров

Последний вопрос, который я хотел бы рассмотреть – создание триггеров c использованием CLR.

Создадим триггер, который проверяет введенную дату в столбце DateBegin. Если дата меньше 1 мая 2006 года, то триггер прерывает транзакцию.

Исходный код триггер:

Using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Server; using System.Data.SqlTypes; public partial class Triggers { //Тег помечающий что данный метод является триггером //Name - Имя триггера //Target - Таблица, для которой создается триггер //Event - Для каких событий используется триггер public static void DateTrigger() { SqlPipe pipe = SqlContext.Pipe; //Получаем "канал" для выдачи результатов SqlTriggerContext triggContext = SqlContext.TriggerContext; //Получаем контекст триггера using (SqlConnection connection = new SqlConnection("context connection = true")) //Создаем соединение с базой данных { SqlDataReader reader; //Определяем переменную для чтения данных SqlDateTime value = new SqlDateTime(); //Определяем переменную, в которую будем сохранять результат чтения int columnNumber = -1;//Номер Колонки connection.Open(); //Открываем соединение SqlCommand command = connection.CreateCommand(); //Создаем объект SqlCommand для текущего соединения command.CommandText = "SELECT * from " + "inserted"; //Записывает запрос в SqlCommand reader = command.ExecuteReader(); //Выполняем запрос на выборку значений из таблицы inserted reader.Read(); //Читаем данные for (int i = 0; i < triggContext.ColumnCount; i++) //Как пример цикла по всем столбцам { if (reader.GetName(i) == "DateBegin" && triggContext.IsUpdatedColumn(i) == true) //Ищем колонку с название DateBegin и проверяем изменилась она или нет { columnNumber = i; //Если она изменилась, сохраняем ее номер break; //Останавливаем цикл } } if (columnNumber != -1) //Если столбец DateBegin изменялся проверяем значение которое в него хотят вставить { value = reader.GetSqlDateTime(columnNumber); //Получаем вставляемое значение } reader.Close(); //Прекращаем чтение if (!value.IsNull) //Если прочитанное значение null но ничего не делаем { if (value.Value <= new DateTime(2006, 5, 1)) //Если дата меньше 1 мая 2006 года, тогда не разрешаем вводить ее { throw new Exception("Дата должная быть больше 1 мая 2006 года"); //Создаем исключение для отмены транзакции } } } } }

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class Triggers "Тег помечающий что данный метод является триггером "Name - Имя триггера "Target - Таблица, для которой создается триггер "Event - Для каких событий используется триггер _ Public Shared Sub DateTrigger() Dim pipe As SqlPipe = SqlContext.Pipe "Получаем "канал" для выдачи результатов Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext "Получаем контекст триггера Using connection As New SqlConnection("context connection = true") "Создаем соединение с базой данных Dim reader As SqlDataReader "Определяем переменную для чтения данных Dim value As New SqlDateTime() "Определяем переменную, в которую будем сохранять результат чтения Dim columnNumber As Integer = -1 "Номер Колонки connection.Open() "Открываем соединение Dim command As SqlCommand = connection.CreateCommand() "Создаем объект SqlCommand для текущего соединения command.CommandText = "SELECT * from " + "inserted" "Записывает запрос в SqlCommand reader = command.ExecuteReader() "Выполняем запрос на выборку значений из таблицы inserted reader.Read() "Читаем данные For i As Integer = 0 To triggContext.ColumnCount - 1 "Как пример цикла по всем столбцам If (reader.GetName(i) = "DateBegin" And triggContext.IsUpdatedColumn(i) = True) Then "Ищем колонку с название DateBegin и проверяем, изменилась она или нет columnNumber = i "Если она изменилась, сохраняем ее номер Exit For "Останавливаем цикл End If Next If (Not columnNumber = -1) Then "Если столбец DateBegin изменялся, проверяем значение, которое в него хотят вставить value = reader.GetSqlDateTime(columnNumber) "Получаем вставляемое значение End If reader.Close() "Прекращаем чтение If (Not value.IsNull) Then "Если прочитанное значение null но ничего не делаем If (value.Value <= New DateTime(2006, 5, 1)) Then "Если дата меньше 1 мая 2006 года, тогда не разрешаем вводить ее Throw New Exception("Дата должная быть больше 1 мая 2006 года") "Создаем исключение для отмены транзакции End If End If End Using End Sub End Class

Чтобы проверить работу триггера в файл Test.sql внесем следующий текст:

После запуска проекта на выполнение результаты запроса из файла Test.sql можно будет увидеть в окне Output.

Если же данный запрос на обновление запустить в SQL Server Management Studio, то результат его работы будет таким (Рис. 10):

Рисунок 10

Пояснения по исходному коду триггера

В данном примере цикл прохода по столбцами сделан для наилучшего понимания работы CLR триггеров.

Использование триггеров доставило мне немало проблем. В BOL и MSDN отсутствует информация о том, как отменить транзакцию, как запретить вставку неправильных данных в таблицу. В примере из статьи Винода Кумара используются методы, не поддерживаемые текущей версией SQL Server 2005.

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

Поэкспериментировав с различными вариантами отмены транзакций, можно сделать вывод – есть только один способ сделать это – вызвать исключение в CLR функции. Также можно в качестве результата передать пользователю RAISERROR , но самое главное – выполнение данного запроса не заключать в Try… Catch .

Command.CommandText = "RAISERROR("Дата должная быть больше 1 мая 2006 года",16,1)"; pipe.ExecuteAndSend(command); //throw new Exception("Дата должная быть больше 1 мая 2006 года"); //Создаем исключение для отмены транзакции

Command.CommandText = "RAISERROR("Дата должная быть больше 1 мая 2006 года",16,1)" pipe.ExecuteAndSend(command) "Throw New Exception("Дата должная быть больше 1 мая 2006 года") "Создаем исключение для отмены транзакции

Если следовать такой схеме, то результат вставки неправильных данных в таблицу будут выглядеть так (Рис 11):

Рисунок 11

Теперь я хотел бы пояснить вопрос относительно класса SQLTriggerContext .

Данные класс содержит следующие свойства и методы:

ColumnCount – количество колонок содержащихся в таблице, для которой вызывается триггер

EventData – XML данные о том, что вызвало срабатывание триггера

TriggerAction – Свойство, показывающее, какое действие вызвало срабатывание триггера

IsUpdatedColumn – Метод для проверки изменялись ли данные в определенной колонке.

Основные методы и свойства класса SQLPipe :

IsSendingResults – Свойство, показывающее, что SQLPipe находится в режиме отправки одного результирующего множества клиенту

ExecuteAndSend – Выполняет команду и сразу отправляет результат клиенту

Send – Перегруженный метод, который позволяет отправлять клиенту одну строку результата, несколько строк результата, или сообщение.

SendResultsStart – Используется для отправки клиенту метаданных о том, какие данные он будет получать.

SendResultsRow – Отправка одной строки данных клиенту

SendResultsEnd – Отмечает окончание отправки данных и возвращает SQLPipe в начальное состояние.

Ручное подключение сборки к SQL Server 2005

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

Подключение сборки к базе данных:

Запустив SQL Server Management Studio, перейдите в раздел Assemblies созданной базы данных Test2005 (Рис. 12)

Рисунок 12

В меню правой кнопки мыши выбираем пункт New Assembly, и в появившемся окне нажимаем кнопку Browse (Рис. 13)

Рисунок 13

После этого выбираем файл TestingCLR.dll, находящийся в папке bin\Debug созданного проекта и нажимаем кнопку OK.

Теперь создадим хранимую процедуру, которая будет вызывать созданную нами CLR процедуру. Для этого в SQL Server Management Studio на панели инструментов нажмем кнопку New Query и в открывшемся окне введем следующий текст:

CREATE PROCEDURE @Value datetime --Параметр, который передается в процедуру AS EXTERNAL NAME .. --Ссылка на сборку и на функцию, которая буде выполняться при вызове данной хранимой процедуры

И запускаем его на выполнение.

В результате чего в разделе Programmablity/Store Procedures появится процедура CreateLog .

Результаты подключения можно проверить, выполнив запрос:

Теперь создадим оболочку для функции QuantityWord , для этого в окно выполнения запросов внесем текст:

CREATE FUNCTION (@text nvarchar(4000), --Параметр функции (В котором будет искаться текст) @word nvarchar(4000) --Параметр функции (какой текст будет искаться)) RETURNS AS EXTERNAL NAME ..

После выполнения этой команды в разделе Programmablity/Scalar-valued Function появиться функцияQuantityWord , для ее проверки выполним запрос:

SELECT Name, dbo.QuantityWord(Name,"ро") AS Count FROM Users --dbo.QuantityWord("В чем ищем", "Что ищем")

Создаем оболочку для функции ReturnLetter , в окно выполнения запросов вносим текст:

CREATE FUNCTION (@text nvarchar(4000)--Входной параметр функции) RETURNS TABLE(Chars nchar(1)) --Структура возвращаемой таблицы AS EXTERNAL NAME ..

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

SELECT * FROM dbo.ReturnLetter("Использование Microsoft SQL Server 2005")

Подключение функции агрегирования происходит в том же режиме:

CREATE AGGREGATE (@value nvarchar(4000) -- Передаваемое значение в агрегат) RETURNS nvarchar(4000) --Возвращаемое значение EXTERNAL NAME .

Проверка:

SELECT dbo.SumText_vb(Name) AS SumText, DateBegin FROM Users GROUP BY DateBegin

Подключение триггера:

CREATE TRIGGER ON --Триггер для таблицы Users FOR UPDATE,INSERT --Действия UPDATE и INSERT AS EXTERNAL NAME ..

Проверка:

UPDATE Users SET DateBegin = "20050101" --20050101 - дата 1 января 2005 года в универсальном формате SQL Server WHERE ID = 1

После выполнения всех этих действий структура базы данных должна иметь примерно следующий вид (Рис.14):

Рисунок 14

Заключение

В этой статье я попытался рассмотреть основы использования Common Language Runtime в Microsoft SQL Server 2005. В данной статье не рассмотрен вопрос создания User-Defined Type с использование CLR. Рассмотрению этого вопроса будет посвящена другая статья об интеграции SQL Server 2005 с.NET Framework.

Для чего это все надо

Что же дает интеграция.NET и SQL Server 2005. Теперь для создания хранимых процедур, пользовательских функций и триггеров, реализующих бизнес логику можно использовать такие CLR языки программирования как Visual Basic .NET и C#. Так же появилась возможность создавать свои собственные агрегатные функции (например, написать замену функции SUM или MAX). Теперь можно использовать большую часть функционала.NET, такого как регулярные выражения, работа с веб-службами, сложные математические расчеты. Многие скажут, что такой функционал можно было и на T-SQL реализовать, но попробуйте реализовать созданную в этой статье функцию агрегирования текстовых данных, попробуйте написать проверку введенного пользователем E-mail на T-SQL. И поймете что простое использование регулярного выражения «\b+@+\.{2,4}\b», позволит не только увеличить производительность труда, но и заметно скажется на возможностях реализуемой бизнес логики. Так же теперь к процессу разработки первичной бизнес логики, можно подключать не только людей уверенно знающих T-SQL, но и программистов на VB.NET и C#. Подробнее о том, как устроена работа SQL Server 2005 и.NET можно прочитать в статье Винода Кумара «Как получить максимум от CLR» (http://www.osp.ru/win2000/2005/08/034.htm).

Используемые источники

1. Кумар В. Как получить максимум от CLR – Журнал “WINDOWS IT Pro” #08, 2005год (http://www.osp.ru/win2000/2005/08/034.htm)
2. Microsoft SQL Server 2005 Books Online
3. MSDN Library for Visual Studio 2005

Особая благодарность Туманову Анатолию Александровичу от автора Егорова Никиты (C…R…a…S…H)