Подключение к базе данных(гибкий способ)

587.88K

Category:

database

ADO.net

ADO.NET предоставляет собой технологию работы с данными, которая основана на платформе .NET Framework.
Эта технология представляет нам набор классов, через которые мы можем отправлять запросы к базам данных,
устанавливать подключения, получать ответ от базы данных и производить ряд других операций.
Функционал ADO.NET построен таким образом, чтобы предоставить разработчикам унифицированный интерфейс
для работы с самыми различными СУБД.
Основу интерфейса взаимодействия с базами данных в ADO.NET представляет ограниченный круг объектов:
Connection, Command, DataReader, DataSet и DataAdapter.
С помощью объекта Connection происходит установка подключения к источнику данных.
Объект Command позволяет выполнять операции с данными из БД.
Объект DataReader считывает полученные в результате запроса данные.
Объект DataSet предназначен для хранения данных из БД и позволяет работать с ними независимо от БД.
Объект DataAdapter является посредником между DataSet и источником данных.
Главным образом, через эти объекты и будет идти работа с базой данных.

3.

Однако чтобы использовать один и тот же набор объектов для разных источников данных, необходим
соответствующий провайдер данных. Собственно через провайдер данных в ADO.NET и осуществляется
взаимодействие с базой данных. Причем для каждого источника данных в ADO.NET может быть свой провайдер,
который собственно и определяет конкретную реализацию вышеуказанных классов.
По умолчанию в ADO.NET имеются следующие встроенные провайдеры:
• Провайдер для MS SQL Server
• Провайдер для OLE DB (Предоставляет доступ к некоторым старым версиям MS SQL Server, а также к БД
Access, DB2, MySQL и Oracle)
• Провайдер для ODBC (Провайдер для тех источников данных, для которых нет своих провайдеров)
• Провайдер для Oracle
• Провайдер EntityClient. Провайдер данных для технологии ORM Entity Framework
• Провайдер для сервера SQL Server Compact 4.0

4.

Основные пространства имен, которые используются в ADO.NET:
System.Data: определяет классы, интерфейсы, делегаты, которые реализуют архитектуру ADO.NET
System.Data.Common: содержит классы, общие для всех провайдеров ADO.NET
System.Data.Design: определяет классы, которые используются для создания своих собственных наборов данных
System.Data.Odbc: определяет функциональность провайдера данных для ODBC
System.Data.OleDb: определяет функциональность провайдера данных для OLE DB
System.Data.Sql: хранит классы, которые поддерживают специфичную для SQL Server функциональность
System.Data.OracleClient: определяет функциональность провайдера для баз данных Oracle
System.Data.SqlClient: определяет функциональность провайдера для баз данных MS SQL Server
System.Data.SqlServerCe: определяет функциональность провайдера для SQL Server Compact 4.0
System.Data.SqlTypes: содержит классы для типов данных MS SQL Server
Microsoft.SqlServer.Server: хранит компоненты для взаимодействия SQL Server и среды CLR

5.

6.

Функционально классы ADO.NET можно разбить на два уровня: подключенный и отключенный.
Каждый провайдер данных .NET реализует свои версии объектов Connection, Command, DataReader,
DataAdapter и ряда других, который составляют подключенный уровень. То есть с помощью них
устанавливается подключение к БД и выполняется с ней взаимодействие.
Как правило, реализации этих объектов для каждого конкретного провайдера в своем названии имеют префикс,
который указывает на провайдер:
Другие классы, такие как DataSet, DataTable, DataRow, DataColumn и ряд других составляют отключенный
уровень, так как после извлечения данных в DataSet мы можем работать с этими данными независимо от того,
установлено ли подключение или нет. То есть после получения данных из БД приложение может быть
отключено от источника данных.

7. Взаимодействие с базой данных

Создание базы данных

8.

Для хранения больших наборов данных используются различные базы
данных. В WPF, как и в целом в .NET, мы можем работать с различными
системами управления баз данных - MS SQL Server, Oracle, MySQL,
MongoDB, Postgres и т.д.
Рассмотрим взаимодействие приложения на WPF и базы данных MS
SQL Server.
Прежде всего для работы с базами данных должен быть установлен и
запущен MS SQL Server.
Итак, создадим новый проект. Пусть он будет называться DbApp.
Вначале создадим базу данных, к которой мы будем подключаться. Для
этого откроем специальную программу SQL Server 2019 Management
Studio, которая устанавливается вместе с MS SQL Server и которая
служит для управления базами данных. В левом древовидном меню
найдем пункт Databases, нажмем на него правой кнопкой мыши и в
появившемся контекстном меню выберем пункт New Database...:

9.

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

10.

Затем нам открывается дизайнер таблицы:
В окне свойств таблицы в поле Name надо ввести имя
таблицы - Phones, а в поле Identity Column ввести Id, так
как столбец Id будет идентификатором
Установим курсор на столбец Id и в панели инструментов
программы, нажмем на золотой ключик. Этот ключик будет
указывать, что столбец Id будет выполнять роль первичного
ключа.
После этого нажмем на сохранение и затем на клавишу F5
(обновление), и в узле нашей базы данных появится новая
таблица, которая будет называться dbo.Phones
В нем надо указать четыре столбца: Id, Title, Company и Price, которые
будут представлять соответственно уникальный идентификатор
телефона, название его модели, производителя и цену.
У первого и четвертого столбца надо указать тип int (то есть
целочисленный), а у столбцов Title и Company - тип nvarchar
(строковый).

11.

После создания базы данных последним шагом является добавление в базу данных хранимую процедуру,
которая осуществляет добавление нового объекта в базу данных.
Для этого выберем в узле базы данных пункт Programmability->Stored Procedures. Нажмем на этот узел
правой кнопкой мыши и в контекстном меню выберем пункт Stored Procedure...:
В центральной части программы открывается код процедуры, который
генерируется по умолчанию. Заменим этот код следующим:
CREATE PROCEDURE [dbo].[sp_InsertPhone]
@title nvarchar(50),
@company nvarchar(50),
@price int,
@Id int out
AS
INSERT INTO Phones (Title, Company, Price)
VALUES (@title, @company, @price)
SET @Id=SCOPE_IDENTITY()
GO
Процедура имеет три входных параметра: @title, @company и @price. Через эти параметры будут
передаваться значения для объекта в таблице Phones. В самом теле процедуры после выражения AS
идет стандартное sql-выражение INSERT, которое выполняет добавление данных. И в конце с
помощью выражения SELECT возвращается результат. Выражение SCOPE_IDENTITY() возвращает id
добавленной записи, который присваивается четвертому выходному параметру @Id. Поэтому на
выходе из процедуры мы получим id новой записи. И завершается процедура ключевым словом GO.

12. Подключение к базе данных(гибкий способ)

В любом проекте WPF по умолчанию есть файл конфигурации, который
называется app.config.
Для определения всех подключений в программе в пределах узла
<configuration> добавляется новый узел <connectionStrings>.
В этом узле определяются строки подключения с помощью элемента
<add>.
Каждая строка подключения имеет название, определяемое с помощью
атрибута name.
В данном случае строка подключения называется "DefaultConnection".
Название может быть произвольное.

13.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0"
sku=".NETFramework,Version=v4.6" />
</startup>
<connectionStrings>
<add name="DefaultConnection"
connectionString="Data
Source=.\SQLEXPRESS;Initial
Catalog=mobiledb;Integrated Security=True”
Атрибут connectionString собственно хранит строку
подключения.
Он состоит из трех частей:
Data
Source=\SQLEXPRESS:
указывает
на
название сервера.
По умолчанию для MS SQL Server Express
используется ".\SQLEXPRESS"
Initial Catalog=mobiledb: название базы данных.
Так как база данных называется mobiledb, то
соответственно здесь данное название и указываем
Integrated Security=True: задает режим
аутентификации
providerName="System.Data.SqlClient"/>
Так как мы будем подключаться к базе данных MS
</connectionStrings>
SQL Server, то соответственно мы будем
</configuration>
использовать
провайдер
для
SQL
Server,
функциональность
которого
заключена
в
пространстве имен System.Data.SqlClient.

14.

Подключение к базе данных(жесткий способ)
Необходимо добавить в проект библиотеку
System.Configuration.dll
class Program
{
static void Main(string[] args)
{
string connectionString = @"Data Source=.\SQLEXPRESS; Initial Catalog= mobiledb; Integrated Security=True";
}
}
Строка подключения представляет набор параметров в виде пар ключ=значение.
В данном случае для подключения к ранее созданной базе данных mobiledb мы определяем строку
подключения из трех параметров:
Data Source: указывает на название сервера. По умолчанию это ".\SQLEXPRESS". Поскольку в строке
используется слеш, то в начале строки ставится символ @. Если имя сервера базы данных отличается, то
соответственно его и надо использовать.
Initial Catalog: указывает на название базы данных на сервере
Integrated Security: устанавливает проверку подлинности

15. в xaml

16.

Теперь определим код подключения и все обработчики кнопок в файле кода c#:
using System;
using System.Windows;
using System.Windows.Controls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace DbApp
{
public partial class MainWindow : Window
{
string connectionString;
SqlDataAdapter adapter;
DataTable phonesTable;
public MainWindow()
{
InitializeComponent();
// получаем строку подключения из app.config
connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
}

17.

private void Window_Loaded(object sender, RoutedEventArgs e)
{
string sql = "SELECT * FROM Phones";
phonesTable = new DataTable();
SqlConnection connection=null;
try
{
connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sql, connection);
adapter = new SqlDataAdapter(command);
// установка команды на добавление для вызова хранимой процедуры
adapter.InsertCommand = new SqlCommand("sp_InsertPhone", connection);
adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
adapter.InsertCommand.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar, 50, "Title"));
adapter.InsertCommand.Parameters.Add(new SqlParameter("@company", SqlDbType.NVarChar, 50, "Company"));
adapter.InsertCommand.Parameters.Add(new SqlParameter("@price", SqlDbType.Int, 0, "Price"));

18.

SqlParameter parameter = adapter.InsertCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
parameter.Direction = ParameterDirection.Output;
connection.Open();
adapter.Fill(phonesTable);
phonesGrid.ItemsSource = phonesTable.DefaultView;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if(connection!=null)
connection.Close();
}
}
В конструктор объекту SqlConnection передается строка
подключения, которая инициализирует объект. Чтобы
использовать этот объект и подключаться к базе данных,
мы должны выполнить его метод Open(), а после
завершения работы с базой данных нам надо вызвать
метод Close() для закрытия подключения. В случае
возникновения ошибок закрытие подключения происходит
в блоке finally.

19.

private void UpdateDB()
{
SqlCommandBuilder comandbuilder = new SqlCommandBuilder(adapter);
adapter.Update(phonesTable);
}
private void updateButton_Click(object sender, RoutedEventArgs e)
{
UpdateDB();
}

20.

private void deleteButton_Click(object sender, RoutedEventArgs e)
{
if (phonesGrid.SelectedItems != null)
{
for (int i = 0; i < phonesGrid.SelectedItems.Count; i++)
{
DataRowView datarowView = phonesGrid.SelectedItems[i] as DataRowView;
if (datarowView != null)
{
DataRow dataRow = (DataRow)datarowView.Row;
dataRow.Delete();
}
}
}
UpdateDB();
}
}
}

21.

Пул подключений
Как правило, в программе используется одна или несколько одних и тех же конфигураций подключений.
И чтобы разработчику не приходилось создавать по нескольку раз в коде программы фактически одно и тоже
подключение, в ADO.NET используется механизм пула подключений.
Пул подключений позволяет использовать ранее созданные подключения. Когда менеджер подключений, который
управляет пулом, получает запрос на открытие нового подключения с помощью метода Open(), то он проверяет все
подключения пула.
Если менеджер подключений находит в пуле доступное подключение, которое в текущий момент не используется,
то оно возвращается для использования.
Если же доступного подключения нет, и максимальный размер пула еще не превышен (по умолчанию размер равен
100), то создается новое подключение.
Если доступного подключения нет, но при этом превышен максимальный размер пула, то новое подключение
добавляется в очередь и ожидает, пока в пуле не освободится место, и тогда оно станет доступным.
После закрытия подключения с помощью метода Close() закрытое подключение возвращается в пул подключений,
где оно готово к повторному использованию при следующем вызове метода Open().

22.

В пул помещаются подключения только с одинаковой конфигурацией.
ADO.NET поддерживает несколько пулов одновременно, и для каждой конфигурации строки подключения создается свой
собственный пул.
Все подключения в пуле различаются по нескольким признакам:
•строка подключения
•учетные записи, используемые при подключении
•процесс приложения
В следующем примере в первых двух блоках using будет использоваться одно и то же подключение из пула, поскольку строка
подключения будет совпадать:

23.

string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True";
string connectionString2 = @"Data Source=.\SQLEXPRESS;Initial Catalog=players;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open(); // создается первый пул
Console.WriteLine(connection.ClientConnectionId);
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open(); // подключение извлекается из первого пула
Console.WriteLine(connection.ClientConnectionId);
}
using (SqlConnection connection = new SqlConnection(connectionString2))
{
connection.Open(); // создается второй пул, т.к. строка подключения отличается
Console.WriteLine(connection.ClientConnectionId);
}
Если параметр Min Pool Size не указан в строке подключения или его значение равно 0, то подключения в
пуле будут закрыты после периода отсутствия активности (4-8 минут), либо если разорвана связь с
сервером базы данных. Но если значение параметра Min Pool Size больше 0, пул подключений не
удаляется, пока не будет выгружен домен приложения AppDomain и не завершится процесс.

24.

Выполнение команд и SqlCommand
После установки подключения мы можем выполнить к базе данных какие-либо команды, например, добавить в базу
данных объект, удалить, изменить его или просто извлечь.
Команды представлены объектом интерфейса 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))
{
connection.Open();
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM Users";
command.Connection = connection;
}
или
connection.Open();
SqlCommand command = new SqlCommand(sqlExpression, connection);

25.

Чтобы выполнить команду, необходимо применить один из методов SqlCommand:
• ExecuteNonQuery: просто выполняет sql-выражение и возвращает количество измененных записей.
Подходит для sql-выражений INSERT, UPDATE, DELETE.
• ExecuteReader: выполняет sql-выражение и возвращает строки из таблицы. Подходит для sqlвыражения SELECT.
• ExecuteScalar: выполняет sql-выражение и возвращает одно скалярное значение, например, число.
Подходит для sql-выражения SELECT в паре с одной из встроенных функций SQL, как например, Min,
Max, Sum, Count.

26.

Добавление объектов
Выполним команду по добавлению одного объекта в таблицу 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("Добавлено объектов: {0}", number);
}
метод ExecuteNonOuery() возвращает число затронутых
Console.Read();
строк (в данном случае добавленных в таблицу объектов)
}
}

27.

Для вставки объекта используется sql-выражение INSERT, которое имеет следующий синтаксис:
INSERT INTO название_таблицы (столбец1, столбец2, столбецN) VALUES ( значение1, значение2, значениеN)
Чтобы убедиться, что данные добавлены, мы можем перейти к таблице Users в SQL Server Management Studio и с
помощью опции Select Top 1000 Rows вывести добавленную строку:

28.

Удаление
Удаление производится с помощью 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("Удалено объектов: {0}", number);
}

English Русский Rules