Other Data Provider Objects

We have already presented several data provider classes, such as the Parameter and Parameters objects, in addition to the four core objects listed in Table 4.1. In Chapter 6 we explore the DataAdapter in depth, after introducing DataSet and its associated objects in Chapter 5.

Before ending this chapter, let's look at one last data provider object: the Transaction object. Transactions are used to ensure that multistep operations are completed in an "all or nothing" manner. That is, either all the steps of the overall operation complete successfully or none of them do. The classic example of a transaction is a bank transfer. The operation consists of two steps: subtracting an amount from one account and adding that amount to a different account. We certainly want to avoid the situation where only the first of these two steps is completed successfully!

ADO.NET Data Providers implement a Transaction object, which contains the fundamental methods required for using transactions. The Commit method completes the current transaction, and the Rollback method cancels the current transaction. The transaction is begun and the Transaction object is created by calling the BeginTransaction method on an open Connection object. We show the Transaction object in action in Business Case 4.1.

Business Case 4.1: Writing a Routine to Archive Old Orders By Year

Once a database system has been in use for a long time (the definition of "long" is relative), certain data can and should be archived. Such archiving should be in addition to the mandatory regularly scheduled backups for any production system. Archived data is data that you don't need to have constantly available (online) but that you may need to access on occasion. By removing this data from the main online tables, you can improve the performance of accessing those tables because there are fewer records to search or filter. However, as an archived table is often stored in the identical table format, it can be accessed in a uniform manner, if and when required. In this business case we guide you through development of a form to accomplish a simple archive of data from the tblOrder table in the Novelty database. The form will allow you to select the year of the orders that you would like to archive. After you select the desired year, the following steps must occur.

1. A new table, tblOrderXXXX, is created in the Novelty database, where "XXXX" will be replaced with the year of the orders in the archive table.

2. All the relevant records are copied from the tblOrder table to the tblOrderXXXX table.

3. All the copied records are deleted from tblOrder.

The tricky part here is that you want to ensure that, if any of the those steps fail, the entire operation will be canceled. You don't want to have a new table if you can't put data into it. You don't want records in the archive if you can't delete them from the main table. And you certainly don't want to delete the records from tblOrder if you can't copy them to the archive table. You can make use of the Transaction object and have the database roll back to its previous state in case there are any failures. Go ahead and build a form to do all this. Doing so will also give you a chance to review and practice much of what we presented in this chapter.

1. Launch Visual Studio.NET.

2. Create a new Visual Basic Windows Application project.

3. Name the project BusinessCase4.

4. Specify a path for saving the project files.

5. Enlarge the size of Form1.

6. In the Properties window for Form1, set its Name property to frmArchive and its Text property to Archive Orders.

7. Add a listbox named lstYears, a label named Label1, a button named btnOK, and a button named btnCancel.

8. Set the label's Text property to Archive all orders for the year. Set the Text properties of the buttons to OK and Cancel, respectively.

9. Arrange the controls as shown in Figure 4.6.

   Figure 4.6. Arrangement of the controls on frmArchive

   

At the top of the file insert the first line of code, to import the SqlClient namespace:

 Imports System.Data.SqlClient  

Within the body of the class definition for frmArchive add the code shown in Listing 4.10.

Listing 4.10 Code to archive data to a new table

 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")     'Set Default   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   'Create an instance of an Connection and command objects   Dim cnn As SqlConnection = New SqlConnection ( _     "server=localhost;uid=sa;database=novelty")   Dim cmd As New SqlCommand()   Dim trans As SqlTransaction   'First get year   SelectedYear = lstYears.SelectedItem.ToString   'Put the code inside a Try-Catch block to trap failures   Try     'Open the Connection and begin transaction     cnn.Open()     trans = cnn.BeginTransaction     'Enlist the command in this transaction     cmd.Connection = cnn     cmd.Transaction = trans     'SQL to insert appropriate records into archive table     sql = "SELECT * INTO tblOrder" & SelectedYear & _       "FROM tblOrder WHERE year (OrderDate) =" & SelectedYear     'This command is in the transaction.     cmd.CommandText = sql     result = cmd.ExecuteNonQuery()     'Show results of inserting records into archive     If result > 0 Then       records = result       MessageBox.Show(records & _         "records inserted successfully into tblOrder" _         & SelectedYear)     Else       MessageBox.Show( _         "No records inserted into tblOrder" _         & SelectedYear)       'Since no records, don't keep created       'table cancel / rollback transaction       trans.Rollback()     End If     If records > 0 Then       'SQL to delete appropriate records from current       'table       sql = "delete FROM tblOrder WHERE year(OrderDate) =" _         & SelectedYear       'This command is also in the same transaction       cmd.CommandText = sql       result = cmd.ExecuteNonQuery()       'Show results of deleting records       If result = records Then         MessageBox.Show(records & _           "records deleted successfully")         'If we got to here, then everything         'succeeded         trans.Commit()       Else         MessageBox.Show( _           "Wrong number of records deleted!")           trans.Rollback()       End If     Else         'nothing to do       End If       Catch ex As Exception         'If we got to here, then something failed and         'we cancel (rollback) the entire transaction.         Try           'Display error message.           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 

The routine frmArchive_Load initializes lstYears with the years to choose from and selects the first (earliest) year by default. You could, of course, improve this routine so that it queries tblOrder to retrieve a list of years that have orders in that table. For now, the simpler method will suffice.

The Click event handler for the Cancel button simply closes the form, which in this case will also end the program. All the action takes place in the btnOK Click event handler. After the variable declarations, you should obtain the selected year from lstYears and save it for later. To ensure that you cancel the transaction if any error (exception) occurs, you should wrap all the active code inside a Try-Catch-Finally block.

Because transactions are defined at the connection level, first open the connection and then create the Transaction object by calling BeginTransaction on the open connection. This Connection object and Transaction object are then assigned to the Command object that will be used to execute the database commands.

The first two steps of creating the archive table and copying the specified rows into the new table are performed in a single SQL statement by using the SELECT INTO statement. This is a regular SELECT statement, with the insertion of an Into tablename clause. The table specified in this additional clause is automatically created; the command generates an exception if the table already exists. The year that you select is appended to tblOrder to create the name of the new archive table that is to be created.

Note

The SELECT INTO statement doesn't create any of the indexes that exist in the original table. You would probably want to create indexes on one or more of the fields to improve the performance of queries against that table.

The ExecuteNonQuery method is called to execute the SQL statement. This method returns the number of rows affected. If this number is greater than zero, you know that all went well in creating and populating the new table, and the routine can continue. Otherwise, either the table could not be created or there are no rows to copy. In either case, the transaction is rolled back so that, even if the table was created successfully, the database won't be cluttered with empty, useless tables.

So long as at least one record was added to the archive table, the process continues. The next step is to delete the appropriate records from the original tblOrder. You do so with a simple DELETE statement, with the selected year appended to the WHERE clause. If this method succeeds that is, the number of affected records equals the number of records inserted into the archive table all is well and the transaction is committed. Otherwise, something went wrong (such as changes to relevant records, permission denied, or server down), and the entire transaction is rolled back. This rollback ensures that, if you failed to delete the correct records from tblOrder, the archive table, tblOrderXXXX, will be deleted.

Up to this point, the routine handled those situations that occur as part of the sequential execution of the routine. However, run-time exceptions must also be handled. For example, if you try to create a table that already exists in the database, an exception is generated. Such exceptions are caught and handled in the Catch block. The text of the exception is displayed and the entire transaction is canceled and rolled back.

Note

The second, nested Try-Catch block is needed to handle the case when the archive table cannot be created (for example, because it already exists). The reason is that, although the transaction was begun, no data modification statements were executed and therefore nothing was written into the log that can be rolled back.

In the Finally block, the connection that was used is closed. That needs to be done whether or not an error occurred.

Go ahead and experiment with this project, by creating archives for different years. You can verify its operation by looking at the new archive table, as well as the original tblOrder (both before and after the archiving). Don't forget that you can always reset the contents of tblOrder by running the scripts to create and/or populate tblOrder.