-
Jul 28th, 2016, 08:52 AM
#1
Thread Starter
New Member
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.
-
Jul 28th, 2016, 07:10 PM
#2
Re: Keep DataGridView in constant sync with DataSource
Originally Posted by maku220
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.
-
Jul 29th, 2016, 05:06 AM
#3
Thread Starter
New Member
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
-
Jul 29th, 2016, 10:36 AM
#4
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
-
Jul 30th, 2016, 12:01 AM
#5
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
-
Jul 30th, 2016, 12:12 AM
#6
Re: Keep DataGridView in constant sync with DataSource
Originally Posted by make me rain
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.
-
Jul 30th, 2016, 12:23 AM
#7
Re: Keep DataGridView in constant sync with DataSource
Originally Posted by maku220
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:
'Using MinValue means all data will be retrieved the first time. Private lastRequestTime As Date = Date.MinValue Private ReadOnly table As New DataTable Private ReadOnly adapter As New OleDbDataAdapter("SELECT * FROM MyTable WHERE LastUpdateTime > @LastRequestTime", "connection string here") With {.FillLoadOption = LoadOption.PreserveChanges} Private ReadOnly parameter As OleDbParameter = adapter.SelectCommand.Parameters.Add("@LastRequestTime", OleDbType.Date) Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load GetData() 'Bind data here. End Sub Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick GetData() End Sub Private Sub GetData() 'Get only data that has changed since the last request. parameter.Value = lastRequestTime 'Update the last request time to now for next time. lastRequestTime = Date.Now 'Get the changed data. adapter.Fill(table) End Sub
Last edited by jmcilhinney; Jul 30th, 2016 at 12:26 AM.
-
Jul 30th, 2016, 12:37 AM
#8
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.
-
Jul 30th, 2016, 05:19 AM
#9
Re: Keep DataGridView in constant sync with DataSource
Originally Posted by make me rain
(1) Does SQL server express 201x versions support the push model
As far as I'm aware, but I've never used it myself.
Originally Posted by make me rain
(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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|