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.

The remaining alternative, in which the check writer's account is not decreased but yours is increased, may be a happy outcome for you ("Bank Error In Your Favor Collect $50"), but the bank would not be pleased.

 

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.

All transactions, even failed ones, affect the database in trivial ways (e.g., resources are expended, performance is affected, or an entry is made in the log). The atomic requirement implies only that if a transaction is rolled back, all tables and data (except the log) will be in the state they would have been in had the transaction not been attempted at all.

 

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.

Data Isolation

Creating fully isolated transactions in a multithreaded environment is a nontrivial exercise. There are three ways isolation can be violated:

     

Lost update

One thread reads a record, a second thread updates the record, and then the first thread overwrites the second thread's update.

Dirty read

Thread one writes data; thread two reads what thread one wrote. Thread one then overwrites the data, thus leaving thread two with old data.

Unrepeatable read

Thread one reads data and the data is then overwritten by thread two. Thread one tries to re-read the data, but it has changed.

Database experts identify four degrees of isolation:

• Degree 0 is limited only to preventing the overwriting of data by any other transaction that is of degree 1 or greater.
• Degree 1 isolation has no lost updates.
• Degree 2 isolation has no lost updates and no dirty reads, but may have unrepeatable reads.
• Degree 3 isolation has no lost updates, no dirty reads, and no unrepeatable reads.

While details about transaction isolation is beyond the scope of this book, Section 20.4, later in this chapter, discusses issues related to avoiding violation of isolation.

 

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)

SQL statements and sprocs are not case sensitive.

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

Table 20-1. Controls for the form

Control type	Name	Value
Label	label1	Adding a new bug
Label	label2	Severity
Label	label3	Owner
Label	label4	Reporter
Label	label5	Product
Label	label6	Version
ComboBox	cbSeverity	DropDownStyle: DropDownList
ComboBox	cbSeverity	DropDownStyle: DropDownList
ComboBox	cbStatus	DropDownStyle: DropDownList
ComboBox	cbOwner	DropDownStyle: DropDownList
ComboBox	cbReporter	DropDownStyle: DropDownList
ComboBox	cbProduct	DropDownStyle: DropDownList
TextBox	txtVersion
TextBox	txtDescription	AcceptReturn: true AcceptTab: true Multiline: true Text: bug description
TextBox	txtResponse	AcceptReturn: true AcceptTab: true Multiline: true Text: txtResponse
RadioButton	rbDBTransaction	Database Transaction
RadioButton	rbConnectionTransaction	Connection Transaction
Button	btnSave	BackColor: Lime Font: Bold Text: Save
Button	btnCancel	BackColor: Red Font: Bold Text: Cancel
DataGrid	dgBugs

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.

Normally, of course, you would simply choose the optimal technique for transactions and not offer this as a choice to the user. You might choose SQL transactions to simplify your code, or you might choose .NET transactions if you want direct programmatic control of the transaction or if your transaction will transcend a single stored procedure.

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