Лабораторная работа 5

После установки подключения мы можем взаивмодействовать с базой данных, например, выполнять к базе данных какие-либо команды, в частности, добавление, обновление или удаление данных в базе данных, их получение. Команды в 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, так и через его свойства:
1
2
3
4
5
6
7
8	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;
}
С помощью свойства CommandText устанавливается SQL-выражение, которое будет выполняться. В данном случае это запрос на создание базы данных "adonetdb". А с помощью свойства Connection можно установить объект подключения SqlConnection.
В качестве альтернативы можно было бы использовать одну из версий конструктора класса:
1
2
3
4
5
6
7	string connectionString = "Server=(localdb)\\mssqllocaldb;Database=master;Trusted_Connection=True;";
string sqlExpression = "CREATE DATABASE adonetdb";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    await connection.OpenAsync();
    SqlCommand command = new SqlCommand(sqlExpression, connection);
}
Стоит отметить, что класс 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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28	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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26	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, которая ранее была создана:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25	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($"Добавлено объектов: {number}");
            }
            Console.Read();
        }
    }
}
Для вставки объекта используется sql-выражение INSERT, которое имеет следующий синтаксис:
1	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 и увидеть добавленные данные:
 
Подобным образом можно добавить несколько объектов:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26	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($"Добавлено объектов: {number}");
            }
            Console.Read();
        }
    }
}
Обновление объектов
Обновление будет происходить аналогично, только теперь будет использоваться sql-выражение UPDATE, которое имеет следующий синтаксис:
1
2
3	UPDATE название_таблицы
SET столбец1=значение1, столбец2=значение2, столбецN=значениеN
WHERE некоторый_столбец=некоторое_значение
Применим это выражение:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25	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($"Обновлено объектов: {number}");
            }
            Console.Read();
        }
    }
}
Здесь обновляется строка, в которой Name=Tom, то есть выше добавленный объект. Если в таблице будет несколько строк, у которых Name=Tom, то обновятся все эти строки.
Удаление
Удаление производится с помощью sql-выражения DELETE, которое имеет следующий синтаксис:
1
2	DELETE FROM таблица
WHERE столбец = значение
Удалим, например, всех пользователей, у которых имя Tom:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25	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($"Удалено объектов: {number}");
            }
            Console.Read();
        }
    }
}
Во всех трех случаях фактически меняется только sql-выражение, а остальная логика остается неизменной. И мы также можем выполнять сразу несколько операций:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41	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 ('{name}', {age})";
 
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
 
                // добавление
                SqlCommand command = new SqlCommand(sqlExpression, connection);
                int number = await command.ExecuteNonQueryAsync();
                Console.WriteLine($"Добавлено объектов: {number}");
 
                // обновление ранее добавленного объекта
                Console.WriteLine("Введите новое имя:");
                name = Console.ReadLine();
                sqlExpression = $"UPDATE Users SET Name='{name}' WHERE Age={age}";
                command.CommandText = sqlExpression;
                number = await command.ExecuteNonQueryAsync();
                Console.WriteLine($"Обновлено объектов: {number}");
            }
            Console.Read();
        }
    }
}
Самостаятельно: создать консольное приложение для работы с таблицей