Datagrid Data using MSSQL

[jdsanders24]

Hello,

I have a question about updating a datagrid with changes from a SQL server. I have a application (Multiple instances running across a network) that polls a server every so often and pulls data from a table to update a datagrid with changes that have been made to the table. The current implementation I have is as follows:

1.) Application Pulls data from server and creates new datatable
2.) Application records current selection in datagrid.
3.) Change datagrid ItemsSource to the new datatable that was retrieved.
4.) Set the selection back to the item that was selected before the itemsource was changed.

-This all is executed using a secondary thread as to not interrupt the UI thread.

It works flawless and the update process doesn't cause any flicker but I feel like there was a more direct approach without having to have as much code, also kinda nervous about it I have more than a few hundred rows if I will start noticing performance issues. (Which I should never have more than 50 rows at any given time giving consideration to how the application is being used, just want to plan ahead for the future.)

Do you feel like what I currently have is sufficient enough? or could you point to a article on the best way to accomplish a way to synchronize two datatables (New one from server against the current one Im using to display the data in a datagrid).

Development Information: WPF Vb.net, Framework 4.0, MSSQL

 

[jmcilhinney]

Is the user editing the data in the grid? If they are then you're going to lose any pending edits the way you're doing it. If you're not then at least you won't be losing any changes.

Even if the user is not editing the data, it would be more efficient to retrieve only the data that has changed rather than everything that you've already got as well. To that end, if it's possible, you should have a column in the database table that contains the last time a record was modified. You would then get all the data on the first retrieval but then, on any subsequent retrieval, you only get the records where the last modified time is later than the latest value you already have and merge those records into the DataTable you already have.

 

[jdsanders24]

Wouldn't I still have to get all rows for the server and then compare each one individually to check if the timestamp on the server is greater than the timestamp on my datatable? I would also have to check for rows added or deleted, but I could do that by checking for the primary key Id assume. But using loops for the processing would seem to use a lot of resources. Would there be a different approach to this? (Comparing the timestamps and primary keys)

 

[jmcilhinney]

Wouldn't I still have to get all rows for the server and then compare each one individually to check if the timestamp on the server is greater than the timestamp on my datatable?

No. You simply use a WHERE clause in your query. You first get the latest LastModifiedTime value from the data you already have and then you retrieve data from the database where the LastModifiedTime value is greater than that.

I would also have to check for rows added or deleted, but I could do that by checking for the primary key Id assume. But using loops for the processing would seem to use a lot of resources. Would there be a different approach to this? (Comparing the timestamps and primary keys)

You don't have to check for anything. The LastModifiedTime takes care of it all. Any rows that you already have and have not been modified since you retrieved then are ignored and all you retrieve are the rows that have been added, updated or deleted since that last retrieval.

Now, when I say deleted I don't actually mean removed from the database. If you want to do things this way then you can't actually delete anything. You have to add an extra column to indicate whether a record is active or not. That column gets set to True (1) when a record is inserted and then deleting a record actually consists of setting that column to False (0). You set the LastModifiedDate whenever you insert, update or "delete" a record so your WHERE clause will ensure that you only get records that have changed since you last retrieved data. The code would look something like this:

Dim maxLastModifiedTime = CDate(myDataTable.Compute("MAX(LastModifiedDate)", Nothing))
Dim newTable As New DataTable

Using adapter As New SqlDataAdapter("SELECT * FROM MyTable WHERE LastModifiedTime = @LastModifiedTime", connectionString)
    adapter.Parameters.AddWithValue("@LastModifiedTime", maxLastModifiedTime)
    adapter.Fill(newTable)
End Using

You'd then merge the new DataTable into the existing one, so the data-binding wouldn't change.