Refresh a datatable

[szlamany]

I load up a datatable - bind it to a binding source and a binding navigator - and lots of textboxes on the screen bound to that as well.

When I get a concurrency violation - another user has already changed my vendor, let's say - what is the best method to go about refreshing the datatable with current data from the DB?

I hate to clear the whole datatable just because one row in the DB changed...

[danasegarane]

Can't you add the the New row to the Datable using Datatable.rows.add method ?

[szlamany]

It's not a new row I'm adding - it's simply me changing VENDOR ID #1 when VENDOR ID #1 in my datatable - the original row values - don't match the DB anymore (because another user just changed VENDOR ID #1 before I tried my update).

At any rate - the .Update comes back with a concurrency violation - expected rows to update is 1 and 0 rows got updated.

I just hate to clear all 1000 vendors from my datatable - I just want to kind of refresh it.

[jmcilhinney]

You might want to read this:

http://msdn.microsoft.com/en-us/libr...8VS.90%29.aspx

It suggests how to detect which rows caused concurrency violations, so you can then just re-get those rows. If you get those rows into a second DataTable you can then Merge that into the original.

[jmcilhinney]

Although I've never tried, you might also be able to include a SELECT statement after the UPDATE that will automatically refresh the row.

http://msdn.microsoft.com/en-us/libr...8VS.90%29.aspx

I haven't looked too deeply into that though.

[szlamany]

Thanks!

I found another link that I just finished reading

http://msdn.microsoft.com/en-us/magazine/cc188748.aspx

- I'll look yours as well and see where to go with it.

At the moment I'm just .Clear'ing 4 datasets completely and re-filling them - that's way too much overkill.

And what I really don't like about it is that ref-integrity between drop-downs and other controls really dislikes clearing datasets!

MERGE is designed to "get around" this problem I would imagine - right?

[szlamany]

Although I've never tried, you might also be able to include a SELECT statement after the UPDATE that will automatically refresh the row.

http://msdn.microsoft.com/en-us/libr...8VS.90%29.aspx

I haven't looked too deeply into that though.

I think that will lead to "last in" updates - and no message to the user about concurrency since a ROW comes back after the UPDATE attempt anyway.

Although there are times I could be happy with "last in wins" - this is not one of them.

[stanav]

The only way I can think off to work this issue, which requires that all the users to use your application to interact with the DB, is that whenever the program updates/changes the value for VENDOR_ID field (or any fields that are critical in that matter) to the DB, it also broadcasts an UDP message out on the network to let other folks know what it did. The program also will listen for UDP message and when it receives one, it can look at the message data to determine what it needs to do (for example, in this case, change the vendor id in its datatable to match that in the DB)

[szlamany]

I've already got a timer running on another thread that checks a "data update status" table - but that's beside the point here.

I want to deal with this individual "update" issue before I dive head first into "something in my vendor table" has changed.

I believe if I understand the MERGE properly - if my timer says the VENDOR table has changed I can "re-load" the entire VENDOR table into another dataset and do the MERGE.

Can anyone elaborate on the functionality of the MERGE?

[szlamany]

Well - my research on MERGE turned up very negative information - no speed and scalibility - not what I wanted to hear!

So based on some ideas in the link I posted from MSDN - I worked up this code

Private Sub SaveData()
	Try
		_adapter.Update(_vendortable)
		_adapter2.Update(_vendorcapabilitiestable)
		_adapter3.Update(_vendorspecialtiestable)
		_adapter9.Update(_vendorapplicationstable)
	Catch ex As System.Data.DBConcurrencyException
		_error = True
		MessageBox.Show("Another user has changed this vendor data.  You must make your changes again!", "Changes did not Save!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
	Catch ex As Exception
		MessageBox.Show(ex.Message, "Unknown Errors on VendorBal_C.SaveData", MessageBoxButtons.OK, MessageBoxIcon.Error)
	End Try
End Sub

The above simply catches the error and the code below "refreshes" the row.

Public Sub vendorUpdate()
    If Not _inupdate Then
        Try
            _inupdate = True
            Call SaveData()
            If _error Then
                Using dcn As New SqlConnection(_connection.ConnectionString)
                    Using cmd As New SqlCommand
                        cmd.CommandType = CommandType.Text
                        cmd.CommandText = "Select * From Vendor_V Where VendorId=1"
                        cmd.Connection = dcn
                        dcn.Open()
                        Using rdr As SqlDataReader = cmd.ExecuteReader
                            rdr.Read()
                            Dim dr As DataRow = _vendortable.Rows.Find(1)
                            For Each dc As DataColumn In dr.Table.Columns
                                If Not dc.ReadOnly Then dr.Item(dc.ColumnName) = rdr.Item(dc.ColumnName)
                            Next
                            dr.AcceptChanges()
                        End Using
                    End Using
                End Using
                MessageBox.Show("Vendor now displaying current data", "Vendor Refreshed", MessageBoxButtons.OK, MessageBoxIcon.Information)
                _error = False
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Re-filling Vendor Dataset errors!", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            _inupdate = False
        End Try
    End If
End Sub

It's hardwired to only "refresh" vendorid=1 - that was just for testing.

What I'm going to do is make this a method of the vendor business access class - to refresh any vendorid in the class's datatables.

I am going to call that method internally when the class detects this type of error.

I am also going to call the method every time the user changes which vendorid they are looking at with the binding navigator.

That way each time they change from one vendor to another - I get the most recent data for that vendor.

Does any of this make sense? Am I drinking my own kool aide?

[szlamany]

Well - I ended up using this code and it appears to work very nicely.

Here are the private sub's in the class that SAVE and REFRESH.

Private Sub SaveData()
    Try
        _adapter.Update(_vendortable)
        _adapter2.Update(_vendorcapabilitiestable)
        _adapter3.Update(_vendorspecialtiestable)
        _adapter9.Update(_vendorapplicationstable)
    Catch ex As System.Data.DBConcurrencyException
        _error = True
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Unknown Errors on VendorBal_C.SaveData", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub

Private Sub RefreshRow(ByVal VendorId As String, Optional ByVal dr As DataRow = Nothing)
    Try
        _inrefresh = True
        Using dcn As New SqlConnection(_connection.ConnectionString)
            Using cmd As New SqlCommand
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "Select * From Vendor_V Where VendorId=" & VendorId
                cmd.Connection = dcn
                dcn.Open()
                Using rdr As SqlDataReader = cmd.ExecuteReader
                    rdr.Read()
                    If dr Is Nothing Then dr = _vendortable.Rows.Find(VendorId)
                    For Each dc As DataColumn In dr.Table.Columns
                        If Not dc.ReadOnly Then dr.Item(dc.ColumnName) = rdr.Item(dc.ColumnName)
                    Next
                    dr.AcceptChanges()
                End Using
            End Using
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Unknown Errors on VendorBal_C.RefreshRow", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Finally
        _inrefresh = False
    End Try
End Sub

Then the methods of the class that UPDATE and REFRESH the VENDOR. The UPDATE is smart in that if detech SAVE errors - loops through the rows looking for the error rows and then REFRESH's those rows - clearing the errors.

Public Sub vendorUpdate()
    If Not (_inupdate Or _inrefresh) Then
        Try
            _inupdate = True
            Call SaveData()
            If _error Then
                MessageBox.Show("Another user has changed this vendor data.  You must make your changes again!", "Changes did not Save!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                For Each drv As DataRow In _vendortable.Rows
                    If drv.HasErrors Then
                        RefreshRow(drv("VendorId").ToString, drv)
                        drv.ClearErrors()
                    End If
                Next
                MessageBox.Show("Vendor now displaying current data", "Vendor Refreshed", MessageBoxButtons.OK, MessageBoxIcon.Information)
                _error = False
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Re-filling Vendor Dataset errors!", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            _inupdate = False
        End Try
    End If
End Sub

Public Sub vendorRefresh(ByVal VendorId As String)
    RefreshRow(VendorId)
End Sub

and in the UI code I already had a TEXTCHANGED event for the vendor id - and I added the REFRESH method to that event

Private Sub VendorId_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles VendorId.TextChanged
    vendorBAL_C.vendorRefresh(VendorId.Text)
    Call capabilitiesLoad(VendorId.Text)
    Call vendorfileLoad(VendorId.Text)
    Call SpecAppColors()
    Call ChangeMoreColor()
End Sub

No concurrency is respected on two users saving at the same time and I guarantee a "fresh" vendor is shown every time you flip to another vendor.

I like this!