4.3.3. Параметризированные запросы

Выше были рассмотрены основные методы объекта Command в консольных приложениях. Это дало возможность понять синтаксис самих методов, без привязки к какому-либо интерфейсу. Однако, после того как синтаксис стал ясен, возникает вопрос - как же использовать эти методы в реальных приложениях? Очевидно, что простое копирование кода в конструктор формы, по сути, не изменит пример. Следовательно, необходимо привязывать вводимые значения к элементам пользовательского интерфейса, например, к текстовым полям. Но это означает, что параметры строки запроса будут неизвестны до тех пор, пока пользователь не введет соответствующие значения. Например, для метода ExecuteNonQuery строка commandText имела следующий вид:

myCommand.CommandText =
                           "UPDATE Туристы SET Фамилия = 'Сергеева' WHERE Кодтуриста = 3";

Если создадим приложение, где пользователь будет вводить фамилию и код туриста, то мы не можем заранее указать, какие это будут значения. Логически запрос можно представить примерно так:

myCommand.CommandText = " UPDATE Туристы SET
                   Фамилия = 'Какая-то_фамилия,_которую_введет_пользователь'
                   WHERE Кодтуриста = 'Какой-то_код,_который_введет_пользователь' ";

Для решения таких задач, которые возникли еще в самом начале разработки языка SQL, были придуманы параметризированные запросы. В них неизвестные значения заменяются параметрами следующим образом:

myCommand.CommandText =
                   "UPDATE Туристы SET Фамилия = @Family WHERE Кодтуриста = @TouristID";

Здесь @Family - параметр для неизвестного значения фамилии, @TouristID - параметр для неизвестного значения кода туриста. Отметим, что параметры пишутся без кавычек.

Теперь можно выполнить привязку параметров к тексту, вводимому пользователем.

Использование метода ExecuteNonQuery

Для демонстрации привязки параметров создадим новое Windows- приложение и разместим на нем компоненты пользовательского интерфейса. Форма в режиме дизайна будет иметь следующий вид (рис. 87).

Рис. 87. Разработанное приложение, вид формы в режиме дизайна

Подключаем пространство имен для работы с базой данных:

using System.Data.SqlClient;

В классе формы создаем экземпляр conn:

SqlConnection conn = null;

Обработчик кнопки btnUpdate будет иметь следующий вид:

private void btnUpdate_Click(object sender, System.EventArgs e) {
         try {
            string Family = Convert.ToString(this.txtFamilyUpdate.Text);
            int TouristID = int.Parse(this.txtTouristIDUpdate.Text);
            conn = new SqlConnection();
            conn.ConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=" +
                      @"D:BMИFor ADOBDTur_firmSQL2.mdf" +
                      "Integrated Security=True;Connect Timeout=30;User Instance=True";
            conn.Open();
            SqlCommand myCommand = conn.CreateCommand();
            myCommand.CommandText = "UPDATE Туристы SET Фамилия = @Family WHERE [Код туриста] = @TouristID";
            myCommand.Parameters.Add("@Family", SqlDbType.NVarChar, 50);
  myCommand. Parameters["@Family"].Value = Family;
  myCommand.Parameters.Add("@TouristID", SqlDbType.Int, 4);
  myCommand. Parameters["@TouristID"].Value = TouristID;
            int UspeshnoeIzmenenie = myCommand.ExecuteNonQuery();
            if (UspeshnoeIzmenenie !=0) {
                        MessageBox.Show("Изменения внесены", "Изменение записи");
            }
            else {
                        MessageBox.Show(“Не удалось внести изменения", "Изменение записи");
            }
            conn.Close();
          }
          catch(Exception ex) {
            MessageBox.Show(ex.ToString());
          }
          finally
          {
            conn.Close();
          }
}

Обратим внимание на то, что в блоке finally происходит закрытие соединения, это нужно сделать в любом случае, независимо от результата выполнения команды. Значения, введенные пользователем в текстовые поля txtFamilyUpdate и txtTouristIDUpdate, помещаются в переменные Family и TouristID. В запросе к базе данных используются два параметра - @Family и @TouristID. Они добавляются в коллекцию объекта Command с помощью метода Add свойства Parameters, а затем значения параметров устанавливаются равными переменным Family и TouristID. Метод Add перегружен. Использованный в данном фрагменте кода первый вариант этого метода принимает наибольшее количество свойств. Описание некоторых свойств метода Add приводится в таблице 16.

Конструкция метода Add, свойства Parameters объекта Command для поставщика данных OLE DB имеет в точности такую же структуру.

Таблица 16. Свойства метода Add

Добавим обработчик кнопки btnInsert.

private void btnInsert_Click(object sender, System.EventArgs e) {
          try {
            int TouristID = int.Parse(this.txtTouristIDInsert.Text);
            string Family = Convert.ToString(this.txtFamilyInsert.Text);
            string FirstName = Convert.ToString(this.txtFirstNameInsert.Text);
            string MiddleName = Convert.ToString(this.txtMiddleNameInsert.Text);
            conn = new SqlConnection();
            conn.ConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=" +
                        @"D:ВМИFor ADOBDTur_firmSQL2.mdf" +
                        ";Integrated Security=True;Connect Timeout=30;User Instance=True";
            conn.Open();
            SqlCommand myCommand = conn.CreateCommand();
            myCommand.CommandText = "INSERT INTO " +
                        "Туристы ([Код туриста], Фамилия, Имя, Отчество) " +
                        "VALUES (@TouristID, @Family, @FirstName, @MiddleName)";
                        myCommand.Parameters.Add("@TouristID", SqlDbType.Int, 4);
                        myCommand. Parameters["@TouristID"].Value = TouristID;
                        myCommand.Parameters.Add("@Family", SqlDbType.NVarChar, 50);
                        myCommand. Parameters["@Family"].Value = Family;
                        myCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50);
                        myCommand. Parameters["@FirstName"].Value = FirstName;
                        myCommand.Parameters.Add("@MiddleName", SqlDbType.NVarChar, 50);
                        myCommand. Parameters["@MiddleName"].Value = MiddleName;
                        int UspeshnoeIzmenenie = myCommand.ExecuteNonQuery();
                        if (UspeshnoeIzmenenie !=0) {
                                   MessageBox.Show("Изменения внесены", "Изменение записи");
                        }
                        else {
                                   MessageBox.Show(“Не удалось внести изменения", "Изменение записи");
                        }
            }
            catch(Exception ex) {
                        MessageBox.Show(ex.ToString());
            }
            finally {
                        conn.Close();
            }
}

В запросе используются четыре параметра: @TouristID, @Family, @FirstName, @MiddleName. Тип данных создаваемых параметров соответствует типу данных полей таблицы «Туристы» в базе.

Добавим обработчик кнопки btnDelete.

private void btnDelete_Click(object sender, System.EventArgs e) {
          try {
            int TouristID = int.Parse(this.txtTouristIDDelete.Text);
            conn = new SqlConnection();
            conn.ConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=" +
                        @"D:ВМИFor ADOBDTur_firmSQL2.mdf" +
                        "integrated Security=True;Connect Timeout=30;User Instance=True";
  conn.Open();
            SqlCommand myCommand = conn.CreateCommand();
            myCommand.CommandText = "DELETE FROM Туристы" +
                        "WHERE [Код туриста] = @TouristID";
            myCommand.Parameters.Add("@TouristID", SqlDbType.Int, 4);
            myCommand. Parameters["@TouristID"].Value = TouristID;
  int UspeshnoeIzmenenie = myCommand.ExecuteNonQuery();
            if (UspeshnoeIzmenenie !=0) {
                        MessageBox.Show("Изменения внесены", "Изменение записи");
  }
            else {
                        MessageBox.Show(“Не удалось внести изменения", "Изменение записи");
            }
          }
          catch(Exception ex) {
            MessageBox.Show(ex.ToString());
          }
          finally {
            conn.Close();
          }
}

Запускаем приложение. В каждой из групп заполняем поля, затем нажимаем на кнопки. Проверять результат можно, запуская Management Studio и просматривая каждый раз содержимое таблицы «Туристы» (рис. 88 - 90).

Рис. 88. Пример обновления

Рис. 89. Пример удаления

Рис. 90. Пример добавления

Использование метода ExecuteScalar

Применять метод ExecuteScalar объекта Command в Windows-приложениях очень легко - достаточно указать элемент управления (текстовое поле, надпись) для вывода одиночного значения.

Использование метода ExecuteReader

Теперь рассмотрим метод ExecuteReader. Одна из главных задач при использовании этого метода - разместить возвращаемый набор данных в элементе управления на форме.

Создайте новое приложение. Расположите на форме элемент ListBox, его свойству Dock установите значение Bottom. Добавим элемент Splitter, свойству Dock которого также установим значение Bottom. Наконец, перетаскиваем элемент ListView, свойству Dock которого устанавливаем значение Fill.

Далее, нужно настроить внешний вид элемента ListView: в окне Properties в поле свойства Columns нажимаем на кнопку (...).

В редакторе «Column Header Collection Editor» добавляем следующие четыре элемента:

Name                            Text
chTouristID             Код туриста
chFamily                 Фамилия
chFirstName            Имя
chMiddleName        Отчество

Для отображения созданных столбцов свойству View элемента устанавливаем значение Details. Также включим режим отображения линий сетки - в свойстве GridLines выбираем значение True. Сделанные изменения немедленно отобразятся на элементе.

Подключаем пространство имен для работы с базой:

using System.Data.SqlClient;

В классе формы создаем объекты conn и dataReader:

SqlConnection conn = null;
SqlDataReader dataReader;

В конструкторе формы добавляем код для заполнения данными элементов управления:

public Form1() {
          InitializeComponent();
          try {
            conn = new SqlConnection();
            conn.ConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=" +
                        @"D:ВМИFor ADOBDTur_firmSQL2.mdf" +
                        "integrated Security=True;Connect Timeout=30;User Instance=True";
            conn.Open();
            SqlCommand myCommand = conn.CreateCommand();
            myCommand.CommandText = "SELECT * FROM Туристы";
            dataReader = myCommand.ExecuteReader();
            while (dataReader.Read()) {
            // Создаем переменные, получаем для них значения из объекта dataReader,
            //используя метод GetТипДанных
                        int TouristID = dataReader.GetInt32(0);
                        string Family = dataReader.GetString(1);
                        string FirstName = dataReader.GetString(2);
                        string MiddleName = dataReader.GetString(3);
            //Выводим данные в элемент listBox1
                        listBox1.Items.Add("Код туриста: " + TouristID+ " Фамилия: " + Family + " Имя: "+ FirstName + " Отчество: " + MiddleName);
            //Создаем экземпляр item класса ListViewItem для записи в него
            //данных из dataReader
                        ListViewItem item = new ListViewItem(new string[]
                                   {Convert.ToString(dataReader[0]), Convert.ToString(dataReader[1]),
                                    Convert.ToString(dataReader[2]), Convert.ToString(dataReader[3])});
                        listView1.Items.Add(item);
            }
          }
          catch(Exception ex) {
            MessageBox.Show(ex.ToString());
          }
          finally {
            dataReader.Close();
            conn.Close();
          }
}

Метод GetТипДанных позволяет приводить значения, возвращаемые объектом DataReader, если заранее известен их тип данных.

Запускаем приложение. На форму выводятся данные в виде списка в элементе ListBox и в виде таблицы в элементе ListView (рис. 91).

Вывод данных в элемент ListView приводит к достаточно удовлетворительному результату, однако более привычным является вывод в элемент DataGridView. Ранее, при использовании объекта DataSet, источник данных для элемента DataGridView указывался следующим образом:

dataGridView1.DataSource = dataset.Tables["Название_таблицы"].DefaultView;

Или так:

dataGridView1.DataSource = dataset;

Объект DataReader не поддерживает аналогичного вывода - мы не можем связать объекты таким простым образом:

dataGridView1.DataSource = datareader;

Рис. 91. Использование метода ExecuteReader

Одним из способов вывода является применение дополнительных объектов DataTable. Объект DataTable предназначен для хранения таблицы, полученной из базы данных.

Создадим новое приложение. Перетаскиваем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение Fill.

Подключаем пространство имен для работы с базой:

using System.Data.SqlClient;

В классе формы создаем следующие объекты:

SqlConnection conn = null;
//Создаем экземпляр FullDataTable, в который будут помещаться данные
DataTable FullDataTable = new DataTable();
//Создаем экземпляр FullDataTable для получения структуры таблицы из базы данных
DataTable ShemaDataTable = new DataTable();
SqlDataReader dataReader;
SqlCommand myCommand;
//Создаем объект objectRow для получения информации о числе столбцов
object[] objectRow;
//Создаем объект myDataRow для помещения записей
DataRow myDataRow;
Основной код помещаем в конструктор формы:
public Form1() {
          InitializeComponent();
          try {
            conn = new SqlConnection();
            conn.ConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=" +
                        @"D:ВМИFor ADOBDTur_firmSQL2.mdf" +
                        "integrated Security=True;Connect Timeout=30;User Instance=True";
            conn.Open();
            myCommand = conn.CreateCommand();
            myCommand.CommandText = "SELECT * FROM Туристы";
            dataReader = myCommand.ExecuteReader();
            //Вызываем метод GetSchemaTable, который получает схему таблицы из базы
            //и передает ее объекту ShemaDataTable
            ShemaDataTable = dataReader.GetSchemaTable();
            //Свойство FieldCount возвращает число столбцов для текущей записей.
            //Передаем это значение объекту objectRow
            objectRow = new object[dataReader.FieldCount];
            //Определяем структуру объекта FullDataTable
            for(int i =0; i <dataReader.FieldCount; i++) {
                        FullDataTable.Columns.Add(ShemaDataTable.Rows[i]["ColumnName"].ToString(),
                                            ((System.Type)ShemaDataTable.Rows[i]["DataType"]));
            }
            //Добавляем записи в объект FullDataTable
            while(dataReader.Read()) {
                        dataReader. GetValues(objectRow);
                        myDataRow = FullDataTable.Rows.Add(objectRow);
            }
            //Определяем источник данных для элемента
            dataGrid1 dataGrid1 .DataSource = FullDataTable;
          }
          catch (Exception ex) {
            MessageBox.Show(ex.ToString());
          }
          finally {
            dataReader.Close();
            conn.Close();
          }
}

Запускаем приложение (рис. 92):

Рис. 92. Демонстрация работы приложения