ASP.NET SQL подключение через ADO.NET

ADO.NET - платформа доступа к данным

ADO.NET - это базовая технология доступа к данным. Основной целью технологии является снижение объемов кодирования и обслуживания. ADO.NET предоставляет самый прямой способ доступа к источникам данным MS SQL Server. Веб приложения ASP.NET используют возможности ADO.NET для соединения базами: для получения, обработки и обновления имеющихся в них данных.

Драйверы доступа к базам MS SQL Server

Сайты, построенные на платформе ASP.NET имеют возможность выбора нескольких драйверов (способов) работы с базой данных SQL Server посредством технологии ADO.NET. Для этого используются драйверы для объектно-реляционного сопоставления и драйверы объектно-реляционного доступа.

Драйверы объектно-реляционного сопоставления возвращают данные таблиц базы данных в виде экземпляров классов и их свойств. Примером драйверов объектно-ориентированного доступа к базам данных являются Entity Framework и Entity Framework Core.

Классические драйверы SQL для реляционного доступа используют SQL-язык для выполнения команд получения и обновления данных таблиц базы. Примером таких модулей являются System.Data.SqlClient и Microsoft.Data.SqlClient (введение в новый Microsoft.Data.SqlClient ➹).

Поставщик данных Microsoft.Data.SqlClient создан для .NET Framework, .NET Core и .NET Standard с перспективой долгосрочной поддержки. Модуль Microsoft.Data.SqlClient можно установить через диспетчера пакетов NuGet. В конце статьи прикреплен исходник использования данного модуля доступа к данным.

База данных исходника

База данных DBMSSQL идентична используемой в статье Entity Framework Core подключение к MS SQL. Редактировать таблицы по своему усмотрению можно используя инструмент работы с базами данных SQL Server Management Studio.

public static List CreateQuerySqlSeveralTables(string queryString, bool pooling=true)
{
    List listTables = new(); // C# 9.0

    string connectionString = Constants.SqlConnectionIntegratedSecurityPooling;
    if (pooling == false) connectionString = Constants.SqlConnectionIntegratedSecurityNoPooling;

    using SqlConnection connection = new(connectionString); // C# 9.0
    using SqlCommand command = new(queryString, connection); // C# 9.0

    connection.Open();

    using SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);

    while (reader.IsClosed == false)
    {
        DataTable result = new(); // C# 9.0
        result.Load(reader);

        listTables.Add(result);
    }

    return listTables;
}

Строка подключения к MS SQL Server создана с помощью построителя, объекта класса SqlConnectionStringBuilder входящего в состав пространства имён Microsoft.Data.SqlClient. Таким способом создаётся синтактически правильная строка подключения к серверу базы данных. Подробнее о создании строки подключения к серверу баз данных можно прочитать в статье о взаимодействии с базой данных через модуль Entity Framework Core.

Объединение подключений - Pooling

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

Для оптимизации подключений к базе данных технология ADO.NET генерирует пулы соединений. Пул в программировании - это набор ресурсов первоначально сформированных и хранящихся готовыми к использованию. При первом подключении создаётся готовая конфигурация соединения с сервером. После закрытия активного соединения объект подключения не уничтожается, а помещается в пул. При следующем подключении к серверу базы данных используется уже готовое соединение хранящееся в пуле. ADO.NET может создавать несколько пулов. В каждом пуле, в состоянии ожидания, могут храниться несколько десятков неактивных сформированных соединений. В одном пуле могут хранится только одинаковые соединения, имеющие идентичные строки подключения.

В ADO.NET по умолчанию включена поддержка пулов соединений. Если по каким-либо причинам понадобится отключить создание пулов, это можно сделать в строке подключения ключевым словом Pooling со значением False.

Pooling - сравнение производительности

Для сравнения скорости обработки SQL запросов создадим два одинаковых кода запросов, с оптимизацией подключений и без неё. Две строки запроса к базе: в первой получение данных из одной таблицы, во второй одновременное выполнение сложной команды чтения данных. Для повышения точности измерения каждая команда повторяется установленное количество раз.

Одиночная команда, запрос к одной таблице: строка команды = "SELECT * FROM Sciences";

Сложная команда, запрос данных в одной строке из нескольких таблиц: строка команды = ("SELECT * FROM Sciences;SELECT * FROM Sections;SELECT * FROM SubSections; . . . SELECT * FROM TableN");

public IActionResult Index()
{
    // Одиночная команда чтения.
    string queryString1 = "SELECT * FROM  Sciences";

    // --- Формирование сложной команды ---
    string queryString3 = null;
    int sizeQueryString3 = 10;
    for (int i = 0; i < sizeQueryString3; i++)
    {
        queryString3 += "SELECT * FROM  Sciences;" + 
                        "SELECT * FROM  Sections;" + 
                        "SELECT * FROM  SubSections;";
    }
    ViewBag.SizeQueryString3 = sizeQueryString3;

    // Количество повторений команд.
    int numRepeat = 100;
    ViewBag.NumRepeat = numRepeat;

    List dataTables = null;


    // --- Запросы с созданием пулов соединений ---

    DateTime dtPooling1 = DateTime.Now;
    for (int i = 0; i < numRepeat; i++)
    {
        dataTables = SqlQueries.CreateQuerySqlSeveralTables(queryString1);
    }
    ViewBag.PerfomancePooling1 = (DateTime.Now - dtPooling1).TotalMilliseconds;

    DateTime dtPooling3 = DateTime.Now;
    for (int i = 0; i < numRepeat; i++)
    {
        dataTables = SqlQueries.CreateQuerySqlSeveralTables(queryString3);
    }
    ViewBag.PerfomancePooling3 = (DateTime.Now - dtPooling3).TotalMilliseconds;


    // --- Запросы без создания пулов соединений ---

    DateTime dt1 = DateTime.Now;
    for (int i = 0; i < numRepeat; i++)
    {
        dataTables = SqlQueries.CreateQuerySqlSeveralTables(queryString1, false);
    }
    ViewBag.Perfomance1 = (DateTime.Now - dt1).TotalMilliseconds;

    DateTime dt3 = DateTime.Now;
    for (int i = 0; i < numRepeat; i++)
    {
        dataTables = SqlQueries.CreateQuerySqlSeveralTables(queryString3, false);
    }
    ViewBag.Perfomance3 = (DateTime.Now - dt3).TotalMilliseconds;


    return View(dataTables);
}

Быстродействие SQLDataReader

Сравнительный тест быстродействия двух вариантов команд с включенным и отключенным пулингом соединений. В первом варианте выполняется команда запроса к одной таблице с повтором 100 раз. Во втором варианте выполняется команда запроса данных у 30 таблиц одновременно, также с повтором 100 раз.

Первое подключение к базе данных:
При первом соединении с базой данных одиночная команда с пулингом выполняется почти в 2 раза дольше, чем с отключёнными пулами. Сложные команды выполняются практически одинаковое время. Логично предположить, что дополнительное время затрачивается на создание пула.

Последующие запросы к базе данных:
Последующие запросы показывают эффективность "пуловой" оптимизации подключений технологии ADO.NET. Одиночные команды выполняются почти в 8 раз быстрее при использовании пулов подключений. В случае со сложными командами эффект использования оптимизации снижается, но всё равно без пулов запросы выполняются медленнее в 1,73 раза.

Быстродействие SQLDataAdapter

Первое подключение к базе данных:
Первое чтение из базы данных с помощью SQLDataAdapter происходит практически аналогично ситуации с SQLDataReader. Но комплексные команды, при включенном пулинге выполняются гораздо быстрее.

Последующие запросы к базе данных:
Последующие запросы одиночных команды выполняются почти в 14 раз быстрее при использовании пулов подключений. Сложные запросы выполняются также чувствительно быстрее с пулингом: в 2,6 раз быстрее подключений без оптимизации. Можно отметить, что SQLDataAdapter обеспечивает гораздо большую скорость выполнения запросов по сравнению с функциональностью SQLDataReader.

Инструменты для работы с исходниками

• Сервер баз данных - MS SQL Server, MS SQL Server Express или в крайнем случае можно использовать локальный сервер MSSQLLocalDB, идущий в составе Visual Studio).
• MS SQL Server Management Studio для создания и редактирования таблиц.
• Среда программирования MS Visual Studio 2019

Прикреплённые исходники

К статье прикреплены исходники сайта с подключением к базе данных MS SQL Server через технологию ADO.NET и непосредственно файл базы данных DBMSSQL. В исходниках простейший пример чтения данных из базы объектами чтения и обновления SQLDataReader и SQLDataAdapter. Сравнение скорости работы объектов при запросах к базе данных с оптимизацией и без.