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

figs/pnwa_2002.gif

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

figs/pnwa_2003.gif

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

figs/pnwa_2004.gif

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#

figs/csharpicon.gif

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

figs/vbicon.gif

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:

figs/csharpicon.gif

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:

figs/csharpicon.gif

 // 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( );

figs/vbicon.gif

' 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:

figs/csharpicon.gif

 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);

figs/vbicon.gif

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:

figs/csharpicon.gif

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:

figs/csharpicon.gif

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:

figs/csharpicon.gif

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:

figs/csharpicon.gif

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

figs/vbicon.gif

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

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

figs/csharpicon.gif

cbProduct.DataSource = theTable.DefaultView;

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

figs/csharpicon.gif

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.

figs/csharpicon.gif

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:

figs/csharpicon.gif

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

figs/vbicon.gif

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.

figs/csharpicon.gif

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:

figs/csharpicon.gif

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

figs/vbicon.gif

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:

figs/csharpicon.gif

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;

figs/vbicon.gif

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:

figs/csharpicon.gif

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( ));

figs/vbicon.gif

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.

figs/csharpicon.gif

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:

figs/csharpicon.gif

System.Data.SqlClient.SqlParameter param;

figs/vbicon.gif

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:

figs/csharpicon.gif

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:

figs/csharpicon.gif

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:

figs/csharpicon.gif

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:

figs/csharpicon.gif

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:

figs/csharpicon.gif

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.

figs/csharpicon.gif

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.

figs/csharpicon.gif

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:

    figs/csharpicon.gif

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

    figs/vbicon.gif

    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:

    figs/csharpicon.gif

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

    figs/vbicon.gif

    Dim myCommand As New System.Data.SqlClient.SqlCommand( )
  3. Open the connection:

    figs/csharpicon.gif

    connection.Open( );

    figs/vbicon.gif

    myConnection.Open( )
  4. Instantiate a SqlTransaction object by calling the BeginTransaction method of the SqlConnection object:

    figs/csharpicon.gif

    SqlTransaction transaction;
    transaction = connection.BeginTransaction( );

    figs/vbicon.gif

    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.

    figs/csharpicon.gif

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

    figs/vbicon.gif

    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:

    figs/csharpicon.gif

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

    figs/vbicon.gif

    Try
     myCommand.CommandText = "spAddBug"
     myCommand.CommandType = CommandType.StoredProcedure
  7. Add all the parameters, including the output parameters:

    figs/csharpicon.gif

    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;

    figs/vbicon.gif

    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:

    figs/csharpicon.gif

    command.ExecuteNonQuery( );

    figs/vbicon.gif

    myCommand.ExecuteNonQuery( )
  9. Get back the BugID:

    figs/csharpicon.gif

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

    figs/vbicon.gif

    Dim BugID As Integer = _
     Convert.ToInt32(myCommand.Parameters("@BugID").Value)
  10. Create a SQL statement from the controls:

    figs/csharpicon.gif

    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 + ")";

    figs/vbicon.gif

    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:

    figs/csharpicon.gif

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

    figs/vbicon.gif

    myCommand.CommandType = CommandType.Text
    myCommand.CommandText = strAddBugHistory
     
    ' execute the insert statement
    myCommand.ExecuteNonQuery( )
  12. Commit the transaction:

    figs/csharpicon.gif

    transaction.Commit( );

    figs/vbicon.gif

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

    figs/csharpicon.gif

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

    figs/vbicon.gif

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





Programming. NET Windows Applications
Programming .Net Windows Applications
ISBN: 0596003218
EAN: 2147483647
Year: 2003
Pages: 148
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net