Updating Data with Transactions

An important feature of most industrial-strength databases is support for transactions. A transaction is a set of database operations that must all complete or fail together. That is, either all operations must complete successfully (commit the transaction), or all must be undone (roll back the transaction) to leave the database in the state it was in before the transaction began.

The canonical transaction is depositing a check. If you receive a check for $50 and you deposit it, you and the check writer both expect that once the bank transaction is completed, your account will have increased by $50 and the check writer's will have decreased by $50. Presumably the bank computer accomplishes this transaction in two steps:

1. Reduce the check writer's account by $50.
2. Increase your account by $50.

If the system fails between steps 1 and 2, or for any reason your account cannot be increased by $50, the transaction should be rolled back; that is, it should fail as a whole (neither account should be affected).

If the check writer's account is reduced by $50 and your account is not increased, then the database has become corrupted. This should never be allowed, and it is the job of transactions to ensure that either both actions are taken or neither is.

20.2.1 The ACID Test

Database designers define the requirements of a transaction in the so-called Atomic, Consistent, Isolated, and Durable (ACID) test. Here's a brief summary of what each of these terms means:

Atomic

An atomic interaction is indivisible (i.e., it cannot be partially implemented). Every transaction must be atomic. For instance, in the previous banking example, it must not be possible to decrement the check writer's account but fail to increment yours. If the transaction fails, it must return the database to the state it would have been in without the transaction.

Consistent

The database is presumed to be in a consistent state before the transaction begins, and the transaction must leave it in a consistent state when it completes. While the transaction is being processed, however, the database need not be in a consistent state. To continue with our example of depositing a check, the database need not be consistent during the transaction (e.g., it is okay to decrement the check writer's account before incrementing your account), but it must end in a consistent state (i.e., when the transaction completes, the books must balance).

Isolated

Transactions are not processed one at a time. Typically, a database may process many transactions at once, switching its attention back and forth among various operations. This creates the possibility that a transaction can view and act upon data that reflects intermediate changes from another transaction that is still in progress and that therefore currently has its data in an inconsistent state. Transaction isolation is designed to prevent this problem. For a transaction to be isolated, the effects of the transaction must be exactly as if the transaction were acted on alone; there can be no effects on or dependencies on other database activities. For more information, see the sidebar Sidebar 20-2 in this chapter.

Durable

Once a transaction is committed, the effect on the database is permanent.

20.2.2 Implementing Transactions

There are two ways to implement transactions when building a Windows application. You can allow the database to manage the transaction by using transactions within your stored procedure, or you can use connection-based transactions. In the latter case, the transaction is created and enforced outside the database and allows your transaction to span multiple interactions between the client and the database.

You will remember from Chapter 19 that the Bug database is designed to record each bug event as one record in Bugs and one or more records in BugHistory. In the next example, you will elicit information from the user about a new bug (e.g., the description, severity, etc.), and you will update both the Bug table and the BugHistory table.

If the update to the BugHistory table fails for any reason, make sure that the update to the Bug table rolls back as well. To ensure this, wrap these updates in a transaction.

In this example, you will offer the user the option to have the transaction implemented by either the database or the connection.

If the user selects DB Transaction, you will call a stored procedure that implements the transaction semantics. If the user selects Connection Transaction, you will manage the transaction yourself, using an instance of the System.Data.SqlClient.SqlTransaction class.

The very simple user interface you'll use is shown in Figure 20-2. At the bottom of the form are two radio buttons: Data Base Transaction and Connection Transaction. The user will choose which type of transaction to use to update the database.

Figure 20-2. Adding a new bug with transactions

20.2.2.1 Database transactions

To implement the DB Transaction option, you need a stored procedure (or sproc) that adds a record to the Bugs table and to the BugsHistory table, using SQL transaction support.

To decide which parameters to provide this sproc, examine the two tables you will update, as shown in Figure 20-3.

Figure 20-3. Bugs and bug history

Twelve fields must be filled in for the two tables. For Bugs, the required fields are BugID, Product, Version, Description, and Reporter. However, you don't need to provide a BugID, since it is an identity column provided by the database.

For BugHistory, the obligatory fields are BugHistoryID, BugID, Status, Severity, Response, Owner, and DateStamp. BugID must match the BugID generated by Bugs. So, rather than passing the BugID into the stored procedure, you'll get it back from the database when you add the Bug record

The BugHistoryID numbers are specific to each bug. Bug 1 corresponds to BugHistory records numbered 1 through n, and Bug 2 has its own BugHistory records numbered 1 through x. Thus, the BugHistoryID starts over at 1 for each new bug. Since this is a new Bug, you'll set the BugHistoryID to 1. The DateStamp need not be passed as a parameter, since by default the database gets the current date. You therefore need only pass in eight parameters. See the sidebar Sidebar 20-1 in this chapter.

CREATE PROCEDURE spAddBugWithTransactions @ProductID int, @Version varChar(50), @Description varChar(8000), @Response varChar(8000), @Reporter int, @Owner int, @Status int, @Severity int

The core of the procedure is a pair of insert statements. First, you will insert values into the Bugs table:

Insert into Bugs values (@ProductID, @Version, @Description, @Reporter)

The Bugs table has an identity column, which you can retrieve with the SQL keyword @@identity:

declare @bugID int select @bugID = @@identity

With that bugID in hand, you are ready to insert a record into BugHistory:

Insert into BugHistory (bugHistoryID, bugID, status, severity, response, owner) values ( 1, -- BugHistoryID @bugID, @Status, @Severity, @Response, @Owner )

To make this all work with database transactions, before the Insert statement that adds a record to the first table, you need to begin with the line:

Begin Transaction

After the insert, check the @@error value, which should be 0 if the insert succeeded:

if @@Error <> 0 goto ErrorHandler

If there is an error, jump to the error handler, where you'll call Rollback Transaction:

ErrorHandler: rollback transaction

If there is no error, continue on to the second Insert statement. If there is no error after that insert, you are ready to commit the transaction and exit the sproc:

if @@Error <> 0 goto ErrorHandler commit transaction return

The net effect is that either both insert statements are acted on, or neither is. The complete sproc is shown in Example 20-4.

Example 20-4. Stored procedure spAddBugWithTransactions

CREATE PROCEDURE spAddBugWithTransactions @ProductID int, @Version varChar(50), @Description varChar(8000), @Response varChar(8000), @Reporter int, @Owner int, @Status int, @Severity int AS Begin Transaction declare @bugID int Insert into Bugs values (@ProductID, @Version, @Description, @Reporter) select @bugID = @@identity if @@Error <> 0 goto ErrorHandler Insert into BugHistory (bugHistoryID, bugID, status, severity, response, owner) values ( 1, @bugID, @Status, -- status @Severity, @Response, @Owner ) if @@Error <> 0 goto ErrorHandler commit transaction return ErrorHandler: rollback transaction return

With the stored procedure in hand, you are ready to create the form that allows the user to choose a database transaction or a connection-based transaction. Create a new VB.NET or C# project named AddBugWithTransactions and add the controls shown in Figure 20-4, and described in Table 20-1.

Figure 20-4. Adding the controls to the form

The complete listing in C# is shown in Example 20-5, and the complete VB.NET listing is shown in Example 20-6. Detailed analysis follows.

Example 20-5. DB and connection transactions in C#

using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace AddBugWithTransactionsCS { public class Form1 : System.Windows.Forms.Form { private System.Windows.Forms.RadioButton rbDBTransaction; private System.Windows.Forms.RadioButton rbConnectionTransaction; private System.Windows.Forms.Button btnCancel; private System.Windows.Forms.Button btnSave; private System.Windows.Forms.Label label5; private System.Windows.Forms.Label label4; private System.Windows.Forms.Label label3; private System.Windows.Forms.Label label2; private System.Windows.Forms.Label label1; private System.Windows.Forms.ComboBox cbProduct; private System.Windows.Forms.ComboBox cbOwner; private System.Windows.Forms.ComboBox cbReporter; private System.Windows.Forms.ComboBox cbStatus; private System.Windows.Forms.ComboBox cbSeverity; private System.Windows.Forms.TextBox txtResponse; private System.Windows.Forms.TextBox txtDescription; private System.Windows.Forms.Label label6; private System.Windows.Forms.Label label7; private System.Windows.Forms.TextBox txtVersion; private System.Windows.Forms.DataGrid dgBugs; private System.ComponentModel.Container components = null; public Form1( ) { InitializeComponent( ); PopulateListBoxes( ); FillDataGrid( ); } protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose( ); } } base.Dispose( disposing ); } private void PopulateListBoxes( ) { // creat the dataset DataSet dataSet = new System.Data.DataSet( ); dataSet.CaseSensitive=true; // connect to the database string connectionString = "server=YourServer; uid=sa; pwd=YourPW; " + " database=WindForm_Bugs"; SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); SqlCommand command1 = new System.Data.SqlClient.SqlCommand( ); command1.Connection=connection; // fill the various tables command1.CommandText = "Select * from lkProduct"; SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter( ); dataAdapter.SelectCommand= command1; dataAdapter.TableMappings.Add("Table","Products"); dataAdapter.Fill(dataSet); command1.CommandText = "Select * from lkSeverity"; dataAdapter = new System.Data.SqlClient.SqlDataAdapter( ); dataAdapter.SelectCommand= command1; dataAdapter.TableMappings.Add("Table","Severity"); dataAdapter.Fill(dataSet); command1.CommandText = "Select * from lkStatus"; dataAdapter = new System.Data.SqlClient.SqlDataAdapter( ); dataAdapter.SelectCommand= command1; dataAdapter.TableMappings.Add("Table","Status"); dataAdapter.Fill(dataSet); command1.CommandText = "Select * from People"; dataAdapter = new System.Data.SqlClient.SqlDataAdapter( ); dataAdapter.SelectCommand= command1; dataAdapter.TableMappings.Add("Table","Reporter"); dataAdapter.Fill(dataSet); command1.CommandText = "Select * from People"; dataAdapter = new System.Data.SqlClient.SqlDataAdapter( ); dataAdapter.SelectCommand= command1; dataAdapter.TableMappings.Add("Table","Owner"); dataAdapter.Fill(dataSet); connection.Close( ); // bind the controls to the tables DataTable theTable = dataSet.Tables["Products"]; cbProduct.DataSource = theTable.DefaultView; cbProduct.DisplayMember = "ProductDescription"; cbProduct.ValueMember = "ProductID"; theTable = dataSet.Tables["Severity"]; cbSeverity.DataSource = theTable.DefaultView; cbSeverity.DisplayMember = "SeverityDescription"; cbSeverity.ValueMember = "SeverityID"; theTable = dataSet.Tables["Status"]; cbStatus.DataSource = theTable.DefaultView; cbStatus.DisplayMember = "StatusDescription"; cbStatus.ValueMember = "StatusID"; theTable = dataSet.Tables["Owner"]; cbOwner.DataSource = theTable.DefaultView; cbOwner.DisplayMember = "FullName"; cbOwner.ValueMember = "PersonID"; theTable = dataSet.Tables["Reporter"]; cbReporter.DataSource = theTable.DefaultView; cbReporter.DisplayMember = "FullName"; cbReporter.ValueMember = "PersonID"; rbDBTransaction.Checked = true; // pick a radio button } // close PopulateListBoxes #region Windows Form Designer generated code #endregion ///

/// The main entry point for the application. ///

[STAThread] static void Main( ) { Application.Run(new Form1( )); } private void btnSave_Click(object sender, System.EventArgs e) { if (rbDBTransaction.Checked) { UpdateDBTransaction( ); } else { UpdateConnectionTransaction( ); } MessageBox.Show("Bug added!"); PopulateListBoxes( ); FillDataGrid( ); } private void UpdateConnectionTransaction( ) { string connectionString = "server=YourServer; uid=sa; pwd=YourPW; " + " database=WindForm_Bugs"; // Create connection object, initialize with // connection string. Open it. System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); // declare the command object for the sql statements System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); // connection string to connect to the Bugs Database connection.Open( ); // declare an instance of SqlTransaction SqlTransaction transaction; // begin the transaction transaction = connection.BeginTransaction( ); // attach the transaction to the command command.Transaction = transaction; // attach connection to the command command.Connection = connection; try { command.CommandText = "spAddBug"; command.CommandType = CommandType.StoredProcedure; // declare the parameter object System.Data.SqlClient.SqlParameter param; int productID = Convert.ToInt32(cbProduct.SelectedValue.ToString( )); int reporterID = Convert.ToInt32(cbReporter.SelectedValue.ToString( )); // add each parameter and set its direciton and value param = command.Parameters.Add("@ProductID",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.Value = productID; param = command.Parameters.Add("@Version",SqlDbType.VarChar,50); param.Direction = ParameterDirection.Input; param.Value = txtVersion.Text; param = command.Parameters.Add("@Description", SqlDbType.VarChar,8000); param.Direction = ParameterDirection.Input; param.Value = txtDescription.Text; param = command.Parameters.Add("@Reporter",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.Value = reporterID; param = command.Parameters.Add("@BugID",SqlDbType.Int); param.Direction = ParameterDirection.Output; command.ExecuteNonQuery( ); // execute the sproc // retrieve the identity column int BugID = Convert.ToInt32(command.Parameters["@BugID"].Value); int ownerID = Convert.ToInt32(cbOwner.SelectedValue.ToString( )); int statusID = Convert.ToInt32(cbStatus.SelectedValue.ToString( )); int severityID = Convert.ToInt32(cbSeverity.SelectedValue.ToString( )); // formulate the string to update the bug history string strAddBugHistory = "Insert into BugHistory " + "(bugHistoryID, bugID, status, severity, response, owner)"+ " values (1, " + BugID + ", " + statusID + ", " + severityID + ", '" + txtResponse.Text + "', " + ownerID + ")"; // set up the command object to update the bug history command.CommandType = CommandType.Text; command.CommandText = strAddBugHistory; // execute the insert statement command.ExecuteNonQuery( ); // commit the transaction transaction.Commit( ); } catch (Exception e) { MessageBox.Show("Exception caught! " + e.Message); transaction.Rollback( ); } } //close UpdateConnectionTransaction private void UpdateDBTransaction( ) { // connection string to connect to the Bugs Database string connectionString = "server=YourServer; uid=sa; pwd=YourPW; " + " database=WindForm_Bugs"; // Create connection object, initialize with // connection string. Open it. System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.Connection = connection; command.CommandText= "spAddBugWithTransactions"; command.CommandType = CommandType.StoredProcedure; int productID = Convert.ToInt32(cbProduct.SelectedValue.ToString( )); int reporterID = Convert.ToInt32(cbReporter.SelectedValue.ToString( )); int ownerID = Convert.ToInt32(cbOwner.SelectedValue.ToString( )); int statusID = Convert.ToInt32(cbStatus.SelectedValue.ToString( )); int severityID = Convert.ToInt32(cbSeverity.SelectedValue.ToString( )); // declare the parameter object System.Data.SqlClient.SqlParameter param; // add each parameter and set its direciton and value param = command.Parameters.Add("@ProductID",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.Value = productID; param = command.Parameters.Add("@Version",SqlDbType.VarChar,50); param.Direction = ParameterDirection.Input; param.Value = txtVersion.Text; param = command.Parameters.Add("@Description",SqlDbType.VarChar,8000); param.Direction = ParameterDirection.Input; param.Value = txtDescription.Text; param = command.Parameters.Add("@Response",SqlDbType.VarChar,8000); param.Direction = ParameterDirection.Input; param.Value = txtResponse.Text; param = command.Parameters.Add("@Reporter",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.Value = reporterID; param = command.Parameters.Add("@Owner",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.Value = ownerID; param = command.Parameters.Add("@Status",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.Value = statusID; param = command.Parameters.Add("@Severity",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.Value = severityID; command.ExecuteNonQuery( ); // execute the sproc } // close UpdateDBTransaction private void FillDataGrid( ) { string connectionString = "server=YourServer; uid=sa; pwd=YourPW; " + " database=WindForm_Bugs"; // create the connection, open it, and create the DataSet SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); DataSet dataSet = new System.Data.DataSet( ); dataSet.CaseSensitive=true; //The first command gets the bugs table string bugsCommandString = "Select * from bugs"; SqlCommand bugCommand = new System.Data.SqlClient.SqlCommand( ); bugCommand.Connection=connection; bugCommand.CommandText= bugsCommandString; // the second command gets the history table string historyCommandString = "Select * from bugHistory"; SqlCommand historyCommand = new System.Data.SqlClient.SqlCommand( ); historyCommand.Connection=connection; historyCommand.CommandText= historyCommandString; // create a dataAdapter to get the Bugs table and use it // to populate the dataset SqlDataAdapter bugDataAdapter = new SqlDataAdapter( ); bugDataAdapter.SelectCommand = bugCommand; bugDataAdapter.TableMappings.Add("Table", "Bugs"); bugDataAdapter.Fill(dataSet); // create a dataAdapter to get the history table and use it // to populate the dataset SqlDataAdapter historyDataAdapter = new SqlDataAdapter( ); historyDataAdapter.SelectCommand = historyCommand; historyDataAdapter.TableMappings.Add("Table", "BugHistory"); historyDataAdapter.Fill(dataSet); // create a DataRelation object and two dataColumn objects System.Data.DataColumn dataColumn1; System.Data.DataColumn dataColumn2; // Use the dataColumns to represent the Bugs field in both tables dataColumn1 = dataSet.Tables["Bugs"].Columns["BugID"]; dataColumn2 = dataSet.Tables["BugHistory"].Columns["BugID"]; // Instantiate the DataRelation object with the two columns // name the relationship BugsToHistory DataRelation bugsToHistory = new System.Data.DataRelation( "BugsToHistory",dataColumn1, dataColumn2); // Add the DataRelation object to the Relations table in // the dataset dataSet.Relations.Add(bugsToHistory); // Bind the DataSet to the DataGrid DataViewManager dataView = dataSet.DefaultViewManager; dgBugs.DataSource= dataView; } // close FillDataGrid } // close Form1 }

Example 20-6. DB and connection transactions in VB.NET

Imports System.Data.SqlClient Public Class Form1 Inherits System.Windows.Forms.Form #Region " Windows Form Designer generated code " Public Sub New( ) MyBase.New( ) 'This call is required by the Windows Form Designer. InitializeComponent( ) PopulateListBoxes( ) FillDataGrid( ) End Sub #End Region Private Sub PopulateListBoxes( ) ' creat the dataset Dim myDataSet As New System.Data.DataSet( ) myDataSet.CaseSensitive = True ' connect to the database Dim connectionString As String = _ "server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs" Dim myConnection As New System.Data.SqlClient.SqlConnection(connectionString) myConnection.Open( ) Dim command1 As New System.Data.SqlClient.SqlCommand( ) command1.Connection = myConnection ' fill the various tables command1.CommandText = "Select * from lkProduct" Dim dataAdapter As New System.Data.SqlClient.SqlDataAdapter( ) dataAdapter.SelectCommand = command1 dataAdapter.TableMappings.Add("Table", "Products") dataAdapter.Fill(myDataSet) command1.CommandText = "Select * from lkSeverity" dataAdapter = New System.Data.SqlClient.SqlDataAdapter( ) dataAdapter.SelectCommand = command1 dataAdapter.TableMappings.Add("Table", "Severity") dataAdapter.Fill(myDataSet) command1.CommandText = "Select * from lkStatus" dataAdapter = New System.Data.SqlClient.SqlDataAdapter( ) dataAdapter.SelectCommand = command1 dataAdapter.TableMappings.Add("Table", "Status") dataAdapter.Fill(myDataSet) command1.CommandText = "Select * from People" dataAdapter = New System.Data.SqlClient.SqlDataAdapter( ) dataAdapter.SelectCommand = command1 dataAdapter.TableMappings.Add("Table", "Reporter") dataAdapter.Fill(myDataSet) command1.CommandText = "Select * from People" dataAdapter = New System.Data.SqlClient.SqlDataAdapter( ) dataAdapter.SelectCommand = command1 dataAdapter.TableMappings.Add("Table", "Owner") dataAdapter.Fill(myDataSet) myConnection.Close( ) ' bind the controls to the tables Dim theTable As DataTable = myDataSet.Tables("Products") cbProduct.DataSource = theTable.DefaultView cbProduct.DisplayMember = "ProductDescription" cbProduct.ValueMember = "ProductID" theTable = myDataSet.Tables("Severity") cbSeverity.DataSource = theTable.DefaultView cbSeverity.DisplayMember = "SeverityDescription" cbSeverity.ValueMember = "SeverityID" theTable = myDataSet.Tables("Status") cbStatus.DataSource = theTable.DefaultView cbStatus.DisplayMember = "StatusDescription" cbStatus.ValueMember = "StatusID" theTable = myDataSet.Tables("Owner") cbOwner.DataSource = theTable.DefaultView cbOwner.DisplayMember = "FullName" cbOwner.ValueMember = "PersonID" theTable = myDataSet.Tables("Reporter") cbReporter.DataSource = theTable.DefaultView cbReporter.DisplayMember = "FullName" cbReporter.ValueMember = "PersonID" rbDBTransaction.Checked = True ' pick a radio button End Sub ' close PopulateListBoxes Private Sub btnSave_Click( _ ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles btnSave.Click If rbDBTransaction.Checked = True Then UpdateDBTransaction( ) Else UpdateConnectionTransaction( ) End If MessageBox.Show("Bug added!") PopulateListBoxes( ) FillDataGrid( ) End Sub Private Sub UpdateDBTransaction( ) ' connection string to connect to the Bugs Database Dim connectionString As String = _ "server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs" ' Create connection object, initialize with ' connection string. Open it. Dim myConnection As New System.Data.SqlClient.SqlConnection(connectionString) myConnection.Open( ) Dim myCommand As New System.Data.SqlClient.SqlCommand( ) myCommand.Connection = myConnection myCommand.CommandText = "spAddBugWithTransactions" myCommand.CommandType = CommandType.StoredProcedure Dim productID As Int32 = CInt(cbProduct.SelectedValue) Dim reporterID As Int32 = CInt(cbReporter.SelectedValue) Dim ownerID As Int32 = CInt(cbOwner.SelectedValue) Dim statusID As Int32 = CInt(cbStatus.SelectedValue) Dim severityID As Int32 = CInt(cbSeverity.SelectedValue) ' declare the parameter object Dim param As New System.Data.SqlClient.SqlParameter( ) ' add each parameter and set its direciton and value param = myCommand.Parameters.Add("@ProductID", SqlDbType.Int) param.Direction = ParameterDirection.Input param.Value = productID param = myCommand.Parameters.Add("@Version", SqlDbType.VarChar, 50) param.Direction = ParameterDirection.Input param.Value = txtVersion.Text param = myCommand.Parameters.Add("@Description", _ SqlDbType.VarChar, 8000) param.Direction = ParameterDirection.Input param.Value = txtDescription.Text param = myCommand.Parameters.Add("@Response", SqlDbType.VarChar, _ 8000) param.Direction = ParameterDirection.Input param.Value = txtResponse.Text param = myCommand.Parameters.Add("@Reporter", SqlDbType.Int) param.Direction = ParameterDirection.Input param.Value = reporterID param = myCommand.Parameters.Add("@Owner", SqlDbType.Int) param.Direction = ParameterDirection.Input param.Value = ownerID param = myCommand.Parameters.Add("@Status", SqlDbType.Int) param.Direction = ParameterDirection.Input param.Value = statusID param = myCommand.Parameters.Add("@Severity", SqlDbType.Int) param.Direction = ParameterDirection.Input param.Value = severityID myCommand.ExecuteNonQuery( ) ' execute the sproc MessageBox.Show("Bug added!") PopulateListBoxes( ) FillDataGrid( ) End Sub ' close UpdateDBTransaction Private Sub FillDataGrid( ) Dim connectionString As String connectionString = _ "Server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs" Dim myConnection As New _ System.Data.SqlClient.SqlConnection(connectionString) myConnection.Open( ) Dim myDataSet As New System.Data.DataSet( ) myDataSet.CaseSensitive = True 'The first command gets the bugs table Dim bugCommandString As String bugCommandString = "Select * from bugs" Dim bugCommand As New System.Data.SqlClient.SqlCommand( ) bugCommand.Connection = myConnection bugCommand.CommandText = bugCommandString 'the second command gets the history table Dim historyCommandString As String historyCommandString = "Select * from bugHistory" Dim historyCommand As New System.Data.SqlClient.SqlCommand( ) historyCommand.Connection = myConnection historyCommand.CommandText = historyCommandString ' create the dataAdapter to get the Bugs table and use ' it to populate the dataset Dim bugDataAdapter As New SqlDataAdapter( ) bugDataAdapter.SelectCommand = bugCommand bugDataAdapter.TableMappings.Add("Table", "Bugs") bugDataAdapter.Fill(myDataSet) ' create the dataAdapter to get the history table and ' use it to populate the dataset Dim historyDataAdapter As New SqlDataAdapter( ) historyDataAdapter.SelectCommand = historyCommand historyDataAdapter.TableMappings.Add("Table", "BugHistory") historyDataAdapter.Fill(myDataSet) Dim dataColumn1 As DataColumn Dim dataColumn2 As DataColumn dataColumn1 = myDataSet.Tables("Bugs").Columns("BugID") dataColumn2 = myDataSet.Tables("BugHistory").Columns("BugID") Dim bugHistory As New DataRelation("BugsToHistory", dataColumn1, _ dataColumn2) myDataSet.Relations.Add(bugHistory) Dim dataView As DataViewManager = myDataSet.DefaultViewManager dgBugs.DataSource = dataView End Sub ' close FillDataGrid Private Sub UpdateConnectionTransaction( ) Dim connectionString As String = _ "server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs" ' Create connection object, initialize with ' connection string. Open it. Dim myConnection As New System.Data.SqlClient.SqlConnection(connectionString) ' declare the command object for the sql statements Dim myCommand As New System.Data.SqlClient.SqlCommand( ) ' connection string to connect to the Bugs Database myConnection.Open( ) ' declare an instance of SqlTransaction Dim transaction As SqlTransaction ' begin the transaction transaction = myConnection.BeginTransaction( ) ' attach the transaction to the command myCommand.Transaction = transaction ' attach connection to the command myCommand.Connection = myConnection Try myCommand.CommandText = "spAddBug" myCommand.CommandType = CommandType.StoredProcedure ' declare the parameter object Dim param As System.Data.SqlClient.SqlParameter Dim productID As Int32 = CInt(cbProduct.SelectedValue) Dim reporterID As Int32 = CInt(cbReporter.SelectedValue) Dim ownerID As Int32 = CInt(cbOwner.SelectedValue) Dim statusID As Int32 = CInt(cbStatus.SelectedValue) Dim severityID As Int32 = CInt(cbSeverity.SelectedValue) ' add each parameter and set its direciton and value param = myCommand.Parameters.Add("@ProductID", SqlDbType.Int) param.Direction = ParameterDirection.Input param.Value = productID param = myCommand.Parameters.Add("@Version", SqlDbType.VarChar, 50) param.Direction = ParameterDirection.Input param.Value = txtVersion.Text param = myCommand.Parameters.Add("@Description", SqlDbType.VarChar, 8000) param.Direction = ParameterDirection.Input param.Value = txtDescription.Text param = myCommand.Parameters.Add("@Reporter", SqlDbType.Int) param.Direction = ParameterDirection.Input param.Value = reporterID param = myCommand.Parameters.Add("@BugID", SqlDbType.Int) param.Direction = ParameterDirection.Output myCommand.ExecuteNonQuery( ) ' execute the sproc ' retrieve the identity column Dim BugID As Integer = _ Convert.ToInt32(myCommand.Parameters("@BugID").Value) ' formulate the string to update the bug history Dim strAddBugHistory As String = _ "Insert into BugHistory " & _ "(bugHistoryID, bugID, status, severity, response, owner)" + _ " values (1, " & _ BugID & ", " & statusID & ", " & severityID & ", '" & _ txtResponse.Text & "', " & ownerID & ")" ' set up the command object to update the bug hsitory myCommand.CommandType = CommandType.Text myCommand.CommandText = strAddBugHistory ' execute the insert statement myCommand.ExecuteNonQuery( ) ' commit the transaction transaction.Commit( ) Catch e As Exception MessageBox.Show(("Exception caught! " + e.Message)) transaction.Rollback( ) End Try End Sub ' close UpdateConnectionTransaction End Class

20.2.2.2 Filling in the ListBoxes

The first method you create will be the helper method called PopulateListBoxes that will fill your combo boxes:

private void PopulateListBoxes( ) { private Sub PopulateListBoxes( )

You'll create a DataSet to hold the tables representing the various lookup tables (e.g., Severity, Status, etc.). Begin by creating the DataSet and Connection objects:

// create the dataset DataSet dataSet = new System.Data.DataSet( ); dataSet.CaseSensitive=true; // connect to the database string connectionString = "server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs"; SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( );

' create the dataset Dim myDataSet As New System.Data.DataSet( ) myDataSet.CaseSensitive = True ' connect to the database Dim connectionString As String = _ "server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs" Dim myConnection As New System.Data.SqlClient.SqlConnection(connectionString) myConnection.Open( )

Create a Command object to assign to the DataAdapter. The Text property of the Command object will hold the select statement. You will then fill a table in the DataSet using the DataAdapter:

SqlCommand command1 = new System.Data.SqlClient.SqlCommand( ); command1.Connection=connection; // fill the various tables command1.CommandText = "Select * from lkProduct"; SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapt dataAdapter.SelectCommand= command1; dataAdapter.TableMappings.Add("Table","Products"); dataAdapter.Fill(dataSet);

Dim command1 As New System.Data.SqlClient.SqlCommand( ) command1.Connection = myConnection ' fill the various tables command1.CommandText = "Select * from lkProduct" Dim dataAdapter As New System.Data.SqlClient.SqlDataAdapter( ) dataAdapter.SelectCommand = command1 dataAdapter.TableMappings.Add("Table", "Products") dataAdapter.Fill(myDataSet)

You can now modify the text in the Select statement and reuse it to fill the next table:

command1.CommandText = "Select * from lkSeverity"; dataAdapter = new System.Data.SqlClient.SqlDataAdapter( ); dataAdapter.SelectCommand= command1; dataAdapter.TableMappings.Add("Table","Severity"); dataAdapter.Fill(dataSet);

Do this repeatedly for each lookup table. For convenience, you'll create a table called Reporter and one called Owner, both based on the lookup table People:

command1.CommandText = "Select * from People"; dataAdapter = new System.Data.SqlClient.SqlDataAdapter( ); dataAdapter.SelectCommand= command1; dataAdapter.TableMappings.Add("Table","Reporter"); dataAdapter.Fill(dataSet); command1.CommandText = "Select * from People"; dataAdapter = new System.Data.SqlClient.SqlDataAdapter( ); dataAdapter.SelectCommand= command1; dataAdapter.TableMappings.Add("Table","Owner"); dataAdapter.Fill(dataSet);

Once all the tables are created, you can close the connection:

connection.Close( );

You are now ready to bind the tables to the controls. Start by creating a reference to the table you want to bind to the control:

DataTable theTable = dataSet.Tables["Products"];

Dim theTable As DataTable = myDataSet.Tables("Products")

Set the DataSource property of the control to the DefaultView property of the table.

cbProduct.DataSource = theTable.DefaultView;

This binds the control to the table, but you must tell the control which field holds the value to display.

cbProduct.DisplayMember = "ProductDescription";

The listbox will now display the description of the product, but when it is time to update the database, you don't want the description you want the product ID. You can tell the listbox to hold the associated ProductID in its ValueMember property.

cbProduct.ValueMember = "ProductID";

You can do the same for each of the other ListBoxes. For example, you can set the severity ListBox with this code:

theTable = dataSet.Tables["Severity"]; cbSeverity.DataSource = theTable.DefaultView; cbSeverity.DisplayMember = "SeverityDescription"; cbSeverity.ValueMember = "SeverityID";

theTable = myDataSet.Tables("Severity") cbSeverity.DataSource = theTable.DefaultView cbSeverity.DisplayMember = "SeverityDescription" cbSeverity.ValueMember = "SeverityID"

Once all the listboxes are set, set the first of the two radio buttons to be the default selection.

rbDBTransaction.Checked = true;

With that in place, you can test the application. The various listboxes should be populated, although the application will not do anything as of yet.

The job of the button handler is to determine which of the two radio buttons is selected and invoke the appropriate method. If the user chooses a database transaction, invoke the private UpdateDBTransaction helper method, which in turn invokes the spAddBugWithTransactions stored procedure (described earlier).

Double-click on the Save button and add this C# code to the event handler:

private void btnSave_Click(object sender, System.EventArgs e) { if (rbDBTransaction.Checked) { UpdateDBTransaction( ); } else { UpdateConnectionTransaction( ); } }

Private Sub btnSave_Click( _

ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles btnSave.Click If rbDBTransaction.Checked = True Then UpdateDBTransaction( ) Else UpdateConnectionTransaction( ) End If End Sub

20.2.3 DataBase Transactions

In the UpdateDBTransaction method, you create a connection and a Command object in the normal way, setting the Command object's CommandType property to CommandType.StoredProcedure:

string connectionString = "server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs"; System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.Connection = connection; command.CommandText= "spAddBugWithTransactions"; command.CommandType = CommandType.StoredProcedure;

Dim connectionString As String = _ "server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs" Dim myConnection As New _ System.Data.SqlClient.SqlConnection(connectionString) myConnection.Open( ) Dim myCommand As New System.Data.SqlClient.SqlCommand( ) myCommand.Connection = myConnection myCommand.CommandText = "spAddBugWithTransactions" myCommand.CommandType = CommandType.StoredProcedure

Before creating the parameters, you'll create interim integer variables to hold the choices in the ListBoxes. You must extract each selected value as a string and then convert it to an integer:

int productID = Convert.ToInt32(cbProduct.SelectedValue.ToString( )); int reporterID = Convert.ToInt32(cbReporter.SelectedValue.ToString( )); int ownerID = Convert.ToInt32(cbOwner.SelectedValue.ToString( )); int statusID = Convert.ToInt32(cbStatus.SelectedValue.ToString( )); int severityID = Convert.ToInt32(cbSeverity.SelectedValue.ToString( ));

Dim productID As Int32 = CInt(cbProduct.SelectedValue) Dim reporterID As Int32 = CInt(cbReporter.SelectedValue) Dim ownerID As Int32 = CInt(cbOwner.SelectedValue) Dim statusID As Int32 = CInt(cbStatus.SelectedValue) Dim severityID As Int32 = CInt(cbSeverity.SelectedValue)

Add parameters to the Command object's parameters collection by calling the Add method.

command.Parameters.Add("@ProductID",SqlDbType.Int);

What you get back is a reference to the new parameter. To hold that reference, you'll create a local variable:

System.Data.SqlClient.SqlParameter param;

Dim param As System.Data.SqlClient.SqlParameter( )

Set the Direction property on the parameter to input (rather than output) and set its value to the value for the parameter:

param.Direction = ParameterDirection.Input; param.Value = productID;

You can then go on and add each of the other parameters in turn. For example, to add the @Version parameter, you will write:

param = command.Parameters.Add("@Version",SqlDbType.VarChar,50); param.Direction = ParameterDirection.Input; param.Value = txtVersion.Text;

Invoke the stored procedure by calling the ExecuteNonQuery method:

command.ExecuteNonQuery( );

Once you update the database, return to btnSave_Click, where you display a message in this case using a MessageBox object (supplied by the .NET Framework), resetting the ListBoxes to their default settings, and updating the DataGrid:

MessageBox.Show("Bug added!"); PopulateListBoxes( ); FillDataGrid( );

The call to FillDataGrid queries the database again and binds the DataGrid to the new returned tables. This is similar to the code shown in the previous chapter. In fact, there is nothing new or surprising in any of this code so far; all the work that supports the transaction is actually done in the stored procedure itself.

20.2.4 Connection Transaction

The user may choose to use a Connection Transaction rather than a DB Transaction.

With a Connection transaction, there is no transaction support provided by the stored procedure. Instead, add the transaction support by creating an SQLTransaction object.

In Example 20-5 and Example 20-6, the user had the option of choosing either a database connection or a connection transaction. If the connection transaction radio button is chosen, the btnSave_Click event handler will invoke UpdateConnectionTransaction:

private void btnSave_Click(object sender, System.EventArgs e) { if (rbDBTransaction.Checked) { UpdateDBTransaction( ); } else { UpdateConnectionTransaction( ); }

To learn how Connection Transactions work, you'll update the Bug and BugHistory tables in two steps. In Step 1, you'll call a stored procedure spAddBug, shown in Example 20-7.

Example 20-7. SpAddBug stored procedure

CREATE PROCEDURE spAddBug @ProductID int, @Version varChar(50), @Description varChar(8000), @Reporter int, @BugID int output AS Insert into Bugs values (@ProductID, @Version, @Description, @Reporter) select @BugID = @@identity

This stored procedure takes five parameters and updates the Bugs table. The final parameter, BugID, is marked output. You do not pass a BugID into the procedure (BugID is an identity column), but you should retrieve this value. The output parameter returns the BugID after the bug is added.

Once the bug is added, you'll update the BugHistory table with a SQL statement that you'll create based on the values in various UI components.

string strAddBugHistory = "Insert into BugHistory " + "(bugHistoryID, bugID, status, severity, response, owner) values (1, " + BugID + ", " + statusID + ", " + severityID + ", '" + txtResponse.Text + "', " + ownerID + ")";

Execute the SQL statement by calling ExecuteNonQuery.

command.CommandType = CommandType.Text; command.CommandText = strAddBugHistory; command.ExecuteNonQuery( );

Either event could fail. That is, the stored procedure might generate an error, or the query might fail to properly add a record to the database. If either fails, you do not want any part of the effort to succeed. In short, you need to wrap both steps within a transaction. By using a transaction, if either step fails, the entire transaction is rolled back and the database is left in the exact state it was before you began the transaction.

The job of the UpdateConnectionTransaction method, shown in Example 20-5 and Example 20-6, invokes both the stored procedure and the SQL update statement by using a Connection transaction. The steps are as follows:

1. Create the connection string and the SqlConnection object:

   string connectionString = "server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs"; System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);

   Dim connectionString As String = _ "server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs" Dim myConnection As New _ System.Data.SqlClient.SqlConnection(connectionString)

2. Create the SqlCommand object:

   System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( );

   Dim myCommand As New System.Data.SqlClient.SqlCommand( )

3. Open the connection:

   connection.Open( );

   myConnection.Open( )

4. Instantiate a SqlTransaction object by calling the BeginTransaction method of the SqlConnection object:

   SqlTransaction transaction; transaction = connection.BeginTransaction( );

   Dim transaction As SqlTransaction transaction = myConnection.BeginTransaction( )

5. Set the SqlCommand object's Transaction property to the SqlTransaction object you've instantiated, and set the SqlCommand object's Connection property to the SqlConnection object you've created.

   command.Transaction = transaction; command.Connection = connection;

   myCommand.Transaction = transaction myCommand.Connection = myConnection

6. Open a try block, in which you will try to update the two tables. Set the SQL Command object's CommandText property to the name of the stored procedure, and set the CommandType property to CommandType.StoredProcedure:

   try { command.CommandText = "spAddBug"; command.CommandType = CommandType.StoredProcedure;

   Try myCommand.CommandText = "spAddBug" myCommand.CommandType = CommandType.StoredProcedure

7. Add all the parameters, including the output parameters:

   System.Data.SqlClient.SqlParameter param; int productID = Convert.ToInt32(cbProduct.SelectedValue.ToString( )); int reporterID = Convert.ToInt32(cbReporter.SelectedValue.ToString( )); // add each parameter and set its direciton and value param = command.Parameters.Add("@ProductID",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.Value = productID; param = command.Parameters.Add("@Version",SqlDbType.VarChar,50); param.Direction = ParameterDirection.Input; param.Value = txtVersion.Text; param = command.Parameters.Add("@Description",SqlDbType.VarChar,8000); param.Direction = ParameterDirection.Input; param.Value = txtDescription.Text; param = command.Parameters.Add("@Reporter",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.Value = reporterID; param = command.Parameters.Add("@BugID",SqlDbType.Int); param.Direction = ParameterDirection.Output;

   Dim param As System.Data.SqlClient.SqlParameter Dim productID As Int32 = CInt(cbProduct.SelectedValue) Dim reporterID As Int32 = CInt(cbReporter.SelectedValue) Dim ownerID As Int32 = CInt(cbOwner.SelectedValue) Dim statusID As Int32 = CInt(cbStatus.SelectedValue) Dim severityID As Int32 = CInt(cbSeverity.SelectedValue) ' add each parameter and set its direciton and value param = myCommand.Parameters.Add("@ProductID", SqlDbType.Int) param.Direction = ParameterDirection.Input param.Value = productID param = myCommand.Parameters.Add("@Version", SqlDbType.VarChar, 50) param.Direction = ParameterDirection.Input param.Value = txtVersion.Text param = myCommand.Parameters.Add("@Description", SqlDbType.VarChar, 8000) param.Direction = ParameterDirection.Input param.Value = txtDescription.Text param = myCommand.Parameters.Add("@Reporter", SqlDbType.Int) param.Direction = ParameterDirection.Input param.Value = reporterID param = myCommand.Parameters.Add("@BugID", SqlDbType.Int) param.Direction = ParameterDirection.Output

8. Invoke the query:

   command.ExecuteNonQuery( );

   myCommand.ExecuteNonQuery( )

9. Get back the BugID:

   int BugID = Convert.ToInt32(command.Parameters["@BugID"].Value);

   Dim BugID As Integer = _ Convert.ToInt32(myCommand.Parameters("@BugID").Value)

10. Create a SQL statement from the controls:

    int ownerID = Convert.ToInt32(cbOwner.SelectedValue.ToString( )); int statusID = Convert.ToInt32(cbStatus.SelectedValue.ToString( )); int severityID = Convert.ToInt32(cbSeverity.SelectedValue.ToString( )); string strAddBugHistory = "Insert into BugHistory " + "(bugHistoryID, bugID, status, severity, response, owner) values (1, " + BugID + ", " + statusID + ", " + severityID + ", '" + txtResponse.Text + "', " + ownerID + ")";

    Dim ownerID As Int32 = CInt(cbOwner.SelectedValue) Dim statusID As Int32 = CInt(cbStatus.SelectedValue) Dim severityID As Int32 = CInt(cbSeverity.SelectedValue) Dim strAddBugHistory As String = _ "Insert into BugHistory " & _ "(bugHistoryID, bugID, status, severity, response, owner) values (1, " & _ BugID & ", " & statusID & ", " & severityID & ", '" & _ txtResponse.Text & "', " & ownerID & ")"

11. Execute the query:

    command.CommandType = CommandType.Text; command.CommandText = strAddBugHistory; command.ExecuteNonQuery( );

    myCommand.CommandType = CommandType.Text myCommand.CommandText = strAddBugHistory ' execute the insert statement myCommand.ExecuteNonQuery( )

12. Commit the transaction:

    transaction.Commit( );

    transaction.Commit( )

13. If an exception is thrown, catch the exception and roll back the transaction:

    catch (Exception e) { transaction.Rollback( ); MessageBox.Show("Exception caught! " + e.Message); }

    Catch e As Exception

    MessageBox.Show(("Exception caught! " + e.Message)) transaction.Rollback( ) End Try