Gustav Brock
Gustav at cactus.dk
Fri Feb 8 10:41:39 CST 2008
Hi Charlotte and Stuart et al
The hash code of a row cannot be used as it is based on the parent (name of DateTable or DataSet object) and the position of the row in this, not the content. And ToString of the ItemArray and other objects just returns the name of the object, not the content or value of it.
However, I located a very simple method to compare two arrays with "normal" content - not BLOBS or binaries - without having to code loops and so on.
Once again, the .Net framework came to rescue - this time with Concat which (also) is able to concatenate the rows of an array.
So:
// DataTableAdapter to copy from.
VrsSourceTableAdapters.DataTableOrdreTableAdapter ordreSAdapter =
new VrsSourceTableAdapters.DataTableOrdreTableAdapter();
VrsSource.DataTableOrdreDataTable ordreS;
// DataTableAdapter to copy to.
VrsTargetTableAdapters.DataTableOrdreTableAdapter ordreTAdapter =
new VrsTargetTableAdapters.DataTableOrdreTableAdapter();
VrsTarget.DataTableOrdreDataTable ordreT;
// DataTable for source.
ordreS = ordreSAdapter.GetDataVrsOrdrer();
// DataTable for target.
ordreT = ordreTAdapter.GetDataVrsOrdrer();
// Read each row from the source rows.
// Update those found in the target table.
// Append those not found in the target table.
// Variable to hold primary key to locate.
Int32 key;
// Create row to hold a found row in the target.
VrsTarget.DataTableOrdreRow foundRow = null;
// Variables to hold hash codes of row contents.
int hashSource;
int hashTarget;
foreach (VrsSource.DataTableOrdreRow ordreSRow in ordreS)
{
// Value of primary key to locate.
key = ordreSRow.ordreNr;
// Find a matching row in the target.
foundRow = ordreT.FindByOrdreNr(key);
if (foundRow == null)
{
// Row is not found.
// Set the RowState of the source row as Added.
ordreSRow.SetAdded();
// Append it to the target preserving the RowState.
ordreT.ImportRow(ordreSRow);
}
else
{
// Row is found.
Console.WriteLine(" Found: " + key.ToString());
// Calculate hash code to see if source row and target row match.
hashSource = String.Concat(ordreSRow.ItemArray).GetHashCode();
hashTarget = String.Concat(foundRow.ItemArray).GetHashCode();
Console.WriteLine(" - Hash: " + hashSource.ToString() + " - " + hashTarget.ToString());
if (hashSource != hashTarget)
{
// The source row is updated.
Console.WriteLine(" Modified OrdreNr: " + ordreSRow.ordreNr + "");
// Copy full content from source row to target row.
// This sets RowState of the target row to Modified.
foundRow.ItemArray = ordreSRow.ItemArray;
// Do NOT call AcceptChanges here as this would clear property RowState.
}
}
}
// Write back to the database table the updated/appended rows of target DataTable.
ordreTAdapter.Update(ordreT);
Of course, if you have juxtaposed columns with similar content like:
Val1 Val2 Concatenated string
3 44 344
34 4 344
just concatenating these may in some cases fail as the right column shows, but that is not the case for my current task.
So now I only update modified records and append new records leaving the bunch of not modified records untouched!
/gustav
>>> Gustav at cactus.dk 08-02-2008 00:00 >>>
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