Results 1 to 4 of 4

Thread: Datagrid Data using MSSQL

  1. #1
    jdsanders24 is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.0
    Join Date
    Oct 2014
    Posts
    4
    Reputation
    0

    Datagrid Data using MSSQL

    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
    Last edited by jdsanders24; 10-17-2014 at 8:41 AM.

  2. #2
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,813
    Reputation
    1707
    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.

  3. #3
    jdsanders24 is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.0
    Join Date
    Oct 2014
    Posts
    4
    Reputation
    0
    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)

  4. #4
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,813
    Reputation
    1707
    Quote Originally Posted by jdsanders24 View Post
    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.
    Quote Originally Posted by jdsanders24 View Post
    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.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •