Advice of how to do this ...

[motil]

Hi all

this is my problem :

I have DataGridView control which i populate with some rows from the database
the database table which i work with changes a lot so every 30 seconds I re-query the database to check if there is new record or existing record changed or record deleted, I can't clear and re-populate the grid view because people might work with it and they're might doing some changes in some rows.

so if the row was changed i just want to update the changes in the existing row without recreating it, if some row deleted i want to remove it and if a row was added i want to add the new row

anyone did something like this before ?

Best Regards,
Moti.

[kareninstructor]

Here is a thought. Add a field to the table(s) LAST_CHANGED as a Date Time field which we have on all of our tables (we use a different name for the field) and use it in the WHERE clause of a SQL statement which would pick up new and changed rows. With that in mind go through the result set one by one and update the row in the underlying source of the DataGridView which would be easy if you are using a BindingSource which would allow you to locate a single row using the find method of the BindingSource and update the row casted as a DataRow. In regards to deleted rows, you could have a second Date Time field that if not NULL would indicate a deleted row and similar logic could be used to remove the row from the BindingSource as with added and edited rows. Any ways making changes as mentioned above to the underlying DataSource of the DataGridView would be reflected in the DataGridView visually to the user.

We use similar logic in my agency (Oregon Department of Revenue) with billons of records along with tracking who added edited or removed data from one of the databases. BTW we use IBM-DB2 on a massive scale and adding the fields above which was done around eight years ago has not slowed down any data operations.

Hope this is helpful.

[techgnome]

Look at the Merge function of the datatable... might provide the functionality you need.

-tg

[szlamany]

How far are you into architecting this?

This routine does a refresh of a row in a datatable

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

That was one of the battles I fought trying to achieve this.

As you can see I'm currently debugging multi-user issues and have some debug messages in place...

[szlamany]

Look at the Merge function of the datatable... might provide the functionality you need.

-tg

I've tried MERGE and found I was not in control enough of what was going on.

[motil]

thanks for the answers guys, I have an idea of how to do it in my head I just wanted to make sure if there is "build-in" or "streamed" way to do it before I implementing my idea.

I guess this is my green light of doing it as I thought i should.

[jmcilhinney]

I've tried MERGE and found I was not in control enough of what was going on.

Control is one of those things where you need it if you need it but you don't if you don't. If you can do what you need to do with a single method call then you may as well just use a single method call. Here's an example of DataTable.Merge at work:

Public Class Form1
 
    Dim data As DataTable
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.data = Me.GetOriginalData()
        Me.BindingSource1.Filter = "IsDeleted = False"
        Me.BindingSource1.DataSource = Me.data
        Me.DataGridView1.DataSource = Me.BindingSource1
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.data.Merge(Me.GetUpdatedData(), True)
 
        For Each row As DataRow In Me.data.Select()
            If CBool(row("IsDeleted")) Then
                Me.data.Rows.Remove(row)
            End If
        Next
    End Sub
 
    Private Function GetTable() As DataTable
        Dim table As New DataTable
 
        With table.Columns
            table.PrimaryKey = New DataColumn() {.Add("ID", GetType(Integer))}
            .Add("Text", GetType(String))
            .Add("UpdatedTime", GetType(Date))
            .Add("IsDeleted", GetType(Boolean))
        End With
 
        Return table
    End Function
 
    Private Function GetOriginalData() As DataTable
        Dim table As DataTable = Me.GetTable()
 
        With table.Rows
            .Add(1, "Row 1, Original Text", Date.Now, False)
            .Add(2, "Row 2, Original Text", Date.Now, False)
            .Add(3, "Row 3, Original Text", Date.Now, False)
        End With
 
        Return table
    End Function
 
    Private Function GetUpdatedData() As DataTable
        Dim table As DataTable = Me.GetTable()
 
        With table.Rows
            .Add(1, "Row 1, Updated Text", Date.Now, False)
            .Add(2, "Row 2, Deleted", Date.Now, True)
            .Add(4, "Row 4, New Text", Date.Now, False)
        End With
 
        Return table
    End Function
 
End Class

Add a Button, DataGridView and BindingSource to a form and then run that code. You will intially see three rows with their original data. Click the Button and then the data will be updated, adding one new row, updating one existing row and deleting another existing row. The code to remove the deleted rows is not strictly necessary as the Filter will exclude those rows anyway, but it's a good idea if the data will be used for some time.

Note that records can't actually be deleted from the database if you want to be able to update an application based on database changes. You often can't delete for other reasons anyway though.

Of course, if you do need to implement logic that isn't supported by the Merge method then you will need to write more code of your own. That's the same with anything though.

[motil]

Hi,
thanks for the example JM, In my case the records is not deleted but moving from one table to table (pre_orders to orders) I also never use datatables instead I use a list of custom class that I get back from my DAL -> BLL layers.

I'll try to go head and implement what I had in mind today.

[szlamany]

Control is one of those things where you need it if you need it but you don't if you don't. If you can do what you need to do with a single method call then you may as well just use a single method call...

Sorry - I mispoke...

It was speed and scalability - merge wasn't good for the number of records I was dealing with and it was only in the couple of thousand area.

Mentioned in this post from May

http://www.vbforums.com/showthread.p...ighlight=merge