[dba-VB] Copy tables: Update or insert rows in a DataTableAdapter from another DataTableAdapter

Hi Charlotte

Thanks for that explanation, I can see why you do it this way.

My situation is somewhat different as no data will be live - the source is an Access mdb permanently linked to the SQL Server - and the import will be run at regular intervals. Thus, should a row or two be lost during an import they will be fetched at the next run.

As for the array compare, I will check out your idea of a temp table.

/gustav

>>> cfoust at infostatsystems.com 08-02-2008 02:16 >>>
I think the speed depends on how you use it and whether your BE is
Access or SQL Server. Doing it this way handles those pesky concurrency
issues. Plus, we don't know whether the row is going to be updated or
inserted when we try the update call.  

The ImportAdapter we call in the routine adds a handler for the
RowUpdated event of the DataAdapter.  When the event fires, we trap any
OleDbException and decide what to do about it.  If an update call
failed, we try creating a dataset clone and attempting to LoadDataRow
using an ItemArray of the items in the row.  If that works, we call the
ImportAdapter to update the row from the clone dataset and we set the
row's UpdateStatus to SkipCurrentRow.  If we tried an insert and it
failed with a duplicatevalue exception, we know the row exists, so we
acceptchanges on it, setModifed and then call the ImportAdapter to
update the row then we set the row's UpdateStatus to SkipCurrentRow.

And if you're updating live data, you don't have much choice because
someone else could change it between the time you accepted a changed row
and the time it was actually written back to the DB.

I don't know of a way to directly compare ItemArrays, but that doesn't
mean one doesn't exist.  I can see why you can't cast them as strings
though, since they can hold any kind of object.  What about importing
one ItemArray into a temp datatable and then trying to update it with
the other one?  If the update succeeds, they don't match.

Charlotte Foust

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com 
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, February 07, 2008 3:01 PM
To: dba-vb at databaseadvisors.com 
Subject: Re: [dba-VB] Copy tables: Update or insert rows ina
DataTableAdapter from another DataTableAdapter

Hi Charlotte

OK, now I see. Sounds pretty as the exact same task I have to carry out.

But isn't that terrible slow? I mean, for each row to import you call
the Update method of the DataTableAdapter which causes a roundtrip to
the database server. 
I was looking for a method to update the complete DateTable (or DataSet)
and then save the complete collection of updated/appended rows in on go.


However, right now my code saves the complete DataTable as all existing
rows are updated. I'm looking for method to check if a row actually is
changed or not. As the target and source rows come from two different
DataTableAdapters I cannot use Equal. It seems I have to compare the
content of the rows to look for a match.

This doesn't seem to work:

   targetRow.ItemArray == sourceRow.ItemArray

but perhaps this will:

   targetRow.ItemArray.ToString() == sourceRow.ItemArray.ToString()

Anyway, I'm looking for a general ans simple way - without looping the
fields - to compare two arrays.
Any suggestions?

/gustav


>>> cfoust at infostatsystems.com 07-02-2008 19:07 >>>
My last reply wasn't too enlightening.  What's going on here is we are
importing using a dataadapter to a table of the same name.  In other
words, we're updating an existing table and adding any new rows.  The
data may be from a file, a table, a dataset, or whatever, but by the
time it gets to this routine, it's in a datatable.  Normally, we're
importing from XML.

We create a copy of the table passed in using the Clone method so we
have the structure automatically.  For each datarow in the table passed
in,  we clear the clone's datarow collection and import the row into the
clone.  We then acceptchanges to get the row solidly into the clone
table and the SetModified to mark it as changed.  Then we pass that
clone with its single modified row to the update method of the
dataadapter, which takes care of shoving it into the existing table
we're updating.


Charlotte Foust

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com 
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, February 07, 2008 8:59 AM
To: dba-vb at databaseadvisors.com 
Subject: Re: [dba-VB] Copy tables: Update or insert rows ina
DataTableAdapter from another DataTableAdapter

Hi Charlotte

Thanks. Yes, my task is obviously slightly more complicated ... and, of
course, in most situations you operate on one database only.

I can see that you use the ImportRow method too. And as I read it, you
copy a row by clearing the target first and then importing the
source/remote row? But what does method Clear do here? As far as I know,
Clear "erases" the row and leave it - it does not delete it.

/gustav

>>> cfoust at infostatsystems.com 07-02-2008 17:31 >>>
The thing is, you aren't just copying a table.  It sounds like you're
only making a copy if one doesn't exist.  Otherwise, you're making sure
the records are in sync.  That's a bit more complicated than just
copying a table, so I can see why you didn't find any code specifically
for that.  A datatable has a Copy method, and that would be the logical
way to copy a table, if that's all you were doing.

Here's an example of an approach we use when importing a table.  The
ImportAdaptor method it calls simply returns an instantiated
OleDb.OleDbDataAdapter that has already retrieved a pre-build
dataadapter for handing that table.  Maybe it will give you some ideas:

    Public Sub ImportTable(ByVal table As System.Data.DataTable)
Implements IMiscData.ImportTable
        Dim da As OleDb.OleDbDataAdapter =
ImportAdapter(table.TableName)
        Dim dsTmp As DataSet = table.DataSet.Clone

        For Each rowRemote As DataRow In table.Rows
            dsTmp.Tables(0).Rows.Clear()
            dsTmp.Tables(0).ImportRow(rowRemote)

            dsTmp.Tables(0).Rows(0).AcceptChanges()
            dsTmp.Tables(0).Rows(0).SetModified()

            da.Update(dsTmp.Tables(0))
        Next

        dsTmp.Dispose()
    End Sub

Charlotte Foust