Comparing Two Databases and Synchronizing Them

[Brian Pelton]

I have two databases, Source and Target. Data will always flow from the
source to the target, never the other way around. Target database is
used for reporting.

I want to be sure that I'm not missing some ADO.NET functionality; it
seems like this is a common problem and ADO.NET might take care of it.

I have written code to pull the data for a given table from source and
from target and compare them to find the differences. This is working
just fine. To find the changes, I iterate over each row and compare
each column's values - because the Merge function doesn't do this for me.

Now that I have a DataTable that contains only the changes, I want to
update the target database. I'm my ADO.NET book, it says "If you're
deleting, inserting, and updating data with known parameters, using
Execute methods is faster than using DataAdapter's Update method."

Okay, so, now I should write SQL statements when I'm doing my comparison
because that will be faster? I thought I could just use the
DataAdapter to update Target in one shot, just by passing the DataTable
that has the changes.

Is there a better way to do this???

--Brian

 

[Chris van Bergen]

Hello Brian,

As far as I can tell you're doing the right thing by creating your own sql-update/insert-commands.
It's true that it will be probably faster than using the DataAdapter.Update,
because under the hood the DataAdapter will perform similar actions.
So, is there a better way? I don't think so (and if there is I sure would
like to know too). The choice between the two will be a choice between 'neeter'
code by using the DataAdapter.Update and the faster code by using self-made
sql-statements.

However, when you use the DataAdapter.Update(DataTable), don't forget to
make sure that the rows in your DataTable have the DataRowState.Modified
or DataRowState.Added, otherwise they will not be placed in your database.

HTH
Christiaan

 

[Cor Ligthert [MVP]]

Brian,

The author of the book will probably mean: "When you have not first filled
them in a dataset".

The dataadapter uses only executecommands to delete, update and insert your
database. If it is in the way you describe it, than I am curious which
Adonet book you use.

The benefit from the dataadapter is, that is standards uses delete, update
and insert commands that first asks if the data has not changed meanwhile by
another user.

"UPDATE Employees SET FirstName = @Firstname WHERE (EmployeeID =
@Original_Employe" & _
"eID) AND (FirstName = @Original_FirstName); SELECT EmployeeID,
FirstName FROM Em" & _
"ployees WHERE (EmployeeID = @EmployeeID)"

I assume you are not using SQL Server because than I would (although this is
not really my speciality) ask: "Why are you not using replication?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replover_694n.asp

I hope this helps,

Cor