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

Статья
06/25/2023

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

Введение

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

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

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

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

Примечание

Управляемые объекты базы данных предоставляют некоторые преимущества по сравнению с их аналогами SQL. Богатство и знакомство с языком, а также возможность повторного использования существующего кода и логики являются main преимуществами. Но управляемые объекты базы данных, скорее всего, будут менее эффективными при работе с наборами данных, которые не включают в себя много процедурной логики. Для более подробного обсуждения преимуществ использования управляемого кода по сравнению с T-SQL проверка из раздела Преимущества использования управляемого кода для создания объектов базы данных.

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

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

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

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

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

Снимок экрана: окно

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

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

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

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

Примечание

При подключении к экземпляру SQL Server 2005, экспресс-выпуск через Management Studio диалоговое окно Присоединение баз данных не позволяет детализировать каталоги профилей пользователей, например Мои документы. Поэтому поместите NORTHWND.MDF файлы и NORTHWND_log.LDF в каталог профилей, отличный от пользователя.

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

Переименование базы данных в Northwind

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

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

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

Примечание

Если вы используете Visual Web Developer или Стандартный выпуск Visual Studio, вам придется использовать ручной подход. На шаге 13 приведены подробные инструкции по выполнению этих действий вручную. Рекомендуется ознакомиться с шагами 2–12 перед чтением шага 13, так как эти действия содержат важные SQL Server инструкции по настройке, которые необходимо применять независимо от используемой версии Visual Studio.

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

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

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

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

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

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

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

Включение отладки SQL/CLR

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

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

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

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

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

Значение NORTHWNDConnectionString в в Web.config данный момент ссылается на NORTHWND.MDF файл в папке App_Data . Так как мы удалили эту базу данных из App_Data и явно зарегистрировали ее в экземпляре NORTHWNDConnectionString базы данных SQL Server 2005, экспресс-выпуск, необходимо соответствующим образом обновить значение. Откройте файл на 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>

Примечание

Как обсуждалось в предыдущем руководстве, при отладке объекта SQL Server из клиентского приложения, например веб-сайта ASP.NET, необходимо отключить пул подключений. Приведенный выше строка подключения отключает пул подключений ( Pooling=false ). Если вы не планируете отладку управляемых хранимых процедур и определяемых пользователем функций с веб-сайта ASP.NET, включите пул подключений.

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

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

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

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

При этом будет создан новый файл класса Visual Basic со следующим содержимым:

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  GetDiscontinuedProducts ()
        ' Add your code here
    End Sub
End Class

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

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

' Create the command
Dim myCommand As 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 клиентское приложение.

Примечание

Управляемые объекты базы данных лучше всего подходят для хранимых процедур и определяемых пользователем функций, которые используют процедурную логику, а не логику на основе наборов. Процедурная логика включает в себя работу с наборами данных построчно или со скалярными данными. Однако GetDiscontinuedProducts только что созданный метод не включает в себя процедурную логику. Поэтому в идеале она будет реализована в виде хранимой процедуры T-SQL. Он реализуется как управляемая хранимая процедура для демонстрации шагов, необходимых для создания и развертывания управляемых хранимых процедур.

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

После завершения этого кода мы готовы развернуть его в базе данных Northwind. Развертывание SQL Server Project компилирует код в сборку, регистрирует сборку в базе данных и создает соответствующие объекты в базе данных, связывая их с соответствующими методами в сборке. Точный набор задач, выполняемых параметром Deploy, более точно указан на шаге 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 .

Сборка ManagedDatabaseConstructs зарегистрирована в базе данных Northwind

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

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

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

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

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

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

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

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

exec sp_configure 'clr enabled', 1

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

После завершения настройки 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.vb. Как мы видели на шаге 3, будет создан новый файл класса Visual Basic с методом с именем GetProductsWithPriceLessThan , размещенным Partial в классе StoredProcedures.

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

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub GetProductsWithPriceLessThan(ByVal price As SqlMoney)
    'Create the command
    Dim myCommand As 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)
End Sub

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

После добавления этого кода повторно разверните SQL Server Project. Затем вернитесь в SQL Server Management Studio и обновите папку Хранимых процедур. Должна появиться новая запись GetProductsWithPriceLessThan. В окне запроса введите и выполните команду exec GetProductsWithPriceLessThan 25, которая отобразит список всех продуктов менее 25 долл. США, как показано на рисунке 14.

Рис. 14. Отображаются продукты под 25 долл. США (щелкните для просмотра полноразмерного изображения)

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

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

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

Примечание

Так как мы переместили базу данных Northwind из App_Data папки в экземпляр базы данных SQL Server 2005, экспресс-выпуск, необходимо, чтобы соответствующие строка подключения в Web.config были обновлены в соответствии с этим изменением. На шаге 2 мы обсуждали обновление NORTHWNDConnectionString значения в Web.config. Если вы забыли выполнить это обновление, появится сообщение об ошибке Не удалось добавить запрос. Не удалось найти соединение NORTHWNDConnectionString для объекта Web.config в диалоговом окне при попытке добавить новый метод в TableAdapter. Чтобы устранить эту ошибку, нажмите кнопку ОК, а затем перейдите к Web.config и обновите значение, NORTHWNDConnectionString как описано в шаге 2. Затем попробуйте повторно добавить метод в TableAdapter. На этот раз он должен работать без ошибок.

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

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

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

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

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

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

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

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

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

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

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

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

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

<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetDiscontinuedProducts() As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetDiscontinuedProducts()
End Function
<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsWithPriceLessThan(ByVal priceLessThan As Decimal) _
    As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetProductsWithPriceLessThan(priceLessThan)
End Function

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

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

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

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

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

Рис. 21. Выбор GetDiscontinuedProducts метода из списка Drop-Down на вкладке 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 вызывает ProductsBLLWithSprocs метод класса s GetDiscontinuedProducts . Как мы видели на шаге 7, этот метод вызывает метод класса GetDiscontinuedProducts DAL sProductsDataTable, который вызывает хранимую GetDiscontinuedProducts процедуру. Эта хранимая процедура является управляемой хранимой процедурой и выполняет код, созданный на шаге 3, возвращая неподдерживаемые продукты.

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

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

Для дальнейшей практики добавьте элемент TextBox и еще один Элемент GridView на страницу. Чтобы этот элемент GridView отображал продукты меньше суммы, введенной в TextBox, путем вызова ProductsBLLWithSprocs метода класса s GetProductsWithPriceLessThan .

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

User-Defined Функции, или определяемые пользователем функции, являются объектами базы данных, которые тесно имитируют семантику функций в языках программирования. Как и функция в Visual Basic, определяемые пользователем функции могут включать переменное количество входных параметров и возвращать значение определенного типа. Определяемая пользователем функция может возвращать скалярные данные ( строку, целое число и т. д.) или табличные данные. Давайте кратко рассмотрим оба типа определяемых пользователем функций, начиная с определяемой пользователем функции, которая возвращает скалярный тип данных.

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

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

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

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

Я добавил определяемую пользователем udf_ComputeInventoryValue функцию в базу данных Northwind; На рисунке 23 показаны выходные данные приведенного выше SELECT запроса при просмотре в Среде Management Studio. Также обратите внимание, что определяемая пользователем функция указана в папке Scalar-value Functions в обозреватель объектов.

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

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

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_GetProductsByCategoryID пользователем функция принимает входной @CategoryID параметр и возвращает результаты указанного SELECT запроса. После создания на эту определяемую пользователем функцию можно ссылаться в предложении FROMSELECT (или JOIN) запроса. В следующем примере возвращаются ProductIDзначения , ProductNameи CategoryID для каждого напитка.

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

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

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

Примечание

Дополнительные сведения о создании и использовании определяемых пользователем функций проверка в разделе Введение в User-Defined Функции. Кроме того, проверка преимущества и недостатки функций User-Defined.

Шаг 10. Создание управляемой пользовательской функции

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

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

Рис. 25. Добавление новой управляемой пользовательской функции в ManagedDatabaseConstructs проект (щелкните для просмотра полноразмерного изображения)

Шаблон функции User-Defined создает Partial класс с именем с методом UserDefinedFunctions , имя которого совпадает с именем файла класса (udf_ComputeInventoryValue_Managedв данном экземпляре). Этот метод декорирован с помощью атрибутаSqlFunction , который помечает метод как управляемую определяемую пользователем функцию.

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()> _
    Public Shared Function udf_ComputeInventoryValue_Managed() As SqlString
        ' Add your code here
        Return New SqlString("Hello")
    End Function
End Class

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

<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function udf_ComputeInventoryValue_Managed _
    (UnitPrice As SqlMoney, UnitsInStock As SqlInt16, Discontinued As SqlBoolean) _
    As SqlMoney
    Dim inventoryValue As SqlMoney = 0
    If Not UnitPrice.IsNull AndAlso Not UnitsInStock.IsNull Then
        inventoryValue = UnitPrice * UnitsInStock
        If Discontinued = True Then
            inventoryValue = inventoryValue * New SqlMoney(0.5)
        End If
    End If
    Return inventoryValue
End Function

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

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

Примечание

Объект SqlMoney позволяет умножить только два SqlMoney экземпляра. Он не позволяет SqlMoney умножить экземпляр на литеральное число с плавающей запятой. Таким образом, чтобы уменьшить inventoryValue вдвое, мы умножаем его на новый SqlMoney экземпляр со значением 0,5.

Шаг 11. Развертывание управляемой пользовательской функции

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

После развертывания проекта вернитесь в SQL Server Management Studio и обновите папку Функции со скалярным значением. Теперь вы увидите две записи:

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

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

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

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

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

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

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

Рис. 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 хранимую процедуру, замените @CategoryID существующее Test.sql содержимое файла следующей инструкцией, которая вызывает GetProductsWithPriceLessThan управляемую хранимую процедуру, передавая значение 14,95:

exec GetProductsWithPriceLessThan 14.95

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

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

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

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

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

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

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

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

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 GetProductsWithPriceGreaterThan(ByVal price As SqlMoney)
        'Create the command
        Dim myCommand As 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)
    End Sub
End Class

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

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

vbc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.vb

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

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

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

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

Примечание

Вместо компиляции GetProductsWithPriceGreaterThan.vb файла класса из командной строки можно использовать Visual Basic Express Edition или создать отдельный проект библиотеки классов в Visual Studio Standard Edition. S ren Jacob Lauritsen любезно предоставил такой проект Visual Basic Express Edition с кодом для хранимой GetProductsWithPriceGreaterThan процедуры и двух управляемых хранимых процедур и определяемых пользователем функций, созданных в шагах 3, 5 и 10. Проект S ren s также включает команды T-SQL, необходимые для добавления соответствующих объектов базы данных.

После компиляции кода в сборку можно зарегистрировать сборку в базе данных 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 в базу данных (щелкните, чтобы просмотреть полноразмерное изображение)

Снимок экрана: окно обозреватель объектов с выделенной сборкой 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, таких как Visual Basic. В этом руководстве мы создали две управляемые хранимые процедуры и управляемую функцию User-Defined.

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

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

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

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

Об авторе

Скотт Митчелл( Scott Mitchell), автор семи книг ASP/ASP.NET и основатель 4GuysFromRolla.com, работает с веб-технологиями Майкрософт с 1998 года. Скотт работает независимым консультантом, тренером и писателем. Его последняя книга Sams Teach Yourself ASP.NET 2.0 в 24 часах. Он может быть доступен в mitchell@4GuysFromRolla.com. или через его блог, который можно найти по адресу http://ScottOnWriting.NET.

Особая благодарность

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

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

Введение

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

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

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

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

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

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

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

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

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

Шаг 10. Создание управляемой пользовательской функции

Шаг 11. Развертывание управляемой пользовательской функции

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

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

Сводка

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

Об авторе

Особая благодарность

Обратная связь

Обратная связь

Дополнительные ресурсы