Вызов хранимых процедур. Работа с транзакциями

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

На практике наиболее часто используются хранимые процедуры с входными и выходными параметрами (см. таблицу 5.3). Создайте новое приложение и назовите его "VisualOutputParameter". Устанавливаем следующие свойства формы:

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

Переходим на вкладку Server Explorer, раскрываем узел Stored Procedures базы данных BDTur_firm2 и перетаскиваем процедуру (...) для перехода к редактору SqlParameter Collection Editor. Среда сгенерировала три параметра - @RETURN_VALUE, @TouristID, @LastName (рис. 7.4):

Сообщение об успешности выполнения команды возвращается при помощи параметра @RETURN_VALUE, значение которого для запроса данной хранимой процедуры будет равно нулю. Для процедур, содержащих запросы UPDATE, INSERT и DELETE, возвращаемое значение будет равно числу измененных записей. Обратите внимание, в тексте процедуры мы не создавали этот параметр - среда сгенерировала его автоматически! Два последних параметра были определены при создании хранимой процедуры. Подключаем пространство имен для работы с базой:

using System.Data.SqlClient;

В обработчике кнопки btnRun выводим фамилию искомого туриста в качестве текста надписи:

private void btnRun_Click(object sender, System.EventArgs e)
{
	try
	{
		int TouristID = int.Parse(this.txtTouristID.Text);
		sqlCommand1.Parameters["@TouristID"].Value = TouristID;
		sqlConnection1.Open();
		sqlCommand1.ExecuteScalar();
		lblFamily.Text =
		 Convert.ToString(sqlCommand1.Parameters["@LastName"].Value);
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.ToString());
	}
	finally
	{
		sqlConnection1.Close();
	}
}

Запускаем приложение. Если в таблице имеется фамилия, соответствующая заданному коду, она выводится на форму (рис. 7.5):

При использовании визуальных средств код в обработчике ничем не отличается от рассматриваемого ранее - мы нигде не указывали значение output параметра @LastName. Дело в том, что среда сама настроила это значение в свойстве Direction (см. рис. 7.4). При программном вызове хранимой процедуры нам придется определять его вручную. Скопируйте папку приложения VisualOutputParameter и переименуйте ее в "ProgrammOutputParameter". Открываем проект, удаляем все объекты с панели компонент формы. В классе формы создаем объект Connection:

SqlConnection conn = null;

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

private void btnRun_Click(object sender, System.EventArgs e)
{
	try
	{
		conn = new SqlConnection();
		conn.ConnectionString = "integrated security=SSPI;data
		 source=\".\"; persist security info=False;
		 initial catalog=BDTur_firm2";
		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, указывать его явно не нужно. Перечисление ParameterDirection принимает еще два значения - InputOutput и ReturnValue (рис. 7.6).

Для параметров, работающих в двустороннем режиме, устанавливается значение InputOutput, для параметров, возвращающих данные о выполнения хранимой процедуры, - ReturnValue. Примером последнего может служить @RETURN_VALUE (см. рис. 7.4).

В программном обеспечении к курсу вы найдете приложения Visual OutputParameter и ProgrammOutputParameter (Code\Glava3 \VisualOutput Parameter и ProgrammOutputParameter).

Вызов хранимых процедур, содержащих несколько SQL-конструкций

Хранимая процедура может содержать несколько SQL-конструкций, определяющих работу приложения. При ее вызове возникает задача распределения данных, получаемых от разных конструкций. Запускаем Visual Studio .NET, переходим на вкладку Server Explorer, раскрываем узел базы BDTur_firm2. Щелкаем правой кнопкой на узле Stored Procedures и в появившемся меню выбираем "New Stored Procedure". Процедура proc_NextResult будет состоять из двух конструкций: первая будет возвращать содержимое таблицы "Туристы", а вторая - содержимое таблицы "Туры":

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

После сохранения процедуры создайте новое Windows-приложение VisualNextResult. Перетаскиваем на форму элемент управления ListBox, его свойству Dock устанавливаем значение Bottom. Добавляем элемент Splitter (разделитель), свойству Dock которого также устанавливаем значение Bottom. Наконец, добавляем еще один элемент ListBox, свойству Dock которого устанавливаем значение Fill. Из окна Server Explorer перетаскиваем на форму только что созданную процедуру proc_NextResult. В классе формы добавляем пространство имен для работы с базой:

using System.Data.SqlClient;

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

public Form1()
{
	InitializeComponent();
	sqlConnection1.Open();
	SqlDataReader dataReader = sqlCommand1.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();
	sqlConnection1.Close();
}

Метод GetString объекта DataReader позволяет получать содержимое столбца c заданным индексом, приведенное к типу String. В первом цикле while мы получаем результаты первого запроса SELECT, затем, вызывая метод NextResult объекта DataReader, переходим к результатам второго запроса. Запускаем приложение - в каждом элементе содержится свой набор записей (рис. 7.7):

Нетрудно сделать это же самое приложение без применения визуальных средств студии. Скопируйте папку приложения VisualNextResult и переименуйте ее в ProgrammNextResult. Открываем проект, удаляем все объекты с панели компонент формы. Конструктор формы примет следующий вид:

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

В программном обеспечении к курсу вы найдете приложения VisualNext Result и ProgrammNextResult (Code\Glava3\VisualNextResult и Programm NextResult).

Работа с транзакциями

Транзакцией называется выполнение последовательности команд (SQL-конструкций) в базе данных, которая либо фиксируется при успешном извлечении каждой команды, либо отменяется при неудачном извлечении хотя бы одной команды. Большинство современных СУБД поддерживают механизм транзакций, и подавляющее большинство клиентских приложений, работающих с ними, используют для выполнения команд транзакции. Зачем нужны транзакции? Представим себе, что в базу данных BDTur_firm2 требуется вставить связанные записи в две таблицы - "Туристы" и "Информацияотуристах". Если запись, вставляемая в таблицу "Туристы", окажется неверной, например, из-за неправильно указанного кода туриста, база данных не позволит внести изменения, а тогда в таблице "Информацияотуристах" появится ненужная запись. Запускаем SQL Query Analyzer, в новом бланке вводим запрос для добавления двух записей:

INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество)
VALUES (6, 'Тихомиров', 'Андрей', 'Борисович');
INSERT INTO Информацияотуристах(Кодтуриста, Серияпаспорта,
 Город, Страна, Телефон, Индекс)
VALUES (6, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548);

Две записи успешно добавляются в базу данных:

(1 row(s) affected)

(1 row(s) affected)

Изменим код туриста только во втором запросе:

INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество)
VALUES (6, 'Тихомиров', 'Андрей', 'Борисович');
INSERT INTO Информацияотуристах(Кодтуриста, Серияпаспорта,
 Город, Страна, Телефон, Индекс)
VALUES (7, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548);

Появляется сообщение о невозможности вставки первой записи с уже имеющимся значением ключевого поля. Вторая запись, однако, была добавлена в таблицу:

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Туристы'.
 Cannot insert duplicate key in object 'Туристы'.
The statement has been terminated.
(1 row(s) affected)

Извлекаем содержимое обеих таблиц следующим двойным запросом:

SELECT * FROM Туристы
SELECT * FROM Информацияотуристах

В таблице "Информацияотуристах" последняя запись добавилась безо всякой связи с записью таблицы "Туристы" (рис. 7.8):

Для того чтобы избегать подобных ошибок, нам нужно применить транзакцию. Удалим все внесенные записи из обеих таблиц (это можно сделать с помощью запроса или в SQL Server Enterprise Manager) и оформим исходные SQL-конструкции в виде транзакции:

BEGIN TRAN 
DECLARE @OshibkiTabliciTourists int, @OshibkiTabliciInfoTourists int 
INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество)
VALUES (6, 'Тихомиров', 'Андрей', 'Борисович');
SELECT @OshibkiTabliciTourists=@@ERROR
INSERT INTO Информацияотуристах(Кодтуриста, Серияпаспорта,
 Город, Страна, Телефон, Индекс)
VALUES (6, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548);
SELECT @OshibkiTabliciInfoTourists=@@ERROR
IF @OshibkiTabliciTourists=0 AND @OshibkiTabliciInfoTourists=0
COMMIT TRAN
ELSE
ROLLBACK TRAN

Начало транзакции мы объявляем с помощью команды BEGIN TRAN. Далее создаем два параметра - @OshibkiTabliciTourists, @OshibkiTabliciInfoTourists для сбора ошибок. После первого запроса возвращаем значение, которое встроенная функция @@ERROR присваивает первому параметру:

SELECT @OshibkiTabliciTourists=@@ERROR

То же самое делаем после второго запроса для другого параметра:

SELECT @OshibkiTabliciInfoTourists=@@ERROR

Проверяем значения обоих параметров, которые должны быть равными нулю при отсутствии ошибок:

IF @OshibkiTabliciTourists=0 AND @OshibkiTabliciInfoTourists=0

В этом случае подтверждаем транзакцию (внесение изменений) при помощи команды COMMIT TRAN. В противном случае - если значение хотя бы одного из параметров @OshibkiTabliciTourists и @Oshibki TabliciInfoTourists оказывается отличным от нуля, отменяем транзакцию при помощи команды ROLLBACK TRAN.

После выполнения транзакции появляется уже знакомое сообщение:

(1 row(s) affected)

(1 row(s) affected)

Снова изменим код туриста во втором запросе:

BEGIN TRAN 
DECLARE @OshibkiTabliciTourists int, @OshibkiTabliciInfoTourists int 
INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество)
VALUES (6, 'Тихомиров', 'Андрей', 'Борисович');
SELECT @OshibkiTabliciTourists=@@ERROR
INSERT INTO Информацияотуристах(Кодтуриста, Серияпаспорта,
 Город, Страна, Телефон, Индекс)
VALUES (7, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548);
SELECT @OshibkiTabliciInfoTourists=@@ERROR
IF @OshibkiTabliciTourists=0 AND @OshibkiTabliciInfoTourists=0
COMMIT TRAN
ELSE
ROLLBACK TRAN

Запускаем транзакцию - появляется в точности такое же сообщение, что и в случае применения обычных запросов:

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Туристы'.
 Cannot insert duplicate key in object 'Туристы'.
The statement has been terminated.

(1 row(s) affected)

Однако теперь изменения не были внесены во вторую таблицу (рис. 7.9):

Сообщение "(1 row(s) affected)", указывающее на "добавление" одной записи, в данном случае всего лишь означает, что вторая SQL-конструкция была верной и запись могла быть добавлена в случае успешного выполнения транзакции. Сделаем ошибку во втором запросе и снова попытаемся выполнить транзакцию:

BEGIN TRAN 
DECLARE @OshibkiTabliciTourists int, @OshibkiTabliciInfoTourists int 
INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество)
VALUES (7, 'Тихомиров', 'Андрей', 'Борисович');
SELECT @OshibkiTabliciTourists=@@ERROR
INSERT INTO Информацияотуристах(Кодтуриста, Серияпаспорта,
 Город, Страна, Телефон, Индекс)
VALUES (6, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548);
SELECT @OshibkiTabliciInfoTourists=@@ERROR
IF @OshibkiTabliciTourists=0 AND @OshibkiTabliciInfoTourists=0
COMMIT TRAN
ELSE
ROLLBACK TRAN

Появляется аналогичное сообщение:

(1 row(s) affected)

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Информацияотуристах'.
 Cannot insert duplicate key in object 'Информацияотуристах'.
The statement has been terminated.

Изменения снова не были внесены в базу данных - в этом можно убедиться, вернув содержимое обеих таблиц. Читатель, хорошо знакомый с теорией баз данных, может заметить, что обеспечить целостность данных двух таблиц (в данном случае это именно так и называется) вполне можно и другими средствами, например, просто связать их и установить соответствующие правила. Это правильно, но для нас сейчас важно понимать, что в одной транзакции можно выполнить несколько самых разных запросов, которые можно разом применить или отклонить. Начало транзакции мы объявляем с помощью команды BEGIN TRAN, а затем принимаем ее - COMMIT TRAN - или отклоняем (откатываем) - ROLLBACK TRAN.

Перейдем теперь к рассмотрению транзакций в ADO .NET. Создайте новое консольное приложение и назовите его "EasyTransaction". Поставим задачу: передать те же самые данные в две таблицы - "Туристы" и "Информацияотуристах". Привожу полный листинг консольного приложения:

using System;
using System.Data.SqlClient;

namespace EasyTransaction
{
class Class1
{
	[STAThread]
	static void Main(string[] args)
	{
		SqlConnection 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.Transaction = conn.BeginTransaction();
		try
		{
			myCommand.CommandText = "INSERT INTO
			 Туристы (Кодтуриста, Фамилия, Имя, Отчество) VALUES
			 (6, 'Тихомиров', 'Андрей',
			 'Борисович')";
			myCommand.ExecuteNonQuery();
			myCommand.CommandText = "INSERT INTO
			 Информацияотуристах(Кодтуриста, Серияпаспорта,
			 Город, Страна, Телефон, Индекс) VALUES
			 (6, 'CA 1234567', apos;Новосибирск',
			 'Россия', 1234567, 996548)";
			myCommand.ExecuteNonQuery();
			//Подтверждаем транзакцию
			myCommand.Transaction.Commit();
			Console.WriteLine("Передача данных успешно завершена");
		}
		catch(Exception ex)
		{
			//Отклоняем транзакцию
			myCommand.Transaction.Rollback();
			Console.WriteLine("При передаче данных произошла ошибка:
			 "+ ex.Message);
		}
		finally 
		{ 
			conn.Close(); 
		}
	}
}
}

Перед запуском приложения снова удаляем все добавленные записи из таблиц. При успешном выполнении запроса появляется соответствующее сообщение, а в таблицы добавляются записи (рис. 7.10):

Повторный запуск этого приложения приводит к отклонению транзакции - нельзя вставлять записи с одинаковыми значениями первичных ключей (рис. 7.11):

В виде транзакции можно заключать выполнение одной или нескольких хранимых процедур, - в самом деле, общая конструкция имеет следующий вид:

//Создаем соединение
//Создаем транзакцию
myCommand.Transaction = conn.BeginTransaction();
try
	{
	//Выполняем команды, вызываем одну или несколько хранимых процедур
	//Подтверждаем транзакцию
		myCommand.Transaction.Commit();
	}
	catch(Exception ex)
	{
		//Отклоняем транзакцию
		myCommand.Transaction.Rollback();
	}
	finally 
	{ 
		//Закрываем соединение
		conn.Close(); 
	}

При выполнении транзакций несколькими пользователями одной базы данных могут возникать следующие проблемы:

Для решения этих проблем разработаны четыре уровня изоляции транзакции:

По умолчанию устанавливается уровень Read committed. В справке Microsoft SQL Server 2000Она называется SQL Server Books Online. Как вы могли заметить, эту справку можно вызвать из любого приложения, входящего в пакет Microsoft SQL Server 2000. (Указатель - вводим "isolation levels" - заголовок "overview") приводится таблица, иллюстрирующая различные уровни изоляции (рис. 7.12):

Использование наибольшего уровня изоляции ( Serializable ) означает наибольшую безопасность и вместе с тем наименьшую производительность - все транзакции выполняются в виде серии, последующая вынуждена ждать завершения предыдущей. И наоборот, применение наименьшего уровня ( Read uncommitted ) означает максимальную производительность и полное отсутствие безопасности. Впрочем, нельзя дать универсальных рекомендаций по применению этих уровней - в каждой конкретной ситуации решение будет зависеть от структуры базы данных и характера выполняемых запросов.

Для установки уровня изоляции применяется следующая команда:

SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED 
или READ COMMITTED 
 или REPEATABLE READ
 или SERIALIZABLE

Например, в транзакции, добавляющей две записи, уровень изоляции указывается следующим образом:

BEGIN TRAN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DECLARE @OshibkiTabliciTourists int, @OshibkiTabliciInfoTourists int 
...
ROLLBACK TRAN

В ADO .NET уровень изоляции можно установить при создании транзакции:

myCommand.Transaction = conn.BeginTransaction
 (System.Data.IsolationLevel.Serializable);

Дополнительно поддерживаются еще два уровня (см. рис. 7.13):

Транзакции обеспечивают целостность базы данных, при разработке многоуровневых приложений их применение является обязательным правилом.

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

Хранимые процедуры в Microsoft Access

В завершение этой лекции рассмотрим хранимые процедуры в Microsoft Access. Хранимые процедуры? А разве Microsoft Access их поддерживает? По правде говоря, нет. Мы не можем создавать в MSAccess такие процедуры, как мы это делали в MS SQL Server, синтаксис не будет содержать ключевого слова "PROC" или "PROCEDURE", и вообще, это совсем не так называется! Но база данных способна хранить SQL-запросы, и если их запускать из внешнего приложения, то функциональность уже будет напоминать саму концепцию хранимых процедур.

Открываем базу BDTur_firm2.mdb. В окне базы данных переключаемся на вкладку "Запросы" и дважды щелкаем на заголовке "Создание запроса в режиме конструктора" (рис. 7.14):

В появившемся окне добавления таблицы выбираем "Туристы" и нажимаем кнопку "OK" (рис. 7.15).

В режиме конструктора требуется установить столбцы, которые будут извлекаться в запросе. Для этого последовательно выбираем из выпадающего списка названия полей таблицы (рис. 7.16).

Добавим сортировку по столбцу "Фамилия". В поле "Сортировка" из выпадающего списка выбираем значение "по возрастанию" (рис. 7.17).

Можно просмотреть SQL-конструкцию готового запроса. В главном меню выбираем "Вид \ Режим SQL". Окно конструктора изменяет свой вид - в нем появляется текст запроса:

SELECT Туристы.Кодтуриста, Туристы.Фамилия, Туристы.Имя, Туристы.Отчество
FROM Туристы
ORDER BY Туристы.Фамилия;

Сохраняем запрос, называя его "Сортировка_туристы". Дважды щелкнув на нем в окне базы данных, запускаем - записи таблицы отсортированы (рис. 7.18).

Займемся теперь созданием приложения, которое будет запускать этот запрос как хранимую процедуру. Создайте новый Windows-проект и назовите его "Stored_Procedure_MSAccess". Перетаскиваем на форму элемент управления ListBox, его свойству Dock устанавливаем значение "Fill". Подключаем пространство имен для работы с базой данных:

using System.Data.OleDb;

В классе формы определяем строку connectionString:

string connectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0"";
 Data Source=""D:\Uchebnik\Code\Glava3\BDTur_firm2.mdb"
 ";User ID=Admin;Jet OLEDB:Encrypt Database=False";

В конструкторе формы создаем объекты ADO .NET, причем в свойстве CommandType объекта Command задаем тип запроса StoredProcedure:

public Form1()
{
	InitializeComponent();
	OleDbConnection conn = new OleDbConnection();
	conn.ConnectionString = connectionString;
	OleDbCommand myCommand = conn.CreateCommand();
	myCommand.CommandType = CommandType.StoredProcedure;
	myCommand.CommandText = "[Сортировка_туристов]";
	conn.Open();
	OleDbDataReader 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);
		//Выводим данные в элемент llistBox1:
		listBox1.Items.Add("Код туриста: " + TouristID+
		 " Фамилия: " + Family + " Имя: "+
		 FirstName + " Отчество: " + MiddleName);
	}
	conn.Close();
}

Весь код уже достаточно хорошо знаком - мы его применяли для запуска хранимых процедур MS SQL Server. Запускаем приложение - на форму выводится результат запроса (рис. 7.19).

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