Charlotte Foust
cfoust at infostatsystems.com
Thu Feb 7 10:31:25 CST 2008
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
-----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:36 AM
To: dba-vb at databaseadvisors.com
Subject: Re: [dba-VB] Copy tables: Update or insert rows ina
DataTableAdapter from another DataTableAdapter
Hi Charlotte and Stuart
That's a good idea - if the code works. I have not much hair left after
having browsed thousands of sites (so it feels). I thought it would be a
simple job to locate code somewhere around that I could modify, but no.
Well, yes, but that is basic code snippets working with connection
strings and SQL commands and so on for ASP kiddies. This must be very
special, though I thought it to be some kind of standard routine. But
no.
So again, should you ever have a task like the subject to do, make a
note of this method.
Main issue is that the DataTableAdapter wizard creates a buggy SQL
Update command which raised the error when you finally call the Update
method of the DataTableAdapter:
"Concurrency violation: the UpdateCommand affected 0 of the expected 1
records."
The key to solve this I found here:
http://geekswithblogs.net/gaijin42/archive/2007/09/05/update_dataset_wit
h_new_rows_acceptchanges_setmodified_orm_business_objects.aspx
During testing I received first the "Concurrency violation" message and
only a Select statement was found for the Update SQL command.
Later - somehow - the wizard generated the full Update SQL command with
a bunch of parameters - and that failed too.
Then I edited the Update SQL as described by Jason to include the
parameter for the primary key only. That worked!
Finally, I found that the simplest method to copy a row of one type to a
row of another type (remember, source and target are from different
DataTableAdapters) is to copy the ItemArray.
So now I can update and append to the target rows from the source rows
in one go with this code:
// DataTableAdapter to copy from.
VrsSourceTableAdapters.DataTableKundeTableAdapter
kundeAdapterS =
new
VrsSourceTableAdapters.DataTableKundeTableAdapter();
VrsSource.DataTableKundeDataTable kunderS;
// DataTableAdapter to copy to.
VrsTargetTableAdapters.DataTableKundeTableAdapter
kundeAdapterT =
new
VrsTargetTableAdapters.DataTableKundeTableAdapter();
VrsTarget.DataTableKundeDataTable kunderT;
// DataTable for source.
kunderS = kundeAdapterS.GetDataVrsKunder();
// DataTable for target.
kunderT = kundeAdapterT.GetDataVrsKunder();
// 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.DataTableKundeRow foundRow = null;
foreach (VrsSource.DataTableKundeRow kundeRowS in
kunderS)
{
// Value of primary key to locate.
key = kundeRowS.kundeNr;
// Find a matching row in the target.
foundRow = kunderT.FindByKundeNr(key);
if (foundRow == null)
{
// Row is not found.
// Set the RowState of the source row as Added.
kundeRowS.SetAdded();
// Append it to the target preserving the
RowState.
kunderT.ImportRow(kundeRowS);
}
else
{
// Row is found.
// Copy full content from source row to target
row.
// This sets RowState of the target row to
Modified.
foundRow.ItemArray = kundeRowS.ItemArray;
// Do NOT call AcceptChanges here as this would
clear property RowState.
}
}
Console.WriteLine("s: " + kunderS.Count.ToString());
Console.WriteLine("t: " + kunderT.Count.ToString());
// Write back to the database table the updated target
DataTable.
kundeAdapterT.Update(kunderT);
// Call AcceptChanges if needed for further processing.
// kunderT.AcceptChanges();
Still, if anyone have a better method, I'm all ears. This is by no means
an expert area for me.
/gustav
>>> cfoust at infostatsystems.com 07-02-2008 02:25 >>>
I've fallen over that one before too, and the best way to avoid that is
to put the code in a public helper class and then call it when you need
to do that task.
Charlotte Foust
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Wednesday, February 06, 2008 3:14 PM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] Copy tables
Glad it's not just me trying to get my head around .Net and data at the
moment. :-) After 15 years of Access and classic VB development, there's
so much new stuff to learn here :-(
On 6 Feb 2008 at 14:30, Gustav Brock wrote:
> Hi Stuart et al
>
> Oh my, make a note on this should you ever wish to copy data between
DataSets or DataTables with method ImportRow.
>
> The secret is the value of property RowState of the individual rows.
Rows read from a table have a RowState of Unchanged.
> If a row is copied to another DataSet/DataTable by method ImportRow,
RowState remains Unchanged.
>
> In my code sample below, I marked rows as Modified with method
SetModified.
>
> The Update method of the DataTableAdapter, which is called to write
the data rows to the underlying table, is quite clever as it doesn't
touch rows with RowState Unchanged.
> However, rows with RowState Modified will only be used to update
existing records in the target table, thus it will not attempt to append
or insert any such row.
>
> For a row to be inserted by the later Update call, it has to have
RowState set to Added.
>
> So, for my code below, I have to decide if a row should update an
existing record, or if it should be appended, or if it should be ignored
- and then call the method SetModified or SetAdded or nothing:
>
> // Mark row as modified.
> kundeRowS.SetModified();
> or:
> // Mark row as new.
> kundeRowS.SetAdded();
>
> When I changed the code from SetModified to SetAdded and replaced the
Fill method with:
>
> kundeAdapterT.Update(kunderT);
>
> all records were written to the target table.
>
> Of course, if you can write your code to add rows not using method
ImportRow - with LoadDataRow or like:
>
> kunderT.Rows.Add(rowToAdd);
>
> RowState of those rows, I guess, will be switched to Added
automatically.
>
> /gustav
>
>
> >>> Gustav at cactus.dk 05-02-2008 18:02 >>>
> Hi Stuart et al
>
> No there is no GUI, just code.
> I have some code like this. It add rows but will not save them to the
database table:
>
> VrsSourceTableAdapters.DataTableKundeTableAdapter
kundeAdapterS =
> new
VrsSourceTableAdapters.DataTableKundeTableAdapter();
> VrsSource.DataTableKundeDataTable kunderS;
>
> VrsTargetTableAdapters.DataTableKundeTableAdapter
kundeAdapterT =
> new
VrsTargetTableAdapters.DataTableKundeTableAdapter();
> VrsTarget.DataTableKundeDataTable kunderT;
>
> kunderS = kundeAdapterS.GetDataVrsKunder();
> kunderT = kundeAdapterT.GetDataVrsKunder();
>
> foreach (VrsSource.DataTableKundeRow kundeRowS in
kunderS)
> {
> Console.WriteLine("Kunde: " + kundeRowS.navn +
"");
> Console.WriteLine(" Kundenr: " +
kundeRowS.kundeNr + "");
> // Mark row as modified.
> kundeRowS.SetModified();
> kunderT.ImportRow(kundeRowS);
> }
>
> Console.WriteLine("s: " + kunderS.Count.ToString());
> Console.WriteLine("t: " + kunderT.Count.ToString());
>
> kundeAdapterT.FillVrsKunder(kunderT);
>
> Target table is residing in MSDE.
> Count of s returns the count of records to add.
> Count of t returns existing records + count of records to add.
>
> What am I missing?
>
> /gustav
>
>
> >>> stuart at lexacorp.com.pg 05-02-2008 00:06:55 >>>
> Do you mean have the user physically browse through the rows looking
for specific records?
>
> If that is the case, I would probably go with a DatagridView bound to
the source data.
> When the user selects a row (double click, highlight and click a
> button or whatever), read the row data and feed it to a
TableAdapter.Insert on the target table.
>
>
> On 4 Feb 2008 at 18:41, Gustav Brock wrote:
>
> > Hi all
> >
> > I have created two tableadapters, a source and a target, and need to
> > browse through the source looking for rows and, when a row meeting
> > some conditions is found, copy this to the target. What is the best
> > method?
> >
> > /gustav
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com