Как часто Вам приходиться писать хранимые процедуры на языке T-SQL? Насколько сложными могут быть эти процедуры?

Я хотел бы показать как можно упростить этот процесс и показать как можно использовать LINQ To SQL непосредственно внутри Microsoft(r) SQL Server 2005.

Я создал тестовую базу данных, у которой есть три поля, - ID пользователя, его имя и фамилия.

Какую хранимую процедуру нам необходимо написать, которая будет добавлять нового пользователя? Я предполагаю такую:

CREATE PROCEDURE dbo.RegisterUser
(
    @First_Name nvarchar(50),
    @Last_Name nvarchar(50)
)
AS
    SET NOCOUNT OFF;
INSERT INTO UserInfo
                      ([First Name], [Last Name])
VALUES     (@First_Name,@Last_Name)
GO

Это маленький легкий пример, но обычно хранимые процедуры пишут, чтобы заложить в них некую логику работы. Пример:

Create procedure [dbo].[csp_OrderBook]
    @UserName    nvarchar (100),
    @BookId        int,
    @ReturnId    int output,               
    @ReturnMsg    nvarchar (255) output
as
begin
    DECLARE @BookStatus int
    DECLARE @title nvarchar (500)
    DECLARE @message nvarchar (2000)

    DECLARE @ServerName nvarchar (255)

    DECLARE @NotificationString nvarchar (1000)

    DECLARE @ReturnId_Email int
    DECLARE @ReturnMsg_Email nvarchar (255)

...

set @ReturnId = 1 
set @ReturnMsg = ''

select @title = title from Repository where id = @RepositoryId

select @RepositoryStatus= [status] from DueRep
where BookId = @BookId

if @somethingis is null
begin

...

end

      Платформа .NET привлекает многих разработчиков своей простотой, и тем набором функциональности, которым она обладает. Microsoft(r) интергрировала .NET Framework в большинство своих приложений, это так же коснулось Microsoft(r) SQL Server начиная с версии 2005.

С выходом .NET Framework 3.5 вышла в свет технология LINQ, и с ней такие технологии как LINQ To Xml, LINQ To SQL, ...

    Как же можно применять LINQ To SQL непосредственно внутри хранимой процедуры? Для выполнения необходимо включить SQL CLR (1) разрешить базе данных работать в режиме unsafe (2)

(1)

EXEC sp_configure 'clr enabled', 1

GO

RECONFIGURE WITH OVERRIDE

GO

(2) ALTER DATABASE [yourdatabase] SET trustworthy ON

Далее необходимо добавить сборки, необходимые для работы с LINQ.

create assembly [System.Core]
authorization dbo
from 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\system.core.dll'
with permission_set = unsafe

create assembly [smdiagnostics]
authorization dbo
from 'C:\WINDOWS\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMDiagnostics.dll'
with permission_set = unsafe

create assembly [system.runtime.serialization]
authorization dbo
from 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.Runtime.Serialization.dll'
with permission_set = unsafe

create assembly [System.Data.Linq]
authorization dbo
from 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Data.Linq.dll'
with permission_set = unsafe

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

После создания проекта, необходимо в Visual Studio изменить уровень разрешения сборки на Unsafe

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

После того как проект будет успешно завершен, можно начинать применять непосредственно LINQ.

Поскольку, среди Items, которые мы можем добавить, нету LINQ To SQL

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

После запуска sqlmetal мы получим готовый dbml файл с "описанием"  структуры базы данных.

(sqlmetal /server:.\sqlexpress /database:Test /dbml:Test.dbml )

В данном случае нам нет необходимости генерировать сразу код, т.к. код автоматически сгенерирует Visual Studio после того, как мы добавим dbml файл к проекту.

Перед добавлением неоходимо убедиться, что проект использует Framework 3.5

Далее мы добавляем Stored Procedure к проекту

и Жмем Add.

После чего у нас готово пространство для написания кода на C#

Добавляем необходимые параметры для процедуры (если они нужны)

и далее пишем код, как обычно для LINQ To SQL

[Microsoft.SqlServer.Server.SqlProcedure]
public static void RegisterUser(string firstName, string lastName)
{
    var db = new Test("context connection=true");
    UserInfo ui = new UserInfo { FirstName = firstName, LastName = lastName };

    db.UserInfo.InsertOnSubmit(ui);
    db.SubmitChanges();
}

Поскольку код будет выполнятся на стороне SQL сервера, мы должны указать в строке соединения context connection=true.

Чтобы не делать этого мы можем добавить ещё один конструктов для класса Test

public Test() :
      base("context connection=true", mappingSource)
  {
      OnCreated();
  }

После этого снова делаем Deploy нашей библиотеки на SQL Server.

После чего у нас должна появиться хранимая процедура, которая может быть выполнена так же как и обычная SP.

USE [Test]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[RegisterUser]
        @firstName = N'Nikolay',
        @lastName = N'Podkolzin'

SELECT    'Return Value' = @return_value

GO

Развернутая сборка становиться частью SQL базы. т.е при генерации скриптов для базы данных все сборки тоже будет заскриптованы

--

Nikolay Podkolzin

Microsoft Student Partner