Присоединенный режим (connected mode). Класс DbParameter. Использование параметров

Перед изучением данной темы рекомендуется ознакомиться со следующей темой:

Класс DbCommand. Примеры

Поиск на других ресурсах:

Содержание

1. Класс DbParameter. Назначение

Класс DbParameter используется для выполнения параметризированных запросов в объекте типа DbCommand. DbCommand содержит коллекцию Parameters. Эта коллекция может содержать набор параметров, каждый из которых представлен классом (типом) DbParameter. В соответствии с выбранным поставщиком данных вместо DbParameter можно использовать экземпляр одного из унаследованных классов SqlParameter, OledbParameter, OdbcParameter.

⇑

2. Свойства класса DbParameter. Обзор

Наиболее часто используемые свойства класса DbParameter следующие.

Свойство	Тип данных	Объяснение
DbType	DbType	Определяет тип данных параметра
Direction	ParameterDirection	Указывает тип параметра: параметр ввода (Input); параметр вывода (Output); параметр ввода-вывода (Input-Output); параметр возврата (Return). Содержит значения хранимой процедуры.
IsNullable	Boolean	Указывает, может ли параметр принимать значение null
ParameterName	String	Указывает имя параметра
Precision	Byte	Указывает точность параметра
Scale	Byte	Указывает числовую шкалу параметра
Size	Int32	Указывает размер параметра
SqlDbType	SqlDbType	Указывает Sql-тип данных параметра
SqlValue		Возвращает или задает значение параметра как тип SQL object SqlParameter.SqlValue { get; set; }
…
Value	Object	Задает значение параметра object DbParameter Value { get; set; } По умолчанию равно null.

⇑

3. Класс DbParameter

3.1. Коллекция Parameters. Добавление параметра в SQL-запрос. Методы Add(), AddWithValues(), AddRange()

Коллекция Parameters реализована свойством, имеющим объявление

DbParameterCollection DbCommand.Parameters { get; }

Свойство DbCommand.Parameters может обращаться к методам соответствующего провайдера. Так, для провайдера Sql Server свойство Parameters выглядит следующим образом:

SqlParameterCollection SqlCommand.Parameters { get; }

Чтобы добавить параметр к объекту SqlCommand (SQL Server провайдер) нужно использовать один из методов

Add();
AddWithValue();
AddRange().

Распространенные реализации этих методов содержат следующие объявления:

SqlParameter SqlParameterCollection.Add(SqlParameter value);
SqlParameter SqlParameterCollection.AddWithValue(string parameterName, object value);
void SqlParameterCollection.AddRange(SqlParameter[] values);

здесь

parameterName – название параметра на языке SQL (например «@NameGroup», «@Cost»);
value – значение параметра, которое добавляется;
values – массив добавляемых параметров.

К примеру, добавление параметра с именем «@Cost» в текст SQL-запроса с помощью метода AddWithValue() будет выглядеть следующим образом

...

// 1. Строка SQL-запроса
string queryStr = ...

// 2. Строка подключения
string connStr = ...

// 3. Создать соединение с базой данных
SqlConnection connection = new SqlConnection(connStr);

// 4. Создать объект, который соответствует SQL-запросу queryStr
SqlCommand sqlCmd = connection.CreateCommand();
sqlCmd.CommandText = queryStr;

// 5. Добавить параметр parameterName со значением 100
sqlCmd.Parameters.AddWithValue("@Cost", "100");

...

⇑

3.2. Создание параметра с помощью конструктора соответствующего класса. Добавление параметра в коллекцию Parameters с помощью метода Add()

В коллекции Parameters объекта определяется список параметров для SQL-запроса на основе объекта типа DbCommand (SqlCommand, OleDbCommand, OdbcCommand). Отдельный параметр этой коллекции может быть создан с помощью соответствующего конструктора того или иного провайдера класса SqlParameter, OdbcParameter, OleDbParameter.

Так, например, для создания объекта SqlParameter используются 7 конструкторов

SqlParameter.SqlParameter();
SqlParameter.SqlParameter(string parameterName, object value);
SqlParameter.SqlParameter(string parameterName, SqlDbType dbType);
SqlParameter.SqlParameter(string parameterName, SqlDbType dbType, int size);

SqlParameter.SqlParameter(string parameterName, SqlDbType dbType,
int size, string sourceColumn);

SqlParameter.SqlParameter(
    string parameterName, 
    SqlDbType dbType,
    int size,
    ParameterDirection direction,
    bool isNullable,
    byte precision,
    byte scale,
    string sourceColumn,
    DataRowVersion sourceVersion,
    object value
);

SqlParameter.SqlParameter(
    string parameterName, 
    SqlDbType dbType,
    int size,
    ParameterDirection direction,
    byte precision
);

здесь

parameterName – имя параметра на языке SQL;
value – значение параметра, которое подставляется при выполнении SQL-запроса;
dbType – тип параметра, определенный в вычислении DbType (SqlDbType, OleDbType, OdbcType). Параметр dbType может принимать значения Text, Char, DateTime, Decimal, Float, Int, Money и другие;
size – размер параметра в байтах;
sourceColumn – название столбца таблицы, совпадающее с параметром;
direction – направление задействования параметра. Определяется перечислением ParameterDirection, которое может принимать значения Input, OutPut, InputOutput, ReturnValue;
isNullable – если равно true, то это означает, что поле может принимать значение NULL;
precision – точность. Это общее количество разрядов от десятичной запятой, к которой вычисляется значение value (количество знаков после запятой);
scale – общее количество десятичных разрядов, к которому вычисляется значение value (ширина вывода);
sourceVersion – одно из значений DataRowVersion описывающее версию строки DataRow в таблице;
value – значение параметра.

Для провайдеров OLE DB и ODBC параметры в объявлениях конструкторов такие же.

При использовании конструкторов, не содержащих тип DbType (SqlDbType, OleDbType, OdbcType), этот тип определяется автоматически на основе значения value.

Пример 1.

В нашем примере создается объект типа SqlParameter с помощью конструктора с 2 параметрами.

// создать параметр с числовым значением 150
SqlParameter param = new SqlParameter("@Cost", 150.0);

Тип параметра @Cost автоматически определяется как Float на основе значения 150.0.

Пример 2.

В примере создается объект типа SqlParameter на основе конструктора, получающего 10 параметров

// Создать параметр
SqlDbType dbType = SqlDbType.Float;
SqlParameter paramName = new SqlParameter(
    "@Price",
    dbType,
    8,
    ParameterDirection.InputOutput,
    true,
    2,
    3,
    "Price",
    DataRowVersion.Default,
    215.55
);

В вышеприведенном примере задаются:

название параметра «@Price»;
тип параметра Float;
размер параметра 8 байт;
направление передачи параметра – InputOutput (в направлении ввода и вывода);
параметр может принимать значение NULL (true);
точность 2 знака после запятой;
масштаб 3 знака;
название столбца таблицы, совпадающее с параметром «Price»;
версия строки данных установлена по умолчанию (DataRowVersion.Default). Это означает, что установлено DataRowVersion.Current – строка содержит текущие значения в базе данных;
значение параметра равно 215.55.

⇑

4. Пример использования параметра в тексте SQL-запроса

Чтобы выполнить параметризированный запрос, необходимо добавить объект Parameter в коллекцию Parameters объекта Command. Если используется SQL Client .NET, вместо класса Parameter нужно использовать SqlParameter. Чтобы создать класс SqlParameter, одним из способов является вызов метода AddWithValue() свойства Parameters.

В примере показывается выполнение SQL-запроса использующего параметр. Пример реализуется для приложения типа Windows Forms. Основой для получения данных является таблица с именем Group, которая имеет поле с именем NameGroup.

ID_Group	NameGroup	…
1	DV-215	…
2	DOK-208	…
3	DOK-199	…
…	…	…
…	…	…

С помощью объекта SqlCommand, содержащего SQL-запрос, формируются полученные результаты в объекте SqlDataReader. Затем эти результаты отображаются в ListBox. Текст запроса SQL содержит параметр @NameGroup, значение которого получается из компонента типа TextBox (textBox1).

// Кнопка "Параметризированный запрос"
private void button4_Click(object sender, EventArgs e)
{
  // Передача параметра
  // Например, нужно выполнить фильтр по группе.
  // SQL-команда выглядит так:
  // SELECT * FROM [Group] WHERE NameGroup = @NameGroup
  // здесь @NameGroup - параметр - название группы

  // 1. Получить соединение на основе строки connStr
  string connStr = @"Data Source=(localdb)\ProjectModels;" +
    "Initial Catalog=Database4;Integrated Security=True;";
  SqlConnection connection = new SqlConnection(connStr);

  // Проверка, существует ли соединение
  if (connection == null)
  {
    MessageBox.Show("Error! Cannot open connection");
    return;
  }

  // 2. Создать строку SQL
  string strSQL = "SELECT * FROM [Group] WHERE ([NameGroup] = @NameGroup)";

  try
  {
    // 3. Открыть соединение
    connection.Open();

    // 4. Создать SQL-комманду
    SqlCommand command = new SqlCommand(strSQL, connection);

    // 5. Создать новый параметр - метод AddWithValue,
    //    значение параметра получается из textBox1.Text,
    //    @NameGroup <= textBox1.Text
    command.Parameters.AddWithValue("@NameGroup", textBox1.Text);

    // 6. Создать объект для чтения данных и
    //    заполнить его результатами SQL-комманды
    SqlDataReader reader = command.ExecuteReader();

    // вывести данные в listBox1
    listBox1.Items.Clear();
    while (reader.Read())
    {
      listBox1.Items.Add(reader["NameGroup"].ToString());
    }
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message);
  }
  finally
  {
    connection.Close();
  }
}

⇑

5. Свойства ParameterName и Value. Задание имени и значения параметра. Пример

С помощью свойств ParameterName и Value можно задать имя и значение параметра. Для этого необходимо предварительно создать объект типа используемого провайдера (SqlParameter, OleDbParameter, OdbcParameter). В зависимости от того, какой провайдер, эти свойства имеют соответствующие объявления

string SqlParameter.ParameterName { get; set; }
string OleDbParameter.ParameterName { get; set; }
string OdbcParameter.ParameterName { get; set; }

object SqlParameter.Value { get; set; }
object OleDbParameter.Value { get; set; }
object OdbcParameter.Value { get; set; }

здесь

SqlParameter, OleDbParameter, OdbcParameter – имена классов, реализующих параметры соответственно для поставщиков данных SQL Server, OLE DB и ODBC. Для установки параметров необходимо предварительно создать объекты этих классов.

Значения свойств ParameterName и Value записываются в тип провайдера. Так, для провайдера SQL Server свойства записываются в объект типа SqlParameter примерно следующим образом

SqlParameter paramObj = new SqlParameter();
paramObj.ParameterName = "@NameOfParameter";
paramObj.Value = "ValueAsString";

здесь вместо вымышленных NameOfParameter и ValueAsString нужно задать реальные имена параметров и их значения. Имена параметров и их значений могут получаться из разных полей данных (например, поля TextBox для приложения Windows Forms).

Пример.

В примере приведен фрагмент кода для приложения типа Windows Forms, устанавливающий значение параметра для SQL-запроса, реализуемого в объекте command. Затем в компонентах label1 и label2 происходит вывод установленных значений.

...

// создать команду на языке SQL
DbCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM [Table] WHERE ([Price] = @Cost)"; // текст SQL-запиту
command.CommandType = CommandType.Text;

// создать параметр
SqlParameter param = new SqlParameter();

// заполнить параметр значениями ParameterName:Value
param.ParameterName = "@Cost";
param.Value = "150";

// добавить параметр к списку параметров команды - метод Add()
command.Parameters.Add(param);

// вывести имя параметра в label1
label1.Text = command.Parameters[0].ParameterName.ToString();

// вывести значение параметра в label2
label2.Text = command.Parameters[0].Value.ToString();

// Открыть соединение
connection.Open();

// выполнить действия на основании SQL-запроса command

...

// Закрыть соединение
dataReader.Close();

⇑

6. Свойство DbType. Типы данных параметров

Свойство DbType является перечислением. Для разных провайдеров это свойство реализовано соответственно классам SqlDbType, OleDbType, OdbcType. Свойство применяется для контроля типа данных, используемых при их передаче в базу данных. Эти типы данных устанавливаются для столбцов таблиц базы данных.

В свойстве DbType определены имена следующих типов данных:

AnsiString – поток переменной длины в кодировке не Unicode;
AnsiStringFixedLength – поток фиксированной длины в кодировке не Unicode;
Binary – тип потока двоичных данных;
Byte – 8 разрядное целое число без знака в диапазоне от 0 до 255;
Boolean – тип для представления логических значений true или false;
Currency – тип данных валюты в пределах от -2⁶³..2⁶³-1 с точностью до 0.0001 денежной единицы (одна десятитысячная);
Date – значение даты;
DateTime – значение даты и времени;
DateTimeOffset – значение даты и времени с поддержкой часовых поясов;
Decimal – представление значений в диапазоне от 1.0×10^-28 до 7.9×10²⁸ с 28–29 значимыми цифрами;
Double – тип с плавающей запятой, представляющей значение в диапазоне от 5.0×10^-324 до 1.7×10³⁰⁸ с точностью до 15–16 знаков;
Guid – глобальный уникальный идентификатор (или идентификатор GUID);
Int16 – целочисленный тип для представления 16-битных целых чисел со знаком в диапазоне от -32768 до 32767;
Int32 – целочисленный тип для представления 32-битных целых чисел со знаком в диапазоне от -2147483648 до 2147483647;
Int64 – целочисленный тип для представления 32-битных целых чисел со знаком в диапазоне от -9223372036854775808 до 9223372036854775807;
Object – общий тип представления всех значений и ссылок;
SByte – целочисленный тип для представления 8-битных целых чисел со знаком в диапазоне от -128 до 127;
Single – тип данных с плавающей запятой, представляющей значение в диапазоне от 1.5×10^-45 до 3.4×10³⁸ с точностью до 7 цифр;
String – тип для представления символьных строк Unicode;
StringFixedLength – тип, представляющий строку символов Unicode фиксированной длины;
Time – тип, представляющий значение типа DateTime для провайдера SQL Server;
UInt – целочисленный тип для представления 16-битных целых чисел со значениями от 0 до 65535;
UInt32 – целочисленный тип для представления 32-битных целых чисел со значениями от 0 до 4294967295;
UInt64 – целочисленный тип для представлений 64-битных целых чисел со значениями от 0 до 18446744073709551615;
VarNumeric – числовое значение переменной длины;
Xml – проанализированное представление XML-документа или фрагмента.

Пример.

В примере для параметра @StudentName устанавливается тип String.

// Задать для параметра @StudentName значение типа NVarChar
SqlParameter param = new SqlParameter();
SqlDbType type = SqlDbType.NVarChar;
param.ParameterName = "@StudentName";
param.SqlDbType = type;

⇑

7. Направление параметра. Свойство Direction. Пример

Данные в базе данных могут передаваться или получаться (вытягиваться). Соответственно и параметр может использоваться:

для передачи данных в базу данных. В этом случае параметр считается параметром ввода (Input);
для считывания данных из базы данных (получение фиксированного набора записей из базы данных). Такой параметр называется параметр вывода (Output).

Пример.

В примере показывается использование параметра вывода @Rating для получения значения рейтинга студента в столбце StudentName таблицы Student. Имя студента задается входным параметром @StName. После выполнения запроса в параметре @Rating возвращается рейтинг студента.

// 1. Строка подключения
string connStr = @"Data Source=(localdb)\ProjectModels;" +
  "Initial Catalog=MyDatabase;Integrated Security=True;";

// 2. Текст SQL-запроса
string strSQL = "SELECT @Rating = Rating " +
  "FROM [Student] " +
  "WHERE StudentName = @StName";

// 3. Создать параметры для SQL-запроса
// 3.1. Параметр @Rating
SqlParameter paramRating = new SqlParameter();
paramRating.Direction = ParameterDirection.Output; // параметр выхода
paramRating.DbType = DbType.Double; // тип параметра

// 3.2. Параметр @StudentName
SqlParameter paramStName = new SqlParameter();
paramStName.Direction = ParameterDirection.Input; // входной параметр
paramStName.DbType = DbType.String; // тип параметра

// 4. Создать соединение
SqlConnection connection = new SqlConnection(connStr);

// 5. Создать объект, который связан с SQL-командой,
//    которая содержит параметры @Rating и @StName
// 5.1. Створити об'єкт
SqlCommand command = connection.CreateCommand();

// 5.2. Настроить тип объекта
command.CommandText = strSQL;
command.CommandType = CommandType.Text;   // строка SQL

// 5.3. Добавить параметры
command.Parameters.Add(paramRating);   // @Rating
command.Parameters.Add(paramStName);   // @StName

// 6. Открыть соединение с базой данных
connection.Open();

// 7. Выполнить SQL-комманду
command.ExecuteNonQuery();

// 8. Проверка, найден ли студент
if (paramRating.Value == DBNull.Value)
{
  label1.Text = "No student found";
}
else
{
  // Вывести значение полученного параметра @Rating в компонент label1
  label1.Text = paramRating.Value.ToString();
}

// 9. Закрыть соединение с базой данных
connection.Close();

Для того чтобы указать, что параметр paramRating является выходным, используется строка

paramRating.Direction = ParameterDirection.Output;

Соответственно, входной параметр paramStName определяется

paramStName.Direction = ParameterDirection.Input;

Если в таблице Student не будет найдена строка соответствующая критерию

...
WHERE StudentName = @StName

то в параметер paramRating будет записано значение DbNull.Value.

BestProg

C#. ADO .NET. Присоединенный режим. Класс DbParameter. Использование параметров

Присоединенный режим (connected mode). Класс DbParameter. Использование параметров

Содержание

1. Класс DbParameter. Назначение

⇑

2. Свойства класса DbParameter. Обзор

⇑

3. Класс DbParameter

3.1. Коллекция Parameters. Добавление параметра в SQL-запрос. Методы Add(), AddWithValues(), AddRange()

⇑

3.2. Создание параметра с помощью конструктора соответствующего класса. Добавление параметра в коллекцию Parameters с помощью метода Add()

⇑

4. Пример использования параметра в тексте SQL-запроса

⇑

5. Свойства ParameterName и Value. Задание имени и значения параметра. Пример

⇑

6. Свойство DbType. Типы данных параметров

⇑

7. Направление параметра. Свойство Direction. Пример

⇑

Связанные темы

⇑

Программирование: теория и практика