Devart Forums

Code: Select all

' This example is taken from the pgsqlnet.chm file

Public Sub RunPgSqlTransaction(ByVal myConnString As String)

  Dim pgConnection As New PgSqlConnection(myConnString)
  pgConnection.Open()
  
  Dim pgCommand As New PgSqlCommand
  ' I need to explicitly set the connection property of the pgCommand object, otherwise, 
  ' the pgCommand.ExecuteNonQuery will throw a "ConnectionNotInit" exception.
  ' The connection property is not set in the example.
  pgCommand.Connection = pgConnection

  Dim myTrans As PgSqlTransaction

  ' Start a local transaction
  myTrans = pgConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)
  ' Assign transaction object for a pending local transaction
  pgCommand.Transaction = myTrans
  pgCommand.Connection = pgConnection
  Try
    pgCommand.CommandText = 
    pgCommand.ExecuteNonQuery()

    ' For testing purposes I've put a Messagebox.Show here which will suspend the thread.
    ' When this box shows, I will disable the WIFI connection, simulating a broken connection.
    ' At this point, the first row will be locked.
    Messagebox.Show("Close down WIFI connection...")

    ' Now the connection is down, the next statements will throw an exception.
    ' But how can one perform a rollback, if there isn't a connection?
    ' Should I try to re-establish the connection in the catch block?
    ' Or should I use a different approach?

    pgCommand.CommandText = 
    pgCommand.ExecuteNonQuery()
    myTrans.Commit()
    Console.WriteLine("Both records are written to database.")
  Catch e As Exception
    ' Try to re-establish connection here?
    myTrans.Rollback()
    Console.WriteLine(e.ToString())
    Console.WriteLine("Neither record was written to database.")
  Finally
    pgConnection.Close()
  End Try
End Sub

Code: Select all

        Catch e As PgSqlException
            ' Try to re-establish connection here? 
            If IsFatalError(e) Then
                Console.WriteLine(e.ToString())
                Console.WriteLine("Connection is dead.")
            ElseIf Not IsFatalError(e) Then
                myTrans.Rollback()
                Console.WriteLine(e.ToString())
                Console.WriteLine("Neither record was written to database.")
            End If

Code: Select all

    Const ConnectionFailureCode As String = "08006"

    Shared Function IsFatalError(ByVal exception As PgSqlException) As Boolean
        If exception.ErrorCode = ConnectionFailureCode Then
            Return True
        End If
        Return False
    End Function