Передача изменений в базу данных при помощи хранимых процедур. Объект CommandBuilder

Внимание! Для работы с лекциями 13, 14 необходимы учебные файлы, которые Вы можете загрузить здесь.

Передача изменений в базу данных при помощи хранимых процедур. Объект CommandBuilder

Передача изменений в базу данных при помощи хранимых процедур MS SQL Server

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

CREATE PROCEDURE myInsertSP

	(
		@Кодтуриста int,
		@Фамилия NVarChar (50),
		@Имя NVarChar (50),
		@Отчество NVarChar (50)
	)

AS
	INSERT INTO Туристы
           	(Кодтуриста, Фамилия, Имя, Отчество)
VALUES   	(@Кодтуриста, @Фамилия, @Имя, @Отчество)

	RETURN

Сохраняем процедуру - ее заголовок изменился на ALTER PROCEDURE. Это означает, что она записалась в базу данных, и текущее окно можно закрыть. Создаем процедуру типа UPDATE:

CREATE PROCEDURE myUpdateSP
(
		@Кодтуриста int,
		@Фамилия NVarChar (50),
		@Имя NVarChar (50),
		@Отчество NVarChar (50)
	)
AS
UPDATE Туристы SET Кодтуриста = @Кодтуриста, Фамилия = @Фамилия,
 Имя = @Имя, Отчество = @Отчество WHERE (Кодтуриста = @Кодтуриста)
	RETURN

И, наконец, создаем процедуру типа DELETE:

CREATE PROCEDURE myDeleteSP
(
		@Кодтуриста int,
		@Фамилия NVarChar (50),
		@Имя NVarChar (50),
		@Отчество NVarChar (50)
)
AS
DELETE FROM Туристы
WHERE   (Кодтуриста = @Кодтуриста) OR
           (Фамилия = @Фамилия) OR
           (Имя = @Имя) OR
           (Отчество = @Отчество)

	RETURN

Содержимое SQL-запросов взято из приложения VisualAllSql Commands. Теперь у нас есть хранимые процедуры, которые мы можем вызывать. Создайте новое Windows-приложение и назовите его "Visual UpdateWithSP". Снова переходим на вкладку "Server Explorer", перетаскиваем созданные процедуры на форму. На панели компонент появляется подключение sqlConnection1 и объекты Command, в свойстве Name которых мы вводим значения "myInsertCommand", "myUpdateCommand" и "myDeleteCommand". Для извлечения записей из базы данных необходим еще один объект Command. В окне Toolbox переходим на вкладку Data, перетаскиваем на форму объект SqlCommand. В окне Properties в свойстве Name вводим для него значение "mySelectCommand", а в свойстве Connection выбираем имеющееся соединение "sqlConnection1". В поле свойства CommandText нажимаем на кнопку(...), в запустившемся построителе выражений настраиваем выборку всех записей из таблицы "Туристы". В результате всех проделанных действий окна Properties четырех объектов Command принимают следующий вид (рис. 13.1):

увеличить изображение
Рис. 13.1. Свойства объектов Command

В окне Toolbox переходим на вкладку Data, перетаскиваем на форму объект SqlDataAdapter. В появившемся мастере Data Adapter Configuration Wizard нажимаем кнопку "Cancel". В окне Properties объекта DataAdapter устанавливаем значения свойств "DeleteCommand", "InsertCommand", "SelectCommand" и "UpdateCommand", выбирая из выпадающего списка названия соответствующих объектов (см. рис. 12.37). Нажимаем на ссылку "Generate Dataset". В появившемся окне вводим название объекта "dsTourists". Добавляем на форму элемент DataGrid, его свойству Dock устанавливаем значение "Fill". В конструкторе формы заполняем объект DataSet и устанавливаем источник данных для элемента DataGrid:

public Form1()
{
	InitializeComponent();
	sqlDataAdapter1.Fill(dsTourists1);
	dataGrid1.DataSource = dsTourists1.Tables[0].DefaultView;
}

В обработчике события Closing формы вызываем метод Update объекта DataAdapter:

private void Form1_Closing(object sender,
 System.ComponentModel.CancelEventArgs e)
{
	try
	{
		if (dsTourists1.HasChanges())
		{
			sqlDataAdapter1.Update(dsTourists1);			
		}
	}
	catch(Exception ex)
	{
		MessageBox.Show(ex.ToString());
	}
}

Запускаем приложение. При попытке ввести новую запись и сохранить изменения (закрыть форму) появляется сообщение об ошибке (рис. 13.2):

Рис. 13.2. Приложение VisualUpdateWithSP. Сообщение об ошибке

Дело в том, что среда сама не определяет источник данных для параметров хранимых процедур. Переключаемся в режим дизайна, выделяем объект myInsertCommand, в окне Properties нажимаем на кнопку(...) в поле свойства Parameters. В редакторе SqlParameter Collection Editor для параметров "@Кодтуриста", "@Имя", "@Фамилия", "@Отчество" в свойстве SourceColumn определяем одноименные названия полей (рис. 13.3):

увеличить изображение
Рис. 13.3. Определение свойства SourceColumn

Свойство SourceColumn этих же самых параметров необходимо определить для объектов myUpdateCommand и myDeleteCommand.

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

В программном обеспечении к курсу вы найдете приложение Visual UpdateWithSP (Code\Glava6\ VisualUpdateWithSP).

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

using System.Data.SqlClient;

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

string connectionString = "integrated security=SSPI;data
 source=\".\"; persist security info=False;
 initial catalog=BDTur_firm2";
DataSet dsTourists; 
SqlDataAdapter dataAdapter;

В конструкторе формы создаем все объекты ADO .NET:

public Form1()
{
	InitializeComponent();
	SqlConnection conn = new SqlConnection();
	conn.ConnectionString = connectionString;
	SqlCommand mySelectCommand = conn.CreateCommand();
	mySelectCommand.CommandText =
	 "SELECT * FROM Туристы";
	dataAdapter = new SqlDataAdapter();
	dataAdapter.SelectCommand = mySelectCommand;
	dsTourists = new DataSet();
	dataAdapter.Fill(dsTourists);
	dataGrid1.DataSource = dsTourists.Tables[0].DefaultView;
	//InsertCommand
	SqlCommand myInsertCommand = conn.CreateCommand();
	myInsertCommand.CommandType =
	 CommandType.StoredProcedure;
	myInsertCommand.CommandText = "[myInsertSP]";
	myInsertCommand.Parameters.Add("@Кодтуриста",
	 SqlDbType.Int, 4, "Кодтуриста");
	myInsertCommand.Parameters.Add("@Фамилия",
	 SqlDbType.NVarChar, 50, "Фамилия");
	myInsertCommand.Parameters.Add("@Имя",
	 SqlDbType.NVarChar, 50, "Имя");
	myInsertCommand.Parameters.Add("@Отчество",
	 SqlDbType.NVarChar, 50, "Отчество");
	dataAdapter.InsertCommand = myInsertCommand;
	//UpdateCommand
	SqlCommand myUpdateCommand = conn.CreateCommand();
	myUpdateCommand.CommandType =
	 CommandType.StoredProcedure;
	myUpdateCommand.CommandText = "[myUpdateSP]";
	myUpdateCommand.Parameters.Add("@Кодтуриста",
	 SqlDbType.Int, 4, "Кодтуриста");
	myUpdateCommand.Parameters.Add("@Фамилия",
	 SqlDbType.NVarChar, 50, "Фамилия");
	myUpdateCommand.Parameters.Add("@Имя",
	 SqlDbType.NVarChar, 50, "Имя");
	myUpdateCommand.Parameters.Add("@Отчество",
	 SqlDbType.NVarChar, 50, "Отчество");
	dataAdapter.UpdateCommand = myUpdateCommand;
	//DeleteCommand
	SqlCommand myDeleteCommand = conn.CreateCommand();
	myDeleteCommand.CommandType =
	 CommandType.StoredProcedure;
	myDeleteCommand.CommandText = "[myDeleteSP]";
	myDeleteCommand.Parameters.Add("@Кодтуриста",
	 SqlDbType.Int, 4, "Кодтуриста");
	myDeleteCommand.Parameters.Add("@Фамилия",
	 SqlDbType.NVarChar, 50, "Фамилия");
	myDeleteCommand.Parameters.Add("@Имя",
	 SqlDbType.NVarChar, 50, "Имя");
	myDeleteCommand.Parameters.Add("@Отчество",
	 SqlDbType.NVarChar, 50, "Отчество");
	dataAdapter.DeleteCommand = myDeleteCommand;
}

В обработчике события Closing формы вызываем метод Update объекта DataAdapter:

private void Form1_Closing(object sender,
 System.ComponentModel.CancelEventArgs e)
{
	try
	{
		if (dsTourists.HasChanges())
		{
			dataAdapter.Update(dsTourists);			
		}
	}
	catch(Exception ex)
	{
		MessageBox.Show(ex.ToString());
	}
}

В программном обеспечении к курсу вы найдете приложение Programm UpdateWithSP (Code\Glava6\ProgrammUpdateWithSP).

В "Создание хранимых процедур" при помощи мастера SQL Server Enterprise Manager мы создавали хранимые процедуры "insert_Туристы_1", "update_Туристы_1" и "delete_Туристы_1". Попробуйте самостоятельно разработать приложение, которое будет использовать эти процедуры для вставки, изменения и удаления записей.