Работа с параметризированным и объектами команд

В настоящий момент внутри логики вставки, обновления и удаления для типа InventoryDal используются жестко закодированные строковые литералы, представляющие каждый запрос SQL. В параметризированных запросах параметры SQL являются объектами, а не простыми порциями текста. Трактовка запросов SQL в более объектно-ориентированной манере помогает сократить количество опечаток (учитывая, что свойства строго типизированы). Вдобавок параметризированные запросы обычно выполняются значительно быстрее запросов в виде строковых литералов, т.к. они подвергаются разбору только однажды (а не каждый раз, когда строка с запросом SQL присваивается свойству CommandText). Параметризированные запросы также содействуют в защите против атак внедрением в SQL (хорошо известная проблема безопасности доступа к данным).

Для поддержки параметризированных запросов объекты команд ADO.NET содержат коллекцию индивидуальных объектов параметров. По умолчанию коллекция пуста, но в нее можно вставить любое количество объектов параметров, которые отображаются на параметры-заполнители в запросе SQL. Чтобы ассоциировать параметр внутри запроса SQL с членом коллекции параметров в объекте команды, параметр запроса SQL необходимо снабдить префиксом в виде символа @ (во всяком случае, когда применяется Microsoft SQL Server; не все СУБД поддерживают такую систему обозначений).

Указание параметров с использованием типа DbParameter

Перед построением параметризированного запроса вы должны ознакомиться с типом DbParameter (который является базовым классом для объекта параметра поставщика). Класс DbParameter поддерживает несколько свойств, которые позволяют конфигурировать имя, размер и тип параметра, а также другие характеристики, включая направление движения параметра. Некоторые основные свойства типа DbParameter описаны в табл. 21.6.

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

Обновление метода GetCar()

В исходной реализации метода GetCar() при построении строки SQL для извлечения данных об автомобиле применяется интерполяция строк С#. Чтобы обновить метод GetCar(), создайте экземпляр SqlParameter с соответствующими значениями:

SqlParameter param = new SqlParameter

{

  ParameterName = "@carId",

  Value = id,

  SqlDbType = SqlDbType.Int,

  Direction = ParameterDirection.Input

}

Значение ParameterName должно совпадать с именем, используемым в запросе SQL (который будет модифицирован следующим), тип обязан соответствовать типу столбца базы данных, а направление зависит от того, применяется параметр для отправки данных в запрос (ParameterDirection.Input) или он предназначен для возвращения данных из запроса (ParameterDirection.Output). Параметры также могут определяться как InputOutput или ReturnValue (возвращаемое значение, например, из хранимой процедуры).

Модифицируйте строку SQL для использования имени параметра ("@carid") вместо интерполированной строки C# ("{id}"):

string sql =

  @"SELECT i.Id, i.Color, i.PetName,m.Name as Make

        FROM Inventory i

        INNER JOIN Makes m on m.Id = i.MakeId

        WHERE i.Id = @CarId";

Последнее обновление связано с добавлением нового объекта параметра в коллекцию Parameters объекта команды:

command.Parameters.Add(param);

Обновление метода DeleteCar()

Аналогично в исходной реализации метода DeleteCar() применяется интерполяция строк С#. Чтобы модифицировать этот метод, создайте экземпляр SqlParameter с надлежащими значениями:

SqlParameter param = new SqlParameter

{

  ParameterName = "@carId",

  Value = id,

  SqlDbType = SqlDbType.Int,

  Direction = ParameterDirection.Input

};

Обновите строку SQL для использования имени параметра ("@ carId"):

string sql = "Delete from Inventory where Id = @carId";

В заключение добавьте новый объект параметра в коллекцию Parameters объекта команды:

command.Parameters.Add(param);

Обновление метода UpdateCarPetName()