Технология Microsoft ADO.NET

       

Применение методов объекта Command в Windows-приложениях. Параметризированные запросы


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

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

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

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

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

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

Здесь @Family (обратите внимание, пишется без кавычек!) - параметр для неизвестного значения фамилии, @TouristID - параметр для неизвестного значения кода туриста. Теперь мы можем привязывать параметры к тексту, вводимому пользователем. Создайте новое Windows-приложение и назовите его "ExamWinExecuteNonQuery". Устанавливаем следующие свойства формы:

Form1, форма, свойствоЗначение
FormBorderStyleFixedSingle
MaximizeBoxFalse
Size620; 240

Добавляем на форму элементы управления и устанавливаем их свойства:

gropBox1, свойствоЗначение
Location16; 16
Size296; 112
TextПример UPDATE
gropBox2, свойство Значение
Location 320; 16
Size 280; 176
Text Пример INSERT
gropBox3, свойство Значение
Location 16; 136
Size 296; 56
Text Пример DELETE
button1, свойство Значение
Name btnUpdate
Location 80; 80
Text Обновить
button2, свойство Значение
Name btnInsert
Location 88; 144
Text Добавить
button3, свойство Значение
Name BtnDelete
Location 208; 24
Text Удалить
textBox1, свойство Значение
Name TxtTouristIDUpdate
Location 16; 24
Size 224; 20
Text Введите код туриста
textBox2, свойство Значение
Name TxtFamilyUpdate
Location 16; 56
Size 224; 20
Text Введите фамилию туриста
textBox3, свойство Значение
Name TxtTouristIDInsert
Location 16; 24
Size 224; 20
Text Введите код туриста
textBox4, свойство Значение
Name TxtFamilyInsert
Location 16; 56
Size 224; 20
Text Введите фамилию туриста
textBox5, свойство Значение
Name TxtFirstNameInsert
Location 16; 88
Size 224; 20
Text Введите имя туриста
textBox6, свойство Значение
Name TxtMiddleNameInsert
Location 16; 120
Size 224; 20
Text Введите отчество туриста
textBox7, свойство Значение
Name txtTouristIDDelete
Location 8; 24
Size 192; 20
Text Введите код туриста для удаления


Форма в режиме дизайна будет иметь следующий вид (рис. 6.22):


увеличить изображение
Рис. 6.22.  Приложение ExamWinExecuteNonQuery, вид формы в режиме дизайна

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

using System.Data.SqlClient;

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

SqlConnection conn = null;

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

private void btnUpdate_Click(object sender, System.EventArgs e) { try { //Создаем переменную Family, в которую помещаем значение, //введенное пользователем в поле txtFamilyUpdate: string Family = Convert.ToString(this.txtFamilyUpdate.Text); //Создаем переменную TouristID, в которую помещаем значение, //введенное пользователем в поле txtTouristIDUpdate: int TouristID = int.Parse(this.txtTouristIDUpdate.Text); conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; conn.Open(); SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandText = "UPDATE Туристы SET Фамилия = @Family WHERE Кодтуриста = @TouristID"; //Добавляем параметр @Family в коллекцию параметров //объекта myCommand myCommand.Parameters.Add("@Family", SqlDbType.NVarChar, 50); //Устанавливаем значение параметра @Family //равным значению переменной Family myCommand.Parameters["@Family"].Value = Family; //Добавляем параметр @TouristID в коллекцию параметров //объекта myCommand myCommand.Parameters.Add("@TouristID", SqlDbType.Int, 4); //Устанавливаем значение параметра @TouristID //равным значению переменной TouristID 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(); } }

Обратите внимание - в блоке finally мы закрываем соединение, это нужно сделать в любом случае, независимо от результата выполнения команды.




Значения, введенные пользователем в текстовые поля txtFamilyUpdate и txtTouristIDUpdate, помещаются в переменные Family и TouristID. В запросе к базе данных используются два параметра - @Family и @TouristID. Мы добавляем их в коллекцию объекта Command, используя метод Add свойства Parameters, а затем устанавливаем значения параметров равными переменным Family и TouristID. Конструктор метод Add перегружен, первый вариант принимает наибольшее количество свойств8) (рис. 6.23):


Рис. 6.23.  Конструктор метода Add свойства Parameters объекта Command

Описание некоторых свойств метода Add приводится в таблице 6.2.

Таблица 6.2. Свойства метода AddСвойство Описание
parameterName Название параметра
sqlDbType Тип данных передаваемого параметра
size Размер параметра
sourceColumn Название имени столбца объекта DataSet, на который ссылается данный параметр
Конструктор метода Add свойства Parameters объекта Command для поставщика данных OLE DB имеет в точности такую же структуру (рис. 6.24):


Рис. 6.24.  Конструктор метода Add свойства Parameters объекта Command поставщика данных OLE DB

Добавляем обработчик кнопки 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 = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; 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 = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; 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(); } }

Запускаем приложение. В каждой из групп заполняем поля, затем нажимаем на кнопки. Проверять результат можно, запуская SQL Server Enterprise Manager и просматривая каждый раз содержимое таблицы "Туристы" (рис. 6.25):


Рис. 6.25.  Готовое приложение ExamWinExecuteNonQuery. Таблицы взяты из SQL Server Enterprise Manager

В программном обеспечении к курсу вы найдете приложение Exam WinExecuteNonQuery (Code\Glava3\ ExamWinExecuteNonQuery).

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

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


Создайте новое приложение и назовите его "ExamWinExecuteReader". Перетаскиваем на форму элемент ListBox, его свойству Dock устанавливаем значение Bottom. Добавляем элемент Splitter, свойству Dock также устанавливаем значение Bottom. Наконец, перетаскиваем элемент ListView, свойству Dock устанавливаем значение Fill. Нам нужно настроить внешний вид элемента ListView: в окне Properties в поле свойства Columns нажимаем на кнопку
(...) (рис. 6.26). В редакторе "Column Header Collection Editor" добавляем следующие четыре элемента:

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

увеличить изображение
Рис. 6.26.  Свойство Columns элемента ListView и редактор "ColumnHeader Collection Editor"

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

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

using System.Data.SqlClient;

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

SqlConnection conn = null; SqlDataReader dataReader;

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

public Form1() { InitializeComponent(); try { conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; 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 (рис. 6.27):


Рис. 6.27.  Приложение ExamWinExecuteReader

В программном обеспечении к курсу вы найдете приложение ExamWin ExecuteReader (Code\Glava3\ ExamWinExecuteReader).

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

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

Или так:

dataGrid1.DataSource = dataset;

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

dataGrid1.DataSource = datareader;

Одним из способов вывода является применение дополнительных объектов DataTable. Объект DataTable предназначен для хранения таблицы, полученной из базы данных (в восьмой лекции мы рассмотрим подробно этот объект). Создайте новое приложение и назовите его "DataReaderToDataGrid". Перетаскиваем на форму элемент управления 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;

Основной код помещаем в конструктор формы:

public Form1() { InitializeComponent(); try { conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; 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(); } }



Возможно, этот код показался вам сложным. Лучше будет к нему вернуться еще раз после изучения девятой лекции. Запускаем приложение (рис. 6.28):


Рис. 6.28.  Приложение DataReaderToDataGrid

В программном обеспечении к курсу вы найдете приложение Data ReaderToDataGrid (Code\Glava3\ DataReaderToDataGrid).

  1)

  Конструктор объекта OleDbCommand в точности такой же.

  2)

  Приводится только создание объекта SqlTransaction, без его методов, - см далее Лекцию 7, тему "Работа с транзакциями".

  3)

  Здесь снова "язык DDL языка SQL:" Так уж это принято, ничего не могу с этим поделать.

  4)

  Скорее всего, у вас будет измененное содержимое таблицы "Туры" - если, конечно, вы выполняли все действия, описанные в этой главе. Для получения исходной таблицы запустите SQL Server Enterprise Manager, удалите имеющуюся таблицу "Туры" и импортируйте ее снова из файла BDTur_firm2.mdb ((Code\Glava3\ BDTur_firm2.mdb).

  5)

  Таблица "Туристы" была заменена на оригинальную из базы BDTur_firm2.mdb.

  6)

  Использование индексов повышает производительность приложения.

  7)

  Мы это делаем для включения блока обработки исключений.

  8)

  Вообще-то, раньше свойства, входящие в какой-либо метод, мы называли параметрами. Здесь я не стал использовать это слово, чтобы не было путаницы с параметрами, входящими в коллекцию объекта Command.

Содержание раздела