Работа с базами данных через ADO.NET

Даже если вы привыкли работать с ORM, понимать ADO.NET необходимо: Entity Framework и Dapper строятся на тех же понятиях — соединение, команда, читатель, транзакция. Знание базового уровня помогает находить узкие места, писать быстрые запросы и разбираться в странных исключениях. В статье пройдёмся по основным классам, посмотрим примеры на C# и разберём ошибки, которые чаще всего встречаются в чужом коде.

Из чего состоит ADO.NET

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

• Connection — соединение с источником данных. Для SQL Server это SqlConnection, для других СУБД — свои классы поставщиков.
• Command — SQL-запрос или вызов хранимой процедуры.
• DataReader — быстрый однонаправленный читатель результата.
• DataAdapter — мост между базой и DataSet.
• DataSet и DataTable — кэш данных в памяти с поддержкой связей и ограничений.
• Transaction — группа операций, выполняемых как единое целое.

Строка подключения и открытие соединения

Строка подключения хранит адрес сервера, имя базы и способ аутентификации. Не зашивайте её в код: вынесите в файл конфигурации. Для встроенной проверки подлинности Windows пароль в строке не нужен вовсе, и это лучший вариант.

using System.Data.SqlClient;

string cs = "Server=localhost;Database=Shop;Integrated Security=true;";

using (var conn = new SqlConnection(cs))
{
    conn.Open();
    Console.WriteLine("Версия сервера: " + conn.ServerVersion);
}

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

Выполнение команд

У SqlCommand три основных метода выполнения, и выбирать нужно по задаче.

Параметры: защита и скорость

Никогда не склеивайте SQL-запрос из пользовательских строк. Это прямой путь к SQL-инъекциям, при которых злоумышленник вставляет собственные команды в запрос. Параметры решают проблему: значение передаётся отдельно от текста и не интерпретируется как код.

var sql = "SELECT Id, Name, Price FROM Products WHERE CategoryId = @cat AND Price > @minPrice";

using (var conn = new SqlConnection(cs))
using (var cmd = new SqlCommand(sql, conn))
{
    cmd.Parameters.Add("@cat", SqlDbType.Int).Value = 5;
    cmd.Parameters.Add("@minPrice", SqlDbType.Decimal).Value = 100m;
    conn.Open();

    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            int id = reader.GetInt32(0);
            string name = reader.GetString(1);
            decimal price = reader.GetDecimal(2);
            Console.WriteLine($"{id}: {name} - {price}");
        }
    }
}

Кроме безопасности, параметризованные запросы выигрывают в скорости: сервер повторно использует план выполнения, ведь текст запроса остаётся неизменным. Явно указывайте тип параметра, а не полагайтесь на автоопределение — так вы избежите неожиданных преобразований, которые мешают использовать индексы.

DataReader: быстрое чтение

SqlDataReader читает строки по одной и не загружает весь результат в память. Это идеально для больших выборок и однопроходной обработки. Но есть ограничения: пока читатель открыт, соединение занято, и вернуться назад по результатам нельзя.

• Обращайтесь к полям по индексу или по ординалу, полученному через GetOrdinal, — это быстрее, чем по имени в каждой итерации.
• Проверяйте значения NULL через IsDBNull, иначе получите исключение приведения.
• Закрывайте читатель сразу после использования.
• Для нескольких наборов результатов вызывайте NextResult.

DataSet и DataAdapter

В отключённой модели вы заполняете DataTable одной командой и затем работаете с ней без соединения. Это удобно для привязки к таблицам на экране и для небольших справочников.

var table = new DataTable();
using (var conn = new SqlConnection(cs))
using (var adapter = new SqlDataAdapter("SELECT * FROM Categories", conn))
{
    adapter.Fill(table);
}

foreach (DataRow row in table.Rows)
{
    Console.WriteLine(row["Name"]);
}

Адаптер сам открывает и закрывает соединение, если оно было закрыто до вызова Fill. Для обратной записи изменений используйте Update и заранее настроенные команды вставки, обновления и удаления; для простых случаев помогает SqlCommandBuilder. Но с крупными таблицами будьте осторожны: загружать миллион строк в память ради фильтрации на клиенте — плохая затея.

Транзакции

Если несколько изменений должны либо пройти все, либо не пройти вовсе, оберните их в транзакцию. Классический пример — перевод денег между счетами.

using (var conn = new SqlConnection(cs))
{
    conn.Open();
    using (var tx = conn.BeginTransaction())
    {
        try
        {
            var debit = new SqlCommand("UPDATE Accounts SET Balance = Balance - @s WHERE Id = @a", conn, tx);
            debit.Parameters.AddWithValue("@s", 500m);
            debit.Parameters.AddWithValue("@a", 1);
            debit.ExecuteNonQuery();

            var credit = new SqlCommand("UPDATE Accounts SET Balance = Balance + @s WHERE Id = @a", conn, tx);
            credit.Parameters.AddWithValue("@s", 500m);
            credit.Parameters.AddWithValue("@a", 2);
            credit.ExecuteNonQuery();

            tx.Commit();
        }
        catch
        {
            tx.Rollback();
            throw;
        }
    }
}

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

Асинхронная работа

В приложениях с интерфейсом и в веб-сервисах блокирующие вызовы вредны: поток простаивает, пока база думает. У всех основных методов есть асинхронные версии: OpenAsync, ExecuteReaderAsync, ExecuteNonQueryAsync, ReadAsync. Используйте их вместе с await — тогда поток освобождается для других задач.

Типичные ошибки

• Утечка соединений. Забыли using или закрыли соединение не во всех ветках. Пул исчерпывается, приложение начинает выдавать таймауты.
• Склейка строк в запросах. Уязвимость и лишние компиляции плана.
• Выборка SELECT * без нужды. Передаются лишние данные, ломается покрывающий индекс.
• Запросы в цикле. Тысяча обращений к базе вместо одного запроса с соединением таблиц или пакетной вставки.
• Игнорирование NULL. Исключения при приведении типов в самый неудобный момент.
• Один глобальный объект соединения на всё приложение. Классы соединений не рассчитаны на одновременное использование из нескольких потоков.

Обработка ошибок

Исключение SqlException содержит свойство Number, по которому можно отличить нарушение уникальности, взаимную блокировку и недоступность сервера. Для временных сбоев (разрыв сети, перезапуск сервера) имеет смысл повторить операцию несколько раз с паузой, а для ошибок логики — сразу сообщать пользователю. Не прячьте исключения в пустом catch: в продакшене вы потом не поймёте, что произошло.

Заключение

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