Maintain a local copy of a table from an external database table, ADO.NET

Question

We have built an application which needs a local copy of a table from another database. I would like to write an ado.net routine which will keep the local table in sync with the master. Using .net 2.0, C# and ADO.NET.
Please note I really have no control over the master table which is in a third party, mission critical app I don't wish to mess with.

For example Here is the master data table:

ProjectCodeId   Varchar(20)   [PK]
ProjectCode     Varchar(20)
ProjectDescrip  Varchar(50)
OtherUneededField    int
OtherUneededField2   int

The local table we need to keep in sync...

ProjectCodeId   Varchar(20)   [PK]
ProjectCode     Varchar(20)
ProjectDescrip  Varchar(50)

Perhaps a better approach to this question is what have you done in the past to this type of problem? What has worked best for you or should be avoided at all costs?

My goal with this question is to determine a good way to handle this. So often I am combining data from two or more disjointed data sources. I haven't included database platforms for this reason, it really shouldn't matter. In this current situation both databases are MSSQL, but I prefer the solution not use linked databases or DTS, etc.

Sure, truncating the local table and refilling it each time from the master is an option, but with thousands of rows I don't think this is very efficient. Do you?

Answer 1

I would push in the direction where the application that is inserting the data would insert into one db/table then the other in the same function. Make the application do the work, the db will be pushed already.

Answer 2

Some questions - what db platform? how are you using the data?

I'm going to assume you're just using this data as a lookup... and as you have no timestamp and no ability modify the existing table, i'd just blow away the local copy periodically and pull it down from the master table again.

Unless you've got a hell of a lot of data the overhead for this should be pretty small.

If you need to synch back to the master table, you'll need to do something a bit more exotic.

Answer 3

Can you use SQL replication? This would be preferable to writing code to do it no?

Answer 4

EDIT: First, recognize that what you are doing is hand-rolled replication and replication is never simple. You need to track and apply all of the CRUD state changes. That said, ADO.NET can do this.

To track changes to the source you can use Query Notification with your source database. This requires special permission against the database so the owner of the source database will need to take action to enable this solution. I haven't used this technique myself, but here is a description of it.

See "Query Notifications in SQL Server (ADO.NET)"

Query notifications were introduced in Microsoft SQL Server 2005 and the System.Data.SqlClient namespace in ADO.NET 2.0. Built upon the Service Broker infrastructure, query notifications allow applications to be notified when data has changed. This feature is particularly useful for applications that provide a cache of information from a database, such as a Web application, and need to be notified when the source data is changed.

To apply changes from the source db table you need to retrieve the data from the target db table, apply the changes to the target rows and post the changes back to the target db.

To apply the changes you can either 1) Delete and reinsert all of the rows (simple), or 2) Merge row-by-row changes (hard).

Delete and reinsert is self explanatory, so I won't go into detail on that.

For row-by-row change tracking here is an approach. (I am assuming here that Query Notification doesn't give you row-by-row change information, so you have to calculate it.)

• You need to determine which rows were modified and identify inserted and deleted rows. Create a DataView with a sort for each table to get a Find method you can use to lookup matching rows by ID.
• Identify modified rows by using a datetime/timestamp column, or by comparing all field values. Copy modified values to the target row.

• Identify added and deleted rows by looping over the respective table DataViews and using the Find method of the other DataView to identify rows that do not appear in the first table. Insert or delete rows from the target table as required. (The Delete method doesn't remove the row but marks it for deletion by the TableAdapter Update.)

  Good luck!

+tom