Методические рекомендации по выполнению практических работ по междисциплинарному курсу

• Название: Методические рекомендации по выполнению практических работ по междисциплинарному курсу
• Дата: 05.09.2022
• Размер: 2.6 Mb.
• Имя файла: MU_PR_MDK_01_01.pdf
• Тип: Методические рекомендации; #663423
• страница: 13 из 13

Практическая работа №27 Создание запросов к БД
Для выполнения запросов к базе данных SQLite применяется класс SqliteCommand, который представляет реализацию интерфейса System.Data.IDbCommand. Для создания объекта SqliteCommand можно использовать один из его конструкторов:

SqliteCommand()

SqliteCommand(String): создает объект SqliteCommand, в конструктор которого передается выполняемое выражение SQL

SqliteCommand(String, SqliteConnection): создает объект SqliteCommand, в конструктор которого передается выполняемое выражение SQL и используемое подключение к базе данных в виде объекта SqliteConnection

SqliteCommand(String,
SqliteConnection,
SqliteTransaction): третий параметр представляет применяемую транзакцию в виде объекта SqliteTransaction
Альтернативным способом создания объекта
SqliteCommand представляет метод CreateCommand90 класса SqliteConnection:
1 2
3 4
5 using (var connection = new SqliteConnection("Data Source=usersdata.db"))
{ connection.Open();
SqliteCommand command = connection.CreateCommand();
}
Для конфигурации объекта SqliteCommand можно использовать ряд его свойств, некоторые из них:

CommandText: хранит выполняемую команду SQL

CommandTimeout: хранит временной интервал в секундах, после которого
SqliteCommand прекращает попытки выполнить команду. По умолчанию равен 30 секундам. Значение 0 представляет отстутсвие интервала.

Parameters: предствляет параметры команды

Connection: предоставляет используемое подключение SqliteConnection
Например, установим свойства подключения и выполняемой команды:
1 2
3 4
5 6
7 using (var connection = new SqliteConnection("Data Source=usersdata.db"))
{ connection.Open();
SqliteCommand command = new SqliteCommand(); command.Connection = connection; command.CommandText = "CREATE TABLE Users(_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, Name TEXT NOT
NULL)";
}
Чтобы выполнить команду, необходимо применить один из методов SqliteCommand:

ExecuteNonQuery: выполняет sql-выражение и возвращает количество измененных записей. Подходит для sql-выражений INSERT, UPDATE, DELETE, CREATE.

ExecuteReader(): выполняет sql-выражение и возвращает считанные из таблицы строки. Подходит для sql-выражения SELECT.

ExecuteScalar(): выполняет sql-выражение и возвращает одно скалярное значение, например, число. Подходит для sql-выражения SELECT в паре с одной из встроенных функций SQL, как например, Min, Max, Sum, Count.
Создание таблицы
Для создания базы данных применяется SQL-команда CREATE TABLE, после которой указывается имя создаваемой таблицы и в скобках определения столбцов.
Например, создадим таблицу "Users", которая будет иметь три столбца - _id (уникальный идентификатор), Name (имя), Age (возраст):
1 2 using System; using Microsoft.Data.Sqlite;

3 4
5 6
7 8
9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 namespace HelloApp
{ class Program
{ static void Main(string[] args)
{ using (var connection = new SqliteConnection("Data Source=usersdata.db"))
{ connection.Open();
SqliteCommand command = new SqliteCommand(); command.Connection = connection; command.CommandText = "CREATE TABLE Users(_id INTEGER NOT
NULL PRIMARY KEY AUTOINCREMENT UNIQUE, Name TEXT NOT NULL,
Age INTEGER NOT NULL)"; command.ExecuteNonQuery();
Console.WriteLine("Таблица Users создана");
}
Console.Read();
}
}
}
После выполнения команды в базе данных можно будет найти таблицу Users:
Для просмотра бд SQLite можно использовать специальный инструмент - DB Browser for
SQLite.
Добавление данных
Теперь добавим в выше созданную таблицу Users новый объект:
1 2
3 4
5 6
7 8
9 10 11 12 13 14 15 16 17 18 19 20 21 22 using System; using Microsoft.Data.Sqlite; namespace HelloApp
{ class Program
{ static void Main(string[] args)
{ using (var connection = new SqliteConnection("Data Source=usersdata.db"))
{ connection.Open();
SqliteCommand command = new SqliteCommand(); command.Connection = connection; command.CommandText = "INSERT INTO Users (Name, Age) VALUES
('Tom', 36)"; int number = command.ExecuteNonQuery();
Console.WriteLine($"В таблицу Users добавлено объектов: {number}");
}
Console.Read();

23 24
}
}
}
Для вставки объекта используется sql-выражение INSERT, которое имеет следующий синтаксис:
1
INSERT INTO название_таблицы (столбец1, столбец2, столбецN) VALUES ( значение1, значение2, значениеN)
В ранее созданной таблице Users определены три столбца - __id и Age, которые хранят целое число, и Name, который хранит строку. Поэтому соответственно мы добавляем для столбца Name значение 'Tom', а для столбца Age число 36.
Здесь метод ExecuteNonOuery() возвращает число затронутых строк (в данном случае добавленных в таблицу объектов). Хотя нам необязательно возвращать результат метода, но данный результат может использоваться в качестве проверки, что операция, в частности, добавление, прошла успешно.
После добавления данных мы сможем их увидеть через DB Browser for SQLite:
Подобным образом можно добавить несколько объектов:
1 2
3 4
5 6
7 8
9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 using System; using Microsoft.Data.Sqlite; namespace HelloApp
{ class Program
{ static void Main(string[] args)
{ string sqlExpression = "INSERT INTO Users (Name, Age) VALUES
('Alice', 32), ('Bob', 28)"; using
(var connection
= new
SqliteConnection("Data
Source=usersdata.db"))
{ connection.Open();
SqliteCommand command = new SqliteCommand(sqlExpression, connection); int number = command.ExecuteNonQuery();
Console.WriteLine($"В таблицу Users добавлено объектов:
{number}");
}
Console.Read();
}
}
}
Обновление объектов
Для обновления применяется sql-команда UPDATE, которое имеет следующий синтаксис:
1 2
3
UPDATE название_таблицы
SET столбец1=значение1, столбец2=значение2, столбецN=значениеN
WHERE некоторый_столбец=некоторое_значение
Применим эту команду:
1 using System;

2 3
4 5
6 7
8 9
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 using Microsoft.Data.Sqlite; namespace HelloApp
{ class Program
{ static void Main(string[] args)
{ string sqlExpression = "UPDATE Users SET Age=20 WHERE Name='Tom'"; using (var connection = new SqliteConnection("Data Source=usersdata.db"))
{ connection.Open();
SqliteCommand command = new SqliteCommand(sqlExpression, connection); int number = command.ExecuteNonQuery();
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 using System; using Microsoft.Data.Sqlite; namespace HelloApp
{ class Program
{ static void Main(string[] args)
{ string sqlExpression = "DELETE FROM Users WHERE Name='Tom'"; using (var connection = new SqliteConnection("Data Source=usersdata.db"))
{ connection.Open();
SqliteCommand command = new SqliteCommand(sqlExpression, connection); int number = command.ExecuteNonQuery();
Console.WriteLine($"Удалено объектов: {number}");
}
Console.Read();

22 23 24
}
}
}
Во всех трех случаях фактически меняется только 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 using System; using Microsoft.Data.Sqlite; namespace HelloApp
{ class Program
{ static void Main(string[] args)
{
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 (var connection = new SqliteConnection("Data Source=usersdata.db"))
{ connection.Open();
// добавление
SqliteCommand command = new SqliteCommand(sqlExpression, connection); int number = command.ExecuteNonQuery();
Console.WriteLine($"Добавлено объектов: {number}");
// обновление ранее добавленного объекта
Console.WriteLine("Введите новое имя:"); name = Console.ReadLine(); sqlExpression = $"UPDATE Users SET Name='{name}' WHERE Age={age}"; command.CommandText = sqlExpression; number = command.ExecuteNonQuery();
Console.WriteLine($"Обновлено объектов: {number}");
}
Console.Read();
}
}
}
Консольный вывод:
Введите имя:
Tom
Введите возраст:
36
Добавлено объектов: 1
Введите новое имя:
Sam
Обновлено объектов: 1

Практическая работа №28 Создание хранимых процедур
Сначала вы добавляете хранимую процедуру в ваш проект (при помощи использования меню Project и выбора пункта Add Stored Procedure). В проект будет добавлен новый класс. В листинге 18.1 показан базовый код. который имеется в файле нового класса. Вы можете добавить свой код в статическую процедуру UpdateEmployeeLogin.
\pannMvn К*31 using System; using System.Data; using System.Data.SalClient; using System.Data.SalTypes; using Microsoft.SalServer.Server; public partial class StoredProcedures
[Microsoft.SqlServer.Server.SqlProcedure] public static void UpdateEmployeeLogin()
{
// Здесь вы можете разместить свой код
}
Все объекты управляемого кода (в проекте SQL Server) для выполнения своей работы ис- пользуют классы данных .NET Framework (т. е. ADO.NET). Это означает, что написанные вами хранимые процедуры приведут к созданию и использованию экземпляров таких классов, как SqlConnection и SqlCommand. Код, который вы пишете, идентичен коду доступа к данным, который вы писали бы в любом другом типе проекта .NET: библиотеке классов, Web-проекте или проекте Windows-форм. Поскольку общим знаменателем является использование классов ADO.NET, то разработчикам не нужно изучать других языков (вроде Т- SQL) для работы с базой данных.
Примечание
В задачи данной главы не входит рассмотрение преимуществ и недостатков написания объектов баз данных на управляемом языке по сравнению с Т-SQL. Обратитесь к докладу фирмы Microsoft с названием "Using CLR Integration in SQL Server 2005", который имеется в MSDN. Несмотря на то, что он достаточно старый (написан в ноябре 2004 года), в нем хорошо изложена данная тема, и мы настоятельно рекомендуем его прочитать.
В листинге 18.2 показана процедура на языке С#, которая обновит таблицу Employee базы данных AdventureWorks информацией для входа в систему. Этот код несложен и понятен для любого, у кого есть опыт доступа к данным при помощи языка С#. using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures
[Microsoft.SqlServer.Server.SqlProcedure] public static void UpdateEmployeeLogin(Sqllnt32 employeeld, Sqllnt32 managerld, SqlString loginld, SqlString title, SqlDateTime hireDate, SqlBoolean currentFlag) using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand
UpdateEmployeeLoginCommand
= new
SqlCommand
()
;
UpdateEmployeeLoginCommand. CommandText =
"update HumanResources.Employee SET Managerld = " + managerId.ToString() +
", Loginld = '" + loginld.ToString() + ",M +
", Title = 1" + title.ToString() + "'" +
", HireDate = '" + hireDate.ToString() + "'" +
", CurrentFlag = " + currentFlag.ToString() +
" WHERE Employeeld = " + employeeld.ToString();
UpdateEmployeeLoginCommand.Connection = conn; conn.Open();
UpdateEmployeeLoginCommand.ExecuteNonQuery(); conn.Close();
}

Одна строка кода заслуживает более подробного объяснения. Объект SqlConnection соз- дается следующим образом:
SqlConnection conn = new SqlConnection("context connection=true")
Строка подключения "context connection=true" говорит движку провайдеров данных о том, что подключение должно быть создано в том же контексте, что и вызывающее прило- жение. Поскольку эта процедура будет работать внутри базы данных, то это означает, что вы будете и подключаться к базе данных хоста в контексте (транзакционном и прочем) вызывающего приложения, и работать в нем. Поэтому вам не нужно жестко прописывать здесь полностью всю строку подключения SQL.
Для сравнения в листинге 18.3 показан тот же самый запрос обновления на языке T-SQL.
ALTER PROCEDURE [HumanResources].[uspUpdateEmployeeLogin] @EmployeeID [int],
@ManagerID [int],
@LoginID [nvarchar](256),
@Title [nvarchar] (50),
@HireDate [datetime],
@CurrentFlag [dbo].[Flag]
WITH EXECUTE AS CALLER AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
UPDATE [HumanResources].[Employee]
SET [ManagerlD] = @ManagerID ,[LoginID] = @LoginID , [Title] = @Title ,[HireDate] =
@HireDate ,[CurrentFlag] = @CurrentFlag WHERE [EmployeelD] = @EmployeeID;
END TRY BEGIN CATCH
EXECUTE [dbo].[uspLogError];
END CATCH;
END;

4 Используемая литература и интернет источники
Маркин, А. В. Программирование на SQL : учебное пособие для среднего профессионального образования / А. В. Маркин. — Москва : Издательство Юрайт2020г.
Технология разработки программных продуктов: Учеб.пособие для студ. Сред. Проф. образования. / А.В.Рудаков. - М.:Издательский центр «Академия», 2006. - 208 с