Обработка баз данных на Visual Basic®.NET

Разработчики приложений для работы с базами данных на основе Visual Basic уже привыкли к тому, что Microsoft каждые несколько лет предлагает новую усовершенствованную модель доступа к данным. Кроме новой трехбуквенной аббревиатуры, технология ADO.NET также предлагает новую модель API-интерфейсов и объектов. В течение последних нескольких лет разработчики уже успели познакомиться с предшественниками ADO.NET — технологиями ODBC, DAO, RDO и ADO. При знакомстве с каждой новой технологией им требовалось тщательно изучить ее назначение и принципы работы. При этом они часто задавали себе один и тот же вопрос: имеет ли смысл переходить на новую технологию? В большинстве случаев ответ был положительным, если только новшества не оказывали никакого влияния на текущие и будущие требования проекта. Действительно, чаще всего переход на новые технологии был вполне обоснован, за исключением технологии RDO (Remote Data Objects) для проектов с процессором баз данных Jet (потому что технология ОАО по-прежнему является более удачной технологией работы с Jet).

Мотивация и философия

ADO.NET И ADO 2.X

При работе с моделью ADO.NET нужно учитывать перечисленные ниже отличия от классической модели ADO.

• В классической модели ADO предусмотрен присоединенный к источнику данных способ доступа и использование физической модели данных.

• В модели ADO.NET проводиться четкое разделение между присоединенным способом доступа к данным и отсоединенной моделью программирования.

• В модели ADO.NET нет свойств CursorType, CursorLocation или LockType, потому что в ADO.NET предусмотрены только статические курсоры, клиентские курсоры и оптимистическая блокировка.

• Вместо использования простого многоцелевого объекта recordset в ADO.NET разные функции распределены среди объектов меньшего размера — DataReader, DataSet и DataTable.

• В ADO.NET разрешается полноценное манипулирование данными в формате XML, а не только использование его в качестве формата ввода-вывода данных.

В ADO.NET предусмотрена поддержка строго типизированных наборов данных DataSet, а не использование для всех полей типа Variant. Это позволяет эффективнее обнаруживать ошибки времени выполнения и повышает производительность работы приложений.

Место ADO.NET в архитектуре .NET Framework

На рис. 4.1 показано место классов ADO.NET в архитектуре.NET Framework. В основе этой платформы лежит общеязыковая исполняющая среда (Common Language Runtime — CLR), которая образует единую среду выполнения для всех .NET-совместимых приложений, независимо от используемого языка программирования. Среда CLR включает общую систему типов, управление памятью и жизненным циклом объектов.

На следующем логическом уровне над средой CLR располагаются базовые системные классы. Именно эти классы отвечают за выполнение базовых функций, которые могут использоваться в .NET-приложениях. На рис. 4.1 показаны только некоторые классы библиотеки классов .NET Framework, которая, по сути, является новым набором API-интерфейсов Windows. В прошлом доступ к функциям операционной системы Windows осуществлялся только через API-интерфейсы, которые состояли из большого набора разрозненных и плохо продуманных функций. На платформе.NET Framework такой доступ организован на основе свойств и методов, которые предлагаются базовыми системными классами. Это объектно-ориентированный, последовательный и комфортабельный способ создания приложений Windows, независимо от типа клиентского приложения: традиционного настольного приложения, броузера или Web-службы.

РИС. 4.1. Классы ADO.NET в структуре платформы .NET Framework

Этот уровень включает несколько пространств имен (групп классов и других определений), предназначенных для организации доступа к данным: System.Data, System.OleDb и System.Data.SqlClient. В оставшейся части данной главы, а также в главах 5, 6 и 7 эти классы и пространства имен рассматриваются более подробно.

Прикладные интерфейсы

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

Теперь после первого знакомства с расположением классов ADO.NET в общей структуре платформы.NET Framework рассмотрим подробнее основные объекты ADO.NET.

Несмотря на подчеркнутое значение отсоединенной модели программирования, для извлечения, обновления, вставки и удаления данных все же придется подключиться к физической базе данных. Программное обеспечение ADO.NET для подсоединения и взаимодействия с физической базой данных называется провайдером данных ADO.NET. Провайдер данных (data provider) — это управляемый код .NET, который эквивалентен провайдеру OLEDB или драйверу ODBC. Провайдер данных состоит из нескольких объектов, которые реализуют необходимую функциональность в соответствии с определениями своих классов и интерфейсов.

В настоящее время существует три разных провайдера данных ADO.NET, каждый из которых определен в своем собственном пространстве имен. Для всех объектов в этих пространствах имен используются следующие префиксы: OleDb, Sql и Odbc. Однако при упоминании этих объектов в рамках своего пространства имен имя объекта можно указывать без употребления префикса данного пространства имен.

Провайдер данных SqICIient

Оптимизирован для работы с SQL Server версии 7.0 (или выше) и позволяет добиться более высокой производительности по следующим причинам:

• взаимодействует с базой данных непосредственно через собственный протокол табличной передачи данных (Tabular Data Stream — TDS), а не через OLEDB с отображением интерфейса OLEDB на протокол TDS;

• исключает накладные расходы, связанные с использованием COM-служб взаимодействия;

• отсутствуют ненужные функции, которые не поддерживаются в SQL Server (объекты этого провайдера данных находятся в пространстве имен System.Data.SqlClient).

Провайдер данных Oledb

Основан на существующем COM-поставщике OLEDB и COM-службах взаимодействия платформы .NET Framework, предназначенных для доступа к базе данных. Этот провайдер данных используется для работы с SQL Server более ранних версий, чем 7.0. Он позволяет осуществлять доступ к любой базе данных, для которой имеется поставщик OLEDB. Объекты этого провайдера данных находятся в пространстве имен System.Data.Oledb.

Провайдер данных Odbc

Используется для доступа к базам данных, которые не имеют собственного провайдера данных .NET или COM-поставщика OLEDB. Иногда драйвер ODBC демонстрирует более высокую производительность, чем драйвер OLEDB, поэтому для сравнения их фактической производительности при работе с конкретной базой данных рекомендуется провести ряд тестов. Объекты этого провайдера данных находятся в пространстве имен System. Data.Odbc.

Основные объекты

Каждый провайдер данных имеет четыре основных объекта, которые указаны в табл. 4.1.

Таблица 4.1. Основные объекты провайдера данных

Каждый объект основан на базовом родовом классе и реализует родовой интерфейс, но имеет собственную реализацию. Например, объекты SqlDataAdapter, OleDBDataAdapter и OdbcDataAdapter являются производными от класса DbDataAdapter и реализуют те же интерфейсы. Однако каждый из них реализует их своим собственным способом для соответствующего источника данных.

Пространство имен System. Data. OleDb содержит объекты:

• OleDbConnection;

• OleDbCommand;

• OleDbDataReader;

• OleDbDataAdapter.

Пространство имен System.Data.SqlClient содержит объекты:

• SqlConnection;

• SqlCommand;

• SqlDataReader;

• SqlDataAdapter.

Пространство имен Microsoft.Data.Odbc содержит объекты:

• OdbcConnection;

• OdbcCommand;

• OdbcDataReader;

• OdbcDataAdapter.

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

Аналогично объекту Connection, объект Command модели ADO.NET очень похож на своего предшественника из прежней модели ADO 2.X. Объект Command позволяет выполнять команды по отношению к источнику данных и получать возвращенные данные или результаты выполнения команд.

РИС. 4.3. Состояние приложения DataProviderObjects до и после открытия подключения с помощью кода из листинга 4.1

Этот объект имеет следующие свойства: CommandText и СommandType для определения текста и типа фактической команды; Connection для указания подключения, используемого для выполнения команды; СommandTimeout для указания времени ожидания, по истечении которого команда отменяется и выдается сообщение об ошибке; Parameters для коллекции параметров команды; Transaction для указания транзакции, в которой используется данная команда.

Все три версии объекта Command (в пространствах имен OleDb, Sql, Odbc) имеют идентичные свойства и методы, за исключением того, что объект SqlCommand имеет дополнительный метод, которого нет у двух других вариантов этого объекта, а именно ExecuteXmlReader. Он использует преимущества SQL Server для автоматического возвращения данных в формате XML (если в запрос SQL добавлено предложение FOR XML).

Продолжим работу с формой, показанной на рис. 4.3.

1. Добавим еще одну кнопку сразу под кнопкой btnConnection, перетаскивая пиктограмму кнопки из панели элементов управления.

2. В окне свойств Properties установите значение btnCommand для свойства Name и значение Command для свойства Text.

3. Добавьте для кнопки код, показанный в листинге 4.2.

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

 ByVal e As System.EventArgs) Handles btnCommand.Click

 ' Создание экземпляра объекта Connection

 Dim cnn As SqlConnection = New SqlConnection( _

  "server=localhost;uid=sa;database=pubs")

 ' Создание экземпляра объекта Command

 Dim cmd As SqlCommand = New SqlCommand() txtResults.Clear()

 ' Открытие подключения cnn.Open()

 ' Указание подключения и текста команды

 cmd.Connection = cnn

 cmd.CommandType = CommandType.Text

 cmd.CommandText = _

  "Select au_lname, state from authors"

 ' Вывод текста команды

 txtResults.Text = "Command String:" & ControlChars.CrLf

 txtResults.Text = txtResults.Text & ControlChars.Tab & cmd.CommandText() & ControlChars.CrLf

End Sub

После запуска на выполнение приложения DataProviderObjects щелкните на кнопке Command, и в текстовом поле будет показана команда SQL, которая находится в свойстве CommandText объекта SqlCommand, а именно: SELECT au_lname, state FROM authors.

' Создание экземпляра объекта Connection 

Dim cnn As SqlConnection = New SqlConnection()

' Создание строки подключения

cnn.ConnectionString = "server=localhost;uid=sa;database=pubs"

' Создание экземпляра объекта Connection

Dim cnn SqlConnection = New SqlConnection("server=localhost;uid-sa;database=pubs")

Применение объекта Command с параметрами и хранимыми процедурами

При создании запросов или команд для источника данных часто требуется передавать значения параметров действия (обновление, вставка или удаление данных) или хранимой процедуры. Для решения этой проблемы в объекте Command предусмотрено свойство Parameters, которое является объектом-коллекцией ParameterCollection и содержит коллекцию объектов-параметров Parameter. Это аналогично способу работы, применимому в модели ADO 2.X.

Объекты Parameter и ParameterCollection тесно связаны с соответствующим провайдером данных, поэтому они должны быть реализованы как составная часть провайдера данных ADO.NET. Способы программирования объекта SqlParameterCollection и использование объектов OdbcParameterCollection и OledbParameterCollection имеют существенные отличия. Объекты OdbcParameterCollection и OledbParameterCollection основаны на позиционных параметрах, а объект SqlParameterCollection – на именованных параметрах. Эти различия в значительной степени влияют на способ определения запросов и параметров.

Начнем с создания простого запроса с параметрами для извлечения из базы данных pubs имен и фамилий всех авторов из заданного штата.

С одной стороны, при использовании провайдеров данных OLEDB или ODBC запрос будет иметь следующий вид:

SELECT state, au_fname, au_lname from authors WHERE state = ?

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

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

SELECT state, au_fname, au_lname from authors WHERE state = @MyParam

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

Объект Parameter можно создать явно, используя конструктор Parameter (т.е. с использованием оператора New) или передавая нужные аргументы методу Add объекта-коллекции ParameterCollection (свойство Parameters объекта Command). Помните, что оба метода (конструктор Parameter и метод Add) имеют перегруженные версии.

Ниже приведен один из способов включения параметра команды за счет явного указания объекта-параметра.

Dim rayParameter As New OdbcParameter("@MyParam", OdbcType.Char, 2)

myParameter.Direction = ParameterDirection.Input

myParameter.Value = "CA"

cmd.Parameters.Add(myParameter)

А способ включения параметра команды с помощью метода Add выглядит иначе.

cmd.Parameters.Add("@MyParam", OdbcType.Char, 2)

cmd.Parameters("@MyParam").Direction = ParameterDirection.Input

cmd.Parameters("@MyParam").Value = "CA"

Второй метод короче и обычно предпочтительнее, если только нет особой причины для повторного использования объекта Parameter.

Для метода Add объекта Parameter обычно требуется указать имя, тип и длину параметра. Затем нужно указать направление передачи данных: Input, Output, InputOutput или ReturnValue. По умолчанию используется направление Input. Наконец, для присвоения значения параметру нужно использовать свойство Value объекта Parameter. Кроме того, для параметра можно указать другие свойства, например масштаб (свойство Scale), точность (свойство Precision) и допустимость использования неопределенных значений (свойство IsNullable).

При использовании провайдера данных SqlClient можно применять практически идентичный код. Единственным отличием являются префиксы Odbc вместо префиксов Sql, а также тип перечисления SqlDbType вместо OdbcType.

Dim myParameter As New SqlParameter("@MyParam", SqlDbType.Char, 2)

myParameter.Direction = ParameterDirection.Input

myParameter.Value = "CA"

cmd.Parameters.Add(myParameter)

Аналогично выглядит способ включения параметра команды с помощью метода Add.

cmd.Parameters.Add("@MyParam", SqlDbType.Char, 2)

cmd.Parameters("@MyParam").Direction = ParameterDirection.Input

cmd.Parameters("@MyParam").Value = "CA"

cmd.Parameters("@MyParam").Value = DBNull.Value

Измените код кнопки cmdButton, как показано в листинге 4.3. После запуска программы и щелчка на кнопке cmdButton в текстовом поле появится текст запроса, а также имя и значение параметра.

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

 ByVal e As System.EventArgs) Handles btnCommand.Click

 ' Создание экземпляра объекта Connection

 Dim cnn As SqlConnection = New SqlConnection( _

  "server=localhost;uid=sa;database=pubs")

 ' Создание экземпляра объекта Command и объектов Parameter

 Dim cmd As SqlCommand = New SqlCommand()

 Dim prm As SqlParameter = New SqlParameter()

 txtResults.Clear()

 ' Открытие подключения cnn.Open()

 ' Указание подключения и текста команды

 cmd.Connection = cnn

 cmd.CommandType = CommandType.Text

 cmd.CommandText = _

  "Select au_lname, state from authors where state = @MyParam"

 ' Создание параметра и указание его значения

 cmd.Parameters.Add(New SqlParameter("@MyParam", SqlDbType.Char, 2))

 cmd.Parameters("@MyParam").Value = "CA"

 ' Вывод текста команды

 txtResults.Text = "Command String:" & ControlChars.CrLf

 txtResults.Text = txtResults.Text & ControlChars.Tab & _

  cmd.CommandText() & ControlChars.CrLf

 ' Вывод параметров команды

 txtResults.Text = txtResults.Text & "Command parameters:" & _

  ControlChars.CrLf

 For Each prm In cmd. Parameters

  txtResults.Text = txtResults.Text & ControlChars.Tab & _

  prm.ParameterName & " = " & prm.Value & ControlChars.CrLf

 Next

End Sub

Аналогично вызываются хранимые процедуры, за исключением того, что вместо свойства CommandType.Text используется свойство CommandType.StoredProcedure, а имя хранимой процедуры присваивается свойству CommandText. Таким образом, код вызова хранимой процедуры GetAuthorsFromState с двухсимвольным параметром для извлечения информации обо всех авторах заданного штата будет выглядеть, как показано ниже.

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "GetAuthorsFromState"

cmd.Parameters.Add("@MyParam", SqlDbType.Char, 2)

cmd.Parameters("@MyParam").Direction = ParameterDirection.Input

cmd.Parameters("@MyParam").Value = "CA"

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "{GetAuthorsFromState ?}"

cmd.Parameters.Add("@MyParam", OdbcType.Char, 2)

cmd.Parameters("@MyParam").Direction = ParameterDirection.Input

cmd.Parameters("@MyParam").Value = "CA"

cmd.CommandText = "{? = GetAuthorsFromState ?}"

Чтобы хранимая процедура возвращала значение, нужно указать направление Output, а затем считать значение свойства параметра после вызова хранимой процедуры. В данном примере также определяется возвращаемое значение хранимой процедуры. Поскольку здесь указан тип Int сервера SQL Server, то для этого параметра не нужно указывать длину, так как по определению она составляет 4 байт.

cmd.Parameters.Add(New SqlParameter("result", SqlDbType.Int)

cmd. Parameters ("result").Direction = ParameterDirection.ReturnValue

cmd.Parameters.Add(New SqlParameter("@MyParam", SqlDbType.Int)

cmd.Parameters("@MyParam").Direction = ParameterDirection.Output

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

Msg (cmd.Parameters("@MyParam").Value)

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

Выполнение команд

CREATE TABLE tblStateZipCodes (

 ZipCode char (5) NOT NULL,

 State char (2) NOT NULL

)

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

 ByVal e As System.EventArgs) Handles btnNonQuery.Click

 ' Создание экземпляра объекта Connection.

 Dim cnn As OleDbConnection = New OleDbConnection( _

  "provider=SQLOLEDB;server=localhost;uid=sa;database=pubs")

 Dim sql As String Dim result As Integer

 ' Создание экземпляра объекта Command.

 Dim cmd As OleDbCommand = New OleDbCommand()

 ' Указание подключения и текста команды

 cmd.Connection = cnn

 cmd.CommandType = CommandType.Text

 ' Указание команды SQL для создания новой таблицы

 sql = "CREATE TABLE tblStateZipCodes (" & _

 "ZipCode char (5) NOT NULL, " & _

  "State char (2) NOT NULL) "

 MsgBox(sql) cmd.CommandText = sql

 ' Открытие подключения перед вызовом метода ExecuteNonQuery.

 cnn.Open()

 ' Для обработки исключительных ситуаций нужно поместить 

 ' код в блоке Try-Catch, потому что неудачное выполнение

 ' команды ТАКЖЕ генерирует ошибку времени выполнения.

 Try

  result = cmd.ExecuteNonQuery()

 Catch ex As Exception

  ' Отображение сообщения об ошибке.

 MessageBox.Show(ex.Message)

 End Try

 ' Вывод результатов выполнения команды.

 If result = -1 Then

  MessageBox.Show("Command completed successfully")

  ' MessageBox.Show("Команда выполнена успешно")

 Else

  MessageBox.Show("Command execution failed")

 ' MessageBox.Show("Команда не выполнена")

 End If

 cnn.Close()

End Sub

sql = "CREATE VIEW EmployeeJobs_view AS" & _

 "SELECT TOP 100 PERCENT jobs. job_desc," & _

 "employee.fname, employee.lname" &_

 "FROM jobs INNER JOIN" & _

 "employee ON jobs. job_id = employee. job_id &_

 "ORDER BY jobs.job_desc"

sql = "CREATE PROCEDURE AuthorsInState1 @State char(2)" & _ 

 " AS declare @result int" & _

 " select @result = count (*) from authors " & _

 " where state = @State" &_

 " return (@result)"

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

 ByVal e As System.EventArgs) Handles btnUpdate.Click

 Dim result As Integer

'  Создание экземпляра объекта Connection.

 Dim cnn As SqlConnection = New SqlConnection(_

  "server=localhost;uid=sa;database=pubs")

 ' Создание экземпляра объекта Command.

 Dim cmd As SqlCommand = New SqlCommand()

 txtResults.Clear()

 ' Указание подключения и текста команды.

 cmd.Connection = cnn

 cmd.CommandType = CommandType.Text

 cmd.CommandText = "UPDATE roysched SET royalty = royalty + @param1"

 ' Создание параметра и указание его значения.

 cmd.Parameters.Add(New SqlParameter("@param1", SqlDbType.Int))

 cmd.Parameters("@param1").Direction = ParameterDirection.Input

 cmd.Parameters("@param1").Value = Val(txtParam1.Text)

 ' Открытие подключения перед вызовом метода ExecuteReader().

 cnn.Open()

 result = cmd.ExecuteNonQuery()

 MessageBox.Show(result & " records updated", "DataProviderObjects")

 cnn.Close()

End Sub

CREATE PROCEDURE UpdateRoyalties

@param1 int

AS

UPDATE roysched SET royalty = royalty + @param1

cmd.CommandType = CommandType.StoredProcedure 

cmd.CommandText = "UpdateRoyalties"

CREATE PROCEDURE AuthorsInState2

@param1 char(2)

AS

SELECT count(*) FROM authors WHERE state = @param1

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

 ByVal e As System.EventArgs) Handles btnExecuteScalar.Click

 Dim result As Integer

 ' Создание экземпляра объекта Connection.

 Dim cnn As OdbcConnection = New OdbcConnection( _

 "DRIVER={SQL Server};server=localhost;uid=sa;database=pubs")

 ' Создание экземпляра объекта Command.

 Dim cmd As OdbcCommand = New OdbcCommand()

 txtResults.Clear()

 ' Указание подключения и текста команды.

 cmd.Connection = cnn

 cmd.CommandType = CommandType.StoredProcedure

 cmd.CommandText = "{call AuthorsInState2(?) }"

 ' Создание параметра и указание его значения

 cmd.Parameters.Add("@param1", OdbcType.Char, 2)

 cmd.Parameters("@param1").Value = txtParam1.Text

 ' Открытие подключения перед вызовом метода

 ExecuteReader. cnn.Open()

 result = cmd.ExecuteScalar()

 MessageBox.Show("Count is " & result, "DataProviderObjects")

 cnn.Close()

End Sub

Выше были представлены классы нескольких основных провайдеров данных (например, Parameter и Parameters), а также четыре основных объекта в табл. 4.1. В главе 5, "ADO.NET: объект DataSet", более подробно рассматривается объект DataSet и связанные с ним объекты, а в главе 6, "ADO.NET: объект DataAdapter", – объект DataAdapter.

В оставшейся части главы рассматривается еще один провайдер данных — объект-транзакция Transaction. Транзакции используются для гарантированного успешного завершения сразу нескольких связанных операций по принципу "все или ничего". Это значит, что либо все операции транзакции успешно выполняются, либо они вообще не выполняются. Классическим примером транзакции является банковская операция перечисления денежных средств. Эта операция состоит из двух этапов: удержание денежной суммы с одного счета и зачисление ее на другой счет. При этом желательно избегать ситуаций, когда успешно выполняется только первый этап транзакции!

РИС. 4.5. Результаты отображения данных в форме Form2 с помощью элементов управления OleDbConnection и OleDbCommand

Провайдеры данных ADO.NET содержат объект Transaction, который имеет фундаментальные методы обработки транзакций. Метод Commit фиксирует текущую транзакцию, а метод Rollback – откатывает (отменяет) текущую транзакцию. Выполнение транзакции и создание объекта Transaction осуществляется с помощью вызова метода BeginTransaction по отношению к открытому объекту Connection. Способ использования объекта Transaction демонстрируется на примере бизнес-ситуации 4.1.

Бизнес-ситуация 4.1: создание процедуры для архивирования старых заказов по годам

После относительно длительного использования системы управления базами данных некоторые данные рекомендуется архивировать. В каждой рабочей системе операцию архивирования следует включить в состав обязательных и регулярно выполняемых операций резервного копирования. Архивируемые данные — это данные, которые нужны не для постоянного использования (т.е. в оперативном режиме), а только изредка. Удаление этих данных из основных оперативных таблиц базы данных может повысить производительность операций доступа к этим таблицам, так как при этом приходится обрабатывать и фильтровать меньше записей. Однако архивная таблица часто хранится в идентичном формате таблицы и доступ к ней в случае необходимости можно организовать аналогичным образом. В этой бизнес-ситуации создается простая форма для выполнения простого архивирования данных из таблицы tblOrder базы данных Novelty. Она позволит выбирать и архивировать заказы по годам, т.е. после выбора нужного года выполняются перечисленные ниже действия.

1. Сначала в базе данных создается новая таблица tblOrderXXXX, где ХХХХ обозначает тот год, записи о заказах которого будут архивироваться.

2. Затем все записи о заказах за указанный год копируются из таблицы tblOrder втаблицу tblOrderXXXX.

3. Все скопированные записи о заказах за указанный год удаляются из таблицы tblOrder.

Хитрость здесь заключается в том, чтобы отменить всю транзакцию при неудачном выполнении какой-либо ее операции. Нам не нужна новая таблица, если в нее нельзя скопировать данные. Не нужно архивировать данные, если они не удаляются из основной таблицы. Аналогично, не нужно удалять никакие записи из основной таблицы, если они не скопированы в архивную таблицу. Для решения этой задачи можно использовать объект Transaction и вернуть (откатить) базу данных в исходное состояние в случае сбоя каких-то операций. Для создания формы с этими функциями выполните перечисленные ниже действия.

Запустите интегрированную среду разработки Visual Studio .NET.

2. Создайте новый проект Visual Basic Windows Application.

3. Назовите проект BusinessCase4.

4. Укажите путь к файлам проекта.

5. Увеличьте размер формы Form1.

6. В окне Properties укажите значение frmArchive для свойства (Name) и значение Archive Orders для свойства Text формы Form1.

7. Создайте в форме поле со списком lstYears, надпись Label1, кнопку bntOK и кнопку btnCancel, перетаскивая их из панели элементов управления.

8. В окне Properties укажите значение Archive all orders for the year для свойства Text надписи, значение OK для кнопки btnOK и значение Cancel для кнопки btnCancel.

9. Расположите все элементы управления, как показано на рис. 4.6.

РИС. 4.6. Расположение элементов управления в форме frmArchive

В верхней части файла с исходным кодом вставьте приведенную ниже строку кода для импорта пространства имен SqlClient.

Imports System.Data.SqlClient

В теле определения класса для формы frmArchive включите код из листинга 4.10.

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

 ByVal e As System.EventArgs) Handles MyBase.Load

 lstYears.Items.Add("1995")

 lstYears.Items.Add("1996")

 lstYears.Items.Add("1997")

 lstYears.Items.Add("1998")

 lstYears.Items.Add("1999")

 lstYears.Items.Add("2000")

 lstYears.Items.Add("2001")

 lstYears.Items.Add("2002")

 ' Указание значения по умолчанию.

 lstYears.SelectedIndex = 0

End Sub

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

 ByVal e As System.EventArgs) Handles btnCancel.Click

 Me.Close()

End Sub

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

 ByVal e As System.EventArgs) Handles btnOK.Click

 Dim sql As String

 Dim result As Integer

 Dim records As Integer

 Dim SelectedYear As String

 ' Создание экземпляров объектов Connection и Command.

 Dim cnn As SqlConnection = New SqlConnection( _

  "server=localhost;uid=sa;database=novelty")

 Dim cmd As New SqlCommand()

 Dim trans As SqlTransaction

 ' Получение значения года.

 SelectedYear = lstYears.SelectedItem.ToString

 ' Размещение кода внутри блока Try-Catch для

 ' обработки исключительных ситуаций.

 Try

  ' Открытие объекта Connection и запуск транзакции.

 cnn.Open()

  trans = cnn.BeginTransaction

  ' Включение команды в транзакцию. 

  cmd.Connection = cnn

  cmd.Transaction = trans

  ' Указание команды SQL для вставки соответствующих

 ' записей в архивную таблицу.

 sql = "SELECT * INTO tblOrder" & SelectedYear & _

  FROM tblOrder WHERE year (OrderDate) = " & SelectedYear

 ' Передача текста команды SQL в транзакцию.

  cmd.CommandText = sql

  result = cmd.ExecuteNonQuery()

  ' Отображение результатов вставки записей в архивную таблицу. 

  If result > 0 Then

  records = result MessageBox.Show(records & _

   " records inserted successfully into tblOrder" & SelectedYear)

 Else

  MessageBox.Show( _

   "No records inserted into tblOrder" & SelectedYear)

  ' При отсутствии записей созданная таблица

  ' не нужна и транзакцию нужно откатить.

  trans.Rollback()

  End If

  If records > 0 Then

  ' Команда SQL для удаления соответствующих

  ' записей из текущей таблицы.

  sql = "delete FROM tblOrder WHERE year (OrderDate) = " _

   & SelectedYear

  ' Эта команда находится в той же транзакции.

  cmd.CommandText = sql

  result = cmd.ExecuteNonQuery()

  ' Показать результаты удаления записей.

  If result = records Then

   MessageBox.Show(records & _

   " records deleted successfully")

   ' Все действия успешно выполнены, можно фиксировать транзакцию.

   trans.Commit()

  Else

   MessageBox.Show("No records deleted!")

  End If

  Else

  ' Никаких действий. 

 End If

 Catch ex As Exception

  ' Какие-то действия не выполнены, поэтому нужно

  ' отменить (откатить) всю транзакцию.

  Try

  ' Отображение сообщения об ошибке.

  MessageBox.Show(ex.Message & _

   ControlChars.CrLf & ControlChars.CrLf & _

   "Transaction Failed!")

  trans.Rollback()

  Catch ex2 As Exception

  End Try

 Finally

  cnn.Close()

 End Try

End Sub

Подпрограмма frmArchive_Load инициализирует список lstYears значениями, из которых можно выбрать год архивирования, и выбирает используемый по умолчанию год. Конечно, эту подпрограмму можно было бы усовершенствовать так, чтобы в списке отображались только те годы, для которых существуют записи о заказах. Однако для демонстрации принципов работы транзакций достаточно и такой подпрограммы.

Подпрограмма btnCancel_Click обработки щелчков мышью на кнопке Cancel просто закрывает форму, что в данном случае приводит к закрытию программы. Все необходимые действия выполняются обработчиком щелчков мышью на кнопке OK. После объявлений переменных следует получить выбранный год из списка lstYears и сохранить его для дальнейшего использования. Для гарантированной отмены транзакции в случае возникновения любой исключительной ситуации следует окружить активный код блоком Try-Catch-Finally.

Поскольку транзакции определяются на уровне подключения, то сначала нужно открыть подключение, а затем создать объект Transaction с помощью вызова метода BeginTransaction для открытого подключения. Объекты Connection и Transaction присваиваются объекту Command, который будет использоваться для выполнения команд по отношению к базе данных.

Первые два этапа создания архивной таблицы и копирования выбранных строк выполняются с помощью одной команды SELECT, которая содержит предложение INTO имя_таблицы. Указанная таким образом таблица создается автоматически, а если такая таблица уже существует, то генерируется исключительная ситуация. Выбранное значение года добавляется к имени таблицы tblOrder для создания имени новой архивной таблицы.

Для выполнения команды SQL вызывается метод ExecuteNonQuery, который возвращает количество охваченных запросом записей. Если это возвращаемое значение больше нуля, следовательно, нужные записи найдены и вставлены в новую таблицу. В противном случае это значит, что таблица не может быть создана либо для копирования не найдено никаких записей. В любом из этих двух случаев транзакция откатывается, даже если таблица создана, чтобы база данных не наполнялась пустыми и бесполезными таблицами.

Если хотя бы одна запись скопирована в таблицу, то ее прототип в исходной таблице tblOrder удаляется с помощью команды DELETE, содержащей предложение WHERE с заданным годом. В случае успешного выполнения этой команды, т.e. если количество скопированных и удаленных строк совпадает, транзакция считается успешно завершенной и фиксируется. В противном случае, т.е. если какая-то отдельная операция завершится неудачно (нарушится процесс удаления записей, будет отменено разрешение на удаление архивируемых данных или произойдет сбой сервера), вся транзакция будет отвергнута. Откат транзакции гарантирует, что при неудачной попытке удаления корректных записей из таблицы tblOrder архивная таблица tblOrderХХХХ будет удалена.

До сих пор рассматривались только те исключительные ситуации, которые возникают при нарушении последовательного выполнения операций подпрограммы. Однако следует также позаботиться о многих других исключительных ситуациях, которые могут возникнуть во время выполнения. Например, исключительная ситуация может возникнуть при попытке создания уже существующей таблицы. Для ее перехвата и обработки следует использовать блок Try-Catch, который в данном случае выводит текстовое сообщение об исключительной ситуации, а вся транзакция отвергается.

В блоке Finally используемое подключение закрывается независимо от того, произошла или нет исключительная ситуация.

Выполните все описанные выше действия и поэкспериментируйте с этим приложением, создавая архивы для записей разных лет. Для проверки полученных результатов сопоставьте содержимое новой архивной и исходной таблиц до и после архивирования. Не забывайте, что содержимое исходной таблицы tblOrder всегда можно вернуть в первоначальное состояние, запуская сценарий ее создания и наполнения данными.