Создание хранимых процедур и определяемых пользователем функций с помощью управляемого кода (VB)
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, щелкнув ее правой кнопкой мыши и выбрав команду Переименовать.
Рис. 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.
Рис. 5. Связывание проекта SQL Server с базой данных Northwind
Чтобы отладить управляемые хранимые процедуры и определяемые пользователем функции, которые мы создадим в рамках этого проекта, необходимо включить поддержку отладки SQL/CLR для подключения. Всякий раз при связывании SQL Server Project с новой базой данных (как это было на рис. 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
и явно зарегистрировали ее в экземпляре 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
объект и задает для его CommandText
SELECT
запроса, который возвращает все столбцы из 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
.
Рис. 10. Сборка ManagedDatabaseConstructs
зарегистрирована в базе данных Northwind
Также разверните папку Хранимые процедуры. Там вы увидите хранимую процедуру с именем GetDiscontinuedProducts
. Эта хранимая процедура была создана процессом развертывания и указывает на GetDiscontinuedProducts
метод в сборке ManagedDatabaseConstructs
. При выполнении хранимой GetDiscontinuedProducts
процедуры она, в свою очередь, выполняет GetDiscontinuedProducts
метод . Так как это управляемая хранимая процедура, ее нельзя изменить с помощью Среды Management Studio (поэтому значок блокировки рядом с именем хранимой процедуры).
Рис. 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
хранимой процедуры. В окне запроса введите и выполните команду exec
GetDiscontinuedProducts
. Вызов хранимой процедуры приводит к выполнению соответствующего управляемого кода в методе 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
. Он вычисляет оценочную стоимость инвентаризации путем умножения на 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
После добавления этой определяемой пользователем функции в базу данных ее можно найти в Среде 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
запроса. После создания на эту определяемую пользователем функцию можно ссылаться в предложении FROM
SELECT
(или 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). Этот параметр можно изменить, щелкнув правой кнопкой мыши базу данных в окне Обозреватель сервера.
Рис. 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
предложение и имя параметра, используемого в запросе. В методе GetProductsWithPriceLessThan
WHERE
предложение гласит: 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
в базу данных (щелкните, чтобы просмотреть полноразмерное изображение)
Рис. 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.
Счастливого программирования!
Дополнительные материалы
Дополнительные сведения о темах, рассмотренных в этом руководстве, см. в следующих ресурсах:
- Преимущества и недостатки функций User-Defined
- Создание объектов SQL Server 2005 в управляемом коде
- Практическое руководство. Создание и запуск хранимой процедуры clR SQL Server
- Практическое руководство. Создание и запуск функции SQL Server User-Defined среды CLR
- Практическое руководство. Изменение скрипта
Test.sql
для запуска объектов SQL - Введение в определяемые пользователем функции
- Управляемый код и SQL Server 2005 (видео)
- Справочник по Transact-SQL
- Пошаговое руководство. Создание хранимой процедуры в управляемом коде
Об авторе
Скотт Митчелл( 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.
Обратная связь
https://aka.ms/ContentUserFeedback.
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделеОтправить и просмотреть отзыв по