Создание хранимых процедур для адаптеров таблиц TableAdapter типизированного DataSet (C#)
В предыдущих руководствах мы создавали инструкции SQL в коде и передавали их в базу данных для выполнения. Альтернативным подходом является использование хранимых процедур, в которых инструкции SQL предварительно определены в базе данных. В этом руководстве мы узнаем, как мастер TableAdapter создает для нас новые хранимые процедуры.
Введение
Уровень доступа к данным (DAL) для этих учебников использует типизированные наборы данных. Как описано в учебнике По созданию уровня доступа к данным , типизированные наборы данных состоят из строго типизированных таблиц DataTables и TableAdapters. Таблицы Данных представляют логические сущности в системе, а 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 или SP .
Хранимые процедуры создаются с помощью инструкции 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.aspx
NewSprocs.aspx
ExistingSprocs.aspx
JOINs.aspx
AddingColumns.aspx
ComputedColumns.aspx
EncryptingConfigSections.aspx
ManagedFunctionsAndSprocs.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, создадим новый typed DataSet в папке ~/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
. После инструкции INSERT
SELECT
запрос используется для возврата значений 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
NorthwindWithSprocsTableAdapters.ProductsTableAdapter productsAPI =
new NorthwindWithSprocsTableAdapters.ProductsTableAdapter();
NorthwindWithSprocs.ProductsDataTable products =
new NorthwindWithSprocs.ProductsDataTable();
// Create a new ProductsRow instance and set its properties
NorthwindWithSprocs.ProductsRow product = 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
int newlyAddedProductIDValue = 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.cs
в папке ~/App_Code/BLL
и добавьте в него следующий код:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using NorthwindWithSprocsTableAdapters;
[System.ComponentModel.DataObject]
public class ProductsBLLWithSprocs
{
private ProductsTableAdapter _productsAdapter = null;
protected ProductsTableAdapter Adapter
{
get
{
if (_productsAdapter == null)
_productsAdapter = new ProductsTableAdapter();
return _productsAdapter;
}
}
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Select, true)]
public NorthwindWithSprocs.ProductsDataTable GetProducts()
{
return Adapter.GetProducts();
}
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetProductByProductID(int productID)
{
return Adapter.GetProductByProductID(productID);
}
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Insert, true)]
public bool AddProduct
(string productName, int? supplierID, int? categoryID,
string quantityPerUnit, decimal? unitPrice, short? unitsInStock,
short? unitsOnOrder, short? reorderLevel, bool discontinued)
{
// Create a new ProductRow instance
NorthwindWithSprocs.ProductsDataTable products =
new NorthwindWithSprocs.ProductsDataTable();
NorthwindWithSprocs.ProductsRow product = products.NewProductsRow();
product.ProductName = productName;
if (supplierID == null)
product.SetSupplierIDNull();
else
product.SupplierID = supplierID.Value;
if (categoryID == null)
product.SetCategoryIDNull();
else
product.CategoryID = categoryID.Value;
if (quantityPerUnit == null)
product.SetQuantityPerUnitNull();
else
product.QuantityPerUnit = quantityPerUnit;
if (unitPrice == null)
product.SetUnitPriceNull();
else
product.UnitPrice = unitPrice.Value;
if (unitsInStock == null)
product.SetUnitsInStockNull();
else
product.UnitsInStock = unitsInStock.Value;
if (unitsOnOrder == null)
product.SetUnitsOnOrderNull();
else
product.UnitsOnOrder = unitsOnOrder.Value;
if (reorderLevel == null)
product.SetReorderLevelNull();
else
product.ReorderLevel = reorderLevel.Value;
product.Discontinued = discontinued;
// Add the new product
products.AddProductsRow(product);
int rowsAffected = Adapter.Update(products);
// Return true if precisely one row was inserted, otherwise false
return rowsAffected == 1;
}
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Update, true)]
public bool UpdateProduct
(string productName, int? supplierID, int? categoryID, string quantityPerUnit,
decimal? unitPrice, short? unitsInStock, short? unitsOnOrder,
short? reorderLevel, bool discontinued, int productID)
{
NorthwindWithSprocs.ProductsDataTable products =
Adapter.GetProductByProductID(productID);
if (products.Count == 0)
// no matching record found, return false
return false;
NorthwindWithSprocs.ProductsRow product = products[0];
product.ProductName = productName;
if (supplierID == null)
product.SetSupplierIDNull();
else
product.SupplierID = supplierID.Value;
if (categoryID == null)
product.SetCategoryIDNull();
else
product.CategoryID = categoryID.Value;
if (quantityPerUnit == null)
product.SetQuantityPerUnitNull();
else
product.QuantityPerUnit = quantityPerUnit;
if (unitPrice == null)
product.SetUnitPriceNull();
else
product.UnitPrice = unitPrice.Value;
if (unitsInStock == null)
product.SetUnitsInStockNull();
else
product.UnitsInStock = unitsInStock.Value;
if (unitsOnOrder == null)
product.SetUnitsOnOrderNull();
else
product.UnitsOnOrder = unitsOnOrder.Value;
if (reorderLevel == null)
product.SetReorderLevelNull();
else
product.ReorderLevel = reorderLevel.Value;
product.Discontinued = discontinued;
// Update the product record
int rowsAffected = Adapter.Update(product);
// Return true if precisely one row was updated, otherwise false
return rowsAffected == 1;
}
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Delete, true)]
public bool DeleteProduct(int productID)
{
int rowsAffected = Adapter.Delete(productID);
// Return true if precisely one row was deleted, otherwise false
return rowsAffected == 1;
}
}
Этот класс имитирует семантику ProductsBLL
класса из предыдущих учебников, но использует ProductsTableAdapter
объекты и ProductsDataTable
из NorthwindWithSprocs
DataSet. Например, вместо того, чтобы использовать using NorthwindTableAdapters
оператор в начале файла класса, ProductsBLLWithSprocs
класс ProductsBLL
использует using 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.
Обратная связь
https://aka.ms/ContentUserFeedback.
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделеОтправить и просмотреть отзыв по