Объекты 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"
СОВЕТ
Для передачи параметру неопределенного значения можно использовать свойство Value объекта DBNull.
cmd.Parameters("@MyParam").Value = DBNull.Value
Измените код кнопки cmdButton, как показано в листинге 4.3. После запуска программы и щелчка на кнопке cmdButton в текстовом поле появится текст запроса, а также имя и значение параметра.
Листинг 4.3. Код подготовки и отображения команды и ее параметров
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"
СОВЕТ
Для вызова хранимой процедуры с помощью OdbcCommand нужно использовать стандартные последовательности символов ODBC (с участием фигурных скобок), а не только имя процедуры для свойства CommandText. В качестве заменителей параметров в ODBC используются вопросительные знаки. Вот как выглядит приведенный выше для провайдера данных ODBC:
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "{GetAuthorsFromState ?}"
cmd.Parameters.Add("@MyParam", OdbcType.Char, 2)
cmd.Parameters("@MyParam").Direction = ParameterDirection.Input
cmd.Parameters("@MyParam").Value = "CA"
Хранимая процедура также возвращает значение, которое указывается с помощью специальной последовательности символов (вопросительного знака и знака равенства) у открывающей скобки, как показано ниже.
cmd.CommandText = "{? = GetAuthorsFromState ?}"
Чтобы хранимая процедура возвращала значение, нужно указать направление 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)
НА ЗАМЕТКУ
При определении параметра, который будет использоваться для возвращаемого значения ReturnValue хранимой процедуры его нужно указать первым в списке параметров коллекции Parameters. Это обязательное условие для провайдеров данных OLEDB и ODBC, потому что, как отмечалось выше, параметры этих провайдеров определяются по порядку их расположения, а возвращаемое значение может располагаться только с начала. Однако при определении возвращаемого значения для провайдера данных SQL его можно pасполагать в произвольном месте коллекции параметров, потому что параметры провайдера данных SQL определяются по именам.
Далее представлены другие фрагменты кода с указанием параметров команд и хранимых процедур, а также описываются способы их выполнения.
Выполнение команд
До сих пор мы только указывали свойства и параметры объекта Command, но не выполняли эти команды. Существует три стандартных способа выполнения команд для объекта Command и один способ для объекта SqlCommand.
• Метод ExecuteNonQuery. Выполняет команду SQL и не возвращает записей.
• Метод ExecuteScalar. Выполняет команду SQL и возвращает первое поле первой записи.
• Метод ExecuteReader. Выполняет команду SQL и возвращает набор записей с помощью объекта DataReader.
• Метод ExecuteXmlReader (только для объекта-команды SqlCommand). Выполняет команду SQL и возвращает набор записей в формате XML с помощью объекта XmlReader.
Первые три метода рассматриваются далее в этой главе, а последний – в главе 10, "ADO.NET и XML", при обсуждении способов использования XML в модели ADO.NET.
Метод ExecuteNonQuery
