I have a VB.Net 2008 windows application that reads data from SQL Server. Some of the tables have really large number of rows that need to come back. This data is sent over the internet, so that also affects speed. So for example, I have a table that has 100,000 rows in it... the first time I would not have anything locally, so I would get all 100,000 rows. (There may be 20 different people reading and writing to this table, so it is not exclusive to 1 user.) The second time I read it, I want to recognize that only 5 rows changed and 2 were added and 1 was deleted. Somehow, I only want to get those 7 rows and know to remove the deleted row from my local cache database.
So here are my questions:
1. What is the best format to store this data locally? (Access MDB, XML or what is the fastest)
2. How do I only read the differences instead of all the rows?
3. How is the best way to get it from the dataset result coming back from SQL Server into the locally stored cached format? If I want to store it in XML, I can simply use dataset.ReadXML and dataset.WriteXML ...seems like the simplest, fastest way to locally read and write the entire result set.
As far as sending data back to the server, I can use GetChanges on a datatable to determine what rows have changed and only send back the differences.
I read and write to the database via a webservice, so the client will not have direct access to the SQL database, the client must pass everything through the webservice.