We help IT Professionals succeed at work.
Get Started

Best practices for synchronizing differences from the server to the client

651 Views
Last Modified: 2012-08-14
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.
Comment
Watch Question
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This problem has been solved!
Unlock 1 Answer and 20 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE