Optimistic Concurrency

[barski]

the dataadapter will return and update the dataset with the current "dbrow" values when there are no errors. However, i can't seem to figure the way to return the current "dbrow" when there are errors. any ideas?

[penfold69]

Current version of row:

 

myDT.Rows(myIndex)("FIELD", DataRowVersion.Current)

 

Original version of row:

 

myDT.Rows(myIndex)("FIELD", DataRowVersion.Original)

[barski]

that's not going to give me the current database row just the row as it was when i originally connected and changes i've made. what i need is the current database row so i can compare to the various datarow versions when a dbconcurrency exception is generated

[Nerseus]

You'll have to get that current DB row yourself through another query. Or, you can wrap your Update logic in a stored procedure that does a manual compare rather than let the DB engine throw it's exception/error.

 

-ner

[barski]

thanks for the reply. not the answer i was hoping for but that's the breaks

[penfold69]

Kinda ripped from an application I'm developing at the moment - I've chopped some bits out so it probably won't work, but you shoul dget the general gist.

 

B.

 

 

Protected Overridable Sub BaseSave(Optional ByRef Conn As IDbConnection = Nothing, Optional ByRef Trans As IDbTransaction = Nothing)
	Dim lConn As IDbConnection
	Dim lTrans As IDbTransaction

	If Me.IsDeleted Then
		Dim Sql As String = "DELETE FROM " & mTable & " WHERE " & mIndexField & "=" & Me(mIndexField)
		DB.QueryDatabase(Sql)
		Return
	End If

	If mRow.RowState = DataRowState.Unchanged Then
                       'No need to save to row if its unchanged.
		Return
	End If

	If Conn Is Nothing Then
		lConn = DB.CreateConnection()
		lConn.Open()
	Else
		lConn = Conn
	End If

	If Trans Is Nothing Then
		lTrans = lConn.BeginTransaction()
	Else
		lTrans = Trans
	End If

	Dim sSql As New StringBuilder
	sSql.Append("SELECT * FROM ")
	sSql.Append(mTable)
	sSql.Append(" WHERE ")
	sSql.Append(mIndexField)
	sSql.Append(" = ")

	If mRow.IsNull(mIndexField) Then		  ' Autonumber fields are set to null on purpose
		sSql.Append("0")
	Else
		sSql.Append(mRow(mIndexField))
	End If



	Dim comm As IDbCommand = DB.CreateCommand(sSql.ToString(), lConn)
	Dim rs As IDbDataAdapter = DB.CreateAdapter(comm)

	' Hokey Cokey Smokey - make the CommandBuilder 'Last One Wins'
	Dim cb As DBCommandBuilder = db.CreateCommandBuilder()
	cb.DataAdapter = rs

	Dim changes As New DatabaseChangeCollection

	' Ok.. The first part of our Optimistic locking scenario
	' Firstly, Retrieve the TimeStamp field and compare
	If mRow.RowState = DataRowState.Modified Then
		Dim DataDT As DataTable = DB.RetrieveTable(sSql.ToString)
		If DataDT.Rows.Count > 0 Then
			Dim dataRow As dataRow = DataDT.Rows(0)

			If mRow("TIMESTAMP") <> dataRow("TIMESTAMP") Then
				' Another user (or process) has modified the underlying
				' database record.  We must merge any changes and
				' present the user with a conflict-resolution scenario

				For index As Integer = 0 To mRow.ItemArray.Length - 1
					' The process for merging records follows this scenario:
					' 1.  The original DB record is compared to the current DB record
					'     If these differ, then that field has been modified in the DB
					' 2.  In the case that they differ, the Current value of the field
					'     is compared to the Original value of the field.  If they differ
					'     Then the record has also been modified locally, so we need to 
					'     resolve the conflict manually.
					' 3.  If there is no change in the current value, then the current value
					'     is updated to reflect the current DB value, and is saved back to 
					'     the Database
					Dim bChanged As Boolean = False
					If Convert.IsDBNull(mRow(index, DataRowVersion.Original)) Then
						bChanged = True
					End If
					If Convert.IsDBNull(dataRow(index)) Then
						bChanged = True
					End If

					If bChanged Or (Not bChanged AndAlso mRow(index, DataRowVersion.Original) <> dataRow(index)) Then
						' DB has been updated
						Dim bIsNullOrig As Boolean = False
						Dim bIsNullNew As Boolean = False

						bIsNullOrig = Convert.IsDBNull(mRow(index, DataRowVersion.Original))
						bIsNullNew = Convert.IsDBNull(mRow(index, DataRowVersion.Current))

						If (bIsNullOrig <> bIsNullNew) _
						 Or (Not bIsNullOrig AndAlso Not bIsNullNew AndAlso _
						 (mRow(index, DataRowVersion.Original) <> mRow(index, DataRowVersion.Current))) Then
							If (mRow(index, DataRowVersion.Current) <> dataRow(index)) Then
								' So has our local version
								Dim colName As String = mDT.Columns(index).ColumnName
								Dim c As New DatabaseChange(colName, mRow(index, DataRowVersion.Original), dataRow(index), mRow(index), index)
								changes.Add(c)
							End If

						Else
							' Our local version is fine, overwrite with the current copy from 
							' the database
							mRow(index) = dataRow(index)
						End If
					End If
				Next

				' Ok, we now need to present the user with a conflict resolution box.
				' Once that has been completed, the row can be saved.
				If changes.Count > 0 Then
					Dim frm As New frmReconcileChanges(mRow, changes)
					frm.ShowDialog()

					For index As Integer = 0 To changes.Count - 1
						Dim c As DatabaseChange = changes(index)
						mRow(CInt(c.ColumnIndex)) = c.AcceptedValue
					Next
				End If
			End If
		End If
	End If

	' Must ensure that the Timestamp field resets itself
	mRow("TIMESTAMP") = DBNull.Value

	Try
		CType(rs, DbDataAdapter).Update(mDT)
	Catch ex As DBConcurrencyException
                       MsgBox("The following exception occurred while processing a save." & Environment.NewLine & ex.ToString())
	End Try
	comm.Dispose()
	If Trans Is Nothing Then
		lTrans.Commit()
	End If
	If Conn Is Nothing Then
		lConn.Close()
	End If
	If Conn Is Nothing Then
		lConn.Close()
		lConn.Dispose()
	End If
End Sub

[barski]

thanks a bunch for the reply. but i think i've come up with a way. i'm just about to try it out and see if it works.