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

Бизнес-ситуация 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 всегда можно вернуть в первоначальное состояние, запуская сценарий ее создания и наполнения данными.

Оглавление книги