4.6. Параметризированные запросы

Запросы к базе данных могут содержать параметры. Рассмотрим запрос со следующим текстом:

SELECT name FROM Artists WHERE id = @id

В данном примере параметром является @id, а запрос означает следующее: получить из таблицы Artists все значения колонки name таких записей, у которых колонка id равна параметру @id. Вот пример предыдущего запроса для поставщика OLE DB (маркером параметра является символ «?»):

SELECT name FROM Artists WHERE id = ?

Для работы с параметрами поставщики данных определяют особые классы (например, SqlParameter). Некоторые свойства параметров перечислены ниже (не каждый параметр требует определения всех свойств):

• ParameterName. Имя параметра. У поставщика данных SQL Server имя любого параметра предваряется символом @. Другие поставщики могут не использовать специальных имен вовсе, а определять параметры по позиции.

• DbType. Тип хранящихся в параметре данных. Перечисление DbType содержит элементы, которые можно использовать как значения данного свойства. Кроме этого, каждый поставщик имеет перечисления либо классы, более точно отражающие реальный тип данных СУБД. Например, поставщик SQL Server содержит перечисление SqlDbType.

• Size. Свойство зависит от типа данных параметра и обычно используется для указания его максимальной длины. Например, для строковых типов (VarChar) свойство Size представляет максимальный размер строки. Значение по умолчанию определяется по свойству DbType. В случае числовых типов изменять это значение не требуется.

• Direction. Данное свойство определяет способ передачи параметра хранимой процедуры. Его возможные значения – Input, Output, InputOutput и ReturnValue – представлены перечислением ParameterDirection. По умолчанию используется значение Input.

• IsNullable. Это свойство определяет, может ли параметр принимать пустые значения. По умолчанию свойство установлено в false.

• Value. Значение параметра. Для параметров типа Input и InputOutput это свойство должно быть установлено до выполнения команды, для параметров типа Output, InputOutput и ReturnValue значение свойства устанавливается в результате выполнения команды. Чтобы передать пустой входной параметр, нужно либо не устанавливать значение свойства Value, либо установить его равным DBNull.

• Precision. Определяет число знаков после запятой, использующихся для представления значений параметра. По умолчанию имеет значение 0.

• Scale. Определяет общее число десятичных разрядов для представления параметра.

• SourceColumn и SourceVersion. Данные свойства определяют способ использования параметров с объектом DataAdapter и подробнее будут рассмотрены ниже.

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

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

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "SELECT name FROM Artists WHERE id = @id";

. . .

SqlParameter p = new SqlParameter();

p.ParameterName = "@id";

p.Direction = ParameterDirection.Input;

p.DbType = DbType.Int32;

cmd.Parameters.Add(p);

Для создания и добавления параметра часто достаточно воспользоваться одной из перегруженных версий метода Add() коллекции параметров:

cmd.Parameters.Add("@id", DbType.Int32);

Следующий пример показывает выборку данных из таблицы с использованием параметризированного запроса:

SqlConnection con = new SqlConnection();

con.ConnectionString = "Server=(local);Database=CD_Rent;" +

"Integrated Security=SSPI";

string s = "SELECT name FROM Artists WHERE id = @id";

SqlCommand cmd = new SqlCommand(s, con);

cmd.Parameters.Add("@id", DbType.Int32);

while (true) {

Console.Write("Input id: ");

cmd.Parameters["@id"].Value =

Int32.Parse(Console.ReadLine());

con.Open();

string name = (string)cmd.ExecuteScalar();

Console.WriteLine(name);

con.Close();

}

Заметим, что параметризированные запросы зачастую удобнее сформировать, воспользовавшись функциями для работы со строками (и такой метод более быстрый, чем работа с объектами-параметрами). Однако без параметров не обойтись, если речь заходит о вызове хранимой процедуры на сервере БД.

Пусть на сервере MS SQL Server описана хранимая процедура get_id:

CREATE PROCEDURE get_id @sp_name varchar(50) AS

RETURN (SELECT id FROM Artists WHERE name = @sp_name)

GO

Данная процедура получает в качестве параметра строку с именем исполнителя и возвращает идентификатор данной строки в таблице Artists¹.

Следующий код работает с хранимой процедурой при помощи параметров:

// Создаем и настраиваем соединение

SqlConnection con = new SqlConnection();

con.ConnectionString = "Server=(local);Database=CD_Rent;" +

"Integrated Security=SSPI";

// Создаем команду, текст команды – имя хранимой процедуры

SqlCommand cmd = new SqlCommand("get_id", con);

// Требуется изменить тип команды

cmd.CommandType = CommandType.StoredProcedure;

// Создаем и настраиваем первый (входной) параметр

SqlParameter p1 = new SqlParameter();

p1.ParameterName = "@sp_name";

p1.DbType = DbType.String;

cmd.Parameters.Add(p1);

// Настраиваем параметр, соответствующий выходному значению

SqlParameter p2 = new SqlParameter();

// У такого параметра особое имя!

p2.ParameterName = "@RetVal";

p2.DbType = DbType.Int32;

p2.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(p2);

// Запрашиваем у пользователя информацию...

Console.Write("Input name to find the id: ");

cmd.Parameters["@sp_name"].Value = Console.ReadLine();

// ...и возвращаем ему результат

con.Open();

cmd.ExecuteNonQuery();

Console.WriteLine(cmd.Parameters["@RetVal"].Value);

con.Close();

Заметим, что для удобной работы с хранимыми процедурами в клиентском приложении часто описываются специальные методы-«оболочки». Создание подобного метода оставляем читателям в качестве упражнения.