Trouble with Transactions (Data Access)

Topics: Data Access Application Block
Jan 14, 2009 at 10:54 PM
Need some help from someone out there.  I cannot seem to get Commit/Rollback to work for me in my code.  I finally gave up and wrote a simple routine, shown below, and I still cannot get this to work.  There are no errors but if you tell it to rollback the record still appears in the database (SQL Express 2008).  I've done the transaction by hand in SQL Studio with a BEGIN and ROLLBACK statement and it works correctly - the record is gone after the rollback.  This is part of something larger but I put it into a very simple routine just to try to bug fix my transaction problem.  I've put a breakpoint on the MsgBox line.  I can see the open transaction and the open connection.  When I press No on the MsgBox and step through the action it execute the _transaction.rollback and _connection.close - I can see the status change.  There are no errors but the record is in the database - not rolled back as it should have been.

I'm using the Enterprise Library 4.1 and Visual Studio 2008.  Any help will be greatly appreciated.

    Private Sub SaveInTransaction()

        'Setup variable
        Dim _db As Database
        Dim _dbCommand As Common.DbCommand = Nothing
        Dim _connection As DbConnection = Nothing
        Dim _Transaction As DbTransaction = Nothing

        'Setup Database Connection
        _db = DatabaseFactory.CreateDatabase

        _connection = _db.CreateConnection
        _dbCommand = _connection.CreateCommand

        'Setup the Transaction
        _Transaction = _dbCommand.Transaction

        _Transaction = _connection.BeginTransaction()

        'Add my Parameters
        CreateParameter("varCustomerID", DbType.Guid, ParameterDirection.Input, Guid.NewGuid, _dbCommand)
        CreateParameter("varCustomerName", DbType.String, ParameterDirection.Input, "This Person", _dbCommand)
        CreateParameter("varCustomerEmail", DbType.String, ParameterDirection.Input, "", _dbCommand)
        CreateParameter("varCustomerLogin", DbType.String, ParameterDirection.Input, "myaccount", _dbCommand)
        CreateParameter("varCustomerPassword", DbType.String, ParameterDirection.Input, "mypassword", _dbCommand)

        'Execute the SP
        _dbCommand.CommandType = CommandType.StoredProcedure
        _dbCommand.CommandText = "Customer_Insert"


        If MsgBox("Procedure Complete: Do you want to commit?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
        End If

    End Sub

    Public Sub CreateParameter(ByVal ParamName As String, _
                            ByVal dbType As DbType, _
                            ByVal Direction As ParameterDirection, _
                            ByVal Value As Object, _
                            ByRef _dbCommand As Common.DbCommand)

        Dim _param As System.Data.Common.DbParameter = _dbCommand.CreateParameter()
        _param.ParameterName = ParamName
        _param.DbType = dbType
        _param.Direction = Direction
        _param.Value = Value

    End Sub
Jan 15, 2009 at 12:47 AM
Try enclosing your code in a using statement.

using (DbConnection connection = db.CreateConnection())
    DbTransaction transaction = connection.BeginTransaction();

      // Credit the first account.
      db.ExecuteNonQuery(dbCommand, transaction);

      // Commit the transaction.

      result = true;
      // Roll back the transaction.

    return result;

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
Jan 15, 2009 at 3:11 PM
I appreciate the suggestion but I'm trying to avoid Using.  It would work easily in my demo but not in the actual application.  Any logical reason that I would have to use Using to make the transaction work?
Jan 16, 2009 at 10:00 AM

This might explain, Read the "Community Content" Part:

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.