Use one SQL Connection with multiple transactions

I have a vb.net web app and I am passing a SQL connection and a transaction to a method which writes a single record to the database.

I would like to start and commit a transaction for each record that is written but using the same sql connection until the loop is done.

One method I saw was utilizing a using statement but it didn't work for me.it runs the first time and gives an error on the second that the transact开发者_如何转开发ion has already been commited

Using sqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
  sqlConnection.Open()
  Using transaction = sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted)

      For Each user In users
         Try
            myDataWriteMethod(user, conn, tr)

         Catch ex As Exception
            tranaction.rollback()
         End Try
         transaction.commit()
      Next 
    End Using
End Using  

It looks as if you are committing or rolling back the transaction at each step of the for loop. You probably need to either (1) begin a new transaction for each user, e.g.,

Using sqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
  sqlConnection.Open()
  For Each user In users
      Using transaction = sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted)
      ...

or (2) only commit or rollback the transaction at the end, e.g.

Using sqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
  sqlConnection.Open()
  Using transaction = sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted)

      Try
          For Each user In users
              myDataWriteMethod(user, conn, tr)
          Next
      Catch ex As Exception
          tranaction.rollback()
      End Try

      transaction.commit()
  End Using
End Using

Note that I'm not a VB.NET programmer so my syntax may be wrong.

This should do what you want.

Using sqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
    sqlConnection.Open()
    For Each user In users
        Using transaction = sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted)
            Try
                myDataWriteMethod(user, conn, tr)
                transaction.Commit()
            Catch ex As Exception
                transaction.Rollback()
            End Try
        End Using
    Next   
End Using