Обработка баз данных на Visual Basic®.NET - Джеффри Мак-Манус

Точно такое же обновление можно выполнить с помощью хранимой процедуры, что позволяет добиться более высокой производительности и централизованно хранить код. Потенциальным недостатком использования хранимых процедур является необходимость назначения администратора базы данных или специалиста с опытом создания хранимых процедур. В крупных организациях порой уходят целые дни на то, чтобы администратор базы данных изменил хранимые процедуры, которые можно самостоятельно изменить за несколько минут. Хранимые процедуры создаются с помощью программ SQL Server Enterprise Manager или SQL Query Analyzer, которые описаны в главе 3, "Знакомство с SQL Server 2000". Это можно также сделать с помощью проекта DataProviderObjects, изменив команду SQL, как это делалось ранее.

Итак, в данном примере хранимая процедура имеет следующий вид:

CREATE PROCEDURE UpdateRoyalties

@param1 int

AS

UPDATE roysched SET royalty = royalty + @param1

В листинге 4.6 для организации вызова хранимой процедуры потребуется заменить свойства CommandText и CommandType объекта Command.

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "UpdateRoyalties"

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

Метод ExecuteScalar

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

Включим приведенную ниже хранимую процедуру в базу данных pubs.

CREATE PROCEDURE AuthorsInState2

@param1 char(2)

AS

SELECT count(*) FROM authors WHERE state = @param1

Хранимая процедура AuthorsInState2 принимает параметр, который имеет двухсимвольный код штата, и возвращает из таблицы authors количество авторов из этого штата. Эта процедура с функциональной точки зрения эквивалентна процедуре AuthorsInState1 из листинга 4.5, но возвращает вычисленное значение для набора записей, а не только индикатор успешности выполнения команды.

НА ЗАМЕТКУ

Использование метода ExecuteScalar вместо метода ЕxecuteNonQuerу и передача скалярного значения с помощью параметра ReturnValue связаны с дополнительными накладными расходами. Зачем же его используют? Он проще в употреблении, потому что не нужно заботиться об указании параметров в определениях и вызовах команд.

Для вызова данной хранимой процедуры с помощью провайдера данных ODBC выполните следующее.

1. Создайте дополнительную кнопку под текстовым полем txtParam1.

2. В окне свойств Properties укажите значение cmdScalar для свойства (Name) и значение ExecuteScalar для свойства Text.

3. Создайте код подпрограммы btnExecuteScalar_Click, приведенный в листинге 4.7.

Листинг 4.7. Код извлечения скалярного значения из хранимой процедуры с помощью провайдера данных ODBC

Private Sub btnExecuteScalar_Click(ByVal sender As System.Object, _

 ByVal e As System.EventArgs) Handles btnExecuteScalar.Click

 Dim result As Integer

 ' Создание экземпляра объекта Connection.

 Dim cnn As OdbcConnection = New OdbcConnection( _

 "DRIVER={SQL Server};server=localhost;uid=sa;database=pubs")

 ' Создание экземпляра объекта Command.

 Dim cmd As OdbcCommand = New OdbcCommand()

 txtResults.Clear()

 ' Указание подключения и текста команды.

 cmd.Connection = cnn

 cmd.CommandType = CommandType.StoredProcedure

 cmd.CommandText = "{call AuthorsInState2(?) }"

 ' Создание параметра и указание его значения

 cmd.Parameters.Add("@param1", OdbcType.Char, 2)

 cmd.Parameters("@param1").Value = txtParam1.Text

 ' Открытие подключения перед вызовом метода

 ExecuteReader. cnn.Open()

 result = cmd.ExecuteScalar()

 MessageBox.Show("Count is " & result, "DataProviderObjects")

 cnn.Close()

End Sub

Запустите приложение и введите в текстовом поле над кнопкой ExecuteScalar двухсимвольный код штата. После щелчка на кнопке ExecuteScalar появится диалоговое окно с сообщением о количестве авторов в данном штате. Полученный результат можно проверить с помощью программы SQL Server Enterprise Manager, просматривая данные в таблице authors в базе данных pubs.

НА ЗАМЕТКУ

Учтите, что по умолчанию база данных pubs содержит двух авторов из штата Юта (код UТ) и 15 авторов из штата Калифорния (код СА).

Метод ExecuteReader

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

Объект DataReader

Данный объект предназначен для чтения в прямом направлении небуферизуемого потока записей, полученных от метода ExecuteReader объекта Command. Объект DataReader в основном эквивалентен объекту Recordset модели ADO 2.X, который также предназначен для чтения в прямом направлении. Объект DataReader предлагает наиболее быстрый способ доступа к источнику данных, но в нем не предусмотрены возможности прокрутки и обновления данных. Поскольку данные не буферизуются и не сохраняются в кэше, этот метод прекрасно подходит для извлечения большого объема данных. Для перехода к следующей записи объекта DataReader нужно вызвать его метод Read.

Кроме коллекции Fields, доступ к полям каждой записи можно осуществить с помощью строго типизированных методов. Доступ к полям с помощью коллекции Fields аналогичен способу доступа к полям в модели ADO 2.X.

X = MyReader("Myfield")

НА ЗАМЕТКУ

Объект DataReader не имеет явного конструктора, т.е. его нельзя создать с помощью оператора New(). Для инициализации нового объекта нужно вызвать метод ExecuteReader объекта Command.

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

X = MyReader.GetInt16(1)

или

X = MyReader.GetString(2)

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

Для демонстрации способов использования этих методов выполните следующее.

1. Создайте под кнопкой ExecuteScalar дополнительную кнопку.

2. В окне свойств Properties укажите значение cmdExecuteReader для свойства (Name) и значение ExecuteReader для свойства Text.

3. Создайте код подпрограммы btnExecuteReader_Click, приведенный в листинге 4.8.

НА ЗАМЕТКУ

Кроме способов использования объекта DataReader, в этом примере демонстрируются и другие функциональные возможности. Например, здесь помимо членов Text и StoredProcedure свойства-перечисления CommandType для указания типа команды используется член TableDirect. Он содержит имя таблицы, все поля которой возвращаются данной командой. Учтите, что этот член перечисления поддерживается только для провайдера данных ODBC.

Представления обычно обрабатываются точно также, как и таблицы. Следовательно, при использовании члена TableDirect свойства-перечисления CommandType можно указывать не только имя таблицы, но и имя представления.

Листинг 4.8. Код создания объекта DataReader и извлечения значений полей с помощью представления и члена TableDirect

Private Sub btnExecuteReader_Click(ByVal sender As System.Object, _

 ByVal e As System.EventArgs) Handles btnExecuteReader.Click

 ' Создание экземпляра объекта Connection.

 Dim cnn As OleDbConnection = New OleDbConnection( _

  "provider=SQLOLEDB;server=localhost;uid=sa;database=pubs")

 ' Создание экземпляра объекта Command.

 Dim As OleDbCommand = New OleDbCommand()

 txtResults.Clear()

 ' Указание подключения и текста команды

 cmd.Connection = cnn

 cmd.CommandType = CommandType.TableDirect

 cmd.CommandText = "EmployeeJobs_View"

 ' Открытие подключения перед вызовом метода

 ExecuteReader. cnn.Open()

 Dim reader As OleDbDataReader

 reader = cmd.ExecuteReader{)

 While reader.Read()

  txtResults.Text = txtResults.Text & reader("fname") & _

   ControlChars.Tab & reader("lname") & _

   ControlChars.Tab & ControlChars.Tab & _

   reader("job_desc") & ControlChars.CrLf

 End While

 reader.Close()

 cnn.Close()