Обработка баз данных на Visual Basic®.NET - Джеффри Мак-Манус

Явное указание команд обновления

В отличие от довольно простого способа генерации нужных команд обновления с помощью объекта CommandBuilder, явное указание команд обновления в коде представляет собой хотя и более гибкий, но весьма трудоемкий способ. При его использовании каждая из четырех команд управления данными (Select, Insert, Update и Delete) должна быть создана отдельно. Чаще всего они кодируются в виде хранимой процедуры для каждой команды SQL.

В листинге 6.4 представлен сценарий SQL Server для генерации четырех хранимых процедур. Хранимая процедура SelectEmployees просто выбирает все поля таблицы tblEmployee. Хранимая процедура InsertEmployee принимает четыре параметра, т.е. по одному для каждого обновляемого поля. Хранимая процедура UpdateEmployee принимает те же четыре параметра для обновляемых полей и еще один параметр для исходного значения идентификационного поля, которое используется в предложении WHERE для выбора обновляемой записи (на основании первичного ключа). Хранимая процедура DeleteEmployee принимает исходное значение идентификационного поля для удаления указанной записи.

Листинг 6.4. Сценарий SQL Server для создания хранимых процедур для таблицы tblEmployee

IF EXISTS (SELECT * FROM sysobjects WHERE name =

 'SelectEmployees' AND user__ name(uid) = 'dbo'

 DROP PROCEDURE [dbo].[SelectEmployees]

GO

CREATE PROCEDURE [dbo].[SelectEmployees]

AS

 SET NOCOUNT ON;

 SELECT FirstName, LastName, DepartmentID, Salary, ID FROM

 tblEmployee

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name =

 'InsertEmployee' AND user_name(uid) = 'dbo'

 DROP PROCEDURE [dbo].[InsertEmployee]

GO

CREATE PROCEDURE [dbo].[InsertEmployee] (

 @FirstName varchar(50),

 @LastName varchar(70),

 @DepartmentID int,

 @Salary money

)

AS

 SET NOCOUNT ON;

 SELECT INTO tblEmployee(FirstName, LastName, DepartmentID, Salary)

 VALUES (@FirstName, @LastName, @DepartmentID, @Salary)

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name =

 'UpdateEmployee' and user_name(uid) = 'dbo'

 DROP PROCEDURE [dbo].[UpdateEmployee]

GO

CREATE PROCEDURE [dbo].[UpdateEmployee] (

 @FirstName varchar(50),

 @LastName varchar(70),

 @DepartmentID int,

 @Salary money,

 @Original_ID int

)

AS

 SET NOCOUNT ON;

 UPDATE tblEmployee SET FirstName = @FirstName, LastName = @LastName,

  DepartmentID = @DepartmentID, Salary = @Salary

 WHERE (ID = @Original_ID)

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name

 'DeleteEmployee' and user_name

 DROP PROCEDURE [dbo].[DeleteEmployee]

GO

create procedure [dbo].[DeleteEmployee] (@Original_ID int)

AS

 SET NOCOUNT ON;

 DELETE FROM tblEmployee

 WHERE (ID = @Original_ID)

GO

Вернемся к коду приложения. Во-первых, изменим первую строку кода в подпрограмме btnLoad_Click, т.е. вместо вызова подпрограммы LoadCommandBuilder вставим вызов подпрограммы LoadExplicitCode. Кроме того, для отладки явно заданного кода для команды обновления нужно добавить блок Try-Catch в подпрограмму btnUpdate_Click, которая теперь будет иметь приведенный ниже вид.

Private Sub btnUpdate_Click(ByVal sender As System.Object, _

 ByVal e As System.EventArgs) Handles btnUpdate.Click

 Try

  daEmployees.Update(dsEmployeeInfo, "Employees")

 Catch es As SqlException

  MessageBox.Show(es.Message)

 End Try

End Sub

Наконец, код подпрограммы LoadExplicitCode будет выглядеть так, как показано в листинге 6.5.

Листинг 6.5. Подпрограмма LoadExplicitCode для четырех специализированных команд SQL для адаптера данных daEmployees

Private Sub LoadExplicitCode()

 Dim param As SqlParameter

 If conn.State = ConnectionState.Closed Then

  conn.Open()

 End If

 ' Создание нового объекта DataAdapter.

 daEmployees = New SqlDataAdapter()

 ' Создание специализированной

 ' хранимой процедуры для команды Select.

 daEmployees.SelectCommand = New SqlCommand()

 With daEmployees.SelectCommand

  .Connection = conn

  .CommandType = CommandType.StoredProcedure

  .CommandText = "SelectEmployees"

 End With

 ' Создание специализированной

 ' хранимой процедуры для команды Insert.

 daEmployees.InsertCommand = New SqlCommand()

 With daEmployees.InsertCommand

  .Connection = conn

  .CommandType = CommandType.StoredProcedure.CommandText = "InsertEmployee"

 End With

 param = daEmployees.InsertCommand.Parameters.Add(_

  New SqlParameter(@FirstName", SqlDbType.VarChar, 50))

 param.Direction = ParameterDirection.Input

 param.SourceColumn = "FirstName"

 param.SourceVersion = DataRowVersion.Current

 param = daEmployees.InsertCommand.Parameters.Add( _

 New SqlParameter("@LastName", SqlDbType.VarChar, 70))

 param.Direction = ParameterDirection.Input

 param.SourceColumn = "LastName"

 param.SourceVersion = DataRowVersion.Current

 param = daEmployees.InsertCommand.Parameters.Add(_

  New SqlParameter("@DepartmentID, SqlDbType.Int))

 param.Direction = ParameterDirection.Input

 param.SourceColumn = "DepartmentID"

 param.SourceVersion = DataRowVersion.Current

 param = daEmployees.InsertCommand.Parameters.Add( _

  New SqlParameter("@Salary", SqlDbType.Money))

 param.Direction = ParameterDirection.Input

 param.SourceColumn = "Salary"

 param.SourceVersion = DataRowVersion.Current

 ' Создание специализированной

 ' хранимой процедуры для команды Update.

 daEmployees.UpdateCommand = New SqlCommand()

 With daEmployees.UpdateCommand

  .Connection = conn

  .CommandType = CommandType.StoredProcedure

  .CommandText = "UpdateEmployee"

 End With

 param = daEmployees.UpdateCommand.Parameters.Add( _

  New SqlParameter("@[email protected], SqlDbType.VarChar, 50))

 param.Direction = ParameterDirection.Input

 param.SourceColumn = "FirstName"

 param.SourceVersion = DataRowVersion.Current

 param = daEmployees.UpdateCommand.Parameters.Add( _

  New qlParameter("@LastName", SqlDbType.VarChar, 70))

 param.Direction = ParameterDirection.Input

 param.SourceColumn = "LastName"

 param.SourceVersion = DataRowVersion.Current

 param = daEmployees.UpdateCommand.Parameters.Add( _

  New SqlParameter("@DepartmentID, SqlDbType.Int))

 param.Direction = ParameterDirection.Input

 param.SourceColumn = "DepartmentID"

 param.SourceVersion = DataRowVersion.Current

 param = daEmployees.UpdateCommand.Parameters.Add( _

  New SqlParameter("@Salary, SqlDbType.Money))

 param.Direction = ParameterDirection.Input

 param.SourceColumn = "Salary"

 param.SourceVersion = DataRowVersion.Current

 param = daEmployees.UpdateCommand.Parameters.Add( _

  New SqlParameter("@Original_ID, SqlDbType.Int))

 param.Direction = ParameterDirection.Input

 param.SourceColumn = "ID"

 param.SourceVersion = DataRowVersion.Original

 ' Создание специализированной

 ' хранимой процедуры для команды Delete.

 daEmployees.DeleteCommand = New SqlCommand()

 With daEmployees.DeleteCommand

  .Connection = conn

  .CommandType = CommandType.StoredProcedure

  .CommandText = "DeleteEmployee"

 End With

 param = daEmployees.DeleteCommand.Parameters.Add(_

  New SqlParameter("@Original_ID", SqlDbType.Int))

 param.Direction = ParameterDirection.Input

 param.SourceColumn = "ID"

 param.SourceVersion = DataRowVersion.Original

End Sub

НА ЗАМЕТКУ

Код присвоения значений для каждого из объектов-параметров мог быть более компактным за счет вызова другой перегруженной версии метода Add. Эта альтернативная версия принимает значения для всех необходимых свойств-параметров в одном вызове метода с длинным списком параметров.

Невзирая на большой размер, код подпрограммы LoadExplicitCode имеет очень простую и понятную структуру, если определен интерфейс (параметры и типы) для хранимых процедур. Для всех свойств объекта Command создается новый экземпляр объекта SQLCommand. Ему присваивается общий объект Connection и задаются значения свойств CommandType и CommandText. Затем нужно создать и конфигурировать все параметры каждой команды.

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

Вставка бизнес-логики в команды обновления

Прежде уже описывалось, как хранимые процедуры для специализированных команд обновления можно использовать для вставки бизнес-логики в хранимые процедуры, которые вызываются автоматически. По сравнению с прежними версиями модели ADO и другими моделями доступа к данным новизна заключается не во вставке логики в хранимые процедуры, поскольку эта функциональная возможность существовала и раньше. Дело в том, что эти хранимые процедуры вызываются автоматически при выполнении "пакетных" обновлений вместо явной организации вызовов в коде программы.

Для демонстрации этого подхода попробуем изменить хранимую процедуру. Предположим, что бизнес-логика определяется следующим образом: если при вставке новой записи о сотруднике значение зарплаты в поле Salary не определено или равно 0, то для него автоматически задается значение, определяемое функцией отдела. Для реализации этой бизнес-логики применим очень простой механизм: автоматически присвоенное значение будет равно произведению номера отдела и значения 10000. (Конечно, в реальных условиях компания может использовать более удачный алгоритм для указания этого значения!) Измененная хранимая процедура теперь будет выглядеть так, как показано ниже.