4.3.4. Вызов хранимых процедур

Хранимые процедуры с входными параметрами

Теперь вернемся к работе с хранимыми процедурами. Выше уже применялись самые простые процедуры (они приводятся в таблице 13), содержимое которых представляло собой, по сути, элементарные запросы на выборку в Windows-приложениях. Применение хранимых процедур с параметрами (таблица 14), как правило, связано с интерфейсом приложения - пользователь имеет возможность вводить значение и затем на основании его получать результат.

Для демонстрации программной работы с хранимыми процедурами создадим новое Windows-приложение, главное окно которого представлено на рис. 93.

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

string connectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=" +
                      @"D:BMИFor ADOBDTur_firmSQL2.mdf" +
                       "Integrated Security=True;Connect Timeout=30;User Instance=True";

В каждом из обработчиков кнопок создаем объекты 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();
} // btnRun_p1_Click
 
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();
}

Результаты выполнения данных обработчиков представлены на рисунках 93-95.

Рис. 93. Вызов хранимой процедуры proc_p1

Рис. 94. Вызов хранимой процедуры proc_p5

Рис. 95. Вызов хранимой процедуры proc_p6

Хранимые процедуры с входными и выходными параметрами

На практике наиболее часто используются хранимые процедуры с входными и выходными параметрами (см. таблицу 15). Создадим новое Windows- приложение, вид его формы в режиме дизайна показан на рисунке 96.

Рис. 96. Вид формы в режиме дизайна

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

SqlConnection conn = null;

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

private void btnRun_Click(object sender, System.EventArgs e) {
          try {
            conn = new SqlConnection();
            conn.ConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=" +
                      @"D:BMИFor ADOBDTur_firmSQL2.mdf +
                       "Integrated Security=True;Connect Timeout=30;User Instance=True";
            SqlCommand myCommand = conn.CreateCommand();
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandText = "[proc_po1]";
            int TouristID = int.Parse(this.txtTouristID.Text);
            myCommand.Parameters.Add("@TouristID", SqlDbType.Int, 4);
            myCommand.Parameters["@TouristID"].Value = TouristID;
//Необязательная строка, т.к. совпадает со значением по умолчанию.
//myCommand.Parameters["@TouristID"].Direction = ParameterDirection.Input;
            myCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 60);
            myCommand.Parameters["@LastName"].Direction = ParameterDirection.Output;
            conn.Open();
            myCommand.ExecuteScalar();
            lblFamily.Text = Convert.ToString(myCommand.Parameters["@LastName"].Value);
          }
          catch (Exception ex) {
             MessageBox.Show(ex.ToString());
          } finally {
            conn.Close();
          }
}

Параметр @LastName был добавлен в набор Parameters, причем его значение output указано в свойстве Direction:

myCommand.Parameters["@LastName"].Direction = ParameterDirection.Output;

Параметр @TouristID является исходным, поэтому для него в свойстве Direction указывается Input. Поскольку это является значением по умолчанию для всех параметров набора Parameters, указывать его явно не нужно. Для параметров, работающих в двустороннем режиме, устанавливается значение InputOutput, для параметров, возвращающих данные о выполнения хранимой процедуры, - ReturnValue.

Результат выполнения приложения показан на рисунке 97.

Рис. 97. Результат выполнения приложения

Хранимые процедуры из нескольких SQL-конструкций

Хранимая процедура может содержать несколько SQL-конструкций, определяющих работу приложения. При ее вызове возникает задача распределения данных, получаемых от разных конструкций. Создадим новую хранимую процедуру proc_NextResult. Процедура proc_NextResult будет состоять из двух конструкций: первая будет возвращать содержимое таблицы «Туристы», а вторая - содержимое таблицы «Туры»:

CREATE PROCEDURE proc_NextResult
AS
SET NOCOUNT ON
SELECT * FROM Туристы
SELECT * FROM Туры
RETURN

После сохранения процедуры создадим новое Windows-приложение.

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

Поставим следующую задачу: в первый элемент ListBox вывести несколько произвольных столбцов таблицы «Туры», а во второй - несколько столбцов таблицы «Туристы».

Конструктор формы примет следующий вид:

public Form1() {
          InitializeComponent();
          SqlConnection conn = new SqlConnection();
          conn.ConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=" +
                  @"D:ВМИFor ADOBDTur_firmSQL2.mdf" +
                  "integrated Security=True;Connect Timeout=30;User Instance=True";
          SqlCommand myCommand = conn.CreateCommand();
          myCommand.CommandType = CommandType.StoredProcedure;
          myCommand.CommandText = "[proc_NextResult]";
          conn.Open();
          SqlDataReader dataReader = myCommand.ExecuteReader();
          while(dataReader.Read()) {
            listBox2.Items.Add(dataReader.GetString(1)+" "+dataReader.GetString(2));
          }
          dataReader.NextResult();
          while(dataReader.Read()) {
            listBox1.Items. Add(dataReader.GetString(1) +
                                   ". Дополнительная информация: "+dataReader.GetString(3));
          }
          dataReader.Close();
          conn.Close();
}

Результат выполнения данного приложения приведен на рисунке 98.

Рис. 98. Результат выполнения приложения