Использование существующих хранимых процедур для адаптеров таблиц TableAdapter типизированного DataSet (C#)
В предыдущем руководстве мы узнали, как использовать мастер tableAdapter для создания новых хранимых процедур. В этом руководстве мы узнаем, как тот же мастер TableAdapter может работать с существующими хранимыми процедурами. Мы также узнаем, как вручную добавить новые хранимые процедуры в базу данных.
Введение
В предыдущем руководстве мы узнали, как можно настроить TableAdapters typed DataSet для использования хранимых процедур для доступа к данным, а не специализированных инструкций SQL. В частности, мы рассмотрели, как мастер TableAdapter автоматически создавать эти хранимые процедуры. При переносе устаревшего приложения в ASP.NET 2.0 или при создании веб-сайта ASP.NET 2.0 на основе существующей модели данных существует вероятность того, что база данных уже содержит необходимые хранимые процедуры. Кроме того, вы можете создать хранимые процедуры вручную или с помощью какого-либо средства, отличного от мастера TableAdapter, который автоматически создает хранимые процедуры.
В этом руководстве мы рассмотрим, как настроить TableAdapter для использования существующих хранимых процедур. Так как база данных Northwind содержит только небольшой набор встроенных хранимых процедур, мы также рассмотрим шаги, необходимые для добавления новых хранимых процедур вручную в базу данных с помощью среды Visual Studio. Приступим!
Примечание
В учебнике Упаковка изменений базы данных в рамках транзакции мы добавили методы в TableAdapter для поддержки транзакций (BeginTransaction
, CommitTransaction
и т. д.). Кроме того, транзакциями можно управлять полностью в рамках хранимой процедуры, которая не требует внесения изменений в код уровня доступа к данным. В этом руководстве мы рассмотрим команды T-SQL, используемые для выполнения инструкций хранимой процедуры в область транзакции.
Шаг 1. Добавление хранимых процедур в базу данных Northwind
Visual Studio упрощает добавление новых хранимых процедур в базу данных. Добавим новую хранимую процедуру в базу данных Northwind, которая возвращает все столбцы из Products
таблицы для тех, которые имеют определенное CategoryID
значение. В окне Сервер Обозреватель разверните базу данных Northwind, чтобы отобразить ее папки — Схемы баз данных, Таблицы, Представления и т. д. Как мы видели в предыдущем руководстве, папка Stored Procedures содержит существующие хранимые процедуры базы данных. Чтобы добавить новую хранимую процедуру, просто щелкните правой кнопкой мыши папку Хранимые процедуры и выберите в контекстном меню пункт Добавить новую хранимую процедуру.
Рис. 1. Right-Click папку "Хранимые процедуры" и "Добавить новую хранимую процедуру" (щелкните для просмотра полноразмерного изображения)
Как показано на рисунке 1, при выборе параметра Добавить новую хранимую процедуру в Visual Studio откроется окно скрипта с структурой скрипта SQL, необходимого для создания хранимой процедуры. Наша задача состоит в том, чтобы конкретизировать этот скрипт и выполнить его, после чего хранимая процедура будет добавлена в базу данных.
Введите следующий скрипт:
CREATE PROCEDURE dbo.Products_SelectByCategoryID
(
@CategoryID int
)
AS
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = @CategoryID
При выполнении этого скрипта будет добавлена новая хранимая процедура в базу данных Northwind с именем Products_SelectByCategoryID
. Эта хранимая процедура принимает один входной параметр (@CategoryID
типа int
) и возвращает все поля для этих продуктов с соответствующим значением CategoryID
.
Чтобы выполнить этот CREATE PROCEDURE
скрипт и добавить хранимую процедуру в базу данных, щелкните значок Сохранить на панели инструментов или нажмите клавиши CTRL+S. После этого папка Хранимых процедур обновляется, отображая только что созданную хранимую процедуру. Кроме того, скрипт в окне изменит тонкость с CREATE PROCEDURE dbo.Products_SelectProductByCategoryID
на ALTER PROCEDURE
dbo.Products_SelectProductByCategoryID
. CREATE PROCEDURE
добавляет новую хранимую процедуру в базу данных, а ALTER PROCEDURE
обновляет существующую. С момента запуска скрипта изменился на , изменение входных параметров хранимых процедур или инструкций SQL и нажатие значка Сохранить приведет к ALTER PROCEDURE
обновлению хранимой процедуры с этими изменениями.
На рисунке 2 показана среда Visual Studio после сохранения хранимой Products_SelectByCategoryID
процедуры.
Рис. 2. Хранимая процедура Products_SelectByCategoryID
добавлена в базу данных (щелкните для просмотра полноразмерного изображения)
Шаг 2. Настройка TableAdapter для использования существующей хранимой процедуры
Теперь, когда хранимая Products_SelectByCategoryID
процедура добавлена в базу данных, можно настроить уровень доступа к данным для использования этой хранимой процедуры при вызове одного из ее методов. В частности, мы добавим в типизированный GetProductsByCategoryID(categoryID)
набор данных метод ProductsTableAdapter
NorthwindWithSprocs
, который вызывает только что созданную хранимую Products_SelectByCategoryID
процедуру.
Начните с открытия NorthwindWithSprocs
набора данных. Щелкните правой ProductsTableAdapter
кнопкой мыши и выберите Добавить запрос, чтобы запустить мастер настройки запросов TableAdapter. В предыдущем руководстве мы решили, чтобы TableAdapter создавал новую хранимую процедуру. Однако в этом руководстве мы хотим подключить новый метод TableAdapter к существующей Products_SelectByCategoryID
хранимой процедуре. Поэтому на первом шаге мастера выберите параметр Использовать существующую хранимую процедуру и нажмите кнопку Далее.
Рис. 3. Выбор параметра Использовать существующую хранимую процедуру (щелкните для просмотра полноразмерного изображения)
На следующем экране представлен раскрывающийся список, заполненный хранимыми процедурами базы данных. При выборе хранимой процедуры слева выводится список ее входных параметров, а справа — возвращенные поля данных (если таковые есть). Выберите хранимую Products_SelectByCategoryID
процедуру из списка и нажмите кнопку Далее.
Рис. 4. Выбор хранимой Products_SelectByCategoryID
процедуры (щелкните для просмотра полноразмерного изображения)
На следующем экране показано, какие данные возвращаются хранимой процедурой, и наш ответ определяет тип, возвращаемый методом TableAdapter. Например, если указать, что возвращаются табличные данные, метод вернет ProductsDataTable
экземпляр, заполненный записями, возвращенными хранимой процедурой. В отличие от этого, если указать, что эта хранимая процедура возвращает одно значение, TableAdapter вернет object
значение , которому присваивается значение в первом столбце первой записи, возвращенной хранимой процедурой.
Так как хранимая Products_SelectByCategoryID
процедура возвращает все продукты, относящиеся к определенной категории, выберите первый ответ — Табличные данные — и нажмите кнопку Далее.
Рис. 5. Указание на то, что хранимая процедура возвращает табличные данные (щелкните для просмотра полноразмерного изображения)
Остается только указать, какие шаблоны методов следует использовать, а затем имена этих методов. Оставьте флажки Заполнить dataTable и Вернуть dataTable, но переименуйте методы в FillByCategoryID
и GetProductsByCategoryID
. Затем нажмите кнопку Далее, чтобы просмотреть сводку задач, которые будет выполнять мастер. Если все выглядит правильно, нажмите кнопку Готово.
Рис. 6. Назовите методы FillByCategoryID
и GetProductsByCategoryID
(щелкните для просмотра полноразмерного изображения)
Примечание
Только что созданные FillByCategoryID
методы TableAdapter и GetProductsByCategoryID
ожидают входной параметр типа int
. Это значение входного параметра передается в хранимую процедуру через ее @CategoryID
параметр. При изменении параметров хранимой Products_SelectByCategory
процедуры необходимо также обновить параметры для этих методов TableAdapter. Как упоминалось в предыдущем руководстве, это можно сделать одним из двух способов: вручную добавив или удалив параметры из коллекции параметров или повторно запустив мастер TableAdapter.
Шаг 3. ДобавлениеGetProductsByCategoryID(categoryID)
метода в BLL
GetProductsByCategoryID
После завершения метода DAL следующим шагом является предоставление доступа к этому методу на уровне бизнес-логики. ProductsBLLWithSprocs
Откройте файл класса и добавьте следующий метод:
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetProductByCategoryID(int categoryID)
{
return Adapter.GetProductsByCategoryID(categoryID);
}
Этот метод BLL просто возвращает объект , ProductsDataTable
возвращенный методом ProductsTableAdapter
s GetProductsByCategoryID
. Атрибут DataObjectMethodAttribute
предоставляет метаданные, используемые мастером настройки источника данных ObjectDataSource. В частности, этот метод будет отображаться в раскрывающемся списке на вкладке SELECT.
Шаг 4. Отображение продуктов по категориям
Чтобы протестировать добавленную Products_SelectByCategoryID
хранимую процедуру и соответствующие методы DAL и BLL, создадим ASP.NET страницу, содержащую DropDownList и GridView. DropDownList отобразит список всех категорий в базе данных, а GridView — продукты, относящиеся к выбранной категории.
Примечание
Мы создали интерфейсы master и подробных сведений с помощью DropDownLists в предыдущих руководствах. Более подробные сведения о реализации такого master/подробного отчета см. в учебнике Фильтрация основных и подробных данных с помощью DropDownList.
Откройте страницу ExistingSprocs.aspx
в папке AdvancedDAL
и перетащите dropDownList с панели элементов на Designer. Задайте для свойства DropDownList значение ID
Categories
, а для его AutoPostBack
свойства — значение true
. Затем из смарт-тега привяжите DropDownList к новому объекту ObjectDataSource с именем CategoriesDataSource
. Настройте ObjectDataSource таким образом, чтобы он извлек свои данные из CategoriesBLL
метода класса .GetCategories
Задайте для раскрывающихся списков на вкладках UPDATE, INSERT и DELETE значение (Нет).
Рис. 7. Получение данных из CategoriesBLL
метода класса GetCategories
(щелкните, чтобы просмотреть полноразмерное изображение)
Рис. 8. Задайте для Drop-Down Списки на вкладках UPDATE, INSERT и DELETE значение (Нет) (Щелкните для просмотра полноразмерного изображения)
После завершения работы мастера ObjectDataSource настройте DropDownList для отображения CategoryName
поля данных и использования CategoryID
поля в Value
качестве для каждого ListItem
.
На этом этапе декларативная разметка DropDownList и ObjectDataSource должна выглядеть следующим образом:
<asp:DropDownList ID="Categories" runat="server" AutoPostBack="True"
DataSourceID="CategoriesDataSource" DataTextField="CategoryName"
DataValueField="CategoryID">
</asp:DropDownList>
<asp:ObjectDataSource ID="CategoriesDataSource" runat="server"
OldValuesParameterFormatString="original_{0}"
SelectMethod="GetCategories" TypeName="CategoriesBLL">
</asp:ObjectDataSource>
Затем перетащите элемент GridView на Designer, поместив его под раскрывающимся списком. Задайте для GridView значение ID
ProductsByCategory
и привяжите его из смарт-тега к новому объекту ObjectDataSource с именем ProductsByCategoryDataSource
. ProductsByCategoryDataSource
Настройте ObjectDataSource для использования ProductsBLLWithSprocs
класса , чтобы он извлек его данные с помощью GetProductsByCategoryID(categoryID)
метода . Так как gridView будет использоваться только для отображения данных, установите для раскрывающихся списков на вкладках UPDATE, INSERT и DELETE значение (Нет) и нажмите кнопку Далее.
Рис. 9. Настройка ObjectDataSource для использования ProductsBLLWithSprocs
класса (щелкните для просмотра полноразмерного изображения)
Рис. 10. Получение данных из GetProductsByCategoryID(categoryID)
метода (щелкните для просмотра полноразмерного изображения)
Метод, выбранный на вкладке SELECT, ожидает параметр, поэтому на последнем шаге мастера будет предложено ввести источник параметра. В раскрывающемся списке Источник параметров выберите Элемент управления и выберите Categories
элемент управления в раскрывающемся списке ControlID. Чтобы завершить работу мастера, нажмите кнопку Готово.
Рис. 11. Использование раскрывающегося Categories
списка в качестве источника categoryID
параметра (щелкните для просмотра полноразмерного изображения)
После завершения работы мастера ObjectDataSource Visual Studio добавит BoundFields и CheckBoxField для каждого поля данных продукта. Вы можете настроить эти поля в соответствии с вашими потребностями.
Посетите страницу в браузере. При посещении страницы выбирается категория «Напитки», а соответствующие продукты перечислены в сетке. Изменение раскрывающегося списка на альтернативную категорию, как показано на рисунке 12, вызывает обратную передачу и перезагружает сетку с продуктами только что выбранной категории.
Рис. 12. Отображаются продукты в категории "Продукция" (щелкните для просмотра полноразмерного изображения)
Шаг 5. Упаковка инструкций хранимой процедуры в область действия транзакции
В учебнике Упаковка изменений базы данных в рамках транзакции мы рассмотрели методы выполнения ряда инструкций изменения базы данных в область транзакции. Помните, что изменения, выполненные под эгидой транзакции, либо все успешно выполняются, либо завершаются сбоем, гарантируя атомарность. Ниже представлены методы использования транзакций.
- Используя классы в
System.Transactions
пространстве имен, - Использование уровня доступа к данным ADO.NET классах, таких как
SqlTransaction
, и - Добавление команд транзакций T-SQL непосредственно в хранимую процедуру
В руководстве по переносу изменений базы данных в рамках транзакции использовались классы ADO.NET в DAL. В оставшейся части этого руководства рассматривается управление транзакцией с помощью команд T-SQL из хранимой процедуры.
Три ключевые команды SQL для запуска, фиксации и отката транзакции вручную: BEGIN TRANSACTION
, COMMIT TRANSACTION
и ROLLBACK TRANSACTION
соответственно. Как и в случае с ADO.NET подходом, при использовании транзакций из хранимой процедуры необходимо применить следующий шаблон:
- Укажите начало транзакции.
- Выполните инструкции SQL, составляющие транзакцию.
- Если в какой-либо из инструкций из шага 2 возникает ошибка, откат транзакции
- Если все инструкции из шага 2 выполняются без ошибок, зафиксируйте транзакцию.
Этот шаблон можно реализовать в синтаксисе T-SQL с помощью следующего шаблона:
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
... Perform the SQL statements that makeup the transaction ...
-- If we reach here, success!
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Whoops, there was an error
ROLLBACK TRANSACTION
-- Raise an error with the
-- details of the exception
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
Шаблон начинается с определения TRY...CATCH
блока, новой конструкции SQL Server 2005. Как и в случае с try...catch
блоками в C#, блок SQL TRY...CATCH
выполняет инструкции в блоке TRY
. Если какой-либо оператор вызывает ошибку, элемент управления немедленно передается блоку CATCH
.
Если при выполнении инструкций SQL, создающих транзакцию, нет ошибок, COMMIT TRANSACTION
инструкция фиксирует изменения и завершает транзакцию. Однако если одна из инструкций приводит к ошибке, ROLLBACK TRANSACTION
объект в блоке CATCH
возвращает базу данных в состояние до начала транзакции. Хранимая процедура также вызывает ошибку с помощью команды RAISERROR, которая вызывает SqlException
в приложении .
Примечание
TRY...CATCH
Так как блок является новым для SQL Server 2005, приведенный выше шаблон не будет работать, если вы используете более старые версии Microsoft SQL Server.
Рассмотрим конкретный пример. Между таблицами и Products
существует Categories
ограничение внешнего ключа, то есть каждое CategoryID
Products
поле в таблице должно сопоставляться со CategoryID
значением Categories
в таблице. Любое действие, которое нарушает это ограничение, например попытка удалить категорию со связанными продуктами, приводит к нарушению ограничения внешнего ключа. Чтобы проверить это, вернитесь к примеру Обновление и удаление существующих двоичных данных в разделе Работа с двоичными данными (~/BinaryData/UpdatingAndDeleting.aspx
). На этой странице перечислены все категории в системе вместе с кнопками "Изменить" и "Удалить" (см. рис. 13), но при попытке удалить категорию со связанными продуктами, например "Напитки", удаление завершится сбоем из-за нарушения ограничения внешнего ключа (см. рис. 14).
Рис. 13. Каждая категория отображается в GridView с кнопками "Изменить" и "Удалить" (щелкните для просмотра полноразмерного изображения)
Рис. 14. Невозможно удалить категорию с существующими продуктами (щелкните для просмотра полноразмерного изображения)
Однако представьте, что мы хотим разрешить удаление категорий независимо от того, есть ли у них связанные продукты. Если категория с продуктами будет удалена, представьте, что мы также хотим удалить существующие продукты (хотя другой вариант — просто задать для ее CategoryID
продуктов значение NULL
). Эту функцию можно реализовать с помощью каскадных правил ограничения внешнего ключа. Кроме того, можно создать хранимую процедуру, которая принимает входной @CategoryID
параметр и при вызове явно удаляет все связанные продукты, а затем указанную категорию.
Наша первая попытка выполнить такую хранимую процедуру может выглядеть следующим образом:
CREATE PROCEDURE dbo.Categories_Delete
(
@CategoryID int
)
AS
-- First, delete the associated products...
DELETE FROM Products
WHERE CategoryID = @CategoryID
-- Now delete the category
DELETE FROM Categories
WHERE CategoryID = @CategoryID
Хотя это, безусловно, удалит связанные продукты и категорию, она не делает это под эгидой транзакции. Представьте, что существует другое Categories
ограничение внешнего ключа, которое запрещает удаление определенного @CategoryID
значения. Проблема заключается в том, что в этом случае все продукты будут удалены, прежде чем мы попытаемся удалить категорию. Чистый результат заключается в том, что для такой категории эта хранимая процедура удалит все свои продукты, а категория остается, так как она по-прежнему имеет связанные записи в некоторых других таблицах.
Однако если хранимая процедура была заключена в область транзакции, операции удаления Products
в таблице будут откатаны перед лицом неудачного удаления в Categories
. Следующий скрипт хранимой процедуры использует транзакцию для обеспечения атомарности между двумя DELETE
операторами:
CREATE PROCEDURE dbo.Categories_Delete
(
@CategoryID int
)
AS
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
-- First, delete the associated products...
DELETE FROM Products
WHERE CategoryID = @CategoryID
-- Now delete the category
DELETE FROM Categories
WHERE CategoryID = @CategoryID
-- If we reach here, success!
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Whoops, there was an error
ROLLBACK TRANSACTION
-- Raise an error with the
-- details of the exception
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
Уделите немного времени, чтобы добавить хранимую Categories_Delete
процедуру в базу данных Northwind. Инструкции по добавлению хранимых процедур в базу данных см. в разделе Шаг 1.
Шаг 6. ОбновлениеCategoriesTableAdapter
Хотя мы добавили хранимую Categories_Delete
процедуру в базу данных, DAL в настоящее время настроен для использования нерегламентированных инструкций SQL для выполнения удаления. Необходимо обновить CategoriesTableAdapter
и указать ему использовать хранимую Categories_Delete
процедуру.
Примечание
Ранее в этом руководстве мы работали NorthwindWithSprocs
с DataSet. Но у этого набора данных есть только одна сущность , ProductsDataTable
и нам нужно работать с категориями. Поэтому в оставшейся части этого руководства, когда я говорю об уровне доступа к Northwind
данным, я имею в виду Набор данных, который мы впервые создали в учебнике Создание уровня доступа к данным .
Откройте набор данных Northwind, выберите CategoriesTableAdapter
и перейдите к окно свойств. В окно свойств перечислены InsertCommand
, UpdateCommand
, DeleteCommand
и SelectCommand
, используемые TableAdapter, а также его имя и сведения о подключении. Разверните свойство , DeleteCommand
чтобы просмотреть сведения о нем. Как показано на рисунке 15, свойству DeleteCommand
s CommandType
присвоено значение Text, что указывает ему отправить текст в свойстве CommandText
в виде нерегламентированного SQL-запроса.
Рис. 15. Выберите CategoriesTableAdapter
в Designer, чтобы просмотреть его свойства в окне свойств
Чтобы изменить эти параметры, выберите текст (DeleteCommand) в окно свойств и выберите (Создать) в раскрывающемся списке. Это позволит очистить параметры свойств CommandText
, CommandType
и Parameters
. Затем задайте свойству CommandType
значение StoredProcedure
и введите имя хранимой процедуры для CommandText
(dbo.Categories_Delete
). Если вы обязательно введете свойства в этом порядке, сначала CommandType
, а затем CommandText
— Visual Studio автоматически заполнит коллекцию Parameters. Если вы не введете эти свойства в этом порядке, необходимо вручную добавить параметры с помощью Редактор Коллекции параметров. В любом случае разумно щелкнуть многоточие в свойстве Parameters, чтобы открыть коллекцию параметров Редактор, чтобы убедиться, что были внесены правильные изменения параметров (см. рис. 16). Если параметры в диалоговом окне не отображаются, добавьте @CategoryID
параметр вручную (не нужно добавлять @RETURN_VALUE
параметр ).
Рис. 16. Проверка правильности параметров
После обновления DAL удаление категории автоматически удаляет все связанные с ней продукты и делает это в рамках транзакции. Чтобы проверить это, вернитесь на страницу Обновление и удаление существующих двоичных данных и нажмите кнопку Удалить для одной из категорий. Одним щелчком мыши категория и все связанные с ней продукты будут удалены.
Примечание
Перед тестированием хранимой Categories_Delete
процедуры, которая удалит ряд продуктов вместе с выбранной категорией, может быть целесообразно создать резервную копию базы данных. Если вы используете NORTHWND.MDF
базу данных в App_Data
, просто закройте Visual Studio и скопируйте файлы MDF и LDF в App_Data
другую папку. После тестирования функциональных возможностей можно восстановить базу данных, закрыв Visual Studio и заменив текущие MDF- и LDF-файлы в App_Data
резервными копиями.
Сводка
Хотя мастер TableAdapter автоматически создает хранимые процедуры для нас, бывают случаи, когда такие хранимые процедуры уже созданы или мы хотим создать их вручную или с помощью других средств. Для реализации таких сценариев можно также настроить TableAdapter так, чтобы он указывал на существующую хранимую процедуру. В этом руководстве мы рассмотрели, как вручную добавить хранимые процедуры в базу данных с помощью среды Visual Studio и как подключить методы TableAdapter к этим хранимым процедурам. Мы также изучили команды T-SQL и шаблон скрипта, используемые для запуска, фиксации и отката транзакций в хранимой процедуре.
Счастливого программирования!
Об авторе
Скотт Митчелл (Scott Mitchell), автор семи книг ASP/ASP.NET и основатель 4GuysFromRolla.com, работает с Веб-технологиями Майкрософт с 1998 года. Скотт работает независимым консультантом, тренером и писателем. Его последняя книга Sams Teach Yourself ASP.NET 2.0 в 24 часа. Его можно связать по адресу mitchell@4GuysFromRolla.com. или через его блог, который можно найти по адресу http://ScottOnWriting.NET.
Отдельная благодарность
Эта серия учебников была проверена многими полезными рецензентами. Ведущие рецензенты этого руководства : Хилтон Geisenow, S ren Jacob Lauritsen и Тереса Мерфи. Хотите ознакомиться с моими предстоящими статьями MSDN? Если да, опустите мне строку в mitchell@4GuysFromRolla.com.
Обратная связь
https://aka.ms/ContentUserFeedback.
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделеОтправить и просмотреть отзыв по