Интеграция в sql Server

Для того чтобы использовать программный код, созданный в Visual Studio .NET, вначале следует полученную сборку поместить в SQL Server. В окне Object Explorer SQL Server в разделе, который отведен для конкретной базы данных, имеется подраздел Programmability, а в нем подраздел Assemblies. Вот здесь и должны размещаться все зарегистрированные сборки для текущего пользователя. Для того чтобы зарегистрировать сборку, нужно щелкнуть правой кнопкой мыши по строке Assembly и выбрать в контекстном меню пункт New Assembly. В появившемся окне следует указать полный путь к сборке (рис. 4.11). Имя для сборки присваивается по имени файла, где она содержится. Явно задать имя сборки в SQL Server можно, используя команду create assembly.

Рис. 4.11. Окно создания сборки

Вот пример создания сборки:

create assembly assembly1

authorization dbo

from 'F:\sql\sql1\sql1\bin\Release\sql1.dll'

with permission_set = safe

Здесь:

1. assembll — имя создаваемой сборки;

2. authorization dbo — определяется имя владельца сборки. Если данный раздел опустить, то собственником сборки будет текущий пользователь;

3. from 'F:\sql\sql1\sql1\bin\Release\sql1.dll'— здесь указывается полное имя сборки;

4. with permission_set = safe — данный раздел позволяет настроить безопасность сборки. Существуют три уровня безопасности:

• safe — этот самый высокий уровень безопасности означает, что сборка не может иметь доступ к внешним ресурсам (файлам, реестру, сети и т. п.);

• externalaccess — данный уровень безопасности означает, что сборка может получить доступ к некоторым внешним ресурсам;

• unsafe — данный уровень самый небезопасный. Сборка получает полный доступ к внешним ресурсам. В том числе она может запускать внешний неуправляемый код.

Для удаления сборки из SQL Server 2005 достаточно выполнить команду drop assembly <имя>.

2. Хранимые процедуры

Перейдем теперь к вопросу о создании хранимых процедур на основе .NET-сборок. Итак, пусть имеется проект, созданный по шаблону типа Visual C# | Database (рис. 4.12)

Рис. 4.12. Выбор шаблона проекта для создания приложений с SQL Server

Обратившись к подменю Project, можно выполнить следующие действия:

1. Add User-Defines Function — добавить к проекту модуль, на основе которого можно строить пользовательскую функцию;

2. Add Stored Procedure — добавить к проекту модуль для построения хранимой процедуры;

3. Add Aggregate — добавить к проекту модуль для построения агрегирующей функции;

4. Add Trigger — добавить к проекту модуль для построения триггера;

5. Add User-defined Type — добавить к проекту модуль для построения пользовательского типа данных.

Остановимся вначале на построении хранимых процедур. Добавим к проекту модуль, воспользовавшись командой Project | Add Stored Procedure. Содержимое модуля представлено в листинге 4.6. Будущая хранимая процедура, которую мы объявим в SQL Server 2005, станет ссылаться на процедуру StoredProcedure1.

Листинг 4.6

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 sql()

{

// Put your code here

}

};

Поставим теперь задачу написать простую хранимую процедуру, которая бы возвращала результат некоторого запроса. Основным пространством имен для работы с SQL Server является пространство имен Microsoft.SqlServer.server. В листинге 4.7 представлен пример реализации хранимой процедуры, которая выполняет простой запрос и возвращает этот запрос клиенту.

Листинг 4.7

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 proc1()

{

System.Data.SqlClient.SqlCommand comm = new

System.Data.SqlClient.SqlCommand();

comm.CommandType = System.Data.CommandType.Text;

comm.CommandText = "select * from dbo.students";

Microsoft.SqlServer.Server.SqlContext.Pipe.ExecuteAndSend(comm);

}

};

Для большего понимания текста модуля (см. листинг 4.7) для всех классов и методов мы используем полные имена, несмотря на то, что в начале модуля все нужные пространства имен описаны. Мы могли бы записать процедуру proc1 и более кратко:

public static void proc1()

{

SqlCommand comm = new SqlCommand();

comm.CommandType = CommandType.Text;

comm.CommandText = "select * from dbo.students";

SqlContext.Pipe.ExecuteAndSend(comm);

}

Разберем фрагмент подробнее.

1. Класс SqlCommand. Этот класс предоставляет команду на языке Transact-SQL, которая должна исполняться на стороне SQL Server. Данный класс полезен как при программировании на стороне сервера, так и при программировании на стороне клиента. Член класса CommandText должен содержать текст команды. Член класса CommandType должен содержать тип команды— как содержимое CommandText должен быть интерпретирован. Перечисление system.Data.CommandType содержит возможные типы: Text, StoredProcedure, TableDirect.

2. Метод ExecuteAndSend используется для выполнения команды Transact-SQL и передачи результата выполнения клиенту. Этот метод является членом класса SqlPipe, но доступ к элементам этого класса осуществляется посредством класса SqlContext.Pipe. Выражение в скобках. Это так называемый атрибут. Атрибут поможет развернуть сборку на стороне сервера. В данном случае атрибут определяет свойства метода.

Атрибут — это нововведение фирмы Microsoft. С помощью атрибута можно указывать дополнительные свойства для классов, методов и свойств класса. Эти свойства затем помещаются в метаданные сборки и могут быть использованы для уяснения дополнительных возможностей элементов сборки, которые могут использовать другие программы. В частности, атрибуты широко применяются при создании программных объектов для SQL Server 2005. Атрибут указывается перед тем элементом (методом, классом и т. д.), свойства которого он определяет.

После того как процедура готова, мы можем откомпилировать проект и получить сборку. Далее сборка должна быть помещена в SQL Server. Вот команда, которая осуществляет эту операцию:

create assembly sql1

from 'F:\sql\sql1\sql1\bin\Release\sql1.dll'

with permission_set — safe

Если команда осуществлена успешно, то теперь на класс StoredProcedures и процедуру proc1 можно ссылаться при создании хранимых процедур. Далее представлена команда создания хранимой процедуры, которая ссылается на процедуру proc1 из сборки sql1.

create procedure netproc

as

external name sql1.StoredProcedures.proc1

В команде для ссылки на хранимую процедуру используется последовательность: имя_сборки.имя_класса.имя_процедуры.

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

Возникает законный вопрос: когда нужно использовать хранимые процедуры и другие модули на стороне сервера, написанные на языках .NET? Здесь, разумеется, выбор всецело принадлежит программисту. Если речь идет просто о выполнении запросов, то, разумеется, процедуры, написанные на Transact-SQL, удобнее, а главное будут выполняться быстрее. Но если на стороне сервера необходимо реализовать сложную логику, то здесь языки .NET могут повысить производительность по сравнению Transact-SQL в несколько раз. Таким образом, теперь появляется дополнительная причина переноса бизнес-логики с клиентского приложения на сервер. Следует иметь в виду, что поддержка CLR для SQL Server 2005 по умолчанию отключена. Для того чтобы подключить эту возможность, следует вы полнить команды

execute sp_configure 'clr_enabled', 1

reconfugure

Хранимая процедура sp_configure используется для изменения установок SQL Server 2005.

Мы создали хранимую процедуру без параметров. Рассмотрим, как использовать параметры при написании хранимых процедур в технологии .NET. Но прежде всего, следует установить соответствие между типами данных SQL Server 2005 и типами данных языков .NET. Нужно также иметь в виду, что пространство имен System.Data.sqlType содержит типы данных специально для работы с SQL Server. Это соответствие устанавливает табл. 4.1.

Таблица 4.1. Соответствие между типами SQL Server и типами .NET

Типы SQL Server 2005	Типы пространства имен System. Data. SqiType, предназначенные для работы с SQL Server	Стандартные типы CLR
varbinary	SqlBytes, SqlBinary	Byte[]
binary	SqlBytes, SqlBinary	Byte[]
varbinary(1), binary(1)	SqlBytes, SqlBinary	byte, Byte [ ]
image
varchar
char	—	—
nvarchar(1), nchar(1)	SqlChars, SqlString	Char, String, Char [ ]
nvarchar	SqlChars, SqlString Тип SqlChars удобнее для передачи данных, а тип SqlString удобнее при выполнении строковых операций	String, Char [ ]
nchar	SqlChars, SqlString	String, Char[]
text	—	—.
ntext	—	–
uniqueidentifier	SqlGuid	Guid
rowversion	—	Byte[]
bit	SqlBoolean	Boolean
tinyint	SqlByte	Byte
smallint	SqlInt16	Int16
int	SqlInt32	Int32
bigint	SqlInt64	Int64
smallmoney	SqlMoney	Decimal
money	SqlMoney	Decimal
numeric	SqlDecimal	Decimal
decimal	SqlDecimal	Decimal
real	SqlSingle	Single
float	SqlDouble	Double
smalldatetime	SqlDateTime	DateTime
datetime	SqlDateTime	DateTime
sql_variant	—	Object
User-defined type (UDT)	—	Тот же класс, на основе которого создан пользовательский тип данных
table	–	–
cursor	—	—
timestamp	—	—
xml	SqlXml	—

В листинге 4.8 представлен пример с хранимой процедурой, принимающей два параметра.

листинг 4.8

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 proc1(SqlInt32 id, SqlString s)

{

System.Data.SqlClient.SqlCommand comm = new

System.Data.SqlClient.SqlCommand();

comm.CommandType = System.Data.CommandType.Text;

comm.CommandText = "select * from dbo.students where id>" + id.ToString() + " and name like " + "'" + (string)s + "'";

Microsoft.SqlServer.Server.SqlContext.Pipe.ExecuteAndSend(comm);

}

};

Для того чтобы метод proc1 из листинга 4.8 стал хранимой процедурой, следует выполнить уже знакомые нам команды из листинга 4.9.

листинг 4.9.

--на всякий случай удаляем процедуру и сборку

drop function dbo.netproc

drop assembly sql1

go

--регистрируем сборку

create assembly sql1

from 'F:\sql\sql1\sql1\bin\Release\sql1.dll'

with permission_set = safe

go

--создаем хранимую процедуру

create procedure netproc

(

@id int,

@s nchar(15)

)

as

external name sql1.StoredProcedures.proc1

Обратите внимание, что в листинге 4.9 тип второго параметра хранимой процедуры указан как nchar. Это сделано в полном соответствии с табл. 4.1.