Коллекция параметров
Иногда необходимо параметризировать SQL-запрос. Кроме того, бывает желательно связать входные и выходные аргументы хранимой процедуры с переменными программы.
Для того чтобы сделать это, следует определить свойство Parameters (Параметры) класса SqlCommand, которое является коллекцией экземпляров класса SqlParameter. Процедура инсталляции, имеющаяся на Web-узле данной книги, добавляет в базу данных Northwind хранимую процедуру get_customers. To же самое можно выполнить и вручную с помощью Server Explorer в Visual Studio .NET или SQL Query Analyzer (Анализатор запросов SQL). Еще один способ – запустить макрос SQL, поставляемый вместе с примерами к данной книге. Хранимая процедура get_customers иллюстрирует, как можно использовать простую хранимую процедуру, имеющую один аргумент, а именно – название компании, и возвращающую идентификатор (ID) клиента, т.е. указанной компании.
CREATE PROCEDURE get_customers (dcompanyname nvarchar (40), Scustomerid nchar(5) OUTPUT) AS select @customerid = CustomerlD from Customers where CompanyName = @companyname RETURN GO
Пример StoredProcedure (Хранимая процедура) демонстрирует, как это можно сделать.
command = // команда
new SqlCommand("get_customers", conn);
command › CommandType = // команда
CommandType::StoredProcedure;
SqlParameter *p = 0; p = new SqlParameter(
"@companyname",
SqlDbType::NVarChar, 40);
p › Direction = ParameterDirection::Input;
// Направление = Ввод p › set_Value(S"Ernst Handel");
// Эрнст Хандель command › Parameters › Add(p);
// команда › Параметры › Добавить
p = new SqlParameter(
"@customerid", SqlDbType::NChar, 5);
p › Direction = ParameterDirection::Output;// Направление = Вывод
command › Parameters › Add(p);
// команда › Параметры › Добавить command › ExecuteNonQuery();
// команда
Console::WriteLine(
"{0} Customerld = {!}",
command › get_Parameters() › // команда
get_Item("gcompanyname") › Value, // Значение
command › get_Parameters() › // команда
get_Item("Scustomerid") › Value); // Значение
Каждый отдельный член коллекции SqlParameterCollection, являющийся объектом SqlParameter, соответствует одному параметру SQL-запроса или хранимой процедуры. Как показано в примере, параметру не обязательно иметь какую-либо взаимосвязь с определенной таблицей или столбцом базы данных.
Тем минимумом, который необходимо определить в конструкторе или установкой свойств, являются имя и тип параметра. Если параметр имеет непостоянную длину, необходимо также определить его размер.
В приведенном примере к коллекции параметров добавляются два параметра. Первый соответствует аргументу хранимой процедуры. Второй соответствует возвращаемому хранимой процедурой значению.
Имя параметра соответствует имени аргумента хранимой процедуры get_customers. Другие параметры конструктора SqlParameter определяют тип параметра. В первом случае это строка Unicode переменного размера, длиной до 40 символов. Во втором – строка Unicode постоянного размера (5 символов). Обозначение SqlDbType::NVarChar означает постоянный по длине поток символов Unicode.
Свойство Value (Значение) используется для установки или получения значения параметра. В нашем примере оно используется для инициализации входного параметра @companyname, соответствующего аргументу хранимой процедуры. Оно используется также для получения значения параметра @customerid, соответствующего возвращаемому хранимой процедурой значению.
Выходной параметр должен быть определен как таковой с помощью свойства Direction (Направление). В нашем примере параметр @companyname устанавливается как входной присвоением этому свойству значения ParameterDirection::Input (Входной параметр). Аналогично, параметр @customerid устанавливается как выходной присвоением этому свойству значения ParameterDirection::Output (Выходной параметр). Данная операция для выходного параметра должна быть проведена обязательно, так как по умолчанию свойство Direction (Направление) имеет значение, соответствующее входному параметру.
Для того чтобы связать параметр с возвращаемым хранимой процедурой значением, используется значение ParameterDirection::ReturnValue. Для параметров, используемых в обоих направлениях, берется значение ParameterDirection::InputOutput (Входной и выходной параметр).
Имена параметров можно использовать для доступа к каждому из параметров коллекции параметров SqlCommand. Параметризованные команды могут работать как с классом SqlDataReader, так и с классом DataSet (Набор данных). Позже, при рассмотрении класса DataSet (Набор данных), мы расскажем, как определить свойство параметра Source (Источник), которое указывает, какому именно столбцу объекта DataSet (Набор данных) соответствует параметр.