Хранимые процедуры

Задание параметров с помощью типа DbParameter

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

Пока в логике вставки, изменения и удаления для типа InventoryDAL мы использовали жестко закодированные строковые литералы для каждого SQL-запроса. Вы, видимо, знаете о существовании параметризованных запросов, которые позволяют рассматривать параметры SQL как объекты, а не просто кусок текста.

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

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

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

DbType	Выдает или устанавливает тип данных из параметра, представляемый в виде типа CLR
Direction	Выдает или устанавливает вид параметра: только для ввода, только для вывода, для ввода и для вывода или параметр для возврата значения
IsNullable	Выдает или устанавливает, может ли параметр принимать пустые значения
ParameterName	Выдает или устанавливает имя DbParameter
Size	Выдает или устанавливает максимальный размер данных для параметра (полезно только для текстовых данных)
Value	Выдает или устанавливает значение параметра

Для демонстрации заполнения коллекции объектов команд совместимыми с DBParameter объектами переделаем метод InsertAuto() так, что он будет использовать объекты параметров (аналогично можно переделать и все остальные методы, но нам будет достаточно и настоящего примера):

public void InsertAuto(int id, string color, string make, string petName) { // Оператор SQL string sql = string.Format("Insert Into Inventory" + "(CarID, Make, Color, PetName) Values('{0}','{1}','{2}','{3}')", id, make, color, petName); // Параметризованная команда using (SqlCommand cmd = new SqlCommand(sql, this.connect)) { SqlParameter param = new SqlParameter(); param.ParameterName = "@CarID"; param.Value = id; param.SqlDbType = SqlDbType.Int; cmd.Parameters.Add(param); param = new SqlParameter(); param.ParameterName = "@Make"; param.Value = make; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); param = new SqlParameter(); param.ParameterName = "@Color"; param.Value = color; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); param = new SqlParameter(); param.ParameterName = "@PetName"; param.Value = petName; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); } }

Обратите внимание, что здесь SQL-запрос также содержит четыре символа-заполнителя, перед каждым из которых находится символ @. С помощью свойства ParameterName в типе SqlParameter можно описать каждый из этих заполнителей и задать различную информацию (значение, тип данных, размер и т.д.), причем строго типизированным образом. После подготовки всех объектов параметров они добавляются в коллекцию объекта команды с помощью вызова Add().

Для оформления объектов параметров здесь используются различные свойства. Однако учтите, что объекты параметров поддерживают ряд перегруженных конструкторов, которые позволяют задавать значения различных свойств (что дает более компактную кодовую базу). Учтите также, что в Visual Studio 2010 имеются различные графические конструкторы, которые автоматически создадут за вас большой объем этого утомительного кода работы с параметрами.

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

Хранимая процедура (stored procedure) — это именованный блок SQL-кода, хранимый в базе данных. Хранимые процедуры можно создавать для возврата набора строк или скалярных типов данных, а также выполнения других нужных действий (например, вставки, обновления или удаления); они могут принимать любое количество необязательных параметров. В результате получается рабочая единица, которая ведет себя как типичная функция, только она находится в хранилище данных, а не в двоичном бизнес-объекте.

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

На данном этапе в базе данных AutoLot определена одна хранимая процедура с именем GetPetName, имеющая следующий формат:

ALTER PROCEDURE GetPetName@carID int,@petName char(10) outputAS SELECT @petName = PetName from Inventory where CarID = @carID

Давайте добавим вызов хранимой процедуры в сборку AutoLotDAL.dll, которая рассматривалась в предыдущей статье:

public string LookUpPetName(int carId) { string carPetName = string.Empty; // Задание имени хранимой процедуры using (SqlCommand cmd = new SqlCommand("GetPetName", this.connect)) { cmd.CommandType = CommandType.StoredProcedure; // Входной параметр. SqlParameter param = new SqlParameter(); param.ParameterName = "@carID"; param.SqlDbType = SqlDbType.Int; param.Value = carId; //По умолчанию параметры считаются входными, но все же для ясности: param.Direction = ParameterDirection.Input; cmd.Parameters.Add(param); // Выходной параметр. param = new SqlParameter(); param.ParameterName = "@petName"; param.SqlDbType = SqlDbType.Char; param.Size = 10; param.Direction = ParameterDirection.Output; cmd.Parameters.Add(param); // Выполнение хранимой процедуры. cmd.ExecuteNonQuery(); // Возврат выходного параметра. carPetName = ((string)cmd.Parameters["@petName"].Value).Trim(); } return carPetName; }

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

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

После завершения работы хранимой процедуры с помощью вызова ExecuteNonQuery() значение выходного параметра можно получить с помощью просмотра коллекции параметров для данного объекта команды и соответствующего приведения типа:

carPetName = ((string)cmd.Parameters["@petName"].Value).Trim();

И вот первый вариант библиотеки доступа к данным AutoLotDAL.dll готов! С помощью этой сборки можно создавать произвольные интерфейсы для вывода и редактирования данных (консольные приложения, Windows-приложения, приложения на основе Windows Presentation Foundation или веб-приложения на базе HTML).

Дата добавления: 2015-05-09