Synchronising a DataRow in one DataSet with a structurally identical DataRow from another DataSet

From: Mark Rae (mark_at_mark-N-O-S-P-A-M-rae.co.uk)
Date: 12/05/04 

Date: Sun, 5 Dec 2004 15:40:33 -0000

Hi,

Supposing you had several remote MySQL databases, one for each client, each
of which contains a table that you need to amalgamate into a single local
SQL Server table on a nightly basis, for consolidated reporting etc.

a) The schema of the MySQL source tables is identical to the schema of the
SQL Server destination table, apart from the fact that the SQL Server table
has an initial integer field which denotes each client's unique client
identifier - this field, together with the primary key of the MySQL source
tables, makes a composite primary key in the destination SQL Server table.

b) The MySQL source tables have a timestamp field which is updated whenever
a record is added or updated, so you don't have to pull across every MySQL
record every time - only those which have been inserted or updated since the
last time you did it.

c) One of the fields in the MySQL table has a datatype of 'text'.

Since the current and anticipated number of new and updated records isn't
very high (a few thousand at an absolute maximum) would the best approach be
as follows:

1) For each client, fill an ADO.NET DataSet object with the MsSQL records
inserted or upated since the last time the process was run.

2) Iterate through each records in this source DataSet and try to fetch a
DataSet from the destination SQL Server table which matches the primary key.

If an empty DataSet is returned, we know we have to add a new record to the
destination SQL Server table, so we instantiate a DataRow object using the
<mySQLDataSet>.Tables(0).NewRow method.

If a DataSet with one row is returned, we know that we have to update an
existing record in the SQL Server table, so we instantiate a DataRow object
using the <mySQLDataSet>.Tables(0).Rows(0) method.

3) We synchronise the DataRow in the destination table with the DataRow from
the source table.

4) We call the AcceptChanges method of the destination DataRow object.

Question: is this the most efficient way of accomplishing the above? If not,
I'd be grateful to know your thoughts on a better way.

If so, is there an easy way to "synchronise" a DataRow from one DataSet with
a DataRow from another DataSet without having to iterate through all the
columns?

Any assistance gratefully received.

Best regards,

Mark Rae

Relevant Pages

  • Re: SQL server vs mySQL ???
    ... SQL Server has the broadest range of tools and support utilities of any database system on the planet--by far. ... MS SQL versus MySQL? ... As of 5.0, MySQL finally supports stored procedures, so they are very similar there (you can genericize your data access a bit due to sprocs in both). ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Transfer LARGE DB from MySQL to SQL Server 2K5 problems
    ... newsgroups are managed (and making that a selling point for MSDN ... forums, which, needless to say, drives me away from the MS web sites, and is ... I am going through the MySQL ODBC v3.51 driver, ... streaming across the wire to my SQL Server box. ...
    (microsoft.public.sqlserver.dts)
  • Re: Newbie, True and False
    ... All commands, Insert, Update etc work fine with MySql, but not with MsSql. ... First time working on an SQL database. ... will need to relearn when using sql server. ...
    (microsoft.public.sqlserver.clients)
  • Re: Access 2007 and MySQL
    ... Anyone tried MySQL as backend for recent Acc2k7 FE developments? ... You can however if you want choose SQL server for your back end and then you ... For the most part most your recordset code and most queries should work as ... Set rstRecords = CurrentDb.OpenRecordset ...
    (comp.databases.ms-access)
  • Re: SQL server vs mySQL ???
    ... The easiest way to support multiple databases is to work with a data access ... This question changes depending on which version of MySQL one has. ... * SQL Server has richer trigger support ...
    (microsoft.public.dotnet.framework.adonet)