Results 1 to 9 of 9

Thread: Keep DataGridView in constant sync with DataSource

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2016
    Posts
    10

    Question Keep DataGridView in constant sync with DataSource

    Hi guys I really need your help with something, I'm not the most pro in vb.NET and have stumbled upon a problem I can't solve. Situation is following:

    I have set up a Datagridview with Access database as a DataSource and I'm trying to write a program that "monitors" the database in real-time by showing the contents in the datagridview and update it regularly with a Timer. The Access database is being edited frequently in Office Access.
    I have understood that I need to .Update and then .Fill the 'Table1'TableAdapter with 'DataBase1'DataSet to renew the contents, everything works good however the problem with the .Fill is that it removes all the rows in the datagridview and populates them again with new info, which in turn makes scrolling and clicking through the rows a bit hard because it always jumps to the top again each time and my program requires to be very user-friendly.
    I tried a method where my code goes through all the datagridview rows and database table rows separately and looks if there is something new or something missing, then adds/deletes the corresponding row however when the list is very long (about 1000 rows) then the program becomes really slow and it doesn't seem like a very good idea (or is it just my garbage laptop...). Do you guys maybe know to suggest me how could I approach this situation? Should I look into the BackgroundWorker object? I would be really grateful.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Keep DataGridView in constant sync with DataSource

    Quote Originally Posted by maku220 View Post
    I have understood that I need to .Update and then .Fill the 'Table1'TableAdapter with 'DataBase1'DataSet to renew the contents, everything works good however the problem with the .Fill is that it removes all the rows in the datagridview and populates them again with new info, which in turn makes scrolling and clicking through the rows a bit hard because it always jumps to the top again each time and my program requires to be very user-friendly.
    Firstly, you don't need an Update call to get data from the database. That's to save your own changes. If you want to save your own changes then that's fine but doing that with a Timer, when the user might be halfway through an edit that they may decide they want to cancel, seems like a bad idea.

    As for the problem, are you using the same DataTable each time or are you using a new DataTable and binding that in place of the old one?

    The best option for this type of situation is to have a time stamp on each record for the last modification. You can then query the database for only records that have changed since the last time you retrieved data. That way, you should be pulling back only a small number of records at the most each time and possibly none on many occasions.

  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2016
    Posts
    10

    Re: Keep DataGridView in constant sync with DataSource

    I am using same DataTable.
    The time stamp idea looks pretty good although it sounds pretty complex since this whole DataSource topic is completely new ground for me heh. Would you mind explaining or maybe give an example how can the code query for records that have a specific timestamp?

    Thanks

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Keep DataGridView in constant sync with DataSource

    Using the same datatable seems a bit iffy. For example, if you had a SELECT query that looked like this:

    SELECT * FROM SomeTable

    and used that for a datatable.fill, you'd keep repeating the same rows over and over. There are a variety of ways that you could avoid that, which would have various different impacts on the behavior. So, I guess what I'm asking is: What are you really doing? How are you doing that Fill? Are you getting new records, or all the records? And so forth...
    My usual boring signature: Nothing

  5. #5
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: Keep DataGridView in constant sync with DataSource

    How do stock market applications (or like apps) work Really, Does the front end application in constant ping with the server ?.
    how doe's they work
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Keep DataGridView in constant sync with DataSource

    Quote Originally Posted by make me rain View Post
    How do stock market applications (or like apps) work Really, Does the front end application in constant ping with the server ?.
    how doe's they work
    On the surface of it, you can use a pull model or a push model. A pull model means the client requests data from the server and the server provides it while a push model means that the server just provides it without a specific request having to be made. Under the hood, a pull model may actually be a push model anyway, where a request is made but that request has a long lifetime, so the client will happily wait a long time for the reply. Many applications will simply register themselves with a server and then the server will push data out to all registered clients whenever it has something for them.

    Access has no facility for a push model so your only option is a pull model. If you are going to have a lot of clients then you could do both, i.e. create middleware that pulls data from the database and then pushes it to multiple clients. SQL Server does have the facility for a push model and that is implemented in .NET via the SqlDependency class. Even then though, that can only support a small number of clients without taking a significant performance hit so middleware can be an option there too, although with push and push instead of pull and push.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Keep DataGridView in constant sync with DataSource

    Quote Originally Posted by maku220 View Post
    I am using same DataTable.
    The time stamp idea looks pretty good although it sounds pretty complex since this whole DataSource topic is completely new ground for me heh. Would you mind explaining or maybe give an example how can the code query for records that have a specific timestamp?

    Thanks
    It's actually quite simple.
    vb.net Code:
    1. 'Using MinValue means all data will be retrieved the first time.
    2. Private lastRequestTime As Date = Date.MinValue
    3.  
    4. Private ReadOnly table As New DataTable
    5. Private ReadOnly adapter As New OleDbDataAdapter("SELECT * FROM MyTable WHERE LastUpdateTime > @LastRequestTime", "connection string here") With {.FillLoadOption = LoadOption.PreserveChanges}
    6. Private ReadOnly parameter As OleDbParameter = adapter.SelectCommand.Parameters.Add("@LastRequestTime", OleDbType.Date)
    7.  
    8. Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    9.     GetData()
    10.  
    11.     'Bind data here.
    12. End Sub
    13.  
    14. Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    15.     GetData()
    16. End Sub
    17.  
    18. Private Sub GetData()
    19.     'Get only data that has changed since the last request.
    20.     parameter.Value = lastRequestTime
    21.  
    22.     'Update the last request time to now for next time.
    23.     lastRequestTime = Date.Now
    24.  
    25.     'Get the changed data.
    26.     adapter.Fill(table)
    27. End Sub

  8. #8
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: Keep DataGridView in constant sync with DataSource

    Sir thanks for the reply
    i would like to know
    (1) Does SQL server express 201x versions support the push model
    (2) I am using MySql , and in this regard is it possible to use middle a wear, actually what are these middle wears and how does they work, please give some in sight.
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Keep DataGridView in constant sync with DataSource

    Quote Originally Posted by make me rain View Post
    (1) Does SQL server express 201x versions support the push model
    As far as I'm aware, but I've never used it myself.
    Quote Originally Posted by make me rain View Post
    (2) I am using MySql , and in this regard is it possible to use middle a wear, actually what are these middle wears and how does they work, please give some in sight.
    It's just software. There's no magic to it. It connects to the database like any other software and then it sends the data out to each client in whatever way you deem appropriate.

Posting Permissions

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



Click Here to Expand Forum to Full Width