[dba-VB] Copy tables: Update or insert rows in a 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