Удаление записей
Удалить существующую запись так же просто, как и вставить новую. Но, в отличие от программного кода для InsertNewCar(), ниже демонстрируется важная возможность применения try/catch для обработки попытки удаления автомобиля, используемого в настоящий момент в процессе оформления заказа для покупателя из таблицы Customers (сама эта таблица будет рассмотрена в этой главе позже).
private static void DeleteCar(SqlConnection cn) {
// Получение номера машины для удаления и само удаление.
Console.Write("Введите номер машины для удаления: ");
int carToDelete = int.Parse(Console.ReadLine());
string sql = string.Format("Delete from Inventory where CarID = '{0}'", carToDelete);
SqlCommand cmd = new SqlCommand(sql, cn);
try { cmd.ExecuteNonQuery(); } catch {
Console.WriteLine("Извините, на эту машину оформляется заказ!");
}
}
Обновление записей
Если вы разобрались с программным кодом для DeleteCar() и InsertNewCar(), то и программный код для UpdateCarPetName() не будет для вас сложным (здесь для простоты логика try/catch тоже исключена).
private static void UpdateCarPetName(SqlConnection cn) {
// Получение номера машины для модификации и ввод нового названия.
Console.Write("Введите номер машины для модификации: ");
string newPetName = "";
int carToUpdate = carToUpdate = int.Parse(Console.ReadLine());
Console.Write("Введите новое название: ");
newPetName = Console.ReadLine();
// Обновление записи.
string sql = string.Format("Update Inventory Set PetName='{0}' Where CarID='{1}'", newPetName, carToUpdate);
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.ExecuteNonQuery();
}
На этом создание приложения завершается. На рис. 22.8 показан результат тестового запуска этого приложения.
Рис. 22.8. Вставка, обновление и удаление записей c помощью объектов команд
Работа с объектами параметризованных команд
Показанная выше программная логика вставки, обновления и удаления работает так, как и ожидается, однако обратите внимание на то, что каждый из SQL-запросов здесь представлен "жестко" закодированными строковыми литералами. Вы, возможно, знаете, что с SQL-параметрами можно обращаться, как с объектами, а не с простыми строками текста, если использовать параметризованные запросы Обычно параметризованные запросы выполняются намного быстрее буквальных SQL-строк, поскольку они анализируются только один раз (а не каждый раз, когда SQL-строка присваивается свойству CommandText). Параметризованные запросы также обеспечивают защиту от атак SQL-инъекции (это известная проблема безопасности доступа к данным).
Объекты команд ADO.NET поддерживают коллекцию дискретных типов параметра. По умолчанию эта коллекция пуста, но вы можете добавить в нее любое число объектов параметра, которые должны будут отображаться в "заместитель" параметра в SQL-запросе. Чтобы ассоциировать параметр в SQL-запросе с членом коллекции параметров данного объекта команды, добавьте к текстовому SQL-параметру префикс @ (это работает, как минимум, при использовании Microsoft SQL Server, но такое обозначение поддерживают не все СУБД).
Указание параметров с помощью типа DbParameter
Перед тем как приступить к построению параметризованных запросов, мы должны рассмотреть тип DbParameter (который является базовым классом объектов параметров, специфичных для конкретного поставщика данных). Этот класс поддерживает ряд свойств, позволяющих указать имя, размер и тип данных параметра, а также другие его особенности, например направление параметра. В табл. 22.8 описаны некоторые свойства типа DbParameter.
Таблица 22.8. Ключевые члены типа DbParameter
Свойство Описание DbType Читает или записывает информацию о "родном" типе данных для источника данных, представленную в виде соответствующего типа данных CLR Direction Читает или записывает значение, указывающее направление потока для данного параметра (только ввод, только вывод, двунаправленное движение, предусмотренное возвращение значения) IsNullable Читает или записывает значение, являющееся индикатором того, что параметр допускает значения null ParameterName Читает или устанавливает имя DbParameter Size Читает или устанавливает максимальный размер данных параметра Value Читает или устанавливает значение параметра
Для иллюстрации мы модифицируем предыдущий метод InsertNewCar(), чтобы в нем использовались объекты параметра. Вот как может выглядеть соответствующий программный код.
private static void InsertNewCar(SqlConnection cn) {
…
// Обратите внимание на 'заполнители' в SQL-запросе.
string sql = string.Format("Insert Into Inventory" +
"(CarID, Make, Color, PetName) Values" +
"(@CarID, @Make, @Color, @PetName)");
// Наполнение коллекции параметров.
SqlCommand cmd = new SqlCommand(sql, cn);
SqlParameter param = new SqlParameter();
param.ParameterName = "@CarID";
param.Value = newCarID;
param.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@Make";
param.Value = newCarMake;
param.SqlDbType = SqlDbType.Char;
param.Size = 20;
cmd.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@Color";
param.Value = newCarColor;
param.SqlDbType = SqlDbType.Char;
param.Size = 20;
cmd.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@PetName";
param.Value = newCarPetName;
param.SqlDbType = SqlDbType.Char;
param.Size = 20;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
}
Хотя при создании параметризованного запроса требуется вводить большой объем программного кода, конечный результат оказывается более выгодным с точки зрения программного использования SQL-операторов. Улучшается также и общая производительность. Вы, конечно, можете использовать предлагаемый подход для всех SQL-запросов, но наиболее полезными параметризованные запросы оказываются при запуске хранимых процедур.
Замечание. Здесь для создания объектов параметров использовались различные свойства. Но следует знать и о том, что объекты параметров поддерживают целый ряд перегруженных конструкторов, также позволяющих установить значения свойств (кроме того, в результате получается более компактный базовый программный код).
Выполнение хранимых процедур с помощью DbCommand
Хранимой процедурой называется блок программного кода SQL сохраненный в базе данных. Хранимые процедуры могут создаваться для того, чтобы возвращать наборы строк или скалярных типов данных, и могут иметь любое число необязательных параметров. Результатом является рабочая "единица", которая ведет себя подобно типичной функции, с той очевидной разницей, что размещается она в хранилище данных, а не в двоичном рабочем объекте.
Замечание. Хотя обсуждение соответствующей темы в этой главе не предполагается, самая новая версия Microsoft SQL Server (2005) включает в себя CLR-хост! Таким образом, хранимые процедуры (и другие атомарные единицы базы данных) могут создаваться с помощью управляемых языков (например, C#), а не только с помощью традиционного языка SQL. Подробности можно найти на страницах http://www.microsoft.com/sql/2005.
Для иллюстрации соответствующего процесса давайте добавим в программу CarInventoryUpdate новую опцию, которая позволит пользователю выяснить название автомобиля с помощью хранимой процедуры GetPetName. Этот объект базы данных был создан при установке базы данных Cars, и выглядит он так.
CREATE PROCEDURE GetPetName
@carID int,
@petName char(20) output
AS
SELECT @petName = PetName from Inventory where CarID = @carID
Сначала обновите имеющийся в Main() оператор switch, добавив в него обработку нового случая "P" для вызова новой вспомогательной функции с именем LookUpPetName(). которая принимает параметр SqlConnection и возвращает void. Обновите также метод ShowInstructions(), учитывая новый вариант выбора.
Чтобы выполнить хранимую процедуру, следует, как всегда, сначала создать новый объект соединения, сконфигурировать строку соединения и открыть сеанс. Но при создании объекта команды свойству CommandText следует присвоить имя хранимой процедуры (а не SQL-запрос). Также вы обязательно должны установить для свойства CommandType значение CommandType.StoredProcedure (значением по умолчанию является CommandType.Text).
