Предыстория
Для создания бизнес приложений, основанных на 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 столбца:
| Название |
Тип |
Описание |
| ID |
int |
Уникальный номер |
| Name |
nvarchar(50) |
Имя сотрудника |
| DateBegin |
smalldatetime |
Дата подключения |
| DateEnd |
smalldatetime |
Дата отключения |
Скрипт для создания таблицы:
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL,
[DateBegin] [smalldatetime] NOT NULL,
[DateEnd] [smalldatetime] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Пусть созданная таблица содержит следующие данные:
| Name |
DateBegin |
DateEnd |
| Егоров |
01.01.2006 |
NULL |
| Петров |
02.01.2006 |
NULL |
| Иванов |
01.01.2006 |
04.04.2006 |
| Павлов |
02.01.2006 |
04.04.2006 |
| Иванеева |
05.05.2006 |
NULL |
| Сидоров |
01.01.2006 |
NULL |
После выполнения этих действий можно приступить к созданию сборки.
Создание проекта для работы с базой данных
Для того, чтобы начать создание сборки необходимо создать проект, для этого выполним следующие действия:
Запустим 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 документ с данными о пользователях, дата отключения которых больше либо равна дате, переданной ей как параметр.
Исходный код класса:
C#
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure] //Специальный тег которым помечаются Хранимые процедуры
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("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01//EN\" \"http://www.w3.org/TR/html4/strict.dtd\"><html><head><TITLE>Тестирование CRL</TITLE></head><body>");
st.Append("<table><tr>");
st.Append("<td>№</td><td>Name</td><td>DateBegin</td><td>DateEnd</td></tr>");
//Просматривая tbl, и создаем на ее основе HTML
for (int i = 0; i < tbl.Rows.Count; i++)
{
st.Append("<tr>");
st.Append("<td>");
st.Append(tbl.Rows[i]["ID"].ToString());
st.Append("</td>");
st.Append("<td>");
st.Append(tbl.Rows[i]["Name"].ToString());
st.Append("</td>");
st.Append("<td>");
st.Append(tbl.Rows[i]["DateBegin"].ToString());
st.Append("</td>");
st.Append("<td>");
st.Append(tbl.Rows[i]["DateEnd"].ToString());
st.Append("</td>");
st.Append("</tr>");
}
st.Append(" </table></BODY></HTML>");
//Записываем созданный HTML код в возвращаемый результат
record.SetSqlString(0, st.ToString());
//Отправляем его пользователю
Pipe.Send(record);
}
}
VB.NET
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
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("<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.01//EN"" ""http://www.w3.org/TR/html4/strict.dtd""><html><head><TITLE>Тестирование CRL</TITLE></head><body>")
st.Append("<table><tr>")
st.Append("<td>№</td><td>Name</td><td>DateBegin</td><td>DateEnd</td></tr>")
'Просматривая tbl, и создаем на ее основе HTML
For i As Integer = 0 To tbl.Rows.Count - 1
st.Append("<tr>")
st.Append("<td>")
st.Append(tbl.Rows(i)("ID").ToString())
st.Append("</td>")
st.Append("<td>")
st.Append(tbl.Rows(i)("Name").ToString())
st.Append("</td>")
st.Append("<td>")
st.Append(tbl.Rows(i)("DateBegin").ToString())
st.Append("</td>")
st.Append("<td>")
st.Append(tbl.Rows(i)("DateEnd").ToString())
st.Append("</td>")
st.Append("</tr>")
Next
st.Append(" </table></BODY></HTML>")
'Записываем созданный HTML код в возвращаемый результат
record.SetSqlString(0, st.ToString())
'Отправляем его пользователю
Pipe.Send(record)
End Sub
End Class
Чтобы протестировать созданную хранимую процедуру, добавим в файл Test.sql одну строчку, которая вызываем созданную нами процедуру:
exec CreateLog '20060505' --20060505 - это дата 5 мая 2006 года
Запустим проект на выполнение, и в окне Output можно будет увидеть результаты работы созданной хранимой процедуры.
Теперь выполним в SQL Server Management Studio ту же строчку, что мы добавили в Test.sql, и результат выполнения будет примерно такой (Рис. 5):
Рисунок 5
Если полученный HTML код сохранить, как файл, то получится примерно такая страничка (Рис. 6):
Рисунок 6
Пояснения по исходному коду хранимой процедуры
SQLContext – это абстрактный объект, представляющий собой вызывающий контекст, который позволяет получить доступ к таким объектам, как SQLPipe, SQLTriggerContext и WindowsIdentity.
SQLPipe – объект, являющийся своего рода туннелем, позволяющим передавать необходимую информацию вызывающему контексту.
SQLTriggerContext – объект, содержащий информацию о том, что вызвало срабатывание триггера.
WindowsIdentity – объект, представляющий пользователя в системе Windows
Создание пользовательской функции, возвращающей скалярное значение
Добавим в проект новый файл, для этого в диалоге создания файла (Рис. 4) выберем User-Defined Function и назовем функцию QuantityWord.
Данная функция будет подсчитывать количество определенных символов в тексте. Исходный текст функции:
C#
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction] //Тег пометки что это функция
//Данной функции передается 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
}
}
}
VB.NET
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
<Microsoft.SqlServer.Server.SqlFunction()> _
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.
Данная функция будет возвращать таблицу, в которой посимвольно разбито передаваемое функции слово.
Исходный код функции:
C#
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
//Тег, определяющий, что данная функция возвращает таблицу
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRow", TableDefinition = "Chars nchar(1)")]
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();//Возвращает в таблицу строку
}
}
VB.NET
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
'Тег, определяющий, что данная функция возвращает таблицу
<Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRow", TableDefinition:="Chars nchar(1)")> _
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, <Runtime.InteropServices.Out()> ByRef stringElement As String)
stringElement = obj.ToString() 'Возвращает в таблицу строку
End Sub
End Class
Для проверки созданной функции необходимо изменить файл Test.sql, он должен содержать следующую строчку:
SELECT * FROM dbo.ReturnLetter('Использование Microsoft SQL Server 2005')
После запуска проекта на выполнение в окне Output будут результаты работы функции.
Результат работы запроса, если его запустить в SQL Server Management Studio, будет иметь следующий вид (Рис. 8):
Рисунок 8
Пояснения по исходному коду функций
Хочу обратить внимание на тег, которым помечаются функции:
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRow", TableDefinition = "Chars nchar(1)")]
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, <Runtime.InteropServices.Out()> ByRef stringElement As String)
В примерах, которые поставляются с SQL Server 2005, выходные параметры не помечены как Out(), хотя BOL настоятельно рекомендует это делать (пробовал запускать как с ним, так и без него, никаких изменений не заметил). Но думаю, что лучше следовать тому, что написано в BOL.
Если ваша функция возвращает таблицу с несколькими столбцами, то тогда Out параметры перечисляются через запятую:
Imports System.Runtime.InteropServices
Public Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef TestID As String, <Out()> ByRef TestName As String)
Создание пользовательской функции агрегирования
Функции агрегирования или агрегатные функции – это функции, которые работают с подмножеством значений и возвращают скалярное значение. Ярчайшим примеров таких функция являются функции подсчета сумм Sum и количества строк Count. В этом примере мы построим такую функцию, которая будет объединять строки и как результат выдаст строку, где через запятую перечислены все входящие имена пользователей за месяц.
Исходный код функции:
C#
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
//Данный класс будет сериализоваться
//Формат сериализации будет UserDefined
//Имя агрегатной функции будет SumText
//Максимальный размер возвращаемого значения 8000 байт
//Данный класс поддерживает интерфейс IBinarySerialize
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, Name = "SumText", MaxByteSize = 8000)]
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
}
VB.NET
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
'Данный класс будет сериализоваться
'Формат сериализации будет UserDefined
'Имя агрегатной функции будет SumText
'Максимальный размер возвращаемого значения 8000 байт
'Данный класс поддерживает интерфейс IBinarySerialize
<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, Name:="SumText", MaxByteSize:=8000)> _
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, этот файл должен содержать следующий текст:
SELECT
dbo.SumText_vb(Name) AS SumText,
DateBegin
FROM
Users
GROUP BY
DateBegin
После запуска проекта на выполнения результаты работы можно увидеть в окне 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 года, то триггер прерывает транзакцию.
Исходный код триггер:
C#
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public partial class Triggers
{
//Тег помечающий что данный метод является триггером
//Name - Имя триггера
//Target - Таблица, для которой создается триггер
//Event - Для каких событий используется триггер
[Microsoft.SqlServer.Server.SqlTrigger(Name = "DateTrigger", Target = "Users", Event = "FOR UPDATE,INSERT")]
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 года"); //Создаем исключение для отмены транзакции
}
}
}
}
}
VB.NET
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class Triggers
'Тег помечающий что данный метод является триггером
'Name - Имя триггера
'Target - Таблица, для которой создается триггер
'Event - Для каких событий используется триггер
<Microsoft.SqlServer.Server.SqlTrigger(Name:="DateTrigger", Target:="Users", Event:="FOR UPDATE,INSERT")> _
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 внесем следующий текст:
UPDATE
Users
SET
DateBegin = '20050101' --20050101 - дата 1 января 2005 года в универсальном формате SQL Server
WHERE
ID = 1
После запуска проекта на выполнение результаты запроса из файла Test.sql можно будет увидеть в окне Output.
Если же данный запрос на обновление запустить в SQL Server Management Studio, то результат его работы будет таким (Рис. 10):
Рисунок 10
Пояснения по исходному коду триггера
В данном примере цикл прохода по столбцами сделан для наилучшего понимания работы CLR триггеров.
Использование триггеров доставило мне немало проблем. В BOL и MSDN отсутствует информация о том, как отменить транзакцию, как запретить вставку неправильных данных в таблицу. В примере из статьи Винода Кумара используются методы, не поддерживаемые текущей версией SQL Server 2005.
Покопавшись в BOL, я нашел, что отменить транзакцию, в которой выполняются CLR функции нет никакой возможности. CLR функции могут отменять только те транзакции, которые определены внутри их кода.
Поэкспериментировав с различными вариантами отмены транзакций, можно сделать вывод – есть только один способ сделать это – вызвать исключение в CLR функции. Также можно в качестве результата передать пользователю RAISERROR, но самое главное – выполнение данного запроса не заключать в Try…Catch.
C#
command.CommandText = "RAISERROR('Дата должная быть больше 1 мая 2006 года',16,1)";
pipe.ExecuteAndSend(command);
//throw new Exception("Дата должная быть больше 1 мая 2006 года"); //Создаем исключение для отмены транзакции
VB.NET
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 [CreateLog]
@Value datetime --Параметр, который передается в процедуру
AS
EXTERNAL NAME [TestingCLR].[TestingCLR.StoredProcedures].[CreateLog] --Ссылка на сборку и на функцию, которая буде выполняться при вызове данной хранимой процедуры
И запускаем его на выполнение.
В результате чего в разделе Programmablity/Store Procedures появится процедура CreateLog.
Результаты подключения можно проверить, выполнив запрос:
exec CreateLog '20060505' --20060505 - это дата 5 мая 2006 года
Теперь создадим оболочку для функции QuantityWord, для этого в окно выполнения запросов внесем текст:
CREATE FUNCTION [QuantityWord]
(
@text nvarchar(4000), --Параметр функции (В котором будет искаться текст)
@word nvarchar(4000) --Параметр функции (какой текст будет искаться)
)
RETURNS [int]
AS
EXTERNAL NAME [TestingCLR].[TestingCLR.UserDefinedFunctions].[QuantityWord]
После выполнения этой команды в разделе Programmablity/Scalar-valued Function появиться функцияQuantityWord, для ее проверки выполним запрос:
SELECT Name, dbo.QuantityWord(Name,'ро') AS Count FROM Users --dbo.QuantityWord('В чем ищем', 'Что ищем')
Создаем оболочку для функции ReturnLetter, в окно выполнения запросов вносим текст:
CREATE FUNCTION [ReturnLetter]
(
@text nvarchar(4000)--Входной параметр функции
)
RETURNS TABLE(Chars nchar(1)) --Структура возвращаемой таблицы
AS
EXTERNAL NAME [TestingCLR].[TestingCLR.UserDefinedFunctions].[ReturnLetter]
Выполняем эту команду и проверяем правильность работы:
SELECT * FROM dbo.ReturnLetter('Использование Microsoft SQL Server 2005')
Подключение функции агрегирования происходит в том же режиме:
CREATE AGGREGATE [SumText]
(
@value nvarchar(4000) -- Передаваемое значение в агрегат
)
RETURNS nvarchar(4000) --Возвращаемое значение
EXTERNAL NAME [TestingCLR].[TestingCLR.MonthSum]
Проверка:
SELECT
dbo.SumText_vb(Name) AS SumText,
DateBegin
FROM
Users
GROUP BY
DateBegin
Подключение триггера:
CREATE TRIGGER [DateTrigger]
ON [Users] --Триггер для таблицы Users
FOR UPDATE,INSERT --Действия UPDATE и INSERT
AS
EXTERNAL NAME [TestingCLR].[TestingCLR.Triggers].[DateTrigger]
Проверка:
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[A-Z0-9._%-]+@[A-Z0-9._%-]+\.[A-Z]{2,4}\b», позволит не только увеличить производительность труда, но и заметно скажется на возможностях реализуемой бизнес логики. Так же теперь к процессу разработки первичной бизнес логики, можно подключать не только людей уверенно знающих T-SQL, но и программистов на VB.NET и C#. Подробнее о том, как устроена работа SQL Server 2005 и .NET можно прочитать в статье Винода Кумара «Как получить максимум от CLR» (http://www.osp.ru/win2000/2005/08/034.htm).
Используемые источники
- Кумар В. Как получить максимум от CLR – Журнал “WINDOWS IT Pro” #08, 2005год (http://www.osp.ru/win2000/2005/08/034.htm)
- Microsoft SQL Server 2005 Books Online
- MSDN Library for Visual Studio 2005
Особая благодарность Туманову Анатолию Александровичу от автора Егорова Никиты (C…R…a…S…H)
|