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

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



_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com