Классы Command и DataReader

50

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

Специфичные для поставщика классы Command реализуют стандартную функциональность, в точности как классы Connection. В данном случае небольшой набор ключевых свойств и методов, используемых для выполнения команд через открытое соединение, определяется интерфейсом IDbCommand.

Основные сведения о командах

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

Текстом команды может быть SQL-оператор, хранимая процедура либо имя таблицы. Это зависит от типа используемой команды. Существуют три типа команд, которые описаны в таблице:

Значения перечисления CommandType
Значение Описание
CommandType.Text

Команда будет выполнять прямой оператор SQL. Оператор SQL указывается в свойстве CommandText. Это — значение по умолчанию

CommandType.StoredProcedure

Команда будет выполнять хранимую процедуру в источнике данных. Свойство CommandText представляет имя хранимой процедуры

CommandType.TableDirect

Команда будет опрашивать все записи таблицы. CommandText — имя таблицы, из которой команда извлечет все записи. (Эта опция предназначена только для обратной совместимости с некоторыми драйверами OLE DB. Она не поддерживается поставщиком данных SQL Server и не работает так хорошо, как тщательно направленный запрос.)

Например, ниже показано, как создать объект Command, представляющий запрос:

// Создать объект Connection из строки подключения в файле web.config
string connectionString = 
    WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);

// Создать команду
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM Employees";

А вот более эффективный способ использования одного конструктора Command. Обратите внимание, что указывать CommandType не нужно, поскольку значение CommandType.Text принимается по умолчанию:

// ...
   
// Создать команду
SqlCommand command = new SqlCommand("SELECT * FROM Employees", connection);

В этих примерах объект Command просто определяется, но не выполняется. Объект Command предоставляет три метода, которые можно использовать для выполнения команды, в зависимости от того, нужно ли вам извлечь полный результирующий набор, получить единственное значение или просто выполнить отличную от запроса команду. Эти методы перечислены в таблице ниже:

Методы Command
Метод Описание
ExecuteNonQuery()

Выполняет команды, отличные от select, такие как SQL-операторы вставки, удаления или обновления записей. Возвращаемое значение указывает количество строк, обработанных командой. Метод ExecuteNonQuery() также можно использовать для выполнения команд определения данных, которые создают, изменяют и уничтожают объекты базы данных (наподобие таблиц, индексов, ограничений и т.п.)

ExecuteScalar()

Выполняет запрос select и возвращает значение первого поля первой строки из набора строк, сгенерированного командой. Этот метод обычно применяется при выполнении агрегатной команды select, использующей функции вроде count() или sum() для вычисления единственного значения

ExecuteReader()

Выполняет запрос select и возвращает объект DataReader, который является оболочкой однонаправленного курсора, доступного только для чтения

Чуть позже я продемонстрирую использование этих методов, но для начала нужно разобрать класс DataReader, экземпляр которого возвращает метод ExecuteReader().

Класс DataReader

Класс DataReader позволяет читать данные, возвращенные командой SELECT, по одной строке за раз, в однонаправленном, доступном только для чтения потоке. Иногда это называют курсором. Использование DataReader — простейший путь получения данных, но ему недостает возможностей сортировки и связывания автономного объекта DataSet. Однако DataReader представляет наиболее быстрый способ доступа к данным. Ниже перечислены основные методы DataReader:

Методы класса DataReader
Метод Описание
Read()

Перемещает курсор строки на следующую строку в потоке. Этот метод также должен быть вызван перед чтением первой строки данных (Когда DataReader создается впервые, курсор строки помещается в позицию непосредственно перед первой строкой.) Метод Read() возвращает true, если существует следующая строка для чтения, или false, если прочитана последняя строка в наборе

GetValue()

Возвращает значение, сохраненное в поле с указанным именем столбца или индексом, внутри текущей выбранной строки. Тип возвращенного значения — ближайший тип .NET, наиболее соответствующий встроенному значению, хранимому в источнике данных. Если вы обратитесь к полю по индексу и нечаянно передадите неверный индекс, ссылающийся на несуществующее поле, то получите исключение IndexOutOfRangeException. Используя индексатор для DataReader, можно получить значение по имени поля

GetValues()

Сохраняет значения текущей строки в массиве. Количество сохраняемых полей зависит от размеров массива, переданного этому методу. С помощью свойства DataReader.FieldCount можно определить действительное количество полей в строке и воспользоваться этой информацией для создания массива нужного размера, если нужно сохранить в нем все поля

GetInt32(), GetChar(), GetDateTime(), Get...()

Эти методы возвращают значение поля с указанным индексом в текущей строке, причем тип данных указывается в имени метода. Обратите внимание, что если попытаться присвоить возвращенное значение переменной неверного типа, возникнет исключение InvalidCastException. Кроме того, эти методы не поддерживают типов, допускающих NULL-значения.

Если поле может содержать null, это придется проверить перед вызовом одного из методов. Чтобы проверить на null-значение, сравните непреобразованное значение (которое можно извлечь по позиции методом GetValue() или по имени с помощью индексатора DataReader) с константой DBNull.Value

NextResult()

Если команда, которая сгенерировала DataReader, возвратила более одного набора строк, этот метод перемещает указатель на следующую строку и устанавливает его непосредственно перед первой строкой

Close()

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

Метод ExecuteReader() и DataReader

В следующем примере создается простая команда запроса, которая должна вернуть все записи из таблицы Employees базы данных Northwind. Команда создается при загрузке страницы. Соединение открывается, и команда выполняется методом ExecuteReader(), который возвращает SqlDataReader. Получив DataReader, можно организовать цикл для прохождения по его записям, вызывая метод Read() в теле цикла. Этот метод перемещает курсор строки на следующую запись (при первом вызове — на первую строку). Метод Read() также возвращает булевское значение, означающее наличие последующих строк для чтения. В следующем примере цикл продолжается до тех пор, пока Read() не вернет false, после чего элегантно завершается:

protected void Page_Load(object sender, EventArgs e)
{
        // Создать объект Connection из строки подключения в файле web.config
        string connectionString = 
            WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
        SqlConnection connection = new SqlConnection(connectionString);

        // Создать команду
        SqlCommand command = new SqlCommand("SELECT * FROM Employees", connection);
        string result = "";

        using (connection)
        {
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

            // Пройти в цикле по записям и построить HTML-строку
            while (reader.Read())
            {
                result += String.Format("<li>{0} <b>{1}</b> {2} - работает с {3}</li>",
                    reader["TitleOfCourtesy"], reader.GetString(1), reader.GetString(2), 
                    reader.GetDateTime(6).ToString("y"));
            }

            // Закрыть DataReader
            reader.Close();
        }

        // Вывести в Label1
        Label1.Text = "<h1>Данные сотрудников</h1>" + result;
}

Обратите внимание, что этот код читает значение поля TitleOfCourtesy, обращаясь к нему по имени, через индексатор Item. Поскольку свойство Item — индексатор по умолчанию, явно включать имя свойства Item при извлечении значения поля не понадобится. Далее код читает поля LastName и FirstName, вызывая GetString() с индексом поля (1 и 2 в данном случае). И, наконец, код обращается к полю HireDate, вызывая метод GetDateTime() с индексом поля, равным 6. Все эти подходы эквивалентны и включены сюда для демонстрации поддерживаемых вариантов.

Запустив эту страницу, вы увидите результат, показанный на рисунке ниже:

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

В большинстве страниц ASP.NET столь трудоемкий подход для отображения данных использовать не придется. Вместо этого вы будете применять элементы управления данными, которые описаны в последующих статьях. Однако DataAdapter, скорее всего, будет использоваться при написании кода доступа к данным в компоненте базы данных.

Значения null

Как вам уже наверняка известно, базы данных используют значения null для представления отсутствующей или неопределенной информации. Эту же концепцию можно использовать в .NET с типами, допускающими значения null, которые принимают значения и null-ссылки. Ниже приведен пример с целочисленной переменной, допускающей значение null:

// Допускающее null целое может содержать любое 32-разрядное целое или null
int? nullableInteger = null;

// Проверить nullablelnteger на равенство null
if (nullableInteger.HasValue)
{
    // Делать что-то с nullablelnteger
    nullableInteger += 1;
}

К сожалению, DataReader не интегрируется с допускающими null значениями .NET. Это расхождение объясняется причинами исторического характера. Допускающие null типы данных впервые появились в .NET 2.0, когда модель DataReader уже хорошо устоялась, и ее было трудно менять.

Вместо этого DataReader возвращает константу DBNull.Value, когда встречает в базе данных значение null. Попытка использовать это значение или привести его к другому типу данных вызовет исключение. (Печально, но никакого способа приведения между DBNull.Value и типами, допускающими null, не существует.) В результате должна предприниматься проверка на DBNull.Value там, где это может возникнуть, с использованием следующего кода:

while (reader.Read())
{
		// ...

		int? nullableInteger;

		// Используем тернарный оператор для сравнения
		nullableInteger = (reader["ReportsTo"] == DBNull.Value)
			? null : (int?)reader["ReportsTo"];
}

Перечисление CommandBehavior

Метод ExecuteReader() имеет перегруженную версию, которая принимает в качестве параметра значение перечисления CommandBehavior. Одно из часто используемых его значений — CommandBehavior.CloseConnection. Когда это значение передается ExecuteReader(), то DataReader закрывает ассоциированное с ним соединение, как только закрывается сам DataReader (при использовании конструкции using в применении этой опции нет смысла).

Другое допустимое значение — CommandBehavior.SingleRow — повышает производительность выполнения запроса, когда нужно извлечь единственную строку. Например, если вы извлекаете единственную запись, используя уникальное значение первичного ключа (CustomerID, Production и т.д.), то можно применить эту оптимизацию. Можно также использовать CommandBehavior.SequentialAccess для чтения части двоичного поля, что снижает расход памяти при чтении больших двоичных полей.

Другие значения перечисления применяют реже, поэтому здесь не рассматриваются. Полный список можно найти в документации по .NET.

Обработка множества результирующих наборов

Выполняемая команда не обязательно должна возвращать единственный результирующий набор. Она может выполнить более одного запроса и вернуть более одного результирующего набора. Это удобно для извлечения большого объема взаимосвязанных данных, например, списка товаров и их категорий, которые вместе представляют каталог товаров.

Команда может вернуть более одного результирующего набора двумя способами:

Ниже приведен пример строки, определяющей пакет из трех команд select:

SqlCommand command = new SqlCommand("SELECT TOP 5 * FROM Employees; " +
	"SELECT TOP 5 * FROM Customers; SELECT TOP 5 * FROM Suppliers; ", connection);

Строка содержит три запроса. Все вместе они возвращают первые пять записей из таблицы Employees, первые пять записей из таблицы Customers и первые пять — из таблицы Suppliers. Обработка результатов достаточно проста. Вначале DataReader обеспечивает доступ к результатам из таблицы Employees. По завершении чтения методом Read() всех этих записей вызывается метод NextResult() для перехода к следующему результирующему набору. Когда больше не остается результирующих наборов, этот метод вернет false.

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

protected void Page_Load(object sender, EventArgs e)
{
        // Создать объект Connection из строки подключения в файле web.config
        string connectionString = 
            WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
        SqlConnection connection = new SqlConnection(connectionString);

        // Создать команду
        SqlCommand command = new SqlCommand("SELECT TOP 5 * FROM Employees; " +
            "SELECT TOP 5 * FROM Customers; SELECT TOP 5 * FROM Suppliers; ", connection);
        string result = "";

        using (connection)
        {
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            int i = 0;

            // Цикл по записям всех результирующих наборов с построением HTML-строки
            do
            {
                i++;
                result += "<h1>Таблица №" + i + "</h1>";

                while (reader.Read())
                {
                    result += "<li>";
                    // Получить все поля строки
                    for (int field = 0; field < reader.FieldCount; field++)
                    {
                        result += "<b>" + reader.GetName(field).ToString() + "</b>" + ": " +
                            reader.GetValue(field).ToString() + "<br>";
                    }
                    result += "</li>";
                }
            }
            while (reader.NextResult());
            
            reader.Close();
        }

        // Вывести в Label1
        Label1.Text = result;
}

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

Метод ExecuteScalar()

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

Следующая процедура демонстрирует, как можно с таким подходом получить (и отобразить на странице) количество записей таблицы Employees:

SqlConnection connection = new SqlConnection(connectionString);

// Создать команду
SqlCommand command = new SqlCommand("SELECT COUNT (*) FROM Employees", connection);
int i = 0;

using (connection)
{
    connection.Open();

    // Получить значение COUNT
    i = (int)command.ExecuteScalar();
}

Label1.Text = "Общее число сотрудников: <b>" + i + "</b>";	// 9

Код достаточно прост, но стоит отметить, что вы должны привести возвращаемое значение к правильному типу, поскольку ExecuteScalar() возвращает объект.

Метод ExecuteNonQuery()

Метод ExecuteNonQuery() выполняет команды, которые не возвращают результирующих наборов, такие как INSERT, DELETE или UPDATE. Метод ExecuteNonQuery() возвращает одну порцию информации — количество обработанных записей (или -1, если команда отлична от INSERT, DELETE или UPDATE). Применение этого метода класса Command аналогично предыдущим методам.

Пройди тесты
Лучший чат для C# программистов