Sql Command. Execute Non Query Метод
Пространство имен: Microsoft.Data.SqlClient Сборка: Microsoft.Data.SqlClient.dll Пакет: Microsoft.Data.SqlClient v1.0.19269.1 Пакет: Microsoft.Data.SqlClient v1.1.4 Пакет: Microsoft.Data.SqlClient v2.0.1 Пакет: Microsoft.Data.SqlClient v2.1.4 Пакет: Microsoft.Data.SqlClient v3.0.1 Пакет: Microsoft.Data.SqlClient v3.1.0 Пакет: Microsoft.Data.SqlClient v4.0.1 Пакет: Microsoft.Data.SqlClient v4.1.0 Пакет: Microsoft.Data.SqlClient v5.0.0 Пакет: Microsoft.Data.SqlClient v5.1.0
Некоторые сведения относятся к предварительной версии продукта, в которую до выпуска могут быть внесены существенные изменения. Майкрософт не предоставляет никаких гарантий, явных или подразумеваемых, относительно приведенных здесь сведений.
Выполняет для подключения инструкцию Transact-SQL и возвращает количество задействованных в инструкции строк.
public: override int ExecuteNonQuery();public override int ExecuteNonQuery ();override this.ExecuteNonQuery : unit -> intPublic Overrides Function ExecuteNonQuery () As IntegerВозвращаемое значение
Число обработанных строк.
Исключения
Если SqlDbType для параметра задано значение Stream , отличное от Binary или VarBinary, использовался Value . Дополнительные сведения о потоковой передаче см. в разделе Поддержка потоковой передачи SqlClient.
Если SqlDbType для параметра задано значение TextReader , кроме Char, NChar, NVarChar, VarChar или Xml, использовался Value объект , отличный от Char, NChar, NVarChar, VarChar или Xml.
Объект , отличный SqlDbType от XML , использовался, когда Value для параметра задано значение XmlReader .
Возникло исключение при выполнении команды в заблокированной строке. Это исключение не создается при использовании платформы Microsoft .NET Framework версии 1.0.
Во время операции потоковой передачи истекло время ожидания. Дополнительные сведения о потоковой передаче см. в разделе Поддержка потоковой передачи SqlClient.
Ошибка в объекте Stream или XmlReaderTextReader во время операции потоковой передачи. Дополнительные сведения о потоковой передаче см. в разделе Поддержка потоковой передачи SqlClient.
Подключение SqlConnection, закрытое или удаленное во время операции потоковой передачи. Дополнительные сведения о потоковой передаче см. в разделе Поддержка потоковой передачи SqlClient.
Объект Stream или XmlReaderTextReader был закрыт во время операции потоковой передачи. Дополнительные сведения о потоковой передаче см. в разделе Поддержка потоковой передачи SqlClient.
Примеры
В следующем примере создается SqlCommand и выполняется с помощью ExecuteNonQuery. В этом примере передается строка, которая является инструкцией Transact-SQL (например, UPDATE, INSERT или DELETE), и строка, используемая для подключения к источнику данных.
// using System; using System.Data; using Microsoft.Data.SqlClient; namespace SqlCommandCS < class Program < static void Main() < string str = "Data Source=(local);Initial Catalog=Northwind;" + "Integrated Security=SSPI"; string qs = "SELECT OrderID, CustomerID FROM dbo.Orders;"; CreateCommand(qs, str); >private static void CreateCommand(string queryString, string connectionString) < using (SqlConnection connection = new SqlConnection( connectionString)) < SqlCommand command = new SqlCommand(queryString, connection); command.Connection.Open(); command.ExecuteNonQuery(); >> > > // Комментарии
Можно использовать ExecuteNonQuery для выполнения операций с каталогом (например, для запроса структуры базы данных или создания объектов базы данных, таких как таблицы), или для изменения данных в базе данных без использования DataSet путем выполнения инструкций UPDATE, INSERT или DELETE.
Хотя объект ExecuteNonQuery не возвращает строк, все выходные параметры или возвращаемые значения, сопоставленные с параметрами, заполняются данными.
Операторы UPDATE, INSERT и DELETE возвращают количество строк, которые были обработаны с их помощью. Для всех прочих типов операторов возвращаемым значением является -1. Если триггер существует в вставляемой или обновляемой таблице, возвращаемое значение включает количество строк, затронутых как операцией вставки или обновления, так и количество строк, затронутых триггером или триггерами. Если параметр SET NOCOUNT ON установлен для подключения (до выполнения команды или в рамках выполнения команды или в рамках триггера, инициированного выполнением команды), строки, на которые влияют отдельные инструкции, перестают влиять на количество затронутых строк, возвращаемых этим методом. Если операторы, влияющие на количество, не обнаружены, возвращаемое значение равно -1. Если происходит откат, возвращаемое значение также равно -1.
Executenonquery c что это
После установки подключения мы можем взаивмодействовать с базой данных, например, выполнять к базе данных какие-либо команды, в частности, добавление, обновление или удаление данных в базе данных, их получение. Команды в ADO.NET представлены объектом интерфейса System.Data.IDbCommand . Пакет Microsoft.Data.SqlClient предоставляет его реализацию в виде класса SqlCommand . Этот класс инкапсулирует sql-выражение, которое должно быть выполнено.
Для создания объекта SqlCommand применяется один из его конструкторов:
- SqlCommand()
- SqlCommand(String) : создает объект SqlCommand, в конструктор которого передается выполняемое выражение SQL
- SqlCommand(String, SqlConnection) : создает объект SqlCommand, в конструктор которого передается выполняемое выражение SQL и используемое подключение к базе данных в виде объекта SqlConnection
- SqlCommand(String, SqlConnection, SqlTransaction) : третий параметр представляет применяемую транзакцию в виде объекта SqlTransaction
- SqlCommand(String, SqlConnection, SqlTransaction, SqlCommandColumnEncryptionSetting) : к параметрам из предыдущего конструктора добавляет параметр типа SqlCommandColumnEncryptionSetting , который устанавливает настройки шифрования
Для управления командой применяются свойства класса SqlCommand, из которых следует отметить следующие:
- CommandText : хранит выполняемую команду SQL
- CommandTimeout : хранит временной интервал в секундах, после которого SqlCommand прекращает попытки выполнить команду и, если она не выполнена, генерирует ошибку. По умолчанию равен 30 секундам.
- CommandType : хранит тип выполняемой команды
- Connection : предоставляет используемое подключение SqlConnection
Для выполнения команды нам потребуется sql-выражение и объект подключения, которые мы можем задать как через конструктор класса SqlCommand, так и через его свойства:
string connectionString = «Server=(localdb)\\mssqllocaldb;Database=master;Trusted_Connection=True;»; using (SqlConnection connection = new SqlConnection(connectionString))
С помощью свойства CommandText устанавливается SQL-выражение, которое будет выполняться. В данном случае это запрос на создание базы данных «adonetdb». А с помощью свойства Connection можно установить объект подключения SqlConnection.
В качестве альтернативы можно было бы использовать одну из версий конструктора класса:
string connectionString = «Server=(localdb)\\mssqllocaldb;Database=master;Trusted_Connection=True;»; string sqlExpression = «CREATE DATABASE adonetdb»; using (SqlConnection connection = new SqlConnection(connectionString))
Стоит отметить, что класс SqlCommand реализует интерфейс IDisposable и соответственно имеет метод Dispose. Однако вызывать его необязательно. Соответствующее обсуждение в репозитории SqlCommand.Dispose doesn’t free managed object
Выполнение команды
Чтобы выполнить команду, необходимо применить один из методов SqlCommand:
- ExecuteNonQuery()/ExecuteNonQueryAsync() : просто выполняет sql-выражение и возвращает количество измененных записей. Подходит для sql-выражений INSERT, UPDATE, DELETE, CREATE.
- ExecuteReader()/ExecuteReaderAsync() : выполняет sql-выражение и возвращает строки из таблицы. Подходит для sql-выражения SELECT.
- ExecuteScalar()/ExecuteScalarAsync() : выполняет sql-выражение и возвращает одно скалярное значение, например, число. Подходит для sql-выражения SELECT в паре с одной из встроенных функций SQL, как например, Min, Max, Sum, Count.
Создание базы данных
Для создания базы данных применяется SQL-команда CREATE DATABASE , после которой указывается имя создаваемой базы данных. Например, создадим базу данных с именем adonetdb :
using Microsoft.Data.SqlClient; using System; using System.Threading.Tasks; namespace HelloApp < class Program < static async Task Main(string[] args) < string connectionString = "Server=(localdb)\\mssqllocaldb;Database=master;Trusted_Connection=True;"; using (SqlConnection connection = new SqlConnection(connectionString)) < await connection.OpenAsync(); // открываем подключение SqlCommand command = new SqlCommand(); // определяем выполняемую команду command.CommandText = "CREATE DATABASE adonetdb"; // определяем используемое подключение command.Connection = connection; // выполняем команду await command.ExecuteNonQueryAsync(); Console.WriteLine("База данных создана"); >Console.Read(); > > >
И после выполнения команды в Visual Studio в окне SQL Server Object Explorer мы можем найти созданную базу данных:
Создание таблицы
Для создания базы данных применяется SQL-команда CREATE TABLE , после которой указывается имя создаваемой таблицы и в скобках определения столбцов.
Например, в выше созданной базе данных adonetdb создадим таблицу «Users», которая будет иметь три столбца — Id, Name, Age:
using Microsoft.Data.SqlClient; using System; using System.Threading.Tasks; namespace HelloApp < class Program < static async Task Main(string[] args) < string connectionString = "Server=(localdb)\\mssqllocaldb;Database=adonetdb;Trusted_Connection=True;"; using (SqlConnection connection = new SqlConnection(connectionString)) < await connection.OpenAsync(); SqlCommand command = new SqlCommand(); command.CommandText = "CREATE TABLE Users (Id INT PRIMARY KEY IDENTITY, Age INT NOT NULL, Name NVARCHAR(100) NOT NULL)"; command.Connection = connection; await command.ExecuteNonQueryAsync(); Console.WriteLine("Таблица Users создана"); >Console.Read(); > > >
После выполнения команды в базе данных можно будет найти таблицу Users:
Добавление данных
Выполним команду по добавлению одного объекта в таблицу Users, которая ранее была создана:
using Microsoft.Data.SqlClient; using System; using System.Threading.Tasks; namespace HelloApp < class Program < static async Task Main(string[] args) < string connectionString = "Server=(localdb)\\mssqllocaldb;Database=adonetdb;Trusted_Connection=True;"; string sqlExpression = "INSERT INTO Users (Name, Age) VALUES ('Tom', 36)"; using (SqlConnection connection = new SqlConnection(connectionString)) < await connection.OpenAsync(); SqlCommand command = new SqlCommand(sqlExpression, connection); int number = await command.ExecuteNonQueryAsync(); Console.WriteLine($"Добавлено объектов: "); > Console.Read(); > > >
Для вставки объекта используется sql-выражение INSERT, которое имеет следующий синтаксис:
INSERT INTO название_таблицы (столбец1, столбец2, столбецN) VALUES ( значение1, значение2, значениеN)
В данном случае мы знаем, что в базе данных у нас есть таблица Users, в которой есть три столбца — Id и Age, хранящие целое число, и Name, хранящий строку. Поэтому соответственно мы добавляем для столбца Name значение ‘Tom’, а для столбца Age число 36.
Здесь метод ExecuteNonOueryAsync() возвращает число затронутых строк (в данном случае добавленных в таблицу объектов). Хотя нам необязательно возвращать результат метода, но данный результат может использоваться в качестве проверки, что операция, в частности, добавление, прошла успешно.
Чтобы убедиться, что данные добавлены, мы можем перейти к таблице Users в SQL Server Explorer в Visual Studio или в SQL Server Management Studio и увидеть добавленные данные:
Подобным образом можно добавить несколько объектов:
using Microsoft.Data.SqlClient; using System; using System.Threading.Tasks; namespace HelloApp < class Program < static async Task Main(string[] args) < string connectionString = "Server=(localdb)\\mssqllocaldb;Database=adonetdb;Trusted_Connection=True;"; // добавляем два объекта string sqlExpression = "INSERT INTO Users (Name, Age) VALUES ('Alice', 32), ('Bob', 28)"; using (SqlConnection connection = new SqlConnection(connectionString)) < await connection.OpenAsync(); SqlCommand command = new SqlCommand(sqlExpression, connection); int number = await command.ExecuteNonQueryAsync(); Console.WriteLine($"Добавлено объектов: "); > Console.Read(); > > >
Обновление объектов
Обновление будет происходить аналогично, только теперь будет использоваться sql-выражение UPDATE, которое имеет следующий синтаксис:
UPDATE название_таблицы SET столбец1=значение1, столбец2=значение2, столбецN=значениеN WHERE некоторый_столбец=некоторое_значение
Применим это выражение:
using Microsoft.Data.SqlClient; using System; using System.Threading.Tasks; namespace HelloApp < class Program < static async Task Main(string[] args) < string connectionString = "Server=(localdb)\\mssqllocaldb;Database=adonetdb;Trusted_Connection=True;"; string sqlExpression = "UPDATE Users SET Age=20 WHERE Name='Tom'"; using (SqlConnection connection = new SqlConnection(connectionString)) < await connection.OpenAsync(); SqlCommand command = new SqlCommand(sqlExpression, connection); int number = await command.ExecuteNonQueryAsync(); Console.WriteLine($"Обновлено объектов: "); > Console.Read(); > > >
Здесь обновляется строка, в которой Name=Tom, то есть выше добавленный объект. Если в таблице будет несколько строк, у которых Name=Tom, то обновятся все эти строки.
Удаление
Удаление производится с помощью sql-выражения DELETE, которое имеет следующий синтаксис:
DELETE FROM таблица WHERE столбец = значение
Удалим, например, всех пользователей, у которых имя Tom:
using Microsoft.Data.SqlClient; using System; using System.Threading.Tasks; namespace HelloApp < class Program < static async Task Main(string[] args) < string connectionString = "Server=(localdb)\\mssqllocaldb;Database=adonetdb;Trusted_Connection=True;"; string sqlExpression = "DELETE FROM Users WHERE Name='Tom'"; using (SqlConnection connection = new SqlConnection(connectionString)) < await connection.OpenAsync(); SqlCommand command = new SqlCommand(sqlExpression, connection); int number = await command.ExecuteNonQueryAsync(); Console.WriteLine($"Удалено объектов: "); > Console.Read(); > > >
Во всех трех случаях фактически меняется только sql-выражение, а остальная логика остается неизменной. И мы также можем выполнять сразу несколько операций:
using Microsoft.Data.SqlClient; using System; using System.Threading.Tasks; namespace HelloApp < class Program < static async Task Main(string[] args) < string connectionString = "Server=(localdb)\\mssqllocaldb;Database=adonetdb;Trusted_Connection=True;"; Console.WriteLine("Введите имя:"); string name = Console.ReadLine(); Console.WriteLine("Введите возраст:"); int age = Int32.Parse(Console.ReadLine()); string sqlExpression = $"INSERT INTO Users (Name, Age) VALUES ('', )"; using (SqlConnection connection = new SqlConnection(connectionString)) < await connection.OpenAsync(); // добавление SqlCommand command = new SqlCommand(sqlExpression, connection); int number = await command.ExecuteNonQueryAsync(); Console.WriteLine($"Добавлено объектов: "); // обновление ранее добавленного объекта Console.WriteLine("Введите новое имя:"); name = Console.ReadLine(); sqlExpression = $"UPDATE Users SET Name='' WHERE Age="; command.CommandText = sqlExpression; number = await command.ExecuteNonQueryAsync(); Console.WriteLine($"Обновлено объектов: "); > Console.Read(); > > >
Введите имя: Tom Введите возраст: 41 Добавлено объектов: 1 Введите новое имя: Alex Обновлено объектов: 1
Executenonquery c что это
Данное руководство устарело. Актуальное руководство: по ADO.NET и работе с базами данных в .NET 6
Последнее обновление: 31.10.2015
После установки подключения мы можем выполнить к базе данных какие-либо команды, например, добавить в базу данных объект, удалить, изменить его или просто извлечь. Команды представлены объектом интерфейса System.Data.IDbCommand. Провайдер для MS SQL предоставляет его реализацию в виде класса SqlCommand . Этот класс инкапсулирует sql-выражение, которое должно быть выполнено.
Для выполнения команды нам потребуется sql-выражение и объект подключения:
string connectionString = @»Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True»; using (SqlConnection connection = new SqlConnection(connectionString))
С помощью свойства CommandText устанавливается SQL-выражение, которое будет выполняться. В данном случае это запрос на получение всех объектов из таблицы Users. А с помощью свойства Connection можно установить объект подключения SqlConnection.
В качестве альтернативы можно было бы использовать одну из версий конструктора класса:
string connectionString = @»Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True»; string sqlExpression = «SELECT * FROM Users»; using (SqlConnection connection = new SqlConnection(connectionString))
Чтобы выполнить команду, необходимо применить один из методов SqlCommand:
- ExecuteNonQuery : просто выполняет sql-выражение и возвращает количество измененных записей. Подходит для sql-выражений INSERT, UPDATE, DELETE.
- ExecuteReader : выполняет sql-выражение и возвращает строки из таблицы. Подходит для sql-выражения SELECT.
- ExecuteScalar : выполняет sql-выражение и возвращает одно скалярное значение, например, число. Подходит для sql-выражения SELECT в паре с одной из встроенных функций SQL, как например, Min, Max, Sum, Count.
Добавление объектов
Выполним команду по добавлению одного объекта в таблицу Users базы данных usersdb, которая ранее была создана:
class Program < static void Main(string[] args) < string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"; string sqlExpression = "INSERT INTO Users (Name, Age) VALUES ('Tom', 18)"; using (SqlConnection connection = new SqlConnection(connectionString)) < connection.Open(); SqlCommand command = new SqlCommand(sqlExpression, connection); int number = command.ExecuteNonQuery(); Console.WriteLine("Добавлено объектов: ", number); > Console.Read(); > >
Для вставки объекта используется sql-выражение INSERT, которое имеет следующий синтаксис:
INSERT INTO название_таблицы (столбец1, столбец2, столбецN) VALUES ( значение1, значение2, значениеN)
В данном случае мы знаем, что в базе данных у нас есть таблица Users, в которой есть три столбца — Id и Age, хранящие целое число, и Name, хранящий строку. Поэтому соответственно мы добавляем для столбца Name значение ‘Tom’, а для столбца Age число 18.
Здесь метод ExecuteNonOuery() возвращает число затронутых строк (в данном случае добавленных в таблицу объектов). Хотя нам необязательно возвращать результат метода, но данный результат может использоваться в качестве проверки, что операция, в частности, добавление, прошла успешно.
Чтобы убедиться, что данные добавлены, мы можем перейти к таблице Users в SQL Server Management Studio и с помощью опции Select Top 1000 Rows вывести добавленную строку:
Обновление объектов
Обновление будет происходить аналогично, только теперь будет использоваться sql-выражение UPDATE, которое имеет следующий синтаксис:
UPDATE название_таблицы SET столбец1=значение1, столбец2=значение2, столбецN=значениеN WHERE некоторый_столбец=некоторое_значение
Применим это выражение:
string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"; string sqlExpression = "UPDATE Users SET Age=20 WHERE Name='Tom'"; using (SqlConnection connection = new SqlConnection(connectionString)) < connection.Open(); SqlCommand command = new SqlCommand(sqlExpression, connection); int number = command.ExecuteNonQuery(); Console.WriteLine("Обновлено объектов: ", number); >
Здесь обновляется строка, в которой Name=Tom, то есть выше добавленный объект. Если в таблице будет несколько строк, у которых Name=Tom, то обновятся все эти строки.
Удаление
Удаление производится с помощью sql-выражения DELETE, которое имеет следующий синтаксис:
DELETE FROM таблица WHERE столбец = значение
Удалим, например, всех пользователей, у которых имя Tom:
string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"; string sqlExpression = "DELETE FROM Users WHERE Name='Tom'"; using (SqlConnection connection = new SqlConnection(connectionString)) < connection.Open(); SqlCommand command = new SqlCommand(sqlExpression, connection); int number = command.ExecuteNonQuery(); Console.WriteLine("Удалено объектов: ", number); >
Во всех трех случаях фактически меняется только sql-выражение, а остальная логика остается неизменной. И мы также можем выполнять сразу несколько операций:
static void Main(string[] args) < string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"; Console.WriteLine("Введите имя:"); string name = Console.ReadLine(); Console.WriteLine("Введите возраст:"); int age = Int32.Parse(Console.ReadLine()); string sqlExpression = String.Format("INSERT INTO Users (Name, Age) VALUES ('', )", name, age); using (SqlConnection connection = new SqlConnection(connectionString)) < connection.Open(); // добавление SqlCommand command = new SqlCommand(sqlExpression, connection); int number = command.ExecuteNonQuery(); Console.WriteLine("Добавлено объектов: ", number); // обновление ранее добавленного объекта Console.WriteLine("Введите новое имя:"); name = Console.ReadLine(); sqlExpression = String.Format("UPDATE Users SET Name='' WHERE Age=", name, age); command.CommandText = sqlExpression; number = command.ExecuteNonQuery(); Console.WriteLine("Обновлено объектов: ", number); > Console.Read(); >
Введите имя: Tom Введите возраст: 41 Добавлено объектов: 1 Введите новое имя: Alex Обновлено объектов: 1
Вставка, удаление, обновление записей в базе данных
Метод 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 = ''", 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 = '' Where CarID = ''", newpetName, id); using (SqlCommand cmd = new SqlCommand(sql, this.connect)) < cmd.ExecuteNonQuery(); >>Добавление логики выборки
Теперь необходимо добавить метод для выборки записей. Как было показано ранее, объект чтения данных конкретного поставщика данных позволяет выбирать записи с помощью курсора, допускающего только чтение в прямом направлении. Посредством вызова метода Read() можно обработать каждую запись поочередно. Все это замечательно, но теперь необходимо разобраться, как возвратить эти записи вызывающему уровню приложения.
Одним из подходов может быть получение данных с помощью метода Read() с последующим заполнением и возвратом многомерного массива (или другого объекта вроде обобщенного List).
Еще один способ — возврат объекта 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 поддерживают коллекцию отдельных объектов параметров. По умолчанию эта коллекция пуста, но в нее можно занести любое количество объектов параметров, которые соответствуют в 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('','','','')", 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-запросов, хотя параметризованные запросы наиболее удобны, если нужно запускать хранимые процедуры.