System.Data.SqlClient Namespace

Represents a Transact-SQL transaction to be made in an SQL Server™ database.

The application initializes an SqlTransaction object by calling BeginTransaction on the SqlConnection object. All subsequent operations associated with the transaction ( for example, committing or aborting the transaction ), are performed on the SqlTransaction object.

The following example initializes an SqlConnection and an SqlTransaction. It also demonstrates how to use the BeginTransaction, Commit, and Rollback methods.

[ VB ] 
Public Sub RunSqlTransaction ( connString As String )
    Dim myConn As New SqlConnection ( connString )
    myConn.Open ( )
 
    Dim myCommand As New SqlCommand ( )
    Dim myTrans As SqlTransaction
 
    ' start a local transaction
    myTrans = myConn.BeginTransaction ( )
    ' assign transaction object for a pending local transaction
    myCommand.Transaction = myTrans
 
    Try
        myCommand.CommandText = "Insert into Region ( RegionID, RegionDescription ) VALUES ( 100, 'Description' ) "
        myCommand.ExecuteNonQuery ( )
        myCommand.CommandText = "Insert into Region ( RegionID, RegionDescription ) VALUES ( 101, 'Description' ) "
        myCommand.ExecuteNonQuery ( )
        myTrans.Commit ( )
        Response.Write ( "Both records are written to database." )
    Catch e As Exception
        myTrans.Rollback ( )
        Response.Write ( e.ToString ( ) )
        Response.Write ( "Neither record was written to database." )
    Finally
        myConn.Close ( )
    End Try
End Sub 'RunSqlTransaction

[ C# ] 
void RunSqlTransaction ( string connString )
 {
    SqlConnection myConn = new SqlConnection ( connString );
    myConn.Open ( );

    SqlCommand myCommand = new SqlCommand ( );
    SqlTransaction myTrans;

    // start a local transaction
    myTrans = myConn.BeginTransaction ( );
    // assign transaction object for a pending local transaction
    myCommand.Transaction = myTrans;

    try
    {
      myCommand.CommandText = "Insert into Region ( RegionID, RegionDescription ) VALUES ( 100, 'Description' ) ";
      myCommand.ExecuteNonQuery ( );
      myCommand.CommandText = "Insert into Region ( RegionID, RegionDescription ) VALUES ( 101, 'Description' ) ";
      myCommand.ExecuteNonQuery ( );
      myTrans.Commit ( );
      Response.Write ( "Both records are written to database." );
      }
    catch ( Exception e )
    {
      myTrans.Rollback ( );
      Response.Write ( e.ToString ( ) );
      Response.Write ( "Neither record was written to database." );
      }
    finally
    {
      myConn.Close ( );
   }
}

SqlDataAdapter   SqlConnection

Books and more ...

Custom Search