Класс SqlDataReader пространства имён Microsoft.Data.SqlClient предназначен для получения данных из базы. Как следует из названия, объекты данного класса только читают информацию, но не изменяют данные в базе. Компенсировать это ограничение с лихвой помогает идущая в комплекте ADO.NET возможность прямых SQL запросов вставки и обновления данных. Соответственно имени SqlDataReader предназначен для работы с базами данных MS SQL Server.
Функциональность работы с базой данных SQL Server построенную на ADO.NET: SqlConnection, SqlCommand, SqlDataReader очень быстро можно внедрить в веб приложение ASP.NET. Говоря на принятом сленге, такой способ получения и изменения данных имеет низкий порог знаний для входа.
В подготовленном исходнике веб приложения ASP.NET Core MVC можно посмотреть и провести сравнительные тесты на быстродействие считывания данных объектами класса SqlDataReader при различных компоновках. Теоретическая часть описана в данной статье, практический программный код в прикреплённом исходнике.
Класс SqlDataReader предназначен только для последовательного считывания данных. Для временного хранения данных он не годится. Необходима оболочка для принятия данных. Пока не произошло считывание, объект SqlDataReader держит соединение открытым. В такой ситуации нельзя выполнять другие запросы.
И хотя для новых запросов могут создаваться новые подключения, каждое такое подключение будет опять зафиксировано в открытом состоянии. Количество открытых соединений ограничено и при достижении максимума выбросится исключение: System.InvalidOperationException: "Истекло время ожидания. Период тайм-аута истек до получения соединения из пула. Это могло произойти из-за того, что все соединения пула в использовании и достигнут максимальный размер пула." ("Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.")
Логично считать данные в промежуточный объект и вызвать метод SqlDataReader.Close(), тем самым освободив соединение. Простой способ считать данные через объект SqlDataReader предлагает класс DataTable (представляет одну таблицу в памяти). Метод DataTable.Load(IDataReader) считывает полные данные для одной таблицы. При запросе к нескольким таблицам требуется несколько объектов DataTable.
В чём суть временного промежуточного хранилища? В простейшем приложении можно, например, прочитать данные через SqlDataReader сразу в консоль. Но вот другой пример: в контроллере ASP.NET MVC были запрошены данные для вывода в представление, каким контейнером их передать? Не передавать же в качестве модели объект SqlDataReader и заблокировать на некоторое время соединение. Вот здесь и необходим промежуточный объект хранения извлечённых данных. И потом, в приложении, для удобного получения данных и рационального программного кода, неизбежно понадобится инкапсуляция процесса SqlConnection-> SqlCommand-> SqlDataReader-> получение данных-> соединение закрывается.
У компонентов ADO.NET узкая специализация: SqlConnection обеспечивает соединение с SQL Server, SqlDataReader читает данные. Команды, они же SQL-запросы, непосредственно выполняют объекты класса SqlCommand.
В случае выполнения SQL инструкции SELECT, именно класс SqlCommand создаёт экземпляр SqlDataReader для считывания запрошенных данных. Объект SqlDataReader нельзя создать конструктором, его объект создает исполняющая запрос команда в лице класса SqlCommand. Сначала команда отправляется в открытое соединение, после успешного выполнения SQL запроса возвращается объект SqlDataReader.
В программном коде команды выглядят так:
SqlCommand command = new("SELECT * FROM Table WHERE Id= '1'", connection);
SqlDataReader reader = command.ExecuteReader();
Теперь ридер может считывать запрошенные данные.
Команда обновления данных с использованием параметров:
SqlCommand command = new("UPDATE Sciences SET NumberViews=@nv WHERE uri=@uri");
SqlDataReader reader = command.ExecuteReader();
Ридер возвращает только количество изменённых строк свойством RecordsAffected. Данных для чтения, конечно же, не будет.
Или, альтернативно, можно выполнить команду с помощью другого родственного метода класса SqlCommand:
int rowsUpdated = command.ExecuteNonQuery();
В результате выполнения команды возвратится только число изменённых строк.
В случае, когда необходимо выполнить запросы к нескольким таблицам, лучше отдавать предпочтение формирования одной составной команды, чем нескольким командам к каждой таблице отдельно.
Одиночные команды:
SqlCommand command = new("SELECT * FROM Test;");
SqlCommand command1 = new("SELECT * FROM Test1;");
SqlCommand command2 = new("SELECT * FROM Test2;");
SqlCommand command3 = new("SELECT * FROM Test3;");
SqlCommand command3 = new("SELECT * FROM TableData;");
Вот как выглядит составной SQL запрос при работе с классом SqlCommand:
SqlCommand compoundСommand = new("SELECT * FROM Test;SELECT * FROM Test1;SELECT * FROM Test2;SELECT * FROM Test3;SELECT * FROM TableData;"
Сравнительные тесты на быстродействие чтения из базы данных. Из базы данных запрашиваются данные нескольких таблиц (5-ти штук), при этом запросы повторяются установленное количество раз (100).
В принципе это логично: на создание каждого объекта SqlCommand и отправки команды в соединение с базой данных затрачивается некоторое время.
Безусловно, на разных машинах абсолютное время выполнения будет различным, но соотношение между различными способами построения команд сохраняется на всех устройствах.
Немного отступая от темы чтения данных, хочу написать про изменение бинарных данных. Подобная функциональность часто необходима при создании интерактивных сайтов.
Если команда создаётся без применения параметров приходится сначала массив байтов сериализировать в строку шестнадцатеричных символов, и затем строку запроса явно конвертировать в формат SQL VARBINARY.
Сериализация массива байтов файла изображения в строку шестнадцатеричных символов:
Или с применением входного параметра. Это рекомендуемый способ вставки и изменения бинарных, и вообще любых типов, данных. При этом способе не требуется преобразовывать байты в строку, достаточно в качестве значения SqlParameter.Value указать ссылку на массив.
SqlParameter binary = new()
{
SqlDbType = SqlDbType.VarBinary,
Direction = ParameterDirection.Input,
ParameterName = "ImgData",
Value = bytesImage
};
sqlParameters.Add(binary);
qs = "UPDATE TableData SET ImageName='" + image.FileName + "', ImageData=@ImgData WHERE Id='1'";
SqlCommand command = new(gs);
command.Parameters.Add(binary);
int rowsUpdated = command.ExecuteNonQuery();
Наиболее удобный способ получить данные таблиц с объекта SqlDataReader - это использование класса DataTable. Класс DataTable хранит в памяти одну таблицу. Если через SqlDataReader запрошены данные из нескольких таблиц, то необходимо вызывать метод SqlDataReader.NextResult() для получения данных следующей таблицы. Вызов метода SqlDataReader.Read() позволяет считывать строки текущей таблицы.
Программный код для считывания данных для одной таблицы:
...
using SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
// Если имя таблицы не указывать, название определяется из метаданных
DataTable result = new(); // C# 9.0
result.Load(reader);
...
Если указан параметр CommandBehavior.KeyInfo, название таблицы в объекте DataTable извлекается из метаданных базы (т.е. название будет такое же как и в базе).
Считывание данных для нескольких таблиц:
List<DataTable> listTables = new();
...
using SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
while (reader.IsClosed == false)
{
// Если имя таблицы не указывать, название определяется из метаданных
DataTable result = new(); // C# 9.0
result.Load(reader);
listTables.Add(result);
}
...
Можно данные от SqlDataReader считывать не только в объект DataTable, но и в специальный класс-модель таблицы.
Судя по тестам быстродействия с DataTable SqlDataReader работает несколько медленнее, чем с классом-моделью. Тест состоит из 100 повторов считывания данных в объект DataTable и в объект класса-модели таблицы.
Извлечение данных в альтернативный класс-модель происходит приблизительно в 1,5 раза быстрее.
Программный код универсального класса-модели таблицы:
// Таблица
public class Table
{
public string Name { get; set; }
public List<TableRow> TableRows { get; set; } = new();
public Table(string name) => Name = name;
}
// Строка
public class TableRow
{
public Dictionary<string, object> columns = new();
}
Программный код считывания данных в класс-модель:
...
using SqlDataReader sqlDataReader = command.ExecuteReader(CommandBehavior.KeyInfo);
List<Table> tables = new();
while (sqlDataReader.HasRows == true)
{
Table table = new(sqlDataReader.GetSchemaTable().Rows[0].Field<string>("BaseTableName"));
// Читаем строки
while (sqlDataReader.Read() == true)
{
TableRow tableRow = new();
for (int i = 0; i < sqlDataReader.FieldCount; i++)
{
string colName = sqlDataReader.GetColumnSchema()[i].ColumnName;
tableRow.columns.Add(colName, sqlDataReader.GetValue(colName));
}
table.TableRows.Add(tableRow);
}
tables.Add(table);
// Переход на следующую таблицу
sqlDataReader.NextResult();
}
...
Вывод полученных данных в представление:
<ul>
<li>
@foreach (Table table in Model)
{
<h4>Таблица: @table.Name</h4>
<ul>
@for (int i = 0; i < table.TableRows.Count; i++)
{
<li>
<b>Строка @(i+1)</b>
@foreach (KeyValuePair<string, object> col in table.TableRows[i].columns)
{
<ul class="mb-3">
<li>@col.Key: @col.Value</li>
</ul>
}
</li>
}
</ul>
}
</li>
</ul>
Функциональность ADO.NET даёт возможность получения и изменения данных различными способами. Можно работать исключительно SQL инструкциями, извлечением данных через SqlDataReader
и др. Одним из рекомендуемых способов работы с таблицами базы данных это использование sql-параметров для извлечения, вставки и изменения данных.
При этом выполнение команд объектами SqlCommand может сводится к вызову метода Command.ExecuteNonQuery(). Так как при использовании команд объект SqlDataReader не требуется и даже при использовании command.ExecuteReader() в объект SqlDataReader данные не вносятся. Необходимо отметить, что при выполнении SQL инструкции SELECT нельзя смешивать способы получения данных через параметры и без использования таковых. В таком случае сработает исключение:
Microsoft.Data.SqlClient.SqlException: "Инструкцию SELECT, которая присваивает значение переменной, нельзя использовать вместе с операциями получения данных."
На изображении сравнительные тесты заполнения данными DataTable и использование SELECT запроса с параметрами SqlParameter. Как видно по времени, с параметрами работа происходит заметно быстрее. Скорость работы с sql параметрами сравнима со скоростью извлечения данных в класс-модель таблицы. А вот хоть DataTable удобная оболочка для табличной порции, заполнение её происходит медленнее. Вероятно это связано с внушительной функциональностью предлагаемой классом DataTable.
ADO.NET предоставляет богатый выбор средств доступа к данным. Одним из важных компонентов является SqlDataReader для построчного считывания данных из баз SQL Server. Хотя SqlDataReader только читает данные, сопутствующий класс SqlCommand устраняет это ограничение и даёт возможность на чистом SQL языке исполнять любые команды для изменения данных в базе.
Исходник содержит программные коды возможных вариантов применения классов SqlDataReader и SqlCommand из технологии SqlClient ADO.NET. В веб приложении ASP.NET MVC осуществляется доступ к данным SQL Server и вывод полученной информации на веб страницы. В комплекте с исходными кодами веб приложения идёт файл тестовой базы данных для SQL Server.
Для работы с исходником требуется среда программирования MS Visual Studio 2019, сервер баз данных - MS SQL Server, MS SQL Server Express (можно использовать локальный сервер MSSQLLocalDB, идущий в составе Visual Studio). Желательно MS SQL Server Management Studio для создания и редактирования таблиц.
sqldatareaderadonet.zip