Вызов хранимых процедур с входными параметрами
Теперь, когда мы разобрались с методами объекта Command, мы можем вернуться к работе с хранимыми процедурами. Мы уже применяли самые простые процедуры (они приводятся в таблице 5.1), содержимое которых представляло собой, по сути, простой запрос на выборку в Windows-приложениях. Применение хранимых процедур с параметрами (таблица 5.2), как правило, связано с интерфейсом приложения - пользователь имеет возможность вводить значение и затем на основании его получать результат.
Среда Visual Studio .NET предоставляет средства для визуальной работы с хранимыми процедурами. Создайте новый Windows-проект и назовите его "VisualParametersSP". Устанавливаем следующие свойства формы:
| FormBorderStyle | FixedSingle |
| MaximizeBox | False |
| Size | 450; 330 |
Добавляем на форму элементы управления и устанавливаем их свойства:
| Location | 17; 12 |
| Size | 408; 136 |
| Text | Хранимая процедура proc_p1 |
| Location | 17; 156 |
| Size | 408; 64 |
| Text | Хранимая процедура proc_p5 |
| Location | 17; 228 |
| Size | 408; 56 |
| Text | Хранимая процедура proc6 |
| Name | txtFamily_p1 |
| Location | 16; 32 |
| Size | 288; 20 |
| Text | Введите фамилию туриста |
| Name | txtNameTour_p5 |
| Location | 16; 24 |
| Size | 136; 20 |
| Text | Введите название тура |
| Name | txtKurs_p5 |
| Location | 168; 24 |
| Size | 128; 20 |
| Text | Введите курс валюты |
| Name | btnRun_p1 |
| Location | 320; 32 |
| Text | Запуск |
| Name | btnRun_p5 |
| Location | 320; 24 |
| Text | Запуск |
| Name | btnRun_proc6 |
| Location | 16; 24 |
| Size | 208; 23 |
| Text | Цена самого дорогого тура |
| Name | lbResult_p1 |
| Location | 16; 72 |
| Size | 376; 43 |
| Name | lblPrice_proc6 |
| Location | 264; 24 |
| Text | |
| TextAlign | MiddleCenter |
Интерфейс приложения готов. Переходим в окно Server Explorer, раскрываем узел подключения к базе данных, перетаскиваем на форму процедуры proc_p1, proc_p5 и proc6 (рис. 7.1, А).
На панели компонент проекта появляются объект sqlConnection1 с тремя объектами sqlCommand (рис. 7.1, Б):
увеличить изображение
Рис. 7.1. Хранимые процедуры в окне Server Explorer. А - перемещение на форму, Б - готовая панель компонент
Среда настроила все нужные свойства объектов sqlCommand, такие как CommandType, CommandText, Connection. Выделяем объект sqlCommand1, переходим в окно Properties, в поле свойства Parameters нажимаем на кнопку
увеличить изображение
Рис. 7.2. Окно Properties объекта sqlCommand1 и редактор SqlParameter Collection Editor
В появившемся окне редактора "SqlParameter Collection Editor" можно видеть настроенные свойства "Size" и "ParameterName" параметра "@Фамилия". Эти значения были получены из базы данных. Аналогичным образом настроены другие объекты sqlCommand. Переходим в код формы. Подключаем пространство имен для работы с базой данных:
using System.Data.SqlClient;
Далее нам нужно выбрать, какой из методов объекта Command нужно применить. Для хранимой процедуры proc_p1 это будет ExecuteReader - возвращаемое значение представляет собой запись (см. таблицу 5.2). Добавляем обработчик кнопки btnRun_p1:
private void btnRun_p1_Click(object sender, System.EventArgs e) { string FamilyParameter = Convert.ToString(txtFamily_p1.Text); sqlCommand1.Parameters["@Фамилия"].Value = FamilyParameter; sqlConnection1.Open(); SqlDataReader dataReader = sqlCommand1.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); //Выводим данные в элемент lbResult_p1 lbResult_p1.Items.Add("Код туриста: " + TouristID+ " Фамилия: " + Family + " Имя: "+ FirstName + " Отчество: " + MiddleName); } sqlConnection1.Close(); }
В результате выполнения процедуры proc_p1 изменяется значения поля "Цена" в таблице "Туры" - запрос не возвращает результатов. Поэтому здесь применяем метод ExecuteNonQuery:
private void btnRun_p5_Click(object sender, System.EventArgs e) { string NameTourParameter = Convert.ToString(txtNameTour_p5.Text); double KursParameter = double.Parse(this.txtKurs_p5.Text); sqlCommand2.Parameters["@nameTour"].Value = NameTourParameter; sqlCommand2.Parameters["@Курс"].Value = KursParameter; sqlConnection1.Open(); int UspeshnoeIzmenenie = sqlCommand2.ExecuteNonQuery(); if (UspeshnoeIzmenenie !=0) { MessageBox.Show("Изменения внесены", "Изменение записи"); } else { MessageBox.Show("Не удалось внести изменения", "Изменение записи"); } sqlConnection1.Close(); }
Процедура proc6 возвращает результат в виде значения наибольшей цены в таблице "Туры". Для вывода одиночного значения используем метод ExecuteScalar. Поскольку процедура не имеет входных параметров, обработчик кнопки btnRun_proc6 будет выглядеть предельно просто:
private void btnRun_proc6_Click(object sender, System.EventArgs e) { sqlConnection1.Open(); string MaxPrice = Convert.ToString(sqlCommand3.ExecuteScalar()); lblPrice_proc6.Text = MaxPrice; sqlConnection1.Close(); }
Запускаем приложение (рис. 7.3). Для просмотра результатов выполнения хранимой процедуры proc_p5 (таблицы "Туры") запускаем SQL Server Enterprise Manager.
Рис. 7.3. Готовое приложение VisualParametersSP
В программном обеспечении к курсу вы найдете приложение Visual ParametersSP (Code\Glava3\ VisualParametersSP).
Создадим в точности такое же приложение программно. Для того чтобы не делать заново интерфейс приложения, скопируем всю папку проекта VisualParametersSP, переименуем ее в "ProgrammParametersSP". Открываем проект и удаляем все объекты с панели компонент. В классе формы создаем строку подключения:
string connectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2";
В каждом из обработчиков кнопок создаем объекты Connection и Command, определяем их свойства, для последнего добавляем нужные параметры в набор Parameters:
private void btnRun_p1_Click(object sender, System.EventArgs e) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = connectionString; SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandText = "[proc_p1]"; string FamilyParameter = Convert.ToString(txtFamily_p1.Text); myCommand.Parameters.Add("@Фамилия", SqlDbType.NVarChar, 50); myCommand.Parameters["@Фамилия"].Value = FamilyParameter; conn.Open(); SqlDataReader 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); //Выводим данные в элемент lbResult_p1 lbResult_p1.Items.Add("Код туриста: " + TouristID+ " Фамилия: " + Family + " Имя: "+ FirstName + " Отчество: " + MiddleName); } conn.Close(); }
private void btnRun_p5_Click(object sender, System.EventArgs e) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = connectionString; SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandText = "[proc_p5]"; string NameTourParameter = Convert.ToString(txtNameTour_p5.Text); double KursParameter = double.Parse(this.txtKurs_p5.Text); myCommand.Parameters.Add("@nameTour", SqlDbType.NVarChar, 50); myCommand.Parameters["@nameTour"].Value = NameTourParameter; myCommand.Parameters.Add("@Курс", SqlDbType.Float, 8); myCommand.Parameters["@Курс"].Value = KursParameter; conn.Open(); int UspeshnoeIzmenenie = myCommand.ExecuteNonQuery(); if (UspeshnoeIzmenenie !=0) { MessageBox.Show("Изменения внесены", "Изменение записи"); } else { MessageBox.Show("Не удалось внести изменения", "Изменение записи"); } conn.Close(); }
private void btnRun_proc6_Click(object sender, System.EventArgs e) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = connectionString; SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandText = "[proc6]"; conn.Open(); string MaxPrice = Convert.ToString(myCommand.ExecuteScalar()); lblPrice_proc6.Text = MaxPrice; conn.Close(); }
Результат работы приложения будет такой же, как и в случае применения визуальных средств студии1).
Сравните листинги приложений VisualParametersSP и Programm ParametersSP - в первом из них среда создала все объекты соединения и наборы параметров, нам оставалось только связать значения параметров с элементами управлений при помощи свойства Value. С набором Parameters объекта Command мы уже встречались в приложении ExamWinExecuteNonQuery, когда применяли параметризированные запросы.
В программном обеспечении к курсу вы найдете приложение Programm ParametersSP (Code\Glava3\ ProgrammParametersSP).
