Maybe someone here can figure it out. Everything updates fine with
this code, except where there's an exception, it's not rolling back
by the transaction. What I'm trying to do:
Begin a transaction
Do the update, insert, delete checks on each of the data tables,
using a different npgsqlcommandbuilder for each of the tables.
Commit
if any failure happens, roll back all the changes to the transaction beginning.
I assign the transaction object to each of the commands, but it seems
that some tables will get updated, even when I call rollback. Is
something I'm calling secretly calling "commit" somewhere?
My code follows. Thanks for checking it over. Sorry about the
length, but I wanted you to see that I'm updating multiple tables
with multiple dataadapters.
-Owen
Option Explicit On
Imports System.Windows.Forms
Imports npgsql
Imports System.Xml.Serialization
Imports System.IO
Imports System.Collections.Generic
Imports System.Configuration
' Note: some controls, in the forms designer, cover other controls,
i.e. CommUsageCB covers styleCB
Public Class ParcelDisplayFrm
Public Sub WriteAllData()
Dim trans As NpgsqlTransaction = Nothing
Dim cmd As NpgsqlCommandBuilder
Dim i As Integer
Dim success As Boolean
Try
If Not statusReadOnly Then
i = vbCancel
success = priceIt(Me, full_DataSet, True, True, pc)
dt = full_DataSet.Tables(currentSchema & ".parcel")
dt.Rows(0).EndEdit()
dt = full_DataSet.Tables(currentSchema & ".accounts")
dt.Rows(0).EndEdit()
dt = full_DataSet.Tables(currentSchema & ".bldg")
For i = 0 To dt.Rows.Count - 1
dt.Rows(i).EndEdit()
Next i
dt = full_DataSet.Tables(currentSchema & ".commcost")
For i = 0 To dt.Rows.Count - 1
dt.Rows(i).EndEdit()
Next i
dt = full_DataSet.Tables(currentSchema & ".outbuildings")
For i = 0 To dt.Rows.Count - 1
If dt.Rows(i).RowState = DataRowState.Added Then
dt.Rows(i).Item("maplot") = Form1.currentMapLot
End If
Debug.Print(dt.Rows.Count)
dt.Rows(i).EndEdit()
Next i
If Not dirtySketch And Not full_DataSet.HasChanges Then
Exit Sub ' Nothing to change
End If
Dim dg As New SaveChangesDlog
If dg.ShowDialog = Windows.Forms.DialogResult.Cancel
Then Exit Sub ' don't save
writeFinalize()
dt = full_DataSet.Tables(currentSchema & ".parcel")
m_SqlConnection.Open()
' create a transaction for the rest of all the changes
trans = m_SqlConnection.BeginTransaction
cmd = New NpgsqlCommandBuilder(parcel_DataAdapter)
Dim parcelchanges As DataTable =
dt.GetChanges(DataRowState.Modified)
If parcelchanges IsNot Nothing Then
parcel_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(0))
parcel_DataAdapter.UpdateCommand.Transaction = trans
parcel_DataAdapter.Update(parcelchanges)
End If
parcelchanges = dt.GetChanges(DataRowState.Deleted)
If parcelchanges IsNot Nothing Then
parcel_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
parcel_DataAdapter.DeleteCommand.Transaction = trans
parcel_DataAdapter.Update(parcelchanges)
End If
parcelchanges = dt.GetChanges(DataRowState.Added)
If parcelchanges IsNot Nothing Then
parcel_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(0))
parcel_DataAdapter.InsertCommand.Transaction = trans
parcel_DataAdapter.Update(parcelchanges)
End If
' accounts table
cmd = New NpgsqlCommandBuilder(accts_DataAdapter)
dt = full_DataSet.Tables(currentSchema & ".accounts")
Dim acctchanges As DataTable =
dt.GetChanges(DataRowState.Modified)
If acctchanges IsNot Nothing Then
accts_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(0))
accts_DataAdapter.UpdateCommand.Transaction = trans
accts_DataAdapter.Update(acctchanges)
End If
acctchanges = dt.GetChanges(DataRowState.Deleted)
If acctchanges IsNot Nothing Then
accts_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
accts_DataAdapter.DeleteCommand.Transaction = trans
accts_DataAdapter.Update(acctchanges)
End If
acctchanges = dt.GetChanges(DataRowState.Added)
If acctchanges IsNot Nothing Then
accts_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(0))
accts_DataAdapter.InsertCommand.Transaction = trans
accts_DataAdapter.Update(acctchanges)
End If
' do for every building
dt = full_DataSet.Tables(currentSchema & ".bldg")
If dt.Rows.Count > 0 Then
If dirtySketch Then
For i = currentBuilding To howManyBuildings - 1
returnSketchToDatabase(dt.Rows(0).Item("maplot"), i, trans, Me)
Next i
End If
cmd = New NpgsqlCommandBuilder(bldg_DataAdapter)
' add modified dates
addModDates(dt, "modified")
'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim bldgchanges As DataTable =
dt.GetChanges(DataRowState.Deleted)
If bldgchanges IsNot Nothing Then
bldg_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
bldg_DataAdapter.DeleteCommand.Transaction = trans
bldg_DataAdapter.Update(bldgchanges)
End If
bldgchanges = dt.GetChanges(DataRowState.Modified)
If bldgchanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
bldg_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
bldg_DataAdapter.UpdateCommand.Transaction = trans
bldg_DataAdapter.Update(bldgchanges)
End If
bldgchanges = dt.GetChanges(DataRowState.Added)
If bldgchanges IsNot Nothing Then
bldg_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(0))
bldg_DataAdapter.InsertCommand.Transaction = trans
bldg_DataAdapter.Update(bldgchanges)
End If
End If
dt = full_DataSet.Tables(currentSchema & ".commcost")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(commbldg_DataAdapter)
'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim commBldgChanges As DataTable
commBldgChanges = dt.GetChanges(DataRowState.Deleted)
If commBldgChanges IsNot Nothing Then
commbldg_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
commbldg_DataAdapter.DeleteCommand.Transaction = trans
commbldg_DataAdapter.Update(commBldgChanges)
End If
commBldgChanges = dt.GetChanges(DataRowState.Modified)
If commBldgChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
commbldg_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
commbldg_DataAdapter.UpdateCommand.Transaction = trans
commbldg_DataAdapter.Update(commBldgChanges)
End If
commBldgChanges = dt.GetChanges(DataRowState.Added)
If commBldgChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
commbldg_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
commbldg_DataAdapter.InsertCommand.Transaction = trans
commbldg_DataAdapter.Update(commBldgChanges)
End If
End If
dt = full_DataSet.Tables(currentSchema & ".outbuildings")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(outbldg_DataAdapter)
For i = 0 To dt.Rows.Count - 1
If dt.Rows(i).RowState = DataRowState.Added Then
dt.Rows(i).Item("MapLot") =
full_DataSet.Tables(currentSchema & ".parcel").Rows(0).Item("MapLot")
End If
Next i
'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim outchanges As DataTable
outchanges = dt.GetChanges(DataRowState.Deleted)
If outchanges IsNot Nothing Then
outbldg_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
outbldg_DataAdapter.DeleteCommand.Transaction = trans
outbldg_DataAdapter.Update(outchanges)
End If
outchanges = dt.GetChanges(DataRowState.Modified)
If outchanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
outbldg_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
outbldg_DataAdapter.UpdateCommand.Transaction = trans
outbldg_DataAdapter.Update(outchanges)
End If
outchanges = dt.GetChanges(DataRowState.Added)
If outchanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
outbldg_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
outbldg_DataAdapter.InsertCommand.Transaction = trans
outbldg_DataAdapter.Update(outchanges)
End If
End If
' write changes to sales tables
dt = full_DataSet.Tables(currentSchema & ".sales")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(sales_DataAdapter)
'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim salesChanges As DataTable
salesChanges = dt.GetChanges(DataRowState.Deleted)
If salesChanges IsNot Nothing Then
sales_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
sales_DataAdapter.DeleteCommand.Transaction = trans
sales_DataAdapter.Update(salesChanges)
End If
salesChanges = dt.GetChanges(DataRowState.Modified)
If salesChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
sales_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
sales_DataAdapter.UpdateCommand.Transaction = trans
sales_DataAdapter.Update(salesChanges)
End If
salesChanges = dt.GetChanges(DataRowState.Added)
If salesChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
sales_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
sales_DataAdapter.InsertCommand.Transaction = trans
sales_DataAdapter.Update(salesChanges)
End If
End If
' write changes to sales overflow table
dt = full_DataSet.Tables(currentSchema & ".salesovflowtype")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(salesOF_DataAdapter)
'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim salesOFChanges As DataTable
salesOFChanges = dt.GetChanges(DataRowState.Deleted)
If salesOFChanges IsNot Nothing Then
salesOF_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
salesOF_DataAdapter.DeleteCommand.Transaction = trans
salesOF_DataAdapter.Update(salesOFChanges)
End If
salesOFChanges = dt.GetChanges(DataRowState.Modified)
If salesOFChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
salesOF_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
salesOF_DataAdapter.UpdateCommand.Transaction = trans
salesOF_DataAdapter.Update(salesOFChanges)
End If
salesOFChanges = dt.GetChanges(DataRowState.Added)
If salesOFChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
salesOF_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
salesOF_DataAdapter.InsertCommand.Transaction = trans
salesOF_DataAdapter.Update(salesOFChanges)
End If
End If
trans.Commit()
m_SqlConnection.Close()
dirtySketch = False
BrowserPanel.Refresh()
End If
Catch ex As Exception
MsgBox(" error on writing data " & ex.Message,
MsgBoxStyle.AbortRetryIgnore)
If trans IsNot Nothing Then trans.Rollback()
If m_SqlConnection.State = ConnectionState.Open Then
m_SqlConnection.Close()
End Try
End Sub
End Class
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
