Создание хранимых процедур для адаптеров таблиц TableAdapter типизированного DataSet (VB)
В предыдущих руководствах мы создавали инструкции SQL в коде и передавали их в базу данных для выполнения. Альтернативным подходом является использование хранимых процедур, в которых инструкции SQL предварительно определены в базе данных. В этом руководстве мы узнаем, как мастер tableAdapter создать для нас новые хранимые процедуры.
Введение
Уровень доступа к данным (DAL) для этих руководств использует типизированные наборы данных. Как описано в руководстве По созданию уровня доступа к данным , типизированные наборы данных состоят из строго типизированных таблиц DataTable и TableAdapters. Таблицы DataTable представляют логические сущности в системе, а TableAdapters взаимодействует с базовой базой данных для выполнения работы по доступу к данным. Это включает заполнение dataTables данными, выполнение запросов, возвращающих скалярные данные, а также вставку, обновление и удаление записей из базы данных.
Команды SQL, выполняемые TableAdapters, могут быть нерегламентированными инструкциями SQL, такими как SELECT columnList FROM TableName, или хранимыми процедурами. TableAdapters в нашей архитектуре используют нерегламентированные инструкции SQL. Однако многие разработчики и администраторы баз данных предпочитают хранимые процедуры вместо нерегламентированных инструкций SQL для обеспечения безопасности, удобства обслуживания и обновления. Другие предпочитают нерегламентированные инструкции SQL для их гибкости. В своей работе я предпочитаю хранимые процедуры вместо нерегламентированных инструкций SQL, но для упрощения предыдущих учебников я решил использовать нерегламентированные инструкции SQL.
При определении TableAdapter или добавлении новых методов мастер TableAdapter упрощает создание новых хранимых процедур или использование существующих хранимых процедур, как и использование нерегламентированных инструкций SQL. В этом руководстве мы рассмотрим, как мастер TableAdapter автоматически создавать хранимые процедуры. В следующем руководстве мы рассмотрим, как настроить методы TableAdapter для использования существующих или созданных вручную хранимых процедур.
Примечание
См. запись в блоге Роба Говарда Не использовать хранимые процедуры еще? и Запись в блоге Франса БумыStored Procedures Are Bad, M Kay? для живой дискуссии о плюсах и минусах хранимых процедур и ad-hoc SQL.
Основы хранимых процедур
Функции являются конструкцией, общей для всех языков программирования. Функция — это коллекция инструкций, которые выполняются при вызове функции. Функции могут принимать входные параметры и при необходимости возвращать значения. Хранимые процедуры — это конструкции баз данных, которые имеют много общего с функциями в языках программирования. Хранимая процедура состоит из набора инструкций T-SQL, которые выполняются при вызове хранимой процедуры. Хранимая процедура может принимать от нуля до многих входных параметров и может возвращать скалярные значения, выходные параметры или, как правило, результирующие наборы из SELECT запросов.
Примечание
Хранимые процедуры часто называются sprocs или SPs .
Хранимые процедуры создаются с помощью инструкции CREATE PROCEDURE T-SQL. Например, следующий скрипт T-SQL создает хранимую процедуру с именем GetProductsByCategoryID , которая принимает один параметр с именем @CategoryID и возвращает ProductIDполя , ProductName, UnitPriceи Discontinued из столбцов таблицы Products , имеющих соответствующее CategoryID значение:
CREATE PROCEDURE GetProductsByCategoryID
(
@CategoryID int
)
AS
SELECT ProductID, ProductName, UnitPrice, Discontinued
FROM Products
WHERE CategoryID = @CategoryID
После создания этой хранимой процедуры ее можно вызвать с помощью следующего синтаксиса:
EXEC GetProductsByCategory categoryID
Примечание
В следующем руководстве мы рассмотрим создание хранимых процедур с помощью интегрированной среды разработки Visual Studio. Однако в этом руководстве мы разрешим мастеру TableAdapter автоматически создавать хранимые процедуры.
Помимо простого возврата данных, хранимые процедуры часто используются для выполнения нескольких команд базы данных в область одной транзакции. Например, хранимая процедура с именем DeleteCategoryможет принимать @CategoryID параметр и выполнять две DELETE инструкции: одну для удаления связанных продуктов, а вторую — для удаления указанной категории. Несколько инструкций в хранимой процедуре не помещаются в транзакцию автоматически. Необходимо выполнить дополнительные команды T-SQL, чтобы обеспечить обработку нескольких команд хранимой процедуры как атомарной операции. В следующем руководстве мы рассмотрим, как упаковать команды хранимой процедуры в область транзакции.
При использовании хранимых процедур в архитектуре методы уровня доступа к данным вызывают определенную хранимую процедуру вместо нерегламентированной инструкции SQL. Это позволяет централизовать расположение выполняемых инструкций SQL (в базе данных), а не определять его в архитектуре приложения. Возможно, такая централизация упрощает поиск, анализ и настройку запросов и дает гораздо более четкое представление о том, где и как используется база данных.
Дополнительные сведения об основах хранимых процедур см. в ресурсах в разделе Дополнительные сведения в конце этого руководства.
Шаг 1. Создание веб-страниц расширенных сценариев уровня доступа к данным
Прежде чем мы начнем обсуждение создания DAL с помощью хранимых процедур, давайте сначала уйдем на минутку, чтобы создать страницы ASP.NET в нашем проекте веб-сайта, которые понадобятся нам для этого и следующих нескольких учебников. Начните с добавления новой папки с именем AdvancedDAL. Затем добавьте в нее следующие страницы ASP.NET, обязательно свяжите каждую страницу со страницей Site.master master:
Default.aspxNewSprocs.aspxExistingSprocs.aspxJOINs.aspxAddingColumns.aspxComputedColumns.aspxEncryptingConfigSections.aspxManagedFunctionsAndSprocs.aspx
Рис. 1. Добавление страниц ASP.NET для руководств по сценариям расширенного уровня доступа к данным
Как и в других папках, Default.aspx в папке AdvancedDAL будет отображаться список учебников в своем разделе. Помните, что SectionLevelTutorialListing.ascx пользовательский элемент управления предоставляет эту функцию. Поэтому добавьте этот пользовательский элемент управления в , Default.aspx перетащив его из Обозреватель решений в режим конструктора страницы.
Рис. 2. Добавление пользовательского SectionLevelTutorialListing.ascx элемента управления в Default.aspx (щелкните для просмотра полноразмерного изображения)
Наконец, добавьте эти страницы в качестве записей в Web.sitemap файл. В частности, добавьте следующую разметку после работы с пакетными данными <siteMapNode>:
<siteMapNode url="~/AdvancedDAL/Default.aspx"
title="Advanced DAL Scenarios"
description="Explore a number of advanced Data Access Layer scenarios.">
<siteMapNode url="~/AdvancedDAL/NewSprocs.aspx"
title="Creating New Stored Procedures for TableAdapters"
description="Learn how to have the TableAdapter wizard automatically
create and use stored procedures." />
<siteMapNode url="~/AdvancedDAL/ExistingSprocs.aspx"
title="Using Existing Stored Procedures for TableAdapters"
description="See how to plug existing stored procedures into a
TableAdapter." />
<siteMapNode url="~/AdvancedDAL/JOINs.aspx"
title="Returning Data Using JOINs"
description="Learn how to augment your DataTables to work with data
returned from multiple tables via a JOIN query." />
<siteMapNode url="~/AdvancedDAL/AddingColumns.aspx"
title="Adding DataColumns to a DataTable"
description="Master adding new columns to an existing DataTable." />
<siteMapNode url="~/AdvancedDAL/ComputedColumns.aspx"
title="Working with Computed Columns"
description="Explore how to work with computed columns when using
Typed DataSets." />
<siteMapNode url="~/AdvancedDAL/EncryptingConfigSections.aspx"
title="Protected Connection Strings in Web.config"
description="Protect your connection string information in
Web.config using encryption." />
<siteMapNode url="~/AdvancedDAL/ManagedFunctionsAndSprocs.aspx"
title="Creating Managed SQL Functions and Stored Procedures"
description="See how to create SQL functions and stored procedures
using managed code." />
</siteMapNode>
После обновления Web.sitemapпросмотрите веб-сайт учебников в браузере. Меню слева теперь содержит элементы для руководств по расширенным сценариям DAL.
Рис. 3. Карта сайта теперь включает записи для руководств по расширенным сценариям DAL
Шаг 2. Настройка TableAdapter для создания новых хранимых процедур
Чтобы продемонстрировать создание уровня доступа к данным, использующего хранимые процедуры вместо нерегламентированных инструкций SQL, создайте новый типизированный набор данных в папке ~/App_Code/DAL с именем NorthwindWithSprocs.xsd. Так как мы подробно выполнили этот процесс в предыдущих руководствах, мы быстро перейдем к шагам, описанным здесь. Если вы зависли или вам нужны дополнительные пошаговые инструкции по созданию и настройке типизированного набора данных, ознакомьтесь с руководством Создание уровня доступа к данным .
Добавьте новый набор данных в проект, щелкнув правой кнопкой мыши папку, выбрав Добавить новый элемент и выбрав шаблон DataSet, как показано на DAL рисунке 4.
Рис. 4. Добавление нового типизированного набора данных в проект с именем NorthwindWithSprocs.xsd (щелкните для просмотра полноразмерного изображения)
При этом будет создан новый типизированный набор данных, откроется его Designer, создан новый Объект TableAdapter и запустится мастер настройки TableAdapter. Первый шаг мастера настройки TableAdapter предлагает выбрать базу данных для работы. В раскрывающемся списке должен быть указан строка подключения базы данных Northwind. Выберите этот параметр и нажмите кнопку Далее.
На следующем экране можно выбрать, как TableAdapter должен получить доступ к базе данных. В предыдущих руководствах мы выбрали первый вариант Использовать инструкции SQL. Для работы с этим руководством выберите второй вариант Создать новые хранимые процедуры и нажмите кнопку Далее.
Рис. 5. Указание TableAdapter создать новые хранимые процедуры (щелкните для просмотра полноразмерного изображения)
Как и при использовании нерегламентированных инструкций SQL, на следующем шаге нам будет предложено предоставить инструкцию SELECT для запроса main TableAdapter. Но вместо того, чтобы использовать SELECT указанную здесь инструкцию для прямого выполнения нерегламентированного запроса, мастер TableAdapter создаст хранимую процедуру, содержащую этот SELECT запрос.
Используйте следующий SELECT запрос для этого объекта TableAdapter:
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
Рис. 6. Введите SELECT запрос (щелкните для просмотра полноразмерного изображения)
Примечание
Приведенный выше запрос немного отличается от main запроса ProductsTableAdapter в типизированном наборе Northwind данных. Напомним, что ProductsTableAdapter в типизированном Northwind наборе данных содержится два коррелированных вложенных запроса для возврата названия категории и названия компании для каждой категории продукта и поставщика. В предстоящем руководстве Обновление TableAdapter для использования JOIN мы рассмотрим добавление этих связанных данных в этот объект TableAdapter.
Нажмите кнопку Дополнительные параметры. Здесь можно указать, должен ли мастер также создавать инструкции insert, update и delete для TableAdapter, следует ли использовать оптимистический параллелизм и следует ли обновлять таблицу данных после вставки и обновления. По умолчанию установлен флажок Создать инструкции Insert, Update и Delete. Оставьте флажок. В этом руководстве не устанавливайте флажок Использовать оптимистичный параллелизм.
При автоматическом создании хранимых процедур мастером TableAdapter отображается, что параметр Обновить таблицу данных игнорируется. Независимо от того, установлен ли этот флажок, результирующая хранимая процедура вставки и обновления извлекает только что вставленную или только что обновленную запись, как показано на шаге 3.
Рис. 7. Оставьте флажок Создать инструкции Insert, Update и Delete
Примечание
Если установлен флажок Использовать оптимистичный параллелизм, мастер добавит в WHERE предложение дополнительные условия, которые препятствуют обновлению данных в случае изменений в других полях. Дополнительные сведения об использовании встроенной функции управления оптимистичным параллелизмом в TableAdapter см. в руководстве По реализации оптимистического параллелизма.
После ввода SELECT запроса и подтверждения того, что установлен флажок Создать инструкции Insert, Update и Delete, нажмите кнопку Далее. Следующий экран, показанный на рис. 8, запрашивает имена хранимых процедур, создаваемых мастером для выбора, вставки, обновления и удаления данных. Измените имена этих хранимых процедур на Products_Select, Products_Insert, Products_Updateи Products_Delete.
Рис. 8. Переименование хранимых процедур (щелкните для просмотра полноразмерного изображения)
Чтобы увидеть T-SQL, который мастер TableAdapter будет использовать для создания четырех хранимых процедур, нажмите кнопку Предварительный просмотр скрипта SQL. В диалоговом окне Предварительный просмотр скрипта SQL можно сохранить скрипт в файл или скопировать его в буфер обмена.
Рис. 9. Предварительный просмотр скрипта SQL, используемого для создания хранимых процедур
После присвоения имени хранимым процедурам нажмите кнопку Далее, чтобы присвоить имя соответствующим методам TableAdapter. Как и при использовании нерегламентированных инструкций SQL, можно создать методы, которые заполняют существующую базу данных DataTable или возвращают новую. Можно также указать, должен ли tableAdapter включать шаблон DB-Direct для вставки, обновления и удаления записей. Оставьте все три флажка флажком, но переименуйте метод GetProducts Return a DataTable в (как показано на рис. 10).
Рис. 10. Назовите методы Fill и GetProducts (щелкните, чтобы просмотреть полноразмерное изображение)
Нажмите кнопку Далее, чтобы просмотреть сводку действий, которые будет выполнять мастер. Завершите работу мастера, нажав кнопку Готово. После завершения работы мастера вы вернелись в Designer DataSet, которая теперь должна содержать ProductsDataTable.
Рис. 11. В Designer DataSet отображается добавленный ProductsDataTable элемент (щелкните, чтобы просмотреть полноразмерное изображение)
Шаг 3. Изучение вновь созданных хранимых процедур
Мастер TableAdapter, используемый на шаге 2, автоматически создал хранимые процедуры для выбора, вставки, обновления и удаления данных. Эти хранимые процедуры можно просмотреть или изменить с помощью Visual Studio, перейдя на Обозреватель сервера и детализированные сведения о папке хранимых процедур базы данных. Как показано на рисунке 12, база данных Northwind содержит четыре новые хранимые процедуры: Products_Delete, Products_Insert, Products_Selectи Products_Update.
Рис. 12. Четыре хранимые процедуры, созданные на шаге 2, можно найти в папке хранимых процедур базы данных
Примечание
Если Обозреватель сервера не отображается, перейдите в меню Вид и выберите параметр Сервер Обозреватель. Если вы не видите хранимые процедуры, связанные с продуктом, добавленные на шаге 2, попробуйте щелкнуть правой кнопкой мыши папку Хранимые процедуры и выбрать Обновить.
Чтобы просмотреть или изменить хранимую процедуру, дважды щелкните ее имя в Обозреватель сервера или щелкните правой кнопкой мыши хранимую процедуру и выберите команду Открыть. На рисунке 13 показана хранимая Products_Delete процедура при открытии.
Рис. 13. Хранимые процедуры можно открывать и изменять в Visual Studio (щелкните для просмотра полноразмерного изображения)
Содержимое хранимых Products_Delete процедур и Products_Select довольно простое. С другой стороны, хранимые Products_Insert процедуры и Products_Update требуют более тщательного изучения, поскольку они оба выполняют SELECT заявление после их INSERT и UPDATE заявлений. Например, следующая процедура SQL состоит из хранимой Products_Insert процедуры:
ALTER PROCEDURE dbo.Products_Insert
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit
)
AS
SET NOCOUNT OFF;
INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit],
[UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued])
VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice,
@UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE (ProductID = SCOPE_IDENTITY())
Хранимая процедура принимает в качестве входных параметров Products столбцы, возвращенные запросом SELECT , указанным в мастере TableAdapter, и эти значения используются в инструкции INSERT . После инструкции INSERTSELECT запрос используется для возврата значений Products столбцов (включая ProductID) добавленной записи. Эта возможность обновления полезна при добавлении новой записи с помощью шаблона пакетного обновления, так как она автоматически обновляет свойства добавленных ProductRow экземпляров ProductID с автоматическим увеличением значений, назначенных базой данных.
Эта функция показана в следующем коде. Он содержит и создается ProductsTableAdapter для типизированного NorthwindWithSprocs набора ProductsDataTable данных. Новый продукт добавляется в базу данных путем создания ProductsRow экземпляра, предоставления его значений и вызова метода TableAdapter Update , передавая ProductsDataTable. На внутреннем сервере метод TableAdapter Update перечисляет ProductsRow экземпляры в переданной таблице DataTable (в этом примере есть только один из них , который мы только что добавили) и выполняет соответствующую команду вставки, обновления или удаления. В этом случае Products_Insert выполняется хранимая процедура, которая добавляет новую запись в таблицу Products и возвращает сведения о недавно добавленной записи. Затем ProductsRow обновляется значение экземпляра ProductID . Update После завершения метода мы можем получить доступ к добавленному значению записи ProductID через ProductsRow свойство s ProductID .
' Create the ProductsTableAdapter and ProductsDataTable
Dim productsAPI As New NorthwindWithSprocsTableAdapters.ProductsTableAdapter
Dim products As New NorthwindWithSprocs.ProductsDataTable
' Create a new ProductsRow instance and set its properties
Dim product As NorthwindWithSprocs.ProductsRow = products.NewProductsRow()
product.ProductName = "New Product"
product.CategoryID = 1 ' Beverages
product.Discontinued = False
' Add the ProductsRow instance to the DataTable
products.AddProductsRow(product)
' Update the DataTable using the Batch Update pattern
productsAPI.Update(products)
' At this point, we can determine the value of the newly-added record's ProductID
Dim newlyAddedProductIDValue as Integer = product.ProductID
Хранимая Products_Update процедура также включает SELECT оператор после оператора UPDATE .
ALTER PROCEDURE dbo.Products_Update
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit,
@Original_ProductID int,
@ProductID int
)
AS
SET NOCOUNT OFF;
UPDATE [Products]
SET [ProductName] = @ProductName, [SupplierID] = @SupplierID,
[CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit,
[UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock,
[UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel,
[Discontinued] = @Discontinued
WHERE (([ProductID] = @Original_ProductID));
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE (ProductID = @ProductID)
Обратите внимание, что эта хранимая процедура включает два входных параметра для ProductID: @Original_ProductID и @ProductID. Эта функция позволяет использовать сценарии, в которых первичный ключ может быть изменен. Например, в базе данных сотрудников каждая запись сотрудника может использовать номер социального страхования сотрудника в качестве первичного ключа. Чтобы изменить номер социального страхования существующего сотрудника, необходимо указать как новый номер социального страхования, так и исходный номер. Products Для таблицы такая функциональность не требуется, так как ProductID столбец является столбцом IDENTITY и никогда не должен изменяться. Фактически инструкция UPDATE в хранимой Products_Update процедуре не включает ProductID столбец в свой список столбцов. Таким образом, хотя @Original_ProductID используется в предложении UPDATE оператора WHERE , он является излишним для Products таблицы и может быть заменен параметром @ProductID . При изменении параметров хранимой процедуры важно также обновить методы TableAdapter, использующие хранимую процедуру.
Шаг 4. Изменение параметров хранимой процедуры и обновление TableAdapter
@Original_ProductID Так как параметр является излишним, давайте полностью удалите его из хранимой Products_Update процедуры. Откройте хранимую Products_Update процедуру, удалите @Original_ProductID параметр и в WHERE предложении инструкции UPDATE измените имя параметра с @Original_ProductID на @ProductID. После внесения этих изменений T-SQL в хранимой процедуре должен выглядеть следующим образом:
ALTER PROCEDURE dbo.Products_Update
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit,
@ProductID int
)
AS
SET NOCOUNT OFF;
UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID,
[CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit,
[UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock,
[UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel,
[Discontinued] = @Discontinued
WHERE (([ProductID] = @ProductID));
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE (ProductID = @ProductID)
Чтобы сохранить эти изменения в базе данных, щелкните значок Сохранить на панели инструментов или нажмите клавиши CTRL+S. На этом этапе хранимая Products_Update процедура не ожидает входного @Original_ProductID параметра, но TableAdapter настроен для передачи такого параметра. Чтобы просмотреть параметры, которые TableAdapter будет отправлять в хранимую процедуруProducts_Update, выберите TableAdapter в Designer DataSet, перейдите к окно свойств и щелкните многоточие в UpdateCommand коллекции sParameters. Откроется диалоговое окно Коллекция параметров Редактор, как показано на рис. 14.
Рис. 14. Коллекция параметров Редактор Списки параметров, переданных в хранимую Products_Update процедуру
Этот параметр можно удалить отсюда, просто выбрав @Original_ProductID параметр из списка участников и нажав кнопку Удалить.
Кроме того, можно обновить параметры, используемые для всех методов, щелкнув правой кнопкой мыши TableAdapter в Designer и выбрав Настроить. Откроется мастер настройки TableAdapter, в котором перечислены хранимые процедуры, используемые для выбора, вставки, обновления и удаления, а также параметры, которые хранимые процедуры должны получить. Если щелкнуть раскрывающийся список Обновление, вы увидите ожидаемые входные параметры хранимых Products_Update процедур, которые больше не включаются @Original_ProductID (см. рис. 15). Просто нажмите кнопку Готово, чтобы автоматически обновить коллекцию параметров, используемую TableAdapter.
Рис. 15. Можно также использовать мастер настройки TableAdapter для обновления коллекций параметров методов (щелкните для просмотра полноразмерного изображения)
Шаг 5. Добавление дополнительных методов TableAdapter
Как показано на шаге 2, при создании объекта TableAdapter можно легко автоматически создать соответствующие хранимые процедуры. То же самое относится и к добавлению дополнительных методов в TableAdapter. Чтобы проиллюстрировать это, добавьте GetProductByProductID(productID) метод к объекту , созданному на ProductsTableAdapter шаге 2. Этот метод принимает в качестве входных данных ProductID значение и возвращает сведения об указанном продукте.
Для начала щелкните правой кнопкой мыши TableAdapter и выберите в контекстном меню пункт Добавить запрос.
Рис. 16. Добавление нового запроса в TableAdapter
Запустится мастер настройки запросов TableAdapter, который сначала запрашивает, как TableAdapter должен получить доступ к базе данных. Чтобы создать новую хранимую процедуру, выберите параметр Создать хранимую процедуру и нажмите кнопку Далее.
Рис. 17. Выбор параметра Создать новую хранимую процедуру (щелкните, чтобы просмотреть полноразмерное изображение)
На следующем экране нам будет предлагаться определить тип выполняемого запроса, будет ли он возвращать набор строк или одно скалярное значение или выполнять UPDATEоператор , INSERTили DELETE . GetProductByProductID(productID) Так как метод вернет строку, оставьте выбранным параметр SELECT, который возвращает строку, и нажмите кнопку Далее.
Рис. 18. Выберите параметр SELECT, который возвращает строку (щелкните для просмотра полноразмерного изображения)
На следующем экране отображается запрос main TableAdapter, в котором просто отображается имя хранимой процедуры (dbo.Products_Select). Замените имя хранимой процедуры следующим SELECT оператором, который возвращает все поля продукта для указанного продукта:
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID
Рис. 19. Замена имени хранимой процедуры запросом SELECT (щелкните для просмотра полноразмерного изображения)
На следующем экране появится запрос на присвоение имени создаваемой хранимой процедуре. Введите имя Products_SelectByProductID и нажмите кнопку Далее.
Рис. 20. Имя новой хранимой процедуры Products_SelectByProductID (щелкните для просмотра полноразмерного изображения)
На последнем шаге мастера можно изменить имена созданных методов, а также указать, следует ли использовать шаблон Заполнение dataTable, Возврат шаблона DataTable или и то, и другое. Для этого метода оставьте оба параметра флажки, но переименуйте методы в FillByProductID и GetProductByProductID. Нажмите кнопку Далее, чтобы просмотреть сводку действий, которые будет выполнять мастер, а затем нажмите кнопку Готово, чтобы завершить работу мастера.
Рис. 21. Переименование методов TableAdapter в FillByProductID и GetProductByProductID (Щелкните для просмотра полноразмерного изображения)
После завершения работы мастера TableAdapter получит новый метод, GetProductByProductID(productID) который при вызове будет выполнять Products_SelectByProductID только что созданную хранимую процедуру. Уделите немного времени, чтобы просмотреть эту новую хранимую процедуру на сервере Обозреватель путем детализации папки Хранимых процедур и открытия Products_SelectByProductID (если вы не видите ее, щелкните правой кнопкой мыши папку Хранимые процедуры и выберите Обновить).
Обратите внимание, что хранимая SelectByProductID процедура принимает @ProductID в качестве входного параметра и выполняет инструкцию SELECT , введенную в мастере.
ALTER PROCEDURE dbo.Products_SelectByProductID
(
@ProductID int
)
AS
SET NOCOUNT ON;
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID
Шаг 6. Создание класса уровня бизнес-логики
На протяжении всей серии учебников мы стремились поддерживать многоуровневую архитектуру, в которой уровень презентации делал все свои вызовы к уровню бизнес-логики (BLL). Чтобы выполнить это проектное решение, сначала необходимо создать класс BLL для нового typed DataSet, прежде чем получить доступ к данным продукта из уровня представления.
Создайте файл класса с именем ProductsBLLWithSprocs.vb в папке ~/App_Code/BLL и добавьте в него следующий код:
Imports NorthwindWithSprocsTableAdapters
<System.ComponentModel.DataObject()> _
Public Class ProductsBLLWithSprocs
Private _productsAdapter As ProductsTableAdapter = Nothing
Protected ReadOnly Property Adapter() As ProductsTableAdapter
Get
If _productsAdapter Is Nothing Then
_productsAdapter = New ProductsTableAdapter()
End If
Return _productsAdapter
End Get
End Property
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Select, True)> _
Public Function GetProducts() As NorthwindWithSprocs.ProductsDataTable
Return Adapter.GetProducts()
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductByProductID(ByVal productID As Integer) _
As NorthwindWithSprocs.ProductsDataTable
Return Adapter.GetProductByProductID(productID)
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public Function AddProduct _
(ByVal productName As String, ByVal supplierID As Nullable(Of Integer), _
ByVal categoryID As Nullable(Of Integer), ByVal quantityPerUnit As String, _
ByVal unitPrice As Nullable(Of Decimal), _
ByVal unitsInStock As Nullable(Of Short), _
ByVal unitsOnOrder As Nullable(Of Short), _
ByVal reorderLevel As Nullable(Of Short), _
ByVal discontinued As Boolean) _
As Boolean
' Create a new ProductRow instance
Dim products As New NorthwindWithSprocs.ProductsDataTable()
Dim product As NorthwindWithSprocs.ProductsRow = products.NewProductsRow()
product.ProductName = productName
If Not supplierID.HasValue Then
product.SetSupplierIDNull()
Else
product.SupplierID = supplierID.Value
End If
If Not categoryID.HasValue Then
product.SetCategoryIDNull()
Else
product.CategoryID = categoryID.Value
End If
If quantityPerUnit Is Nothing Then
product.SetQuantityPerUnitNull()
Else
product.QuantityPerUnit = quantityPerUnit
End If
If Not unitPrice.HasValue Then
product.SetUnitPriceNull()
Else
product.UnitPrice = unitPrice.Value
End If
If Not unitsInStock.HasValue Then
product.SetUnitsInStockNull()
Else
product.UnitsInStock = unitsInStock.Value
End If
If Not unitsOnOrder.HasValue Then
product.SetUnitsOnOrderNull()
Else
product.UnitsOnOrder = unitsOnOrder.Value
End If
If Not reorderLevel.HasValue Then
product.SetReorderLevelNull()
Else
product.ReorderLevel = reorderLevel.Value
End If
product.Discontinued = discontinued
' Add the new product
products.AddProductsRow(product)
Dim rowsAffected As Integer = Adapter.Update(products)
' Return true if precisely one row was inserted, otherwise false
Return rowsAffected = 1
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Update, True)> _
Public Function UpdateProduct
(ByVal productName As String, ByVal supplierID As Nullable(Of Integer), _
ByVal categoryID As Nullable(Of Integer), ByVal quantityPerUnit As String, _
ByVal unitPrice As Nullable(Of Decimal), _
ByVal unitsInStock As Nullable(Of Short), _
ByVal unitsOnOrder As Nullable(Of Short), _
ByVal reorderLevel As Nullable(Of Short), _
ByVal discontinued As Boolean, ByVal productID As Integer) _
As Boolean
Dim products As NorthwindWithSprocs.ProductsDataTable = _
Adapter.GetProductByProductID(productID)
If products.Count = 0 Then
' no matching record found, return false
Return False
End If
Dim product As NorthwindWithSprocs.ProductsRow = products(0)
product.ProductName = productName
If Not supplierID.HasValue Then
product.SetSupplierIDNull()
Else
product.SupplierID = supplierID.Value
End If
If Not categoryID.HasValue Then
product.SetCategoryIDNull()
Else
product.CategoryID = categoryID.Value
End If
If quantityPerUnit Is Nothing Then
product.SetQuantityPerUnitNull()
Else
product.QuantityPerUnit = quantityPerUnit
End If
If Not unitPrice.HasValue Then
product.SetUnitPriceNull()
Else
product.UnitPrice = unitPrice.Value
End If
If Not unitsInStock.HasValue Then
product.SetUnitsInStockNull()
Else
product.UnitsInStock = unitsInStock.Value
End If
If Not unitsOnOrder.HasValue Then
product.SetUnitsOnOrderNull()
Else
product.UnitsOnOrder = unitsOnOrder.Value
End If
If Not reorderLevel.HasValue Then
product.SetReorderLevelNull()
Else
product.ReorderLevel = reorderLevel.Value
End If
product.Discontinued = discontinued
' Update the product record
Dim rowsAffected As Integer = Adapter.Update(product)
' Return true if precisely one row was updated, otherwise false
Return rowsAffected = 1
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Delete, True)> _
Public Function DeleteProduct(ByVal productID As Integer) As Boolean
Dim rowsAffected As Integer = Adapter.Delete(productID)
' Return true if precisely one row was deleted, otherwise false
Return rowsAffected = 1
End Function
End Class
Этот класс имитирует семантику ProductsBLL класса из предыдущих учебников, но использует ProductsTableAdapter объекты и ProductsDataTable из NorthwindWithSprocs DataSet. Например, вместо того, чтобы использовать Imports NorthwindTableAdapters оператор в начале файла класса, ProductsBLLWithSprocs класс ProductsBLL использует Imports NorthwindWithSprocsTableAdapters. Аналогичным образом, объекты и ProductsRow , используемые в этом классе, ProductsDataTable имеют префикс пространства NorthwindWithSprocs имен . Класс ProductsBLLWithSprocs предоставляет два метода доступа к данным, GetProducts а GetProductByProductIDтакже методы и для добавления, обновления и удаления одного экземпляра продукта.
Шаг 7. Работа с наборомNorthwindWithSprocsданных из уровня представления
На этом этапе мы создали DAL, который использует хранимые процедуры для доступа к базовым данным базы данных и их изменения. Мы также создали зачаточные BLL с методами извлечения всех продуктов или определенного продукта, а также методы добавления, обновления и удаления продуктов. Чтобы обойти это руководство, создадим ASP.NET страницу, которая использует класс BLL ProductsBLLWithSprocs для отображения, обновления и удаления записей.
Откройте страницу NewSprocs.aspx в папке AdvancedDAL и перетащите элемент GridView из панели элементов в Designer, назвав его Products. В смарт-теге GridView выберите привязку к новому объекту ObjectDataSource с именем ProductsDataSource. Настройте ObjectDataSource для использования класса , как показано на ProductsBLLWithSprocs рисунке 22.
Рис. 22. Настройка ObjectDataSource для использования ProductsBLLWithSprocs класса (щелкните для просмотра полноразмерного изображения)
Раскрывающийся список на вкладке SELECT имеет два параметра: GetProducts и GetProductByProductID. Так как мы хотим отобразить все продукты в GridView, выберите GetProducts метод . Раскрывающийся список на вкладках UPDATE, INSERT и DELETE имеет только один метод. Убедитесь, что в каждом из этих раскрывающихся списков выбран соответствующий метод, и нажмите кнопку Готово.
После завершения работы мастера ObjectDataSource Visual Studio добавит BoundFields и CheckBoxField в GridView для полей данных продукта. Включите встроенные функции редактирования и удаления GridView, установив в смарт-теге параметры Включить редактирование и Включить удаление.
Рис. 23. Страница содержит GridView с включенной поддержкой редактирования и удаления (щелкните для просмотра полноразмерного изображения)
Как мы уже говорили в предыдущих руководствах, по завершении работы мастера ObjectDataSource Visual Studio устанавливает OldValuesParameterFormatString для свойства значение original_{0}. Необходимо вернуть значение по умолчанию {0} , чтобы функции изменения данных правильно работали с учетом параметров, ожидаемых методами в BLL. Поэтому обязательно присвойте свойству OldValuesParameterFormatString значение {0} или полностью удалите свойство из декларативного синтаксиса.
После завершения работы мастера настройки источника данных, включения поддержки редактирования и удаления в GridView и возврата свойства ObjectDataSource OldValuesParameterFormatString в значение по умолчанию декларативная разметка страницы должна выглядеть примерно так:
<asp:GridView ID="Products" runat="server" AutoGenerateColumns="False"
DataKeyNames="ProductID" DataSourceID="ProductsDataSource">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="ProductID" HeaderText="ProductID"
InsertVisible="False" ReadOnly="True"
SortExpression="ProductID" />
<asp:BoundField DataField="ProductName" HeaderText="ProductName"
SortExpression="ProductName" />
<asp:BoundField DataField="SupplierID" HeaderText="SupplierID"
SortExpression="SupplierID" />
<asp:BoundField DataField="CategoryID" HeaderText="CategoryID"
SortExpression="CategoryID" />
<asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit"
SortExpression="QuantityPerUnit" />
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice"
SortExpression="UnitPrice" />
<asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock"
SortExpression="UnitsInStock" />
<asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder"
SortExpression="UnitsOnOrder" />
<asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel"
SortExpression="ReorderLevel" />
<asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued"
SortExpression="Discontinued" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ProductsDataSource" runat="server"
DeleteMethod="DeleteProduct" InsertMethod="AddProduct"
SelectMethod="GetProducts" TypeName="ProductsBLLWithSprocs"
UpdateMethod="UpdateProduct">
<DeleteParameters>
<asp:Parameter Name="productID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="productName" Type="String" />
<asp:Parameter Name="supplierID" Type="Int32" />
<asp:Parameter Name="categoryID" Type="Int32" />
<asp:Parameter Name="quantityPerUnit" Type="String" />
<asp:Parameter Name="unitPrice" Type="Decimal" />
<asp:Parameter Name="unitsInStock" Type="Int16" />
<asp:Parameter Name="unitsOnOrder" Type="Int16" />
<asp:Parameter Name="reorderLevel" Type="Int16" />
<asp:Parameter Name="discontinued" Type="Boolean" />
<asp:Parameter Name="productID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="productName" Type="String" />
<asp:Parameter Name="supplierID" Type="Int32" />
<asp:Parameter Name="categoryID" Type="Int32" />
<asp:Parameter Name="quantityPerUnit" Type="String" />
<asp:Parameter Name="unitPrice" Type="Decimal" />
<asp:Parameter Name="unitsInStock" Type="Int16" />
<asp:Parameter Name="unitsOnOrder" Type="Int16" />
<asp:Parameter Name="reorderLevel" Type="Int16" />
<asp:Parameter Name="discontinued" Type="Boolean" />
</InsertParameters>
</asp:ObjectDataSource>
На этом этапе мы могли бы привести в порядок GridView, настроив интерфейс редактирования, чтобы включить проверку, чтобы столбцы и SupplierID отображались CategoryID как Раскрывающиеся списки и т. д. Мы также можем добавить подтверждение на стороне клиента к кнопке Удалить, и я призываю вас уделить время реализации этих улучшений. Однако, поскольку эти темы были рассмотрены в предыдущих руководствах, мы не будем освещать их здесь.
Независимо от того, улучшаете ли вы GridView, протестируйте основные функции страницы в браузере. Как показано на рисунке 24, на странице перечислены продукты в GridView, который предоставляет возможности редактирования и удаления строк.
Рис. 24. Продукты можно просматривать, редактировать и удалять из GridView (щелкните для просмотра полноразмерного изображения)
Сводка
TableAdapters в типизированном наборе данных могут получать доступ к данным из базы данных с помощью нерегламентированных инструкций SQL или хранимых процедур. При работе с хранимыми процедурами можно использовать существующие хранимые процедуры или проинструктировать мастер TableAdapter для создания новых хранимых SELECT процедур на основе запроса. В этом руководстве мы изучили, как автоматически создавать хранимые процедуры.
Хотя автоматическое создание хранимых процедур помогает сэкономить время, в некоторых случаях хранимая процедура, созданная мастером, не соответствует тому, что мы создали бы самостоятельно. Одним из примеров является хранимая Products_Update процедура, которая ожидала и @Original_ProductID входные параметры, @ProductID даже если @Original_ProductID параметр был лишним.
Во многих сценариях хранимые процедуры, возможно, уже созданы, или мы можем создать их вручную, чтобы иметь более высокий уровень управления командами хранимой процедуры. В любом случае необходимо указать TableAdapter использовать существующие хранимые процедуры для своих методов. Мы посмотрим, как это сделать в следующем руководстве.
Счастливого программирования!
Дополнительные материалы
Дополнительные сведения о темах, рассмотренных в этом руководстве, см. в следующих ресурсах:
Об авторе
Скотт Митчелл( Scott Mitchell), автор семи книг ASP/ASP.NET и основатель 4GuysFromRolla.com, работает с веб-технологиями Майкрософт с 1998 года. Скотт работает независимым консультантом, тренером и писателем. Его последняя книга Sams Teach Yourself ASP.NET 2.0 в 24 часах. Он может быть доступен в mitchell@4GuysFromRolla.com. или через его блог, который можно найти по адресу http://ScottOnWriting.NET.
Особая благодарность
Эта серия учебников была рассмотрена многими полезными рецензентами. Ведущим рецензентом этого руководства был Хилтон Geisenow. Хотите просмотреть предстоящие статьи MSDN? Если да, опустите мне строку на mitchell@4GuysFromRolla.com.
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по