Повторное использование кода T-SQL для извлечения данных в приложениях ADO.NET

Разработчики приложений для базы данных под ADO.NET часто используют в приложениях для извлечения данных выражения T-SQL. Когда разработчики под ADO.NET плохо понимают архитектуру базы данных, или язык T-SQL, или и то и другое, процесс создания строк на T-SQL в их приложениях может приводить к неэффективности организации доступа к данным или даже к выводу наборов данных с неправильно посчитанными значениями. В подобных ситуациях повторное использование уже отлаженного кода T-SQL из таких объектов базы данных, как хранимые процедуры и определяемые пользователем функции (UDF), дают явное преимущество. Повторное использование кода, применяемого в объектах базы данных, может помочь упростить задачи доступа к данным и обезопасить схему базы данных. Разработчики или администратор базы данных могут создать библиотеку кодов на T-SQL, которую потом будут использовать другие разработчики во многих задачах, где требуется работать с данными. И разработчики и администраторы баз данных могут заставить клиентские приложения формировать соединения от имени учетных записей, имеющих разрешение вызывать библиотечные хранимые процедуры и функции UDF, а не обязательно нижележащие объекты.

Теперь рассмотрим методы применения хранимых процедур и функций в ADO.NET и Visual Basic .NET. Мы разберем два приложения в качестве примера. Изучим таблицы Categories и Products из демонстрационной базы данных Northwind, а затем свяжем эти две таблицы по столбцам CategoryID. Можно переработать программный код этой статьи и применить его для любой пары таблиц, связанных по одной или нескольким парам полей. Примеры кодов T-SQL полезны и в другом смысле. Они демонстрируют методику, которая поможет расширить возможность повторного применения кода T-SQL. Эти методы включают использование входных параметров, выходных параметров и значений статуса возврата вместе с хранимыми процедурами, как и применение скалярных значений и таблиц значений, возвращаемых функцией UDF. Изучая и применяя эти методы в приложении, можно значительно расширить повторное использование кода T-SQL в хранимых процедурах. Несмотря на использование в этой статье Visual Basic .NET, можно задействовать любой язык .NET, программирование на котором поддерживается ADO.NET, например C#.

Загружаем библиотеку

Пример библиотеки текстов T-SQL для этой статьи содержит хранимую процедуру и три функции. Все они находятся в базе данных SSMProcFunc. Можно загрузить архивный файл, который содержит полный код с созданием базы данных и ее объектов, с сайта Windows IT Pro /RE в разделе download. Кроме того, код создает учетную запись для регистрации в базе данных SSMProcFunc и дает разрешения для осуществления вызовов объектов базы данных, содержащих код T-SQL.

Рассмотрим этот код, создающий объекты базы данных, которые будут использоваться приложением. Хранимая процедура и три функции UDF предлагают взаимозаменяемые методы для достижения одного и того же результата. Обычно используется какой-нибудь один. При применении методов не исключено, что один подход окажется предпочтительным в контексте специфического приложения.

Хранимая процедура. Хранимая процедура upDiscontinuedProducts, показанная в листинге 1, возвращает набор, выходной параметр и значение статуса возврата. Хранимая процедура принимает входной параметр @categoryID и безупречно работает с вызывающими ее приложениями, которым нужны одно или несколько скалярных значений, вроде агрегированных величин, в соответствии с результирующим набором.

В листинге 1 представлена декларация двух параметров, отделенных друг от друга запятыми. Входной параметр @CategoryID может принимать целочисленную величину от приложения, работающего, например, на ADO.NET. Хранимая процедура upDiscontinuedProducts выдает текущий номер снятых с производства продуктов, возвращая его в выходном параметре @DiscontinuedCount.

Предложение SELECT возвращает три столбца (CategoryID, Category Name и ProductName) из таблиц Categories и Products базы данных Northwind. Эти столбцы принадлежат результирующему набору, к которому посредством ADO.NET приложение может обращаться, вызывая хранимую процедуру. Оператор WHERE определяет три критерия, которые выполняют внутреннее соединение между таблицами Categories и Products.

Предложения SET и RETURN завершают хранимую процедуру. Предложение SET присваивает значение переменной @@ROWCOUNT выходному параметру @DiscontinuedCount. Переменная @@ ROWCOUNT показывает количество строк, обработанных последним предложением, которое в этой хранимой процедуре представляет собой количество снятых с производства продуктов данной категории. Предложение RETURN присваивает статусу возврата хранимой процедуры величину общего количества строк в таблице Products для категории.

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

Поскольку одна функция возвращает только одно значение, использование трех функций может заменить хранимую процедуру upDiscontinued Products. Применительно к этому приложению лучше использовать хранимую процедуру, но ее код будет сложнее, чем код любой из трех UDF. Кроме того, можно сослаться на возвращаемое из табличной UDF значение в операторе FROM. Язык запросов T-SQL не поддерживает ссылки на набор данных от хранимой процедуры в операторе FROM.

Код первой функции ufProductsIn Category представлен в листинге 2. Эта функция возвращает общее количество строк из таблицы Products, соответствующих значению входного параметра @CategoryID, получаемого вызывающим приложением. Код листинга 2 сначала удаляет любую предыдущую версию функции. Поскольку и хранимая процедура, и функция могут применять идентичный код для удаления предварительно существующего объекта, желательно использовать различные имена для хранимых процедур и функций.

Код второй функции ufDiscontinued ProductsInCategory почти идентичен коду листинга 2, но эта функция возвращает количество только для снятых с производства продуктов заданной категории. Чтобы создать функцию ufDiscontinuedProductsInCategory, достаточно просто изменить имена ссылок в предложениях DROP и CREATE и переписать предложение SELECT в листинге 2 следующим образом:

SELECT COUNT(*)
FROM Northwind.dbo.Products AS np
WHERE np.CategoryID = @CategoryID
AND Discontinued = 1

Третья функция ufDiscontinuedProduct RowsInCategory представлена в листинге 3. Это функция, возвращающая значение в виде таблицы. Этот тип функции возвращает набор строк, к которому можно обратиться с помощью оператора FROM в предложении SELECT. Заметим, что в операторе RETURNS функции определяет табличный тип данных. Оператор RETURN включает в себя предложение SELECT, заполняющее переменные таблицы. Это предложение SELECT — то же самое, что и в хранимой процедуре upDiscontinuedProducts.

Получение значений от хранимых процедур

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

Форма Form1 состоит из кнопки Button 1, текстового поля, описания и списка для отображения значений, возвращаемых хранимой процедурой upDiscontinuedProducts. На экране 1 показаны значения, возвращаемые хранимой процедурой для параметра @CategoryID, значение которого равно 6. Пользователь вводит значение @CategoryID в текстовое поле и щелкает на Button1. Процедура Button1_ Click заполняет описание вверху списка и добавляет в список название каждого переставшего выпускаться продукта заданной категории.

Код формы Form1 показывает, как извлекать все типы значений из хранимой процедуры на Visual Basic .NET. Для сбора значений хранимой процедуры нужно начать с создания соединения к базе данных SQL Server, содержащей хранимую процедуру. Для извлечения и отображения значений хранимой процедуры нужно создать объект SqlCommand, представляющий хранимую процедуру, настроить параметры объекта и извлечь набор данных, используя один из двух методов. Рассмотрим каждую задачу более подробно.

Соединение к базе данных. Перед тем как принимать значения от хранимой процедуры, следует соединиться с базой данных, в которой эта процедура содержится. Создать соединение можно двумя путями: используя тип доступа Windows или SQL Server. Здесь код процедуры Button1_Click в форме Form1 использует тип доступа Windows.

Работая с доступом Windows, следует убедиться, что все пользователи имеют учетные записи Windows, соответствующие учетным записям для регистрации в экземпляре SQL Server. В свою очередь, эти учетные записи должны соответствовать учетным записям, имеющим разрешение на вызов объектов базы данных, на которые это приложение опирается (например, на вызов хранимой процедуры upDiscontinuedProducts). Код листинга 1 содержит код процедуры Button1_Click, в которой созданный объект SqlConnection вызывает метод Open для соединения с базой данных SSMProcFunc.

Код процедуры Button1_Click использует предложение Import для пространства имен System.Data.SqlClient:

Imports System.Data.SqlClient

Предложение Import в пространстве имен может упростить ссылки к типам пространства имен вроде классов SqlConnection и Sql.Command в пространстве имен System.Data.SqlClient.

Создание объекта SqlCommand. В листинге 5 показан кусок кода из процедуры Button1_Click формы Form1. Код в метке A создает объект SqlCommand (cmd1), который указывает на хранимую процедуру upDiscontinuedProducts, ссылаясь на имя процедуры в конструкторе объекта SqlCommand с ключевым словом NEW. Нужно дать свойству CommandType объекта SqlCommand значение, которое указывает на использование хранимой процедуры. Дополнительно к имени хранимой процедуры конструктор объекта SqlCommand ссылается на cnn1, соединение, используемое объектом SqlCommand для связи с базой данных SSMProcFunc.

Настройка параметров объекта SqlCommand. Настройка параметров объекта SqlCommand (т. е. параметров для хранимой процедуры upDiscontinuedProducts) слегка меняется в зависимости от типа входных или выходных скалярных величин, на которые происходит ссылка. Для входного параметра вроде @CategoryID можно вызывать метод Add из коллекции Parameters объекта SqlCommand. Следует задать название параметра и тип данных. Также нужно назначить входному параметру значение вроде Integer, основанное на свойстве Text объекта TextBox1. В метке B в листинге 5 выделен код, который ссылается на параметр хранимой процедуры @CategoryID.

Как показано в коде листинга 5 по метке C, код, который ссылается на выходной параметр @Discontinued Count, похож на код, ссылающийся на входной параметр. В этом случае не нужно указывать значение параметра, но требуется задать направление вывода.

Также требуется связать параметр объекта SqlCommand со значением статуса возврата хранимой процедуры, как сделано в коде по метке D. Для этой задачи не имеет значения имя, используемое для ссылки на параметр. Например, foo будет работать так же, как @NumberOfProducts. Тем не менее обязательно для выполнения назначение аргумента Return Value в свойстве параметра Direction.

Сбор результирующего набора. После создания и настройки объекта SqlCommand для работы хранимой процедуры можно извлекать результирующий набор. Предлагается на выбор два метода: один основан на объекте SqlDataReader, другой — на объекте DataSet. Эти два подхода годятся как для хранимой процедуры, так и для табличных функций. Для этого примера мы будем использовать объект SqlDataReader.

Код, который можно найти по метке E, показывает, что для создания и заполнения объекта SqlDataReader применяется метод ExecuteReader объекта SqlCommand. Теперь можно проходить по результирующему набору процедуры предложением цикла Do, который считывает значения до тех пор, пока в результирующем наборе есть строки. Предложение внутри цикла Do добавляет элементы из подходящих строк результирующего набора к списку.

Код, который можно найти по метке F, показывает, что нужно закрывать объект SqlDataReader перед попыткой получить значения статуса возврата выходного параметра из хранимой процедуры. Только затем можно объединять параметр вывода и значения статуса возврата с текстом, присваиваемым свойству Text объекта Label2. Кроме того, в коде объект ListBox1 делается видимым только тогда, когда есть снятые с производства продукты. Процедура события Button1_Click заканчивается закрытием соединения к базе SSMProcFunc.

Извлечение значений из функций

Форма Form2 из проекта SSMProcFunc перехватывает три функции из базы данных SSMProcFunc. Код формы использует объект DataSet вместо объекта SqlDataReader для обращения к набору значений как результату при выборке снятых с производства продуктов по заданной категории. На экране 2 показана Form2 с результатом для @CategoryID, равным 7. Вместо представления снятых с производства продуктов списком эта форма использует объект управления DataGrid, который наполняется объектом DataSet. Запуская проект, можно открыть форму Form2, выбирая ее как объект Startup из Property Pages проекта. Извлечение результирующего набора функций UDF включает процесс, подобный извлечению результирующего набора хранимой процедурой.

Соединение с базой данных. Код из листинга 6 демонстрирует соединения с базой SSMProcFunc. Пользователи инициируют это подключение, когда нажимают на кнопку Button1 в форме Form2. Эта строка подключения отличается от строки подключения из формы Form1, в которой строка определяет учетную запись в SQL Server (SSMProcFuncU1) и пароль (SSMProcFunc). Перед тем как выполнять код формы Form2, следует убедиться, что выполнен код T-SQL сценария SSMProcFunc.sql, в котором создается учетная запись и предоставляются соответствующие разрешения для функций UDF в базе данных SSMProcFunc.

Извлечение и отображение значений функций. В листинге 7 показан кусок кода обработки Button1_Click из формы Form2, который извлекает и отображает данные от трех функций из библиотеки кодов базы данных SSMProcFunc. Комбинация использования функций UDF как источника значений и заполнения DataGrid с объектом DataSet помогает быстрее писать код и обеспечивает гибкость в способе его написания. Например, при таком подходе легче использовать один объект SqlCommand для извлечения множества возвращаемых значений, независимо от того, являются они скалярными или табличными.

Код в листинге 7 по метке A инициализирует и настраивает объект SqlCommand, который представляет собой функцию ufProductsInCategory. Вспомним, что эта функция возвращает скалярную величину. Код для настройки @CategoryID подобен коду, который использовался для настройки входного параметра хранимой процедуры. Вызывая метод ExecuteScalar, код копирует значение функции в переменную int1, которую предшествующее предложение Dim объявляет с целочисленным типом.

Код, который можно найти в листинге 7 по метке B, показывает, как повторно использовать тот же самый объект SqlCommand для извлечения значения функции ufDiscontinuedProductsInCategory. Эта задача выполняется в два этапа. Сначала объекту SqlCommand назначается новое свойство CommandText, которое указывает на новую функцию. Потом в новой переменной сохраняется значение, возвращающее метод ExecuteScalar. Новая переменная int2 также имеет целочисленный тип данных.

Код, который можно найти по метке C, заполняет свойство Text объекта Label2, так же как это сделано в форме Form1. Только в форме Form2 используются переменные int1 и int2 вместо переменных prm1 и prm2.

Код, который можно найти по метке D, показывает, как повторно задействовать объект SqlCommand. Объект SqlCommand используется как аргумент для конструктора объекта SqlDataAdapter. Затем для заполнения набора данных DataSet (das1) вызывается метод Fill объекта SqlDataAdapter. Таблица в das1 — Tables(0), которая содержит результат функции, служит источником данных управляющего объекта DataGrid.

Таким образом, разработчики под ADO.NET могут задействовать хранимые процедуры и функции UDF с их мощной защитой и простотой использования вместо строк T-SQL. Предложенные в статье приемы извлечения и вывода значений, полученных от хранимых процедур и функций UDF, помогут создавать решения для доступа к данным, которые не только проще в использовании, но и безопаснее, чем те решения, которые создавались бы с помощью строк T-SQL.

Рик Добсон - Автор, тренер и Web-мастер, специализирующийся на базах данных Microsoft SQL Server, Visual Basic .NET и Web-приложениях для работы с базами данных. www.programmingmsaccess.com

# Листинг 1. Хранимая процедура

upDiscontinuedProducts
CREATE PROC upDiscontinuedProducts
@CategoryID int,
@DiscontinuedCount int OUTPUT
AS
SELECT nc.CategoryID, nc.CategoryName,
   np.ProductName
FROM Northwind.dbo.Categories AS nc,
   Northwind.dbo.Products AS np
WHERE nc.CategoryID = np.CategoryID AND
   np.Discontinued = 1 AND
   np.CategoryID = @CategoryID
SET @DiscontinuedCount = @@ROWCOUNT
RETURN (SELECT COUNT(*)
   FROM Northwind.dbo.Products
   WHERE CategoryID = @CategoryID)

# Листинг 2. Функция ufProductsIn Category

IF EXISTS(
   SELECT * FROM INFORMATION_SCHEMA.ROUTINES
   WHERE ROUTINE_NAME = ‘ufProductsInCategory’)
DROP FUNCTION ufProductsInCategory
GO

CREATE FUNCTION ufProductsInCategory(@CategoryID AS int)
RETURNS int
AS
Начало
   RETURN(SELECT COUNT(*)
   FROM Northwind.dbo.Products AS np
   WHERE np.CategoryID = @CategoryID)
Конец
GO

# Листинг 3. Функция ufDiscontinued ProductRowsIn Category

CREATE FUNCTION
   ufDiscontinuedProductRowsInCategory(
   @CategoryID int)
RETURNS table
AS
RETURN(
   SELECT nc.CategoryID, nc.CategoryName,
      np.ProductName
   FROM Northwind.dbo.Categories AS nc,
      Northwind.dbo.Products AS np
   WHERE nc.CategoryID = np.CategoryID AND
      np.Discontinued = 1 AND
      np.CategoryID = @CategoryID)

# Листинг 4. Формирование соединения к базе данных

SSMProcFunc с учетной записью Windows. 
Dim cnn1 AS New SqlConnection( _
   «Data Source=(local);» & _
   «Initial Catalog=SSMProcFunc;» & _
   «Integrated Security=SSPI») 
cnn1.Open

# Листинг 5. Создание и настройка объекта SqlCommand, заменяющего Discontinued Products

— Начало метки A
Dim cmd1 AS New SqlCommand( _
   «upDiscontinuedProducts»,cnn1)
cmd1.CommandType = CommandType.StoredProcedure
— Конец метки A

— Set SqlCommand parameters:
— prm1 is an input parameter.
— prm2 is an output parameter.
— prm3 is a return value.
— Начало метки B
Dim prm1 AS SqlParameter = _
cmd1.Parameters.Add(«@CategoryID», _
   SqlDbType.Int)
prm1.value = integer.Parse(textbox1.Text)
— Конец метки B
— Начало метки C
Dim prm2 AS SqlParameter = _
cmd1.Parameters.Add(«@DiscontinuedCount», _
   SqlDbType.Int)
prm2.Direction=ParameterDirection.Output
— Конец метки C
— Начало метки D
Dim prm3 AS SqlParameter = _
cmd1.Parameters.Add(«@NumberOfProducts», _
   SqlDbType.Int)
prm3.Direction=ParameterDirection.ReturnValue
— Конец метки D

— Начало метки E
— Create a SqlDataReader object by
— invoking the ExecuteReader method.
Dim rdr1 AS SqlDataReader = _
cmd1.ExecuteReader()

— Loop through the SqlDataReader object and populate
— a list box.
ListBox1.Items.Clear
Do WHILE rdr1.Read
ListBox1.Items.Add (rdr1.GetString(2))
Loop
— Конец метки E

— Начало метки F
— Close the SqlDataReader.
rdr1.Close
— Конец метки F

— Assign the output parameter and return
— status values to a label; manage the visibility
— of a list box based on whether there are
— discontinued products to show.
Label2.Text = prm2.Value & _
   « discontinued products from a total « & _
   «of» & prm3.Value & «.»
IF prm2.Value=0 THEN
   ListBox1.Visible = False
ELSE
   ListBox1.Visible = True
Конец IF

cnn1.Close

# Листинг 6. Соединение с базой данных SSMProcFunc с использованием учетной записи SQL Server

Dim cnn1 AS New SqlConnection( _
   «Data Source=(local);» & _
   «Initial Catalog=SSMProcFunc;» & _
   «User ID = SSMProcFuncU1; « & _
   «Password= SSMProcFunc»)
cnn1.Open

# Листинг 7. Извлечение и вывод данных из трех UDF

— Начало метки A
dim cmdStr = «SELECT « & _
   «dbo.ufProductsInCategory(@CategoryID)»
dim cmd1 AS New SqlCommand(cmdStr, cnn1)
Dim prm1 AS SqlParameter = _
   cmd1.Parameters.Add(«@CategoryID», _
   SqlDbType.Int)
prm1.value = integer.Parse(Textbox1.Text)
int1 = cmd1.ExecuteScalar
— Конец метки A

— Начало метки B
cmdStr = «SELECT dbo.ufDiscontinued» & _
   «ProductsInCategory(@CategoryID)»
cmd1.CommandText = cmdStr
int2 = cmd1.ExecuteScalar
— Конец метки B

— Начало метки C
Label2.Text = int2 & « discontinued « & _
   «products from a total of « & int1 & «.»
— Конец метки C

— Начало метки D
cmdStr = «SELECT CategoryName, ProductName « & _
   «FROM ufDiscontinuedProductRowsInCategory» & _
   «(@CategoryID)»
cmd1.CommandText = cmdStr
dim dap1 AS New SqlDataAdapter(cmd1)
dap1.Fill(das1)
DataGrid1.DataSource = das1.Tables(0)
— Конец метки D

Источник: Osp.ru