Книга: Обработка баз данных на Visual Basic®.NET

Применение объекта Command с параметрами и хранимыми процедурами

Применение объекта Command с параметрами и хранимыми процедурами

При создании запросов или команд для источника данных часто требуется передавать значения параметров действия (обновление, вставка или удаление данных) или хранимой процедуры. Для решения этой проблемы в объекте Command предусмотрено свойство Parameters, которое является объектом-коллекцией ParameterCollection и содержит коллекцию объектов-параметров Parameter. Это аналогично способу работы, применимому в модели ADO 2.X.

Объекты Parameter и ParameterCollection тесно связаны с соответствующим провайдером данных, поэтому они должны быть реализованы как составная часть провайдера данных ADO.NET. Способы программирования объекта SqlParameterCollection и использование объектов OdbcParameterCollection и OledbParameterCollection имеют существенные отличия. Объекты OdbcParameterCollection и OledbParameterCollection основаны на позиционных параметрах, а объект SqlParameterCollection – на именованных параметрах. Эти различия в значительной степени влияют на способ определения запросов и параметров.

Начнем с создания простого запроса с параметрами для извлечения из базы данных pubs имен и фамилий всех авторов из заданного штата.

С одной стороны, при использовании провайдеров данных OLEDB или ODBC запрос будет иметь следующий вид:

SELECT state, au_fname, au_lname from authors WHERE state = ?

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

С другой стороны, при использовании провайдера данных SqlClient запрос будет выглядеть следующим образом:

SELECT state, au_fname, au_lname from authors WHERE state = @MyParam

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

Объект Parameter можно создать явно, используя конструктор Parameter (т.е. с использованием оператора New) или передавая нужные аргументы методу Add объекта-коллекции ParameterCollection (свойство Parameters объекта Command). Помните, что оба метода (конструктор Parameter и метод Add) имеют перегруженные версии. 

Ниже приведен один из способов включения параметра команды за счет явного указания объекта-параметра.

Dim rayParameter As New OdbcParameter("@MyParam", OdbcType.Char, 2)
myParameter.Direction = ParameterDirection.Input
myParameter.Value = "CA"
cmd.Parameters.Add(myParameter)

А способ включения параметра команды с помощью метода Add выглядит иначе.

cmd.Parameters.Add("@MyParam", OdbcType.Char, 2)
cmd.Parameters("@MyParam").Direction = ParameterDirection.Input
cmd.Parameters("@MyParam").Value = "CA"

Второй метод короче и обычно предпочтительнее, если только нет особой причины для повторного использования объекта Parameter.

Для метода Add объекта Parameter обычно требуется указать имя, тип и длину параметра. Затем нужно указать направление передачи данных: Input, Output, InputOutput или ReturnValue. По умолчанию используется направление Input. Наконец, для присвоения значения параметру нужно использовать свойство Value объекта Parameter. Кроме того, для параметра можно указать другие свойства, например масштаб (свойство Scale), точность (свойство Precision) и допустимость использования неопределенных значений (свойство IsNullable).

При использовании провайдера данных SqlClient можно применять практически идентичный код. Единственным отличием являются префиксы Odbc вместо префиксов Sql, а также тип перечисления SqlDbType вместо OdbcType.

Dim myParameter As New SqlParameter("@MyParam", SqlDbType.Char, 2)
myParameter.Direction = ParameterDirection.Input
myParameter.Value = "CA"
cmd.Parameters.Add(myParameter)

Аналогично выглядит способ включения параметра команды с помощью метода Add.

cmd.Parameters.Add("@MyParam", SqlDbType.Char, 2)
cmd.Parameters("@MyParam").Direction = ParameterDirection.Input
cmd.Parameters("@MyParam").Value = "CA"

Измените код кнопки cmdButton, как показано в листинге 4.3. После запуска программы и щелчка на кнопке cmdButton в текстовом поле появится текст запроса, а также имя и значение параметра.

Private Sub btnCommand_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles btnCommand.Click
 ' Создание экземпляра объекта Connection
 Dim cnn As SqlConnection = New SqlConnection( _
  "server=localhost;uid=sa;database=pubs")
 ' Создание экземпляра объекта Command и объектов Parameter
 Dim cmd As SqlCommand = New SqlCommand()
 Dim prm As SqlParameter = New SqlParameter()
 txtResults.Clear()
  ' Открытие подключения cnn.Open()
 ' Указание подключения и текста команды
 cmd.Connection = cnn
 cmd.CommandType = CommandType.Text
 cmd.CommandText = _
  "Select au_lname, state from authors where state = @MyParam"
 ' Создание параметра и указание его значения
 cmd.Parameters.Add(New SqlParameter("@MyParam", SqlDbType.Char, 2))
 cmd.Parameters("@MyParam").Value = "CA"
 ' Вывод текста команды
 txtResults.Text = "Command String:" & ControlChars.CrLf
 txtResults.Text = txtResults.Text & ControlChars.Tab & _
  cmd.CommandText() & ControlChars.CrLf
 ' Вывод параметров команды
 txtResults.Text = txtResults.Text & "Command parameters:" & _
  ControlChars.CrLf
 For Each prm In cmd. Parameters
  txtResults.Text = txtResults.Text & ControlChars.Tab & _
   prm.ParameterName & " = " & prm.Value & ControlChars.CrLf
 Next
End Sub

Аналогично вызываются хранимые процедуры, за исключением того, что вместо свойства CommandType.Text используется свойство CommandType.StoredProcedure, а имя хранимой процедуры присваивается свойству CommandText. Таким образом, код вызова хранимой процедуры GetAuthorsFromState с двухсимвольным параметром для извлечения информации обо всех авторах заданного штата будет выглядеть, как показано ниже.

cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "GetAuthorsFromState"
cmd.Parameters.Add("@MyParam", SqlDbType.Char, 2)
cmd.Parameters("@MyParam").Direction = ParameterDirection.Input
cmd.Parameters("@MyParam").Value = "CA"

Чтобы хранимая процедура возвращала значение, нужно указать направление Output, а затем считать значение свойства параметра после вызова хранимой процедуры. В данном примере также определяется возвращаемое значение хранимой процедуры. Поскольку здесь указан тип Int сервера SQL Server, то для этого параметра не нужно указывать длину, так как по определению она составляет 4 байт.

cmd.Parameters.Add(New SqlParameter("result", SqlDbType.Int)
cmd. Parameters ("result").Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(New SqlParameter("@MyParam", SqlDbType.Int)
cmd.Parameters("@MyParam").Direction = ParameterDirection.Output
' Вызов хранимой процедуры
Msg (cmd.Parameters("@MyParam").Value)

Далее представлены другие фрагменты кода с указанием параметров команд и хранимых процедур, а также описываются способы их выполнения.

Оглавление книги