Создание хранимых процедур и определяемых пользователем функций с помощью управляемого кода (C#)

Скотт Митчелл

Скачать в формате PDF

Microsoft SQL Server 2005 интегрируется с средой CLR .NET, чтобы разработчики могли создавать объекты базы данных с помощью управляемого кода. В этом руководстве показано, как создать управляемые хранимые процедуры и управляемые пользовательские функции с помощью кода Visual Basic или C#. Мы также видим, как эти выпуски Visual Studio позволяют выполнять отладку таких управляемых объектов базы данных.

Введение

Базы данных, такие как Microsoft SQL Server 2005 используют Transact-SQL (T-SQL) для вставки, изменения и получения данных. Большинство систем баз данных включают конструкции для группировки ряда инструкций SQL, которые затем можно выполнять как единую единицу многократного использования. Примером могут служить хранимые процедуры. Другое — определяемые пользователем функции (ОПРЕДЕЛЯемые пользователем функции), конструкция, которую мы рассмотрим более подробно на шаге 9.

В основном SQL предназначен для работы с наборами данных. Операторы SELECT, UPDATE, и DELETE по сути, применяются ко всем записям в соответствующей таблице и ограничиваются только их WHERE предложениями. Тем не менее существует множество функций языка, предназначенных для работы с одной записью за раз и для управления скалярными данными. CURSOR с возможностью поочерёдного перебора набора записей. Такие функции обработки строк, как LEFT, CHARINDEXи PATINDEX работа с скалярными данными. SQL также включает инструкции потока управления, такие как IF и WHILE.

До Microsoft SQL Server 2005 хранимые процедуры и определяемые пользователем функции можно было определить только как коллекцию инструкций T-SQL. Однако SQL Server 2005 был разработан для обеспечения интеграции со средой CLR, которая является средой выполнения, используемой всеми сборками .NET. Следовательно, хранимые процедуры и пользовательские функции в базе данных SQL Server 2005 могут быть созданы с помощью управляемого кода. То есть можно создать хранимую процедуру или UDF в качестве метода в классе C#. Это позволяет этим хранимым процедурам и определяемым пользователем функциям использовать функциональность в платформе .NET Framework и из ваших собственных пользовательских классов.

В этом руководстве мы рассмотрим, как создавать управляемые хранимые процедуры и определяемые пользователем функции и как интегрировать их в базу данных Northwind. Давайте приступим!

Шаг 1. Перемещение базы данных Northwind из App_Data

Все наши учебники до сих пор использовали файл базы данных Microsoft SQL Server 2005 Express Edition в папке веб-приложения App_Data . Размещение базы данных в App_Data упрощает распределение и выполнение этих учебных пособий, поскольку все файлы находились в одном каталоге и не требовали дополнительных шагов для настройки тестирования.

Однако в этом руководстве давайте переместим базу данных Northwind из App_Data и явно зарегистрируем ее в экземпляре базы данных SQL Server 2005 Express Edition. Хотя мы можем выполнить действия, описанные в этом руководстве с базой данных в App_Data папке, несколько шагов упрощаются путем явной регистрации базы данных в экземпляре базы данных SQL Server 2005 Express Edition.

В загрузке для этого учебника находятся два файла базы данных — NORTHWND.MDF и NORTHWND_log.LDF, которые помещены в папку с именем DataFiles. Если вы следуете инструкциям с собственной реализацией учебников, закройте Visual Studio и переместите файлы NORTHWND.MDF и NORTHWND_log.LDF из папки App_Data веб-сайта в папку за его пределами. После перемещения файлов базы данных в другую папку необходимо зарегистрировать базу данных Northwind в экземпляре базы данных SQL Server 2005 Express Edition. Это можно сделать из SQL Server Management Studio. Если на компьютере установлен выпуск SQL Server 2005, отличный от Express Edition, скорее всего, уже установлен Management Studio. Если на компьютере есть только SQL Server 2005 Express Edition, скачайте и установите Microsoft SQL Server Management Studio.

Запустите SQL Server Management Studio. Как показано на рисунке 1, SQL Server Management Studio начинается с запроса подключения к серверу. Введите localhost\SQLExpress для имени сервера, выберите проверку подлинности Windows в раскрывающемся списке проверки подлинности и нажмите кнопку "Подключить".

Рис. 1. Подключение к соответствующему экземпляру базы данных

После подключения окно обозреватель объектов отобразит сведения о экземпляре базы данных SQL Server 2005 Express Edition, включая ее базы данных, сведения о безопасности, параметры управления и т. д.

Нам нужно подключить базу данных Northwind в папке DataFiles (или где бы вы ни могли переместить ее) к экземпляру базы данных SQL Server 2005 Express Edition. Щелкните правой кнопкой мыши папку "Базы данных" и выберите параметр "Присоединить" в контекстном меню. Откроется диалоговое окно "Присоединение баз данных". Нажмите кнопку "Добавить", выполните детализацию до соответствующего NORTHWND.MDF файла и нажмите кнопку "ОК". На этом этапе экран должен выглядеть примерно так же, как на рис. 2.

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

Нажмите кнопку "ОК", чтобы подключить базу данных. Диалоговое окно "Присоединение баз данных" закроется, и обозреватель объектов теперь должен перечислить только что подключенную базу данных. Скорее всего, база данных Northwind имеет такое имя 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF. Переименуйте базу данных в Northwind, щелкнув правой кнопкой мыши базу данных и выбрав "Переименовать".

Рис. 3. Переименование базы данных в Northwind

Шаг 2. Создание нового решения и проекта SQL Server в Visual Studio

Чтобы создать управляемые хранимые процедуры или функции, определяемые пользователем (UDF), в SQL Server 2005, мы напишем хранимую процедуру и логику UDF в виде кода C# в классе. После написания кода необходимо скомпилировать этот класс в сборку ( .dll файл), зарегистрировать сборку в базе данных SQL Server, а затем создать хранимую процедуру или объект UDF в базе данных, которая указывает на соответствующий метод в сборке. Эти действия можно выполнить вручную. Можно создать код в любом текстовом редакторе, скомпилировать его из командной строки с помощью компилятора C# (csc.exe), зарегистрировать его в базе данных с помощью CREATE ASSEMBLY команды или из Management Studio, а также добавить хранимую процедуру или объект UDF с помощью аналогичных средств. К счастью, версии Professional и Team Systems Visual Studio включают тип проекта SQL Server, который автоматизирует эти задачи. В этом руководстве мы рассмотрим тип проекта SQL Server для создания управляемой хранимой процедуры и UDF.

Начните с открытия Visual Studio. В меню "Файл" выберите "Создать проект", чтобы отобразить диалоговое окно "Новый проект" (см. рис. 4). Перейдите к типу проекта базы данных, а затем из шаблонов, перечисленных справа, выберите проект SQL Server для создания. Я выбрал имя этого проекта ManagedDatabaseConstructs и поместил его в решение с именем Tutorial75.

Рис. 4. Создание нового проекта SQL Server (щелкните, чтобы просмотреть изображение полного размера)

Нажмите кнопку "ОК" в диалоговом окне "Создать проект", чтобы создать решение и проект SQL Server.

Проект SQL Server привязан к определенной базе данных. Следовательно, после создания нового проекта SQL Server мы немедленно просим указать эти сведения. На рисунке 5 показан диалоговое окно "Новая ссылка на базу данных", заполненное для указания базы данных Northwind, зарегистрированной в экземпляре базы данных SQL Server 2005 Express Edition еще на шаге 1.

Рис. 5. Связывание проекта SQL Server с базой данных Northwind

Чтобы отладить управляемые хранимые процедуры и определяемые пользователем функции, которые мы создадим в рамках этого проекта, необходимо включить поддержку отладки SQL/CLR для подключения. Всякий раз при связывании проекта SQL Server с новой базой данных (как и на рис. 5), Visual Studio просит нас включить отладку SQL/CLR в соединении (см. рис. 6). Щелкните Да.

Рис. 6. Включение отладки SQL/CLR

На этом этапе в решение добавлен новый проект SQL Server. Он содержит папку Test Scripts с файлом Test.sql, который используется для отладки управляемых объектов базы данных, созданных в проекте. Мы рассмотрим отладку на шаге 12.

Теперь мы можем добавить в этот проект новые управляемые хранимые процедуры и определяемые пользователем функции, но прежде включим в Решение наше существующее веб-приложение. В меню "Файл" выберите параметр "Добавить" и выберите "Существующий веб-сайт". Перейдите к соответствующей папке веб-сайта и нажмите кнопку "ОК". Как показано на рисунке 7, решение обновится для включения двух проектов: веб-сайта и ManagedDatabaseConstructs проекта SQL Server.

Рис. 7. Теперь Обозреватель решений включает два проекта

Значение NORTHWNDConnectionString в Web.config настоящее время ссылается на NORTHWND.MDF файл в папке App_Data . Так как мы удалили эту базу данных из App_Data и явно зарегистрировали ее в экземпляре базы данных SQL Server 2005 Express Edition, необходимо соответствующим образом обновить значение NORTHWNDConnectionString. Откройте файл Web.config на веб-сайте и измените значение NORTHWNDConnectionString, чтобы строка подключения считывала: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. После этого изменения ваш раздел <connectionStrings> в Web.config должен выглядеть следующим образом:

<connectionStrings>
    <add name="NORTHWNDConnectionString" connectionString=
        "Data Source=localhost\SQLExpress;Initial Catalog=Northwind;
            Integrated Security=True;Pooling=false"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Шаг 3. Создание управляемой хранимой процедуры

Чтобы добавить управляемую хранимую процедуру в базу данных Northwind, сначала необходимо создать хранимую процедуру в качестве метода в проекте SQL Server. В Обозревателе решений щелкните правой кнопкой мыши на имени проекта ManagedDatabaseConstructs, выберите пункт 'Добавить', а затем 'Новый элемент'. Откроется диалоговое окно "Добавление нового элемента", в котором перечислены типы объектов управляемой базы данных, которые можно добавить в проект. Как показано на рисунке 8, это включает хранимые процедуры и определяемые пользователем функции, среди прочего.

Начнем с добавления хранимой процедуры, которая просто возвращает все продукты, которые были сняты с производства. Назовите файл хранимой процедуры GetDiscontinuedProducts.cs.

Рис. 8. Добавление новой хранимой процедуры с именем GetDiscontinuedProducts.cs (щелкните, чтобы просмотреть изображение полного размера)

Будет создан новый файл класса 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 GetDiscontinuedProducts()
    {
        // Put your code here
    }
};

Обратите внимание, что хранимая процедура реализуется как static метод в partial файле класса с именем StoredProcedures. Кроме того, метод GetDiscontinuedProducts декорирован с помощью SqlProcedure attribute, который помечает метод как хранимую процедуру.

Следующий код создает объект SqlCommand и устанавливает для него CommandText запрос SELECT, который возвращает все столбцы из таблицы Products для продуктов, у которых поле Discontinued равно 1. Затем он выполняет команду и отправляет результаты обратно в клиентское приложение. Добавьте этот код в GetDiscontinuedProducts метод.

// Create the command
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = 
      @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
               QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
               ReorderLevel, Discontinued
        FROM Products 
        WHERE Discontinued = 1";
// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);

Все управляемые объекты базы данных имеют доступ к SqlContext объекту , представляющего контекст вызывающего объекта. Этот SqlContext предоставляет доступ к объекту SqlPipe через его свойство Pipe. Этот SqlPipe объект используется для перенаправки сведений между базой данных SQL Server и вызывающим приложением. Как подразумевает его имя, ExecuteAndSend метод выполняет переданный SqlCommand объект и отправляет результаты обратно в клиентское приложение.

Шаг 4. Развертывание управляемой хранимой процедуры

После завершения этого кода мы готовы развернуть его в базе данных Northwind. Развертывание проекта SQL Server компилирует код в сборку, регистрирует сборку в базе данных и создает соответствующие объекты в базе данных, связывая их с соответствующими методами в сборке. Точный набор задач, выполняемых параметром развертывания, более точно определен на шаге 13. Щелкните правой кнопкой мыши по имени проекта ManagedDatabaseConstructs в Обозревателе решений и выберите "Развернуть". Однако развертывание завершается сбоем со следующей ошибкой: неправильный синтаксис рядом с "EXTERNAL". Возможно, следует установить более высокий уровень совместимости для текущей базы данных, чтобы включить эту функцию. См. справку по хранимой процедуре sp_dbcmptlevel.

Это сообщение об ошибке возникает при попытке зарегистрировать сборку в базе данных Northwind. Чтобы зарегистрировать сборку в базе данных SQL Server 2005, уровень совместимости базы данных должен иметь значение 90. По умолчанию новые базы данных SQL Server 2005 имеют уровень совместимости 90. Однако базы данных, созданные с помощью Microsoft SQL Server 2000, имеют уровень совместимости по умолчанию 80. Так как база данных Northwind изначально была базой данных Microsoft SQL Server 2000, уровень совместимости в настоящее время имеет значение 80, поэтому необходимо увеличить до 90, чтобы зарегистрировать управляемые объекты базы данных.

Чтобы обновить уровень совместимости базы данных, откройте окно "Создать запрос" в Management Studio и введите следующее:

exec sp_dbcmptlevel 'Northwind', 90

Щелкните значок "Выполнить" на панели инструментов, чтобы запустить приведенный выше запрос.

Рис. 9. Обновление уровня совместимости базы данных Northwind (щелкните, чтобы просмотреть изображение полного размера)

После обновления уровня совместимости повторно разверните проект SQL Server. На этот раз развертывание должно завершиться без ошибок.

Вернитесь в SQL Server Management Studio, щелкните правой кнопкой мыши базу данных Northwind в обозреватель объектов и нажмите кнопку "Обновить". Затем разверните папку Programmability, а затем откройте папку Сборок. Как показано на рисунке 10, база данных Northwind теперь включает сборку, созданную проектом ManagedDatabaseConstructs .

Рис. 10. Сборка ManagedDatabaseConstructs теперь зарегистрирована в базе данных Northwind

Также разверните папку хранимых процедур. Там вы увидите хранимую процедуру с именем GetDiscontinuedProducts. Эта хранимая процедура, созданная процессом развертывания, указывает на метод GetDiscontinuedProducts в сборке ManagedDatabaseConstructs. При выполнении хранимой процедуры GetDiscontinuedProducts она, в свою очередь, выполняет метод GetDiscontinuedProducts. Так как это управляемая хранимая процедура, ее нельзя изменить с помощью Management Studio (поэтому значок блокировки рядом с именем хранимой процедуры).

Рис. 11. Хранимая GetDiscontinuedProducts процедура указана в папке хранимых процедур

Перед вызовом управляемой хранимой процедуры необходимо преодолеть еще один барьер: база данных настроена для предотвращения выполнения управляемого кода. Проверьте это, открыв новое окно запроса и выполнив хранимую процедуру GetDiscontinuedProducts . Вы получите следующее сообщение об ошибке: выполнение пользовательского кода в платформа .NET Framework отключено. Включите опцию конфигурации с поддержкой clr.

Чтобы проверить сведения о конфигурации базы данных Northwind, введите и выполните команду exec sp_configure в окне запроса. Это показывает, что настройка 'clr enabled' в настоящее время установлена на 0.

Рис. 12. Параметр clr включен в настоящее время имеет значение 0 (щелкните, чтобы просмотреть изображение полного размера)

Обратите внимание, что каждый параметр конфигурации на рис. 12 содержит четыре значения: минимальные и максимальные значения, а также значения конфигурации и запуска. Чтобы обновить значение конфигурации параметра clr enabled, выполните следующую команду:

exec sp_configure 'clr enabled', 1

Если вы повторно запустите exec sp_configure, вы увидите, что инструкция выше обновила значение конфигурации параметра clr до 1, но значение выполнения остаётся 0. Чтобы изменить эту конфигурацию, необходимо выполнить RECONFIGURE команду, которая установит значение запуска для текущего значения конфигурации. Просто введите RECONFIGURE в окно запроса и щелкните значок "Выполнить" на панели инструментов. Если вы запускаете exec sp_configure сейчас, вы должны увидеть значение 1 для параметров конфигурации и выполнения, связанных с включенной настройкой clr.

После завершения конфигурации с поддержкой clr мы готовы выполнить управляемую хранимую GetDiscontinuedProducts процедуру. В окне запроса введите и выполните команду execGetDiscontinuedProducts. Вызов хранимой процедуры приводит к выполнению соответствующего управляемого кода в методе GetDiscontinuedProducts . Этот код выдает SELECT запрос для возврата всех продуктов, которые сняты с производства, и возвращает эти данные приложению, которое их запрашивает - в данном случае, это SQL Server Management Studio. Management Studio получает эти результаты и отображает их в окне результатов.

Рис. 13. Хранимая GetDiscontinuedProducts процедура возвращает все прекращенные продукты (щелкните, чтобы просмотреть изображение полного размера)

Шаг 5. Создание управляемых хранимых процедур, которые принимают входные параметры

Многие созданные в рамках этих учебников запросы и хранимые процедуры используют параметры. Например, в руководстве по созданию хранимых процедур для typed DataSet s TableAdapters мы создали хранимую процедуру с именем GetProductsByCategoryID , которая приняла входной параметр с именем @CategoryID. Затем хранимая процедура вернула все продукты, поле которых CategoryID соответствовало значению предоставленного @CategoryID параметра.

Чтобы создать управляемую хранимую процедуру, принимающую входные параметры, просто укажите эти параметры в определении метода. Чтобы проиллюстрировать это, давайте добавим в проект ManagedDatabaseConstructsеще одну управляемую хранимую процедуруGetProductsWithPriceLessThan. Эта управляемая хранимая процедура принимает входной параметр, указывающий цену, и возвращает все продукты, поле которых UnitPrice меньше значения параметра.

Чтобы добавить в проект новую хранимую процедуру, щелкните правой кнопкой мыши ManagedDatabaseConstructs имя проекта и выберите новую хранимую процедуру. Назовите файл GetProductsWithPriceLessThan.cs. Как мы видели на шаге 3, это создаст новый файл класса C# с методом под именем GetProductsWithPriceLessThan, который будет размещен в классе partialStoredProcedures.

GetProductsWithPriceLessThan Обновите определение метода, чтобы он принял входной SqlMoney параметр с именем price и написать код для выполнения и возврата результатов запроса:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductsWithPriceLessThan(SqlMoney price)
{
    // Create the command
    SqlCommand myCommand = new SqlCommand();
    myCommand.CommandText =
          @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
                   QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
                   ReorderLevel, Discontinued
            FROM Products
            WHERE UnitPrice < @MaxPrice";
    myCommand.Parameters.AddWithValue("@MaxPrice", price);
    // Execute the command and send back the results
    SqlContext.Pipe.ExecuteAndSend(myCommand);
}

Определение GetProductsWithPriceLessThan и код метода тесно похожи на определение и код метода, созданного GetDiscontinuedProducts на шаге 3. Единственное различие заключается в том, что метод GetProductsWithPriceLessThan принимает в качестве входного параметра (price), в запрос SqlCommand включен параметр (@MaxPrice), а параметр добавляется в коллекцию SqlCommands Parameters и получает значение переменной price.

После добавления этого кода повторно разверните проект SQL Server. Затем вернитесь в SQL Server Management Studio и обновите папку хранимых процедур. Вы увидите новую запись. GetProductsWithPriceLessThan В окне запроса введите и выполните команду exec GetProductsWithPriceLessThan 25, которая будет перечислять все продукты менее $ 25, как показано на рисунке 14.

Рис. 14: Продукты стоимостью менее $25 отображаются (Нажмите, чтобы просмотреть изображение в полном размере)

Шаг 6. Вызов управляемой хранимой процедуры из уровня доступа к данным

На этом этапе мы добавили управляемые хранимые процедуры GetDiscontinuedProducts и GetProductsWithPriceLessThan в проект ManagedDatabaseConstructs и зарегистрировали их в базе данных SQL Server Northwind. Мы также вызвали эти управляемые хранимые процедуры из SQL Server Management Studio (см. рис. 13 и 14). Для использования этих управляемых хранимых процедур приложением ASP.NET необходимо добавить их в уровни доступа к данным и бизнес-логике в архитектуре. На этом шаге мы добавим два новых метода ProductsTableAdapter в NorthwindWithSprocs типизированном наборе данных, который изначально был создан в руководстве по созданию новых хранимых процедур для типизированного набора данных таблицыAdapters . На шаге 7 мы добавим соответствующие методы в BLL.

Откройте типизированный NorthwindWithSprocs набор данных в Visual Studio и начните с добавления нового метода в ProductsTableAdapter именованный GetDiscontinuedProductsнабор данных. Чтобы добавить новый метод в TableAdapter, щелкните правой кнопкой мыши имя TableAdapter в конструкторе и выберите параметр "Добавить запрос" в контекстном меню.

Добавление нового метода запускает мастер настройки запросов TableAdapter, который мы использовали много раз в прошлых руководствах. Первый шаг просит указать, как TableAdapter должен получить доступ к базе данных: с помощью нерегламентированной инструкции SQL или с помощью новой или существующей хранимой процедуры. Так как мы уже создали и зарегистрировали управляемую GetDiscontinuedProducts хранимую процедуру в базе данных, выберите параметр "Использовать существующую хранимую процедуру" и нажмите кнопку "Далее".

Рис. 15. Выберите параметр "Использовать существующую хранимую процедуру" (щелкните, чтобы просмотреть изображение полного размера)

На следующем экране будет предложено выбрать хранимую процедуру, которую вызовет метод. Выберите управляемую GetDiscontinuedProducts хранимую процедуру из раскрывающегося списка и нажмите кнопку "Далее".

Рис. 16. Выберите управляемую GetDiscontinuedProducts хранимую процедуру (щелкните, чтобы просмотреть изображение полного размера)

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

Рис. 17. Выберите параметр табличных данных (щелкните, чтобы просмотреть изображение полного размера)

На последнем экране мастера можно указать используемые шаблоны доступа к данным и имена результирующего метода. Оставьте оба флажка установлены и назовите методы FillByDiscontinued и GetDiscontinuedProducts. Чтобы завершить работу мастера, нажмите Готово.

Рис. 18. Назовите методы FillByDiscontinued и GetDiscontinuedProducts (щелкните, чтобы просмотреть изображение полного размера)

Повторите эти действия, чтобы создать методы с именем FillByPriceLessThan и GetProductsWithPriceLessThan в ProductsTableAdapter управляемой хранимой процедуре GetProductsWithPriceLessThan .

На рисунке 19 показан снимок экрана конструктора наборов данных после добавления методов в ProductsTableAdapter для GetDiscontinuedProducts и GetProductsWithPriceLessThan управляемых хранимых процедур.

Рис. 19: Данный шаг включает новые методы, добавленные на этом этапе (щелкните, чтобы просмотреть изображение полного размера)

Шаг 7. Добавление соответствующих методов на уровень бизнес-логики

Теперь, когда мы обновили уровень доступа к данным, чтобы включить методы для вызова управляемых хранимых процедур, добавленных на шаге 4 и 5, необходимо добавить соответствующие методы в уровень бизнес-логики. Добавьте в класс следующие два метода ProductsBLLWithSprocs :

[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetDiscontinuedProducts()
{
    return Adapter.GetDiscontinuedProducts();
}
[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable 
    GetProductsWithPriceLessThan(decimal priceLessThan)
{
    return Adapter.GetProductsWithPriceLessThan(priceLessThan);
}

Оба метода просто вызывают соответствующий метод DAL и возвращают ProductsDataTable экземпляр. Разметка DataObjectMethodAttribute над каждым методом приводит к тому, что эти методы будут включены в раскрывающийся список на вкладке SELECT мастера настройки источника данных ObjectDataSource.

Шаг 8. Вызов управляемых хранимых процедур из слоя презентации

С уровнями бизнес-логики и доступа к данным, расширенными, чтобы включить поддержку вызова GetDiscontinuedProducts и GetProductsWithPriceLessThan управляемых хранимых процедур, теперь можно отобразить результаты этих хранимых процедур на странице ASP.NET.

ManagedFunctionsAndSprocs.aspx Откройте страницу в AdvancedDAL папке и из панели элементов перетащите GridView в конструктор. Установите для свойства ID элемента GridView значение DiscontinuedProducts, затем из его смарт-тега выполните привязку к новому объекту ObjectDataSource с именем DiscontinuedProductsDataSource. Настройте ObjectDataSource для извлечения данных из класса ProductsBLLWithSprocs методом GetDiscontinuedProducts.

Рис. 20. Настройка ObjectDataSource для использования ProductsBLLWithSprocs класса (щелкните, чтобы просмотреть изображение полного размера)

Рис. 21. Выберите GetDiscontinuedProducts метод из раскрывающегося списка на вкладке SELECT (щелкните, чтобы просмотреть изображение полного размера)

Так как эта сетка будет использоваться только для отображения сведений о продукте, задайте раскрывающийся список на вкладках UPDATE, INSERT и DELETE (Нет) и нажмите кнопку "Готово".

После завершения работы мастера Visual Studio автоматически добавит поле BoundField или CheckBoxField для каждого поля данных в ProductsDataTable. Потратьте немного времени, чтобы удалить все эти поля, кроме ProductName и Discontinued, после чего декларативная разметка GridView и ObjectDataSource должна выглядеть примерно следующим образом:

<asp:GridView ID="DiscontinuedProducts" runat="server" 
    AutoGenerateColumns="False" DataKeyNames="ProductID" 
    DataSourceID="DiscontinuedProductsDataSource">
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
            SortExpression="ProductName" />
        <asp:CheckBoxField DataField="Discontinued" 
            HeaderText="Discontinued" 
            SortExpression="Discontinued" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="DiscontinuedProductsDataSource" runat="server" 
    OldValuesParameterFormatString="original_{0}"
    SelectMethod="GetDiscontinuedProducts" TypeName="ProductsBLLWithSprocs">
</asp:ObjectDataSource>

Просмотрите эту страницу через браузер. При посещении страницы, ObjectDataSource вызывает метод GetDiscontinuedProducts класса ProductsBLLWithSprocs. Как мы видели на шаге 7, этот метод вызывает метод ProductsDataTableклассаGetDiscontinuedProducts DAL, который вызывает хранимую процедуру GetDiscontinuedProducts. Эта хранимая процедура является управляемой хранимой процедурой и выполняет код, созданный на шаге 3, возвращая прекращенные продукты.

Результаты, возвращаемые управляемой хранимой процедурой, упаковываются в ProductsDataTable DAL, а затем возвращаются в BLL, который затем возвращает их на уровень презентации, где они привязаны к GridView и отображаются. Как ожидается, сетка перечисляет те продукты, которые были прекращены.

Прекращённые товары перечислены

Рис. 22. Список прекращенных продуктов (щелкните, чтобы просмотреть изображение полного размера)

Для дальнейшей практики добавьте TextBox и другой GridView на страницу. В этом GridView отображаются продукты, количество которых меньше, чем введенное в TextBox, вызовом метода GetProductsWithPriceLessThan класса ProductsBLLWithSprocs.

Шаг 9. Создание и вызов пользовательских функций T-SQL

Функции, определяемые пользователем (UDF), являются объектами базы данных, которые тесно повторяют семантику функций в языках программирования. Как и функция в C#, UDF (определяемые пользователем функции) могут включать переменное число входных параметров и возвращать значение определенного типа. UDF может возвращать скалярные данные — строку, целое число и т. д. — или табличные данные. Рассмотрим оба типа определяемых пользователем функций, начиная с UDF, который возвращает скалярный тип данных.

Следующий UDF вычисляет предполагаемое значение инвентаризации для определенного продукта. Это делается путем приема трех входных параметров — UnitPriceUnitsInStockи Discontinued значений для конкретного продукта — и возвращает значение типаmoney. Он вычисляет предполагаемую стоимость запасов путем умножения UnitPrice на UnitsInStock. Для прекращенных элементов это значение сокращается.

CREATE FUNCTION udf_ComputeInventoryValue
(
    @UnitPrice money,
    @UnitsInStock smallint,
    @Discontinued bit
)
RETURNS money
AS
BEGIN
    DECLARE @Value decimal
    SET @Value = ISNULL(@UnitPrice, 0) * ISNULL(@UnitsInStock, 0)
    IF @Discontinued = 1
        SET @Value = @Value * 0.5
    
    RETURN @Value
END

После добавления UDF в базу данных его можно найти в Management Studio, развернув папку Programmability, затем папку Функции, а затем Скалярные функции. Его можно использовать в запросе SELECT следующим образом:

SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue
    (UnitPrice, UnitsInStock, Discontinued) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

Я добавил udf_ComputeInventoryValue UDF в базу данных Northwind. На рисунке 23 показаны результаты вышеуказанного SELECT запроса при просмотре через Management Studio. Кроме того, обратите внимание, что UDF указан в папке Функции скалярных значений в Обозревателе объектов.

Рис. 23. Список значений инвентаризации каждого продукта (щелкните, чтобы просмотреть изображение полного размера)

Определяемые пользователем функции также могут возвращать табличные данные. Например, мы можем создать UDF, который возвращает продукты, принадлежащие определенной категории:

CREATE FUNCTION dbo.udf_GetProductsByCategoryID
(    
    @CategoryID int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ProductID, ProductName, SupplierID, CategoryID, 
           QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
           ReorderLevel, Discontinued
    FROM Products
    WHERE CategoryID = @CategoryID
)

UDF udf_GetProductsByCategoryID принимает входной @CategoryID параметр и возвращает результаты указанного SELECT запроса. После создания на этот UDF можно ссылаться в условии FROM (или JOIN) запроса SELECT. В следующем примере возвращаются значения ProductID, ProductName и CategoryID для каждого из напитков.

SELECT ProductID, ProductName, CategoryID
FROM dbo.udf_GetProductsByCategoryID(1)

Я добавил UDF в udf_GetProductsByCategoryID базу данных Northwind; На рисунке 24 показаны выходные данные приведенного выше SELECT запроса при просмотре с помощью Management Studio. Определяемые пользователем функции, возвращающие табличные данные, можно найти в папке таблично-значимых функций в обозревателе объектов.

Идентификаторы ProductID, ProductName и CategoryID перечислены для каждого напитка

Рис. 24: ProductID, ProductName и CategoryID перечислены для каждого напитка (щелкните, чтобы просмотреть изображение в полном размере)

Шаг 10. Создание управляемой UDF

Созданные в приведенных выше примерах udf_ComputeInventoryValue и udf_GetProductsByCategoryID - это функции определения пользователем (UDF) T-SQL. SQL Server 2005 также поддерживает управляемые UDF, которые можно добавить в ManagedDatabaseConstructs проект так же, как и менеджируемые хранимые процедуры из шагов 3 и 5. Для этого шага давайте реализуем udf_ComputeInventoryValue UDF в управляемом коде.

Чтобы добавить управляемый UDF в ManagedDatabaseConstructs проект, щелкните правой кнопкой мыши имя проекта в Обозреватель решений и выберите команду "Добавить новый элемент". Выберите определяемый пользователем шаблон в диалоговом окне "Добавление нового элемента" и назовите новый UDF-файл udf_ComputeInventoryValue_Managed.cs.

Рис. 25. Добавление нового управляемого UDF в ManagedDatabaseConstructs проект (щелкните, чтобы просмотреть изображение полного размера)

Шаблон функции, определяемой пользователем, создает класс partial с именем UserDefinedFunctions, и методом, имя которого совпадает с именем файла класса (в данном случае udf_ComputeInventoryValue_Managed). Этот метод аннотирован с использованием атрибута SqlFunction, который помечает метод как управляемый UDF.

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]
    public static SqlString udf_ComputeInventoryValue_Managed()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};

В настоящее время метод udf_ComputeInventoryValue возвращает объект SqlString и не принимает входные параметры. Необходимо обновить определение метода, чтобы он принял три входных параметра — UnitPrice, UnitsInStock и Discontinued — и возвращает объект SqlMoney. Логика вычисления значения инвентаризации идентична этой логике в UDF T-SQL udf_ComputeInventoryValue .

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlMoney udf_ComputeInventoryValue_Managed
    (SqlMoney UnitPrice, SqlInt16 UnitsInStock, SqlBoolean Discontinued)
{
    SqlMoney inventoryValue = 0;
    if (!UnitPrice.IsNull && !UnitsInStock.IsNull)
    {
        inventoryValue = UnitPrice * UnitsInStock;
        if (Discontinued == true)
            inventoryValue = inventoryValue * new SqlMoney(0.5);
    }
    return inventoryValue;
}

Обратите внимание, что входные параметры метода UDF имеют соответствующие типы SQL: SqlMoney для поля, UnitPrice для SqlInt16UnitsInStockи SqlBoolean для Discontinued. Эти типы данных отражают типы, определенные в Products таблице: UnitPrice столбец имеет тип money, UnitsInStock столбец типа smallintи Discontinued столбец типа bit.

Код начинается с создания экземпляра SqlMoney с именем inventoryValue , которому присваивается значение 0. Таблица Products позволяет использовать значения базы данных NULL в UnitsInPrice столбцах и UnitsInStock столбцах. Поэтому сначала необходимо проверить, содержат ли эти значения NULL с помощью объекта SqlMoney и его свойства IsNull. Если и UnitPrice, и UnitsInStock содержат значения, отличные от NULL, мы вычисляем inventoryValue как произведение их значений. Затем, если Discontinued истинно, то мы уменьшаем значение вдвое.

Шаг 11. Развертывание управляемой UDF

Теперь, когда создан управляемый UDF, мы готовы развернуть его в базе данных Northwind. Как мы видели на шаге 4, управляемые объекты в проекте SQL Server развертываются, щелкнув правой кнопкой мыши имя проекта в Обозреватель решений и выбрав параметр "Развернуть" в контекстном меню.

После развертывания проекта вернитесь в SQL Server Management Studio и обновите папку Scalar-valued Functions. Теперь вы увидите две записи:

• dbo.udf_ComputeInventoryValue — UDF T-SQL, созданный на шаге 9, и
• dbo.udf ComputeInventoryValue_Managed — управляемый UDF, созданный на шаге 10 и только что развернутый.

Чтобы протестировать этот управляемый UDF, выполните следующий запрос из Среды Management Studio:

SELECT ProductID, ProductName, 
       dbo.udf_ComputeInventoryValue_Managed(
                 UnitPrice, 
                 UnitsInStock, 
                 Discontinued
              ) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

Эта команда использует управляемый udf ComputeInventoryValue_Managed UDF вместо UDF T-SQL udf_ComputeInventoryValue , но выходные данные совпадают. Вернитесь на рис. 23, чтобы просмотреть снимок экрана выходных данных UDF.

Шаг 12. Отладка объектов управляемой базы данных

В руководстве по отладке хранимых процедур мы обсудили три варианта отладки SQL Server с помощью Visual Studio: Прямая отладка базы данных, отладка приложений и отладка из проекта SQL Server. Управляемые объекты базы данных нельзя отлаживать с помощью прямой отладки базы данных, но их можно отлаживать из клиентского приложения и непосредственно из проекта SQL Server. Однако для отладки база данных SQL Server 2005 должна разрешить отладку SQL/CLR. Помните, что когда мы впервые создали ManagedDatabaseConstructs проект Visual Studio, спросили, хотите ли мы включить отладку SQL/CLR (см. рис. 6 на шаге 2). Этот параметр можно изменить, щелкнув правой кнопкой мыши базу данных в окне обозревателя серверов.

Рис. 26. Убедитесь, что база данных разрешает отладку SQL/CLR

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

Рис. 27. Установка точки останова в методе GetProductsWithPriceLessThan (щелкните, чтобы просмотреть изображение полного размера)

Сначала рассмотрим отладку объектов управляемой базы данных из проекта SQL Server. Так как наше решение включает два проекта — ManagedDatabaseConstructs проект SQL Server вместе с нашим веб-сайтом— для отладки из проекта SQL Server необходимо указать Visual Studio запустить ManagedDatabaseConstructs проект SQL Server при запуске отладки. Щелкните правой кнопкой мыши на проекте ManagedDatabaseConstructs в Обозревателе решений и выберите параметр "Установить как стартовый проект" в контекстном меню.

ManagedDatabaseConstructs Когда проект запускается из отладчика, он выполняет инструкции SQL в Test.sql файле, который находится в папкеTest Scripts. Например, чтобы проверить GetProductsWithPriceLessThan управляемую хранимую процедуру, замените существующее Test.sql содержимое файла следующим оператором, который вызывает GetProductsWithPriceLessThan управляемую хранимую процедуру, передавающую @CategoryID значение 14.95:

exec GetProductsWithPriceLessThan 14.95

После ввода приведенного выше скрипта Test.sqlначните отладку, перейдя в меню отладки и выбрав "Начать отладку" или нажав клавишу F5 или зеленый значок воспроизведения на панели инструментов. При этом будут создаваться проекты в решении, развертывать управляемые объекты базы данных в базе данных Northwind, а затем выполнять Test.sql скрипт. На этом этапе точка останова будет достигнута, и мы можем выполнить шаги по методу GetProductsWithPriceLessThan , проверить значения входных параметров и т. д.

Рис. 28. Точка останова в методе GetProductsWithPriceLessThan была достигнута (щелкните, чтобы просмотреть изображение полного размера)

Чтобы объект базы данных SQL был отлажен с помощью клиентского приложения, необходимо настроить базу данных для поддержки отладки приложений. Щелкните правой кнопкой мыши базу данных в обозревателе серверов и убедитесь, что установлен флажок отладки приложений. Кроме того, необходимо настроить приложение ASP.NET для интеграции с отладчиком SQL и отключить пул подключений. Эти шаги подробно описаны на шаге 2 руководства по отладке хранимых процедур .

После настройки приложения и базы данных ASP.NET задайте веб-сайт ASP.NET в качестве запускаемого проекта и запустите отладку. Если вы перейдете на страницу, которая вызывает один из управляемых объектов с точкой останова, приложение приостановит выполнение и управление будет передано отладчику, где можно выполнить пошаговую отладку кода, как показано на рис. 28.

Шаг 13. Компиляция и развертывание объектов управляемой базы данных вручную

Проекты SQL Server упрощают создание, компиляцию и развертывание объектов управляемой базы данных. К сожалению, проекты SQL Server доступны только в выпусках Professional и Team Systems Visual Studio. Если вы используете Visual Web Developer или выпуск Standard Visual Studio и хотите использовать управляемые объекты базы данных, вам потребуется вручную создать и развернуть их. Это включает четыре шага.

1. Создайте файл, содержащий исходный код для объекта управляемой базы данных,
2. Компиляция объекта в сборку,
3. Зарегистрируйте сборку в базе данных SQL Server 2005 и
4. Создайте объект базы данных в SQL Server, указывающий на соответствующий метод в сборке.

Чтобы проиллюстрировать эти задачи, давайте создадим новую управляемую хранимую процедуру, которая возвращает эти продукты UnitPrice , которые больше указанного значения. Создайте новый файл на компьютере с именем GetProductsWithPriceGreaterThan.cs и введите следующий код в файл (для этого можно использовать Visual Studio, Блокнот или любой текстовый редактор):

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 GetProductsWithPriceGreaterThan(SqlMoney price)
    {
        // Create the command
        SqlCommand myCommand = new SqlCommand();
        myCommand.CommandText =
            @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
                     QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
                     ReorderLevel, Discontinued
              FROM Products
              WHERE UnitPrice > @MinPrice";
        myCommand.Parameters.AddWithValue("@MinPrice", price);
        // Execute the command and send back the results
        SqlContext.Pipe.ExecuteAndSend(myCommand);
    }
};

Этот код почти идентичен методу, созданному GetProductsWithPriceLessThan на шаге 5. Единственными отличиями являются имена методов, WHERE предложение и имя параметра, используемое в запросе. Вернувшись к методу GetProductsWithPriceLessThan, в условии WHERE читается: WHERE UnitPrice < @MaxPrice. Здесь, в GetProductsWithPriceGreaterThan, мы используем: WHERE UnitPrice > @MinPrice

Теперь необходимо скомпилировать этот класс в сборку. В командной строке перейдите в каталог, в котором сохранен GetProductsWithPriceGreaterThan.cs файл, и используйте компилятор C# (csc.exe) для компиляции файла класса в сборку:

csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs

Если папка csc.exe не находится в системе PATH, вам необходимо будет полностью указать ее путь, %WINDOWS%\Microsoft.NET\Framework\version\, например, так:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs

Рис. 29. Компиляция GetProductsWithPriceGreaterThan.cs в сборку (щелкните, чтобы просмотреть изображение полного размера)

Флаг /t указывает, что файл класса C# должен быть скомпилирован в библиотеку DLL (а не исполняемый файл). Флаг /out указывает имя результирующей сборки.

С помощью кода, скомпилированного в сборку, мы готовы зарегистрировать сборку в базе данных SQL Server 2005. Это можно выполнить с помощью T-SQL, с помощью команды CREATE ASSEMBLY или с помощью средства SQL Server Management Studio. Давайте сосредоточимся на использовании Management Studio.

В Management Studio разверните папку Programmability в базе данных Northwind. Одна из её вложенных папок — Assemblies. Чтобы вручную добавить новую сборку в базу данных, щелкните правой кнопкой мыши папку "Сборки" и выберите "Создать сборку" в контекстном меню. Откроется диалоговое окно "Новая сборка" (см. рис. 30). Нажмите кнопку "Обзор", выберите только что скомпилированную сборку ManuallyCreatedDBObjects.dll, затем нажмите "ОК", чтобы добавить сборку в базу данных. Сборка ManuallyCreatedDBObjects.dll не должна отображаться в Обозревателе объектов.

Рис. 30. Добавление сборки в ManuallyCreatedDBObjects.dll базу данных (щелкните, чтобы просмотреть изображение полного размера)

Рис. 31: ManuallyCreatedDBObjects.dll в обозревателе объектов

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

CREATE PROCEDURE [dbo].[GetProductsWithPriceGreaterThan] 
( 
    @price money 
) 
WITH EXECUTE AS CALLER 
AS 
EXTERNAL NAME [ManuallyCreatedDBObjects].[StoredProcedures].[GetProductsWithPriceGreaterThan] 
GO

При этом создается новая хранимая процедура в базе данных Northwind с именем GetProductsWithPriceGreaterThan и связывает ее с управляемым методом GetProductsWithPriceGreaterThan (который находится в классе StoredProcedures, который находится в сборке ManuallyCreatedDBObjects).

После выполнения приведенного выше скрипта обновите папку хранимых процедур в обозреватель объектов. Вы увидите новую запись хранимой процедуры , GetProductsWithPriceGreaterThan которая имеет значок блокировки рядом с ним. Чтобы проверить эту хранимую процедуру, введите и выполните следующий скрипт в окне запроса:

exec GetProductsWithPriceGreaterThan 24.95

Как показано на рисунке 32, приведенная выше команда отображает сведения о тех продуктах, у которых UnitPrice больше чем $24,95.

Рис. 32. ManuallyCreatedDBObjects.dll перечислен в обозревателе объектов (щелкните, чтобы просмотреть изображение полного размера)

Итоги

Microsoft SQL Server 2005 обеспечивает интеграцию со средой CLR, которая позволяет создавать объекты базы данных с помощью управляемого кода. Ранее эти объекты базы данных можно создавать только с помощью T-SQL, но теперь мы можем создать эти объекты с помощью языков программирования .NET, таких как C#. В этом руководстве мы создали две управляемые хранимые процедуры и управляемую определяемую пользователем функцию.

Тип проекта SQL Server Visual Studio упрощает создание, компиляцию и развертывание объектов управляемой базы данных. Кроме того, она предлагает многофункциональную поддержку отладки. Однако типы проектов SQL Server доступны только в выпусках Professional и Team Systems Visual Studio. Для тех, кто использует Visual Web Developer или выпуск Standard Visual Studio, действия по созданию, компиляции и развертыванию должны выполняться вручную, как показано на шаге 13.

Счастливое программирование!

Дополнительные материалы

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

• Преимущества и недостатки определяемых пользователем функций
• Создание объектов SQL Server 2005 в управляемом коде
• Как создать и запустить хранимую процедуру SQL Server CLR
• Как создать и запустить определяемую пользователем функцию CLR SQL Server
• Практическое руководство. Изменение скрипта Test.sql для запуска объектов SQL
• Введение в определяемые пользователем функции
• Управляемый код и SQL Server 2005 (видео)
• Справочник по Transact-SQL
• Пошаговое руководство. Создание хранимой процедуры в управляемом коде

Об авторе

Скотт Митчелл, автор семи книг ASP/ASP.NET и основатель 4GuysFromRolla.com, работает с технологиями Microsoft Web с 1998 года. Скотт работает независимым консультантом, тренером и писателем. Его последняя книга Sams обучает ASP.NET 2.0 за 24 часа. С ним можно связаться по адресу mitchell@4GuysFromRolla.com.

Особое спасибо кому

Эта серия учебников была проверена многими полезными рецензентами. Ведущим рецензентом этого руководства был Сёрен Джейкоб Лоритсен. Помимо просмотра этой статьи, S ren также создал проект Visual C# Express Edition, включенный в эту статью, для компиляции объектов управляемой базы данных вручную. Хотите просмотреть мои предстоящие статьи MSDN? Если да, напишите мне на mitchell@4GuysFromRolla.com.