Вставка, удаление, обновление записей в базе данных
98Работа с базами данных в .NET Framework --- ADO.NET --- Вставка, удаление, обновление записей в базе данных
Метод ExecuteReader() извлекает объект чтения данных, который позволяет просматривать результаты SQL-оператора Select с помощью потока информации, доступного только для чтения в прямом направлении. Однако если требуется выполнить операторы SQL, модифицирующие таблицу данных, то нужен вызов метода ExecuteNonQuery() данного объекта команды. Этот единый метод предназначен для выполнения вставок, изменений и удалений, в зависимости от формата текста команды.
Понятие не запросный (nonquery) означает оператор SQL, который не возвращает результирующий набор. Следовательно, операторы Select представляют собой запросы, а операторы Insert, Update и Delete — нет. Соответственно, метод ExecuteNonQuery() возвращает значение int, содержащее количество строк, на которые повлияли эти операторы, а не новое множество записей.
Чтобы показать, как модифицировать содержимое существующей базы данных с помощью только запроса ExecuteNonQuery(), следующим шагом будет создание собственной библиотеки доступа к данным, в которой инкапсулируется процесс работы с базой данных AutoLot.
В реальной производственной среде ваша логика ADO.NET почти наверняка будет изолирована в .dll-сборке .NET по одной простой причине — повторное использование кода! В предыдущих статьях это не было сделано, чтобы не отвлекать вас от решаемых задач. Но было бы лишними затратами времени разрабатывать ту же самую логику подключения, ту же самую логику чтения данных и ту же самую логику выполнения команд для каждого приложения, которому понадобится работать с базой данных AutoLot.
В результате изоляции логики доступа к данным в кодовой библиотеке .NET различные приложения с любыми пользовательскими интерфейсами (консольный, в стиле рабочего стола, в веб-стиле и т.д.) могут обращаться к существующей библиотеке даже независимо от языка. И если разработать библиотеку доступа к данным на C#, то другие программисты в .NET смогут создавать свои пользовательские интерфейсы на любом языке (например, VB или C++/CLI).
Наша библиотека доступа к данным (AutoLotDAL.dll) будет содержать единое пространство имен (AutoLotConnectedLayer), которое будет взаимодействовать с базой AutoLot с помощью подключенных типов ADO.NET.
Начните с создания нового проекта библиотеки классов (C# Class Library) по имени AutoLotDAL (сокращенно от 'AutoLot Data Access Layer" — "Уровень доступа к данным AutoLot"), а затем смените первоначальное имя файла C#-кода на AutoLotConnDAL.cs.
Потом переименуйте область действия пространства имен в AutoLotConnectedLayer и измените имя первоначального класса на InventoryDAL, т.к. этот класс будет определять различные члены, предназначенные для взаимодействия с таблицей Inventory базы данных AutoLot. И, наконец, импортируйте следующие пространства имен .NET:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace AutoLotConnectedLayer
{
public class InventoryDAL
{
}
}Добавление логики подключения
Первая наша задача — определить методы, позволяющие вызывающему процессу подключаться к источнику данных с помощью допустимой строки подключения и отключаться от него. Поскольку в нашей сборке AutoLotDAL.dll будет жестко закодировано использование типов класса System.Data.SqlClient, определите приватную переменную SqlConnection, которая будет выделяться при создании объекта InventoryDAL.
Кроме того, определите метод OpenConnection(), а затем еще CloseConnection(), которые будут взаимодействовать с этой переменной:
public class InventoryDAL
{
private SqlConnection connect = null;
public void OpenConnection(string connectionString)
{
connect = new SqlConnection(connectionString);
connect.Open();
}
public void CloseConnection()
{
connect.Close();
}
}Для краткости тип InventoryDAL не будет проверять все возможные исключения, и не будет генерировать пользовательские исключения при возникновении различных ситуаций (например, когда строка подключения неверно сформирована). Однако при создании производственной библиотеки доступа к данным вам наверняка пришлось бы задействовать технику структурированной обработки исключений, чтобы учитывать все аномалии, которые могут возникнуть во время выполнения.
Добавление логики вставки
Вставка новой записи в таблицу Inventory сводится к форматированию SQL-оператора Insert (в зависимости от введенных пользователем данных) и вызову метода ExecuteNonQuery() с помощью объекта команды. Для этого добавьте в класс InventoryDAL общедоступный метод InsertAuto(), принимающий четыре параметра, которые соответствуют четырем столбцам таблицы Inventory (CarID, Color, Make и PetName). На основании этих аргументов сформируйте строку для добавления новой записи. И, наконец, выполните SQL-оператор с помощью объекта SqlConnection:
public void InsertAuto(int id, string color, string make, string petName)
{
// Оператор SQL
string sql = string.Format("Insert Into Inventory" +
"(CarID, Make, Color, PetName) Values(@CarId, @Make, @Color, @PetName)");
using (SqlCommand cmd = new SqlCommand(sql, this.connect))
{
// Добавить параметры
cmd.Parameters.AddWithValue("@CarId", id);
cmd.Parameters.AddWithValue("@Make", make);
cmd.Parameters.AddWithValue("@Color", color);
cmd.Parameters.AddWithValue("@PetName", petName);
cmd.ExecuteNonQuery();
}
}Определение классов, представляющих записи в реляционной базе данных — распространенный способ создания библиотеки доступа к данным. Вообще-то, ADO.NET Entity Framework автоматически генерирует строго типизированные классы, которые позволяют взаимодействовать с данными базы. Кстати, автономный уровень ADO.NET генерирует строго типизированные объекты DataSet для представления данных из заданной таблицы в реляционной базе данных.
Создание оператора SQL с помощью конкатенации строк может оказаться опасным с точки зрения безопасности (вспомните атаки вставкой в SQL). Текст команды лучше создавать с помощью параметризованного запроса, который будет описан чуть позже.
Добавление логики удаления
Удаление существующей записи не сложнее вставки новой записи. В отличие от кода InsertAuto(), будет показана одна важная область try/catch, которая обрабатывает возможную ситуацию, когда выполняется попытка удаления автомобиля, уже заказанного кем-то из таблицы Customers. Добавьте в класс InventoryDAL следующий метод:
public void DeleteCar(int id)
{
string sql = string.Format("Delete from Inventory where CarID = '{0}'", id);
using (SqlCommand cmd = new SqlCommand(sql, this.connect))
{
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Exception error = new Exception("К сожалению, эта машина заказана!", ex);
throw error;
}
}
}Добавление логики изменения
Когда дело доходит до обновления существующей записи в таблице Inventory, то сразу же возникает очевидный вопрос: что именно можно позволить изменять вызывающему процессу: цвет автомобиля, дружественное имя, модель или все сразу? Один из способов максимального повышения гибкости — определение метода, принимающего параметр типа string, который может содержать любой оператор SQL, но это, по меньшей мере, рискованно.
В идеале лучше иметь набор методов, которые позволяют вызывающему процессу изменять записи различными способами. Однако для нашей простой библиотеки доступа к данным мы определим единый метод, который позволяет вызывающему процессу изменить дружественное имя указанного автомобиля:
public void UpdateCarPetName(int id, string newpetName)
{
string sql = string.Format("Update Inventory Set PetName = '{0}' Where CarID = '{1}'",
newpetName, id);
using (SqlCommand cmd = new SqlCommand(sql, this.connect))
{
cmd.ExecuteNonQuery();
}
}Добавление логики выборки
Теперь необходимо добавить метод для выборки записей. Как было показано ранее, объект чтения данных конкретного поставщика данных позволяет выбирать записи с помощью курсора, допускающего только чтение в прямом направлении. Посредством вызова метода Read() можно обработать каждую запись поочередно. Все это замечательно, но теперь необходимо разобраться, как возвратить эти записи вызывающему уровню приложения.
Одним из подходов может быть получение данных с помощью метода Read() с последующим заполнением и возвратом многомерного массива (или другого объекта вроде обобщенного List<T>).
Еще один способ — возврат объекта System.Data.DataTable, который вообще-то принадлежит автономному уровню ADO.NET. DataTable — это класс, представляющий табличный блок данных (наподобие бумажной или электронной таблицы).
Класс DataTable содержит данные в виде коллекции строк и столбцов. Эти коллекции можно заполнять программным образом, но в типе DataTable имеется метод Load(), который может автоматически заполнять их с помощью объекта чтения данных! Вот пример, где данные из таблицы Inventory возвращаются в виде DataTable:
public DataTable GetAllInventoryAsDataTable()
{
DataTable inv = new DataTable();
string sql = "Select * From Inventory";
using (SqlCommand cmd = new SqlCommand(sql, this.connect))
{
SqlDataReader dr = cmd.ExecuteReader();
inv.Load(dr);
dr.Close();
}
return inv;
}Работа с параметризованными объектами команд
Пока в логике вставки, изменения и удаления для типа InventoryDAL мы использовали жестко закодированные строковые литералы для каждого SQL-запроса. Вы, видимо, знаете о существовании параметризованных запросов, которые позволяют рассматривать параметры SQL как объекты, а не просто кусок текста.
Работа с SQL-запросами в более объектно-ориентированной манере не только помогает сократить количество опечаток (при наличии строго типизированных свойств), ведь параметризованные запросы обычно выполняются значительно быстрее запросов в виде строковых литералов, поскольку они анализируются только один раз (а не каждый раз, как это происходит, если свойству CommandText присваивается SQL-строка). Кроме того, параметризованные запросы защищают от атак внедрением в SQL (широко известная проблема безопасности доступа к данным).
Для поддержки параметризованных запросов объекты команд ADO.NET поддерживают коллекцию отдельных объектов параметров. По умолчанию эта коллекция пуста, но в нее можно занести любое количество объектов параметров, которые соответствуют параметрам-заполнителям (placeholder parameter) в SQL-запросе. Если нужно связать параметр SQL-запроса с членом коллекции параметров некоторого объекта команды, поставьте перед параметром SQL символ @ (по крайней мере, при работе с Microsoft SQL Server, хотя не все СУБД поддерживают это обозначение).
Задание параметров с помощью типа DbParameter
Прежде чем приступить к созданию параметризованных запросов, ознакомимся с типом 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-запросов, хотя параметризованные запросы наиболее удобны, если нужно запускать хранимые процедуры.
