Пошаговое руководство. Создание хранимой процедуры в управляемом коде

Статья
08/11/2011

Сохраненные процедуры для баз данных Microsoft SQL Server 2005 и SQL Server 2008 можно записать в управляемом коде с помощью языков .NET Framework, таких как Visual Basic и Visual C#. Хранимые процедуры, написанные в управляемом коде, называются хранимыми процедурами SQL Server со средой CLR или хранимыми процедурами SQL CLR.

Хранимые процедуры SQL можно создать путем добавления элементов Хранимая процедура в проекты базы данных SQL CLR. После развертывания хранимых процедур, созданных в управляемом коде, они вызываются и выполняются как любые другие хранимые процедуры.

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

Создание нового проекта Проект базы данных SQL CLR.
Создание хранимой процедуры в управляемом коде.
Развертывание хранимой процедуры в базе данных SQL Server 2008.
Создание скрипта для проверки хранимой процедуры в базе данных.
Запрос данных в базе данных для подтверждения правильности выполнения хранимой процедуры.

Обязательные компоненты

Для выполнения этого пошагового руководства потребуется следующее.

Подключение к образцу базы данных AdventureWorks2008, выполняемой на сервере SQL Server 2008. Дополнительные сведения см. на странице Образцы продукта Microsoft SQL Server на веб-сайте корпорации Майкрософт.

Создание проекта

Создание нового проекта базы данных SQL CLR

В меню Файл создайте новый проект.
Выберите Проект базы данных SQL CLR, назовите проект SQLCLRStoredProcedure и нажмите кнопку ОК. Дополнительные сведения см. в разделе Практическое руководство. Создание проекта для объектов базы данных, использующих интеграцию SQL Server со средой CLR.

Подключение к базе данных SQL Server 2008

Для выполнения этого руководства требуется подключение к образцу базы данных AdventureWorks2008, выполняемой на сервере SQL Server 2008. Если подключение к образцу базы данных AdventureWorks2008 доступно в окне Обозреватель сервера, оно будет занесено в список в Диалоговое окно "Добавление ссылки на базу данных".

Для подключения к базе данных AdventureWorks:

Подключитесь к образцу базы данных AdventureWorks2008, включенному в SQL Server 2008, используя диалоговое окно Добавить подключение. Дополнительные сведения см. в разделе Добавление/изменение подключения (Microsoft SQL Server).

– или –
Выберите существующее подключение к образцу базы данных AdventureWorks2008 в диалоговом окне Добавление ссылки на базу данных. Дополнительные сведения см. в разделе Диалоговое окно "Добавление ссылки на базу данных".

Настройка проекта базы данных SQL CLR

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

Настройка свойств проекта базы данных SQL CLR

В обозревателе решений выберите проект, для которого требуется настроить свойства.
В меню Проект щелкните Свойства ProjectName, где ProjectName — это имя проекта базы данных SQL CLR.
На вкладке Приложение в поле Сборка укажите имя сборки, построенной из проекта базы данных SQL CLR.
На вкладке Приложение в поле Требуемая версия .NET Framework щелкните версию платформы .NET Framework, поддерживаемую целевой версией SQL Server.

Важно!

SQL Server 2005 и SQL Server 2008 поддерживают только проекты SQL Server, созданные с помощью версии 2.0 платформы .NET Framework.Если вы попробуете развернуть SQL Server проекта SQL Server 2005 или SQL Server 2008, появляется сообщение об ошибке: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database(где имя_сборки — это имя сборки, развертывания).
На вкладке База данных в поле Владелец сборки введите имя пользователя или роли в качестве владельца сборки.

Важно!

Это значение должно представлять собой имя роли, к которой относится текущий пользователь, либо текущий пользователь должен иметь разрешение IMPERSONATE.Если владелец сборки не указан, владельцем назначается текущий пользователь.Этот параметр соответствует аргументу AUTHORIZATION имя_владельца для инструкции SQL Server CREATE ASSEMBLY.Дополнительные сведения см. в разделе Создание сборки (Transact-SQL) на веб-сайте корпорации Майкрософт.
В меню Файл выберите команду Сохранить все.

Создание сохраненной процедуры SQL Server

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

Для создания сохраненной процедуры SQL Server:

В меню Проект выберите команду Добавить новый элемент.
В диалоговом окне Добавление нового элемента выберите Хранимая процедура.
Введите InsertCurrency в качестве Имени для новой сохраненной процедуры.
Нажмите Добавить.

В редакторе кодов замените код на следующий:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    <SqlProcedure()>
    Public Shared Sub InsertCurrency(
        ByVal currencyCode As SqlString, ByVal name As SqlString)

        Using conn As New SqlConnection("context connection=true")

            Dim InsertCurrencyCommand As New SqlCommand()
            Dim currencyCodeParam As New SqlParameter("@CurrencyCode", SqlDbType.NVarChar)
            Dim nameParam As New SqlParameter("@Name", SqlDbType.NVarChar)

            currencyCodeParam.Value = currencyCode
            nameParam.Value = name


            InsertCurrencyCommand.Parameters.Add(currencyCodeParam)
            InsertCurrencyCommand.Parameters.Add(nameParam)

            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" &
                " VALUES(@CurrencyCode, @Name, GetDate())"

            InsertCurrencyCommand.Connection = conn

            conn.Open()
            InsertCurrencyCommand.ExecuteNonQuery()
            conn.Close()
        End Using
    End Sub
End Class

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [SqlProcedure()]
    public static void InsertCurrency_CS(
        SqlString currencyCode, SqlString name)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand InsertCurrencyCommand = new SqlCommand();
            SqlParameter currencyCodeParam = new SqlParameter("@CurrencyCode", SqlDbType.NVarChar);
            SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar);

            currencyCodeParam.Value = currencyCode;
            nameParam.Value = name;

            InsertCurrencyCommand.Parameters.Add(currencyCodeParam);
            InsertCurrencyCommand.Parameters.Add(nameParam);

            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
                " VALUES(@CurrencyCode, @Name, GetDate())";

            InsertCurrencyCommand.Connection = conn;

            conn.Open();
            InsertCurrencyCommand.ExecuteNonQuery();
            conn.Close();
        }
    }
}

Развертывание, выполнение и отладка сохраненной процедуры

После создания новой сохраненной процедуры ее можно откомпилировать, развернуть на сервере SQL и отладить нажатием клавиши F5. Сначала в файл Test.sql, находящийся в папке TestScripts проекта, добавьте код для выполнения и проверки сохраненной процедуры. Дополнительные сведения о создании тестовых скриптов см. в разделе Практическое руководство. Изменение скрипта Test.sql для запуска объектов, использующих интеграцию SQL Server со средой CLR.

Дополнительные сведения об отладке SQL см. разделе Debugging SQL Database Objects.

Для развертывания и запуска сохраненной процедуры InsertCurrency:

В окне Обозреватель решений разверните папку TestScripts и дважды щелкните файл Test.sql.

Примечание

В качестве скрипта отладки по умолчанию можно указать другие скрипты.Дополнительные сведения см. в разделе Практическое руководство. Изменение скрипта Test.sql для запуска объектов, использующих интеграцию SQL Server со средой CLR.

Замените код в файле Test.sql на приведенный ниже.

'VB
EXEC InsertCurrency 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

//C#
EXEC InsertCurrency_CS 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

Нажмите клавишу F5, чтобы скомпилировать, развернуть и выполнить отладку сохраненной процедуры. Дополнительные сведения о развертывании без отладки см. в разделе Практическое руководство. Развертывание элементов проекта базы данных SQL в среде CLR на сервере SQL Server.

Просмотрите результаты, которые показаны в вывода окна и выберите Показать вывод из: База данных вывода.

См. также