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

Все исходники /  Язык программирования C# /  OS Windows /  Web ASP.NET /  MS SQL и сайты на ASP.NET / 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. В конце статьи прикреплен исходник использования данного модуля доступа к данным.

Получение данных из базы MS SQL

Построение работы с базой данных на технологии прямого ADO.NET сводится к нескольким основным этапам:
  • Подготовка подключения к базе. Выбор способа подключения и создание строки подключения.
  • Создание команды получения или обновления данных. SQL команды (запросы) создаются посредством объектов класса SQLCommand.
  • Подключение к базе данных и выполнение команд (SQL запросов). Подключение происходит через объект SQLConnection.
  • Получение данных. Если выполняется команда SELECT, то ожидается возврат данных в виде таблиц. Получить данные помогают объекты классов SQLDataAdapter и SQLDataReader.

Примеры строк подключения к MS SQL Server

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

Пример строки подключения. Проверка подлинности SQL Server:
"Data Source=COMP\SQLEXPRESS_2016;Initial Catalog=DBMSSQL;Persist Security Info=False;Integrated Security=False;User ID=pashaSQL2016;Password=1234";
COMP\SQLEXPRESS_2016 - имя сервера
DBMSSQL - название базы данных
Persist Security Info=False - запрет сохранения идентификационных данных после открытия соединения.
Integrated Security=False - проверка подлинности пользователя SQL сервером
pashaSQL2016;Password=1234 - регистрационные данные пользователя на SQL сервере
Пример строки подключения. Проверка подлинности Windows:
Data Source=COMP\SQLEXPRESS_2016;Initial Catalog=DBMSSQL;Integrated Security=True
COMP\SQLEXPRESS_2016 - имя сервера
DBMSSQL - название базы данных
Integrated Security=True - проверка подлинности пользователя зарегистрированного в OS Windows

Подключение к SQL Server

Соединение с базой данных MS SQL Server происходит при помощи экземпляра класса SQLConnection. Завершая сеанс связи с сервером баз данных необходимо явно закрывать соединение вызывая методы Close() или Dispose().

Объект SqlConnection выходя за пределы области видимости не закрывается самостоятельно. Для гарантированного автоматического закрытия соединения рекомендуется использовать оператор using.

Листинг метода подключения и выполнения запросов к базе данных:
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.

Например: "Data Source=PAVEL\SQLEXPRESS_2016;Initial Catalog=DBMSSQL;Integrated Security=True;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 раз.

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

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

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

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

Последующие запросы к базе данных: Последующие подключения через SQLDataAdapter
Последующие запросы одиночных команды выполняются почти в 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. Сравнение скорости работы объектов при запросах к базе данных с оптимизацией и без.

Скачать исходник

  • Файл: aspsqladonet.zip
  • Размер: 1470 Кбайт
  • Загрузки: 122