Параметры команд адаптеров обработки данных

Статья
11/05/2008

Обновлен: Ноябрь 2007

Команды адаптера данных, определенные в свойстве CommandText объектов SelectCommand, InsertCommand, UpdateCommand и DeleteCommand, часто включают в себя параметры. Во время выполнения параметры используются для передачи значений инструкциям SQL или хранимым процедурам, представленным командами.

Примечание.
В предыдущей версии Visual Studio адаптеры данных использовались для связи между приложением и базой данных. Хотя адаптеры данных по-прежнему являются основным компонентом Поставщики данных .NET Framework (ADO.NET), создаваемые с помощью конструктора адаптеры таблиц упрощают процесс перемещения данных между приложением и базой данных. Дополнительные сведения по работе с адаптерами таблиц содержатся в разделе Общие сведения об адаптере таблиц.

В предыдущей версии Visual Studio адаптеры данных использовались для связи между приложением и базой данных. Хотя адаптеры данных по-прежнему являются основным компонентом Поставщики данных .NET Framework (ADO.NET), создаваемые с помощью конструктора адаптеры таблиц упрощают процесс перемещения данных между приложением и базой данных. Дополнительные сведения по работе с адаптерами таблиц содержатся в разделе Общие сведения об адаптере таблиц.

Параметры используются в двух контекстах.

Параметры выбора — при создании приложений часто выполняется выборка только подмножества данных в базе данных. Для этого используется инструкция SQL или хранимая процедура, содержащая предложение WHERE с параметром для критериев выбора, определяемых во время выполнения приложения. Кроме того, при обновлении или удалении записей используется предложение WHERE, точно определяющее местонахождение изменяемой записи или записей. Значения, используемые в предложении WHERE, обычно определяются во время выполнения приложения.

Параметры обновления — при обновлении существующей записи или вставке новой. Значения для столбцов в измененной или новой записи определяются во время выполнения. Кроме того, с помощью параметров задаются значения, используемые для оптимистической блокировки.

Примечание.
В Oracle при использовании именованных параметров в инструкции SQL или сохраненной процедуре перед именем параметра необходимо поставить двоеточие (:). Однако при ссылке на именованный параметр в другом месте кода (например, при вызове Add) двоеточие перед именованным параметром ставить не нужно. Поставщик данных добавляет двоеточие автоматически. Дополнительные сведения содержатся в разделе Класс OracleParameter.

В Oracle при использовании именованных параметров в инструкции SQL или сохраненной процедуре перед именем параметра необходимо поставить двоеточие (:). Однако при ссылке на именованный параметр в другом месте кода (например, при вызове Add) двоеточие перед именованным параметром ставить не нужно. Поставщик данных добавляет двоеточие автоматически. Дополнительные сведения содержатся в разделе Класс OracleParameter.

Параметры выбора

При выборе записей для заполнения набора данных, в предложение WHERE включаются один или несколько параметров, чтобы в процессе выполнения пользователь мог указать записи, которые необходимо выбрать. Например, пользователи имеют возможность искать книгу в базе данных по определенному ключевому слову заголовка, которое они вводят на веб-странице. Для этого в свойстве CommandText метода SelectCommand указывается инструкция SQL, подобная той, что приведена ниже. Параметры указываются либо при помощи символа-заполнителя (знак вопроса), либо именованной переменной параметра. В параметрах для запросов, содержащих объекты OleDbCommand и OdbcCommand, используются знаки вопроса; в запросах, использующих объекты SqlCommand, используются именованные параметры, которые начинаются с символа "@", тогда как в объектах OracleCommand используются именованные параметры, начинающиеся с двоеточия (:).

Ниже приведен пример запроса, в котором используется символ-заполнитель.

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE ?)

Запрос, в котором используются именованные параметры SqlCommand, может выглядеть следующим образом:

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE @title)

Запрос, в котором используются именованные параметры OracleCommand, может выглядеть следующим образом:

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE :title)

В приложении пользователю предлагается ввести заголовок в качестве ключевого слова. Затем задается значение параметра и выполняется команда.

Примечание.
Иногда может возникнуть необходимость получения всего содержимого таблицы базы данных (например, при настройке таблицы поиска), но обычно извлекаются только данные, которые необходимы для эффективной работы приложения.

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

В Visual Studio можно создавать инструкции SQL с параметрами, используя для этого построитель запросов. При перетаскивании элементов из обозревателя серверов Visual Studio может настроить параметры только в некоторых случаях, поэтому завершение настройки выполняется вручную.

Параметры обновления

Вне зависимости от того, содержит ли объект SelectCommand параметризованную команду, команды для свойств UpdateCommand, InsertCommand и DeleteCommand выполняются всегда.

Командам для свойств UpdateCommand и InsertCommand необходимы параметры для каждого столбца в обновляемой базе данных. Кроме того, для инструкций UpdateCommand и DeleteCommand необходимо предложение WHERE с заданными параметрами, которое идентифицирует обновляемую запись подобно тому, как это делается для объекта SelectCommand.

Представим себе приложение, в котором пользователи покупают книги. При покупке пользователи сохраняют покупательскую корзину, которая реализуется как таблица данных. В таблице ShoppingCart пользователи сохраняют запись для каждой книги, которую они хотят приобрести, вместе с идентификаторами книги и пользователя, которые являются ключами к записи в покупательской корзине.

Если пользователи добавляют книгу к своей покупательской корзине, приложение вызывает инструкцию SQL INSERT. В адаптере синтаксис инструкции выглядит следующим образом:

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (?, ?, ?)

Три знака вопроса указывают на три параметра, которые во время выполнения заполнятся значениями идентификатора пользователя, идентификатора книги и количества. Если используются именованные параметры, тот же запрос выглядит следующим образом.

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (@bookid, @custid, @quantity)

Если пользователь решит внести изменения в покупательскую корзину (например, изменить количество), приложение вызовет инструкцию SQL UPDATE. Синтаксис инструкции выглядит следующим образом:

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

А при использовании именованных параметров — следующим образом:

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

В этой инструкции параметры в предложении SET заполняются обновленными значениями для изменяемой записи. Параметры в предложении WHERE идентифицируют обновляемую запись и заполняются исходными значениями из записи.

Пользователь может также удалить позицию из покупательской корзины. В этом случае приложение вызывает инструкцию SQL DELETE со следующим синтаксисом, если используется местозаполнитель параметра:

DELETE FROM ShoppingCart
WHERE (BookId = ? AND CustId = ?)

При использовании именованных параметров синтаксис выглядит следующим образом.

DELETE FROM ShoppingCart
WHERE (BookId = @bookid AND CustId = @custid)

Коллекция параметров и объекты параметров

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

В следующей таблице представлены коллекции параметров для каждого адаптера данных.

Адаптер данных	Коллекция параметров
SqlDataAdapter	SqlParameterCollection
OleDbDataAdapter	OleDbParameterCollection
OdbcDataAdapter	OdbcParameterCollection
OracleDataAdapter	OracleParameterCollection

Примечание.
В Oracle при использовании именованных параметров в инструкции SQL или сохраненной процедуре перед именем параметра необходимо поставить двоеточие (:). Однако при ссылке на именованный параметр в другом месте кода (например, при вызове Add) двоеточие перед именованным параметром ставить не нужно. Поставщик данных .NET Framework для Oracle добавляет двоеточие автоматически.

В Oracle при использовании именованных параметров в инструкции SQL или сохраненной процедуре перед именем параметра необходимо поставить двоеточие (:). Однако при ссылке на именованный параметр в другом месте кода (например, при вызове Add) двоеточие перед именованным параметром ставить не нужно. Поставщик данных .NET Framework для Oracle добавляет двоеточие автоматически.

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

Если для настройки адаптера используется мастер настройки адаптера данных, коллекция параметров настраивается автоматически для всех четырех команд адаптера. Если элементы перетаскиваются из обозревателя серверов на форму или компонент, Visual Studio выполняет следующие настройки.

Если перетащить таблицу или некоторые столбцы в конструктор, Visual Studio создаст объект SelectCommand (в частности, SQL инструкцию SELECT) без параметров и параметризованные объекты UpdateCommand, InsertCommand и DeleteCommand. Если для инструкции объекта SelectCommand нужны параметры, они настраиваются вручную.
Если на поле конструктора перетаскивается хранимая процедура, Visual Studio создает объект SelectCommand с параметрами, необходимыми для хранимой процедуры. Однако, в этом случае необходимо самостоятельно настроить объекты UpdateCommand, InsertCommand и DeleteCommand вместе с их параметрами.

Вообще говоря, при создании запросов с заданными параметрами для адаптера необходимо использовать мастер настройки адаптера данных. Однако в случае необходимости параметры могут настраиваться вручную с помощью окна "Свойства".

Структура коллекции параметров

Элементы в коллекции параметров команды соответствуют параметрам, необходимым для соответствующего командного объекта, один к одному. Если командный объект является инструкцией SQL, элементы в коллекции соответствуют местозаполнителям (знакам вопроса) в инструкции. Для следующей инструкции UPDATE требуется коллекция из пяти элементов параметра:

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

Для той же инструкции с именованными параметрами имеем следующее:

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

Если командный объект ссылается на хранимую процедуру, число элементов параметра в коллекции определяется самой процедурой. Допускается неполное соответствие параметров местозаполнителям в инструкции SQL.

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

Если настройка коллекции параметров выполняется вручную, необходимо четко понимать, какие параметры требуются хранимой процедуре. Многие хранимые процедуры возвращают значение. В этом случае значение передается назад приложению в коллекции параметров, и это необходимо учитывать. Кроме того, некоторые хранимые процедуры содержат несколько инструкций SQL, и необходимо быть уверенным в том, что в коллекции параметров отражены все значения, переданные всем инструкциям в процедуре.

Если параметры не являются именованными, элементы в коллекции поочередно сопоставляются с параметрами, требуемыми командой. Если команда является хранимой процедурой и возвращает значение, первый элемент в коллекции (с нулевым индексом) резервируется для этого возвращаемого значения.

Это позволяет ссылаться на отдельные объекты параметров по позиции индекса в коллекции. Однако, объекты параметров также поддерживают свойство ParameterName, которое предоставляет способ ссылки на параметры независимо от их порядка. Например, следующие две инструкции эквивалентны (предполагается, что второй параметр в коллекции назван Title_Keyword):

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters(1).Value = titleKeyword
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword

// Encloses the keyword in SQL wildcard characters.
string titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters[1].Value = titleKeyword;
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;

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

Установка значений параметров

Существует два способа установки значения параметра.

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

Значение параметра устанавливается явно, когда заполняется набор данных или вызывается команда (т. е. для параметров выбора). Например, в вышеприведенном примере поиска книг приложение может иметь текстовое окно, в котором пользователи вводят ключевое слово заголовка. В этом случае перед вызовом метода адаптера Fill значению параметра явно присваивается текст из текстового окна. Предназначенный для этих целей код задает содержимое текстового окна в качестве параметра перед заполнением набора данных:

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword
OleDbDataAdapter1.Fill(dsAuthors1)

// Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;
this.OleDbDataAdapter1.Fill(dsAuthors1);

В процессе обновления данных используются сопоставленные значения параметров. При вызове метода Update происходит перебор записей в таблице набора данных, а затем для каждой записи выполняется соответствующее изменение (вставка, удаление, обновление). В этом случае значения параметров уже доступны в качестве столбцов в записях наборов данных. Например, когда процесс обновления выполняется для новой записи в таблице набора данных (записи, для которой вызывается инструкция INSERT в базе данных), значения для предложения VALUE инструкции INSERT можно считывать непосредственно из записи.

Это типичные, но не единственно возможные сценарии. Хранимые процедуры иногда возвращают данные с помощью выходных параметров или возвращаемого значения процедуры. В этом случае необходимо сопоставить возвращенные значения столбцам в таблице набора данных.

Также имеется возможность явно установить обновленные параметры. Адаптер поддерживает событие RowUpdating, вызываемое при каждом обновлении строки. Пользователь может создать обработчик для этого события и установить значения параметров. Это позволяет очень точно управлять значениями параметров и выполнять определенные процессы, например задавать такие значения динамически непосредственно перед записью в базу данных.