Приєднаний режим (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. Використання параметрів