Создание команд SqlCommand

Приветствую всех, сегодня рассмотрим примеры создания команд и их использования.

SqlCommand – объект подключаемой части технологии ADO.NET, позволяющий выполнять инструкции T-SQL над источником данных. Для правильной работы объекту SqlCommand нужно предоставить подключение к источнику данных(экземпляр класса SqlConnection)

Способы создания объекта SqlCommand:

1) Использование конструктора по умолчанию

//**********Первый способ**************
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = "Some T-SQL Command";

//**********Первый способ**************

SqlCommand cmd = new SqlCommand();

cmd.Connection = connection;

cmd.CommandText = "Some T-SQL Command";

2) Использование метода CreateCommand() объекта SqlConnection

//**********Второй способ**************
cmd = connection.CreateCommand();
cmd.CommandText = "Some T-SQL Command";

//**********Второй способ**************

cmd = connection.CreateCommand();

cmd.CommandText = "Some T-SQL Command";

3) Использование перегрузки конструктора с двумя параметрами

//**********Третий способ**************
cmd = new SqlCommand("Some T-SQL Command", connection);

1 2	//********Третий способ************ cmd = new SqlCommand("Some T-SQL Command", connection);

Важно! Независимо от способа создания объекта SqlCommand, он должен получить экземпляр SqlConnection.

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

Методы объекта SqlCommand для выполнения команд:

ExecuteNonQuery() – этот метод предназначен для выполнения команд, не возвращающих значения. Такие команды могут выполнять следующие инструкции T-SQL: INSERT, DELETE, ALTER, DROP, CREATE.
ExecuteScalar() – этот метод предназначен для выполнения команд, возвращающих скалярные значения.
EcecuteReader() – этот метод предназначен для выполнения команд, возвращающих данные в табличном представлении(SELECT).

Объект DataReader представляет собой аналог пожарного курсора только для чтения, позволяющий просматривать табличные данные, которые возвращает команда. Получить объект DataReader можно с помощью метода ExecuteReader() объекта Command

 static void Main(string[] args)
        {
            string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения

            SqlConnection connection = new SqlConnection(conStr); // создание подключения

            SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", connection); // построение команды, возвращающей данные в табличном представлениии

            connection.Open();
            SqlDataReader reader = cmd.ExecuteReader(); // метод ExecuteReader возвращает 

            // с помощью объекта SqldataReder можно просматривать резельтаты запроса строка за строкой
            // метод Read() возвращает значение true или false в зависимости от того, имеется ли следующая строка, которую можно посмотреть
            // так же метод Read при каждом его вызове перемещается к следующей строке набора строк, пришедших тот сервера
            while (reader.Read())
            {
                Console.WriteLine(reader.GetFieldValue<int>(0));        // вывод на экран ID клиента испльзуя метод GetFieldValue          
                Console.WriteLine(                                      // вывод на экран ФИО клиента испльзуя метод GetString
                    reader.GetString(2)+" "+
                    reader.GetString(1)+" "+
                    reader.GetString(3)
                    );
                Console.WriteLine(reader.GetFieldValue<string>(7));     // вывод на экран номера телефона клиента 
                Console.WriteLine("{0:D}",reader.GetDateTime(8));       // вывод на экран поля DataInSystem клиента        
                //Console.WriteLine(reader.GetFieldValue<DateTime>(8));
                
                Console.WriteLine(new string('_',20));
            }
            reader.Close();
            connection.Close();
        }

static void Main(string[] args)

{

string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения

SqlConnection connection = new SqlConnection(conStr); // создание подключения

SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", connection); // построение команды, возвращающей данные в табличном представлениии

connection.Open();

SqlDataReader reader = cmd.ExecuteReader(); // метод ExecuteReader возвращает

// с помощью объекта SqldataReder можно просматривать резельтаты запроса строка за строкой

// метод Read() возвращает значение true или false в зависимости от того, имеется ли следующая строка, которую можно посмотреть

// так же метод Read при каждом его вызове перемещается к следующей строке набора строк, пришедших тот сервера

while (reader.Read())

{

Console.WriteLine(reader.GetFieldValue<int>(0)); // вывод на экран ID клиента испльзуя метод GetFieldValue

Console.WriteLine( // вывод на экран ФИО клиента испльзуя метод GetString

reader.GetString(2)+" "+

reader.GetString(1)+" "+

reader.GetString(3)

);

Console.WriteLine(reader.GetFieldValue<string>(7)); // вывод на экран номера телефона клиента

Console.WriteLine("{0:D}",reader.GetDateTime(8)); // вывод на экран поля DataInSystem клиента

//Console.WriteLine(reader.GetFieldValue<DateTime>(8));

Console.WriteLine(new string('_',20));

}

reader.Close();

connection.Close();

}

Важно! С помощью объекта DataReader можно просматривать толькоодну строку в один момент времени и только в одномнаправлении.

SqlDataReader имеет ряд методов, свойств и индексаторов для получения информации, пришедшей от источника данных Ниже приведены некоторые из них.

Read() – этот метод возвращает значение true или false в зависимости от того, достигнут ли конец набора строк и при каждом его вызове перемещается к следующей строке.
FieldCount – свойство, позволяющее получить количество полей у строк, пришедших от источника.
GetName(int index) – метод, позволяющий получить имя поля по индексу Для получения значений отдельных полей строк SqldataReader имеет две перегрузки индексатора с целочисленным индексом и строковым, а так же строготипизированные методы Get<FieldType>(int index) и GetFieldValue<T>(int index)

Асинхронное выполнение команд

При создании UI приложений, использующих объект SqlCommand выполнение команд следует производить асинхронно во избежание блокировки пользовательского интерфейса. Специально для этого объект Command имеет методы асинхронного выполнения команд. Все методы для асинхронного выполнения команд имеют префикс Async.

await cmd.ExecuteNonQueryAsync();
await cmd.ExecuteReaderAsync();
await cmd.ExecuteScalarAsync();

await cmd.ExecuteNonQueryAsync();

await cmd.ExecuteReaderAsync();

await cmd.ExecuteScalarAsync();

Пример использования асинхронного выполнения команд над базой данных

 string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения
        async private void Buttom_Click(object sender, EventArgs e)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                await connection.OpenAsync();   // асинхронное открытие соединения

                SqlCommand cmd = new SqlCommand("WAITFOR DELAY '00:00:10'", connection); // асинхронное выполнение команды

                await cmd.ExecuteNonQueryAsync();
                MessageBox.Show("Command executed async");

            }
        }

string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения

async private void Buttom_Click(object sender, EventArgs e)

{

using (SqlConnection connection = new SqlConnection(conStr))

{

await connection.OpenAsync(); // асинхронное открытие соединения

SqlCommand cmd = new SqlCommand("WAITFOR DELAY '00:00:10'", connection); // асинхронное выполнение команды

await cmd.ExecuteNonQueryAsync();

MessageBox.Show("Command executed async");

}

При создании SqlCommand свойству CommandText можно присвоить сразу несколько операторов T-SQL.

 SqlCommand cmd = new SqlCommand("SELECT * FROM Customers WHERE CustomerNo = 1; SELECT * FROM Employees WHERE EmployeeID = 1;", connection);

1	SqlCommand cmd = new SqlCommand("SELECT * FROM Customers WHERE CustomerNo = 1; SELECT * FROM Employees WHERE EmployeeID = 1;", connection);

При использовании объекта DataReader для такой команды после просмотра данных первого набора строк нужно перейти к следующему набору с помощью метода NextResult() объекта DataReader.

Пример:

 class Program
    {

        public static void WriteReaderData(DbDataReader reader)
        {
            while (reader.Read())                       // вывод данных возвращаемых вторым запросом 
            {
                for (int i = 0; i < reader.FieldCount; i++)
                    Console.WriteLine(reader.GetName(i)+": "+reader[i]);
                Console.WriteLine(new string('_', 20));
            }
        }

        static void Main(string[] args)
        {
            string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения

            SqlConnection connection = new SqlConnection(conStr); // создание подключения
            connection.Open();

            SqlCommand cmd = new SqlCommand("SELECT * FROM Customers WHERE CustomerNo = 1; SELECT * FROM Employees WHERE EmployeeID = 1;", connection);  // создание пакета запросов 

            SqlDataReader reader = cmd.ExecuteReader();

            Console.WriteLine("press any key to see data from Customers");
            Console.ReadKey();

            WriteReaderData(reader);  // вывод на экран данных

            Console.WriteLine("press any key to see data from Employees");
            Console.ReadKey();

            reader.NextResult();      // переход к следующему запросу     

            WriteReaderData(reader);  // вывод данных на экран

            connection.Close();
            reader.Close();           // не забывайте закрывать объект reader
            Console.ReadKey();
        }
    }

class Program

{

public static void WriteReaderData(DbDataReader reader)

{

while (reader.Read()) // вывод данных возвращаемых вторым запросом

{

for (int i = 0; i < reader.FieldCount; i++)

Console.WriteLine(reader.GetName(i)+": "+reader[i]);

Console.WriteLine(new string('_', 20));

}

static void Main(string[] args)

{

string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения

SqlConnection connection = new SqlConnection(conStr); // создание подключения

connection.Open();

SqlCommand cmd = new SqlCommand("SELECT * FROM Customers WHERE CustomerNo = 1; SELECT * FROM Employees WHERE EmployeeID = 1;", connection); // создание пакета запросов

SqlDataReader reader = cmd.ExecuteReader();

Console.WriteLine("press any key to see data from Customers");

Console.ReadKey();

WriteReaderData(reader); // вывод на экран данных

Console.WriteLine("press any key to see data from Employees");

Console.ReadKey();

reader.NextResult(); // переход к следующему запросу

WriteReaderData(reader); // вывод данных на экран

connection.Close();

reader.Close(); // не забывайте закрывать объект reader

Console.ReadKey();

}

ТРАНЗАКЦИИ

Транзакцией называется выполнение последовательности команд (SQL- конструкций) в базе данных, которая либо фиксируется при успешной реализации каждой команды, либо отменяется при неудачном выполнении хотя бы одной команды.

Откат транзакции — это действие, обеспечивающее аннулирование всех изменений данных, которые были сделаны в теле текущей незавершенной транзакции.

 static void Main(string[] args)
        {
            string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения

            SqlConnection connection = new SqlConnection(conStr);

            SqlCommand cmd = new SqlCommand("UPDATE Customers SET Phone = 'TEST' WHERE CustomerNo = 1", connection);
            //cmd = new SqlCommand("UPDATE Customers SET Phone = '(052)1245789' WHERE CustomerNo = 1", connection);

            try
            {
                connection.Open();
               
                cmd.Transaction = connection.BeginTransaction();
                cmd.ExecuteNonQuery();

                throw new Exception();

                cmd.Transaction.Commit();

                Console.WriteLine("Transaction commited");
            }
            catch (Exception)
            {
                cmd.Transaction.Rollback();
                Console.WriteLine("Transaction rollback");
            }          
        }

static void Main(string[] args)

{

string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения

SqlConnection connection = new SqlConnection(conStr);

SqlCommand cmd = new SqlCommand("UPDATE Customers SET Phone = 'TEST' WHERE CustomerNo = 1", connection);

//cmd = new SqlCommand("UPDATE Customers SET Phone = '(052)1245789' WHERE CustomerNo = 1", connection);

try

{

connection.Open();

cmd.Transaction = connection.BeginTransaction();

cmd.ExecuteNonQuery();

throw new Exception();

cmd.Transaction.Commit();

Console.WriteLine("Transaction commited");

}

catch (Exception)

{

cmd.Transaction.Rollback();

Console.WriteLine("Transaction rollback");

}

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

Dirty reads
Non-repeatable reads
Phantom reads

Dirty reads – «грязное» чтение. Первый пользователь начинает транзакцию, изменяющую данные. В это время другой пользователь (или создаваемая им транзакция) извлекает частично измененные данные, которые не являются корректными.

Non-repeatable reads — неповторяемое чтение. Первый пользователь начинает транзакцию, изменяющую данные. В это время другой пользователь начинает и завершает другую транзакцию. Первый пользователь при повторном чтении данных (например, если в его транзакцию входит несколько инструкций SELECT) получает другой набор записей.

Phantom reads — чтение фантомов. Первый пользователь начинает транзакцию, выбирающую данные из таблицы. В это время другой пользователь начинает и завершает транзакцию, вставляющую или удаляющую записи. Первый пользователь получит другой набор данных, содержащий фантомы — удаленные или измененные строки.

Важно! От неповторяющегося чтения оно отличается тем, что результат повторного обращения к данным изменился не из-за изменения/удаления самих этих данных, а из-за появления новых (фантомных) данных.

Для решения этих проблем разработаны четыре уровня изоляции транзакции в SQL:

Read uncommitted.
Read committed.
Repeatable read.
Serializable.

Read uncommitted. Транзакция может считывать данные, с которыми работают другие транзакции. Применение этого уровня изоляции может привести ко всем перечисленным проблемам.

Read committed. Транзакция не может считывать данные, с которыми работают другие транзакции. Применение этого уровня изоляции исключает проблему «грязного» чтения.

Repeatable read. Транзакция не может считывать данные, с которыми работают другие транзакции. Другие транзакции также не могут считывать данные, с которыми работает эта транзакция. Применение этого уровня изоляции исключает все проблемы, кроме чтения фантомов.

Serializable. Транзакция полностью изолирована от других транзакций. Применение этого уровня изоляции полностью исключает все проблемы.

CommandText – текст запроса. Метод ExecuteNonQuery применяется для выполнения запросов, невозвращающих данные, таких как UPDATE, INSERT и DELETE – они вносят изменения в таблицу базы данных, не возвращая ничего назад в результате выполнения.

Пример команд, возвращающих скалярные значения:

static void Main(string[] args)
        {
            string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения
            SqlConnection connection = new SqlConnection(conStr);
            connection.Open(); // открытие подключения

            SqlCommand cmd = new SqlCommand("SELECT Phone FROM Customers WHERE CustomerNo = 1", connection); // создание команды, возвращающей скалярное значение

            string phoneNumber = (string)cmd.ExecuteScalar(); // выполнение команды

            Console.WriteLine(phoneNumber);
        }

static void Main(string[] args)

{

string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения

SqlConnection connection = new SqlConnection(conStr);

connection.Open(); // открытие подключения

SqlCommand cmd = new SqlCommand("SELECT Phone FROM Customers WHERE CustomerNo = 1", connection); // создание команды, возвращающей скалярное значение

string phoneNumber = (string)cmd.ExecuteScalar(); // выполнение команды

Console.WriteLine(phoneNumber);

}

Пример выполнение команд вставки и удаления:

static void Main(string[] args)
        {
            string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения
            
            SqlConnection connection = new SqlConnection(conStr); // создание подключения
            connection.Open();

            // INSERT command

            SqlCommand insertCommand = connection.CreateCommand(); // создание команды на вставку данных
            insertCommand.CommandText = "INSERT Customers VALUES ('Alex', 'Petrov', 'Petrovich', 'Заворотная 7', NULL, 'Kiyv', '(063)8569584', '2010-01-01')";

            int rowAffected = insertCommand.ExecuteNonQuery(); // выполнение команды на вставку
            Console.WriteLine("INSERT command rows affected: "+rowAffected);


            // DELETE command

            SqlCommand deleteCommand = connection.CreateCommand(); // создание команды на удаление данных
            deleteCommand.CommandText = "DELETE Customers WHERE Phone = '(063)8569584'";

            rowAffected = deleteCommand.ExecuteNonQuery(); // выполнение команды на удаление
            Console.WriteLine("DELETE command rows affected: " + rowAffected);

            connection.Close();
        }

static void Main(string[] args)

{

string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения

SqlConnection connection = new SqlConnection(conStr); // создание подключения

connection.Open();

// INSERT command

SqlCommand insertCommand = connection.CreateCommand(); // создание команды на вставку данных

insertCommand.CommandText = "INSERT Customers VALUES ('Alex', 'Petrov', 'Petrovich', 'Заворотная 7', NULL, 'Kiyv', '(063)8569584', '2010-01-01')";

int rowAffected = insertCommand.ExecuteNonQuery(); // выполнение команды на вставку

Console.WriteLine("INSERT command rows affected: "+rowAffected);

// DELETE command

SqlCommand deleteCommand = connection.CreateCommand(); // создание команды на удаление данных

deleteCommand.CommandText = "DELETE Customers WHERE Phone = '(063)8569584'";

rowAffected = deleteCommand.ExecuteNonQuery(); // выполнение команды на удаление

Console.WriteLine("DELETE command rows affected: " + rowAffected);

connection.Close();

}

Пример выполнение команд, возвращающих данные в табличном представлении:

static void Main(string[] args)
        {
            string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения

            SqlConnection connection = new SqlConnection(conStr); // создание подключения
            connection.Open();

            SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", connection); // построение команды, возвращающей данные в табличном представлениии

            SqlDataReader reader = cmd.ExecuteReader(); 

            // с помощью объекта SqldataReder можно просматривать резельтаты запроса строка за строкой
            // метод Read() возвращает значение true или false в зависимости от того, достигнут ли конец пакета строк, пришедших от сервера
            // так же метод Read при каждом его вызове перемещается к следующей строке пакета, пришедшего от сервера
            while (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Console.WriteLine(reader.GetName(i) + ": " + reader[i]);
                }
                Console.WriteLine(new string('_', 20));
            }
            reader.Close();
            connection.Close();
        }

static void Main(string[] args)

{

string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=MyDB; Integrated Security=True;"; // создание строки подключения

SqlConnection connection = new SqlConnection(conStr); // создание подключения

connection.Open();

SqlDataReader reader = cmd.ExecuteReader();

// с помощью объекта SqldataReder можно просматривать резельтаты запроса строка за строкой

// метод Read() возвращает значение true или false в зависимости от того, достигнут ли конец пакета строк, пришедших от сервера

// так же метод Read при каждом его вызове перемещается к следующей строке пакета, пришедшего от сервера

while (reader.Read())

{

for (int i = 0; i < reader.FieldCount; i++)

{

Console.WriteLine(reader.GetName(i) + ": " + reader[i]);

}

Console.WriteLine(new string('_', 20));

}

reader.Close();

connection.Close();

}

Асинхронное выполнение команд

ТРАНЗАКЦИИ

Добавить комментарий Отменить ответ