Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Best way to copy a DataTable to a database via a DataAdapter?

1,554 views
Skip to first unread message

Dave

unread,
Feb 15, 2004, 4:11:05 PM2/15/04
to
I am a .NET newbie and I'm surprised to find almost no posts on this question; only a couple that weren't answered. I hope some kind soul answers this post.

I have a DataTable whose rows are in various RowStates. I want to copy the entire DataTable to a table in an external MS Access database accessed via an OleDbDataAdapter.

Using the DataAdapter.Update method doesn't work unless all the rows are in the Added state, which isn't the case. Since the RowState property is read-only, I can't assign this myself.

I thought using ExecuteNonQuery to do an INSERT of each DataTable row. But since MS Access doesn't support multiple SQL statements in ExecuteNonQuery, I would need to code up a loop doing one ExecuteNonQuery per row. I am concerned this will be too inefficient.

I am currently solving this problem by using the DataTable.Clone method to make a copy of the DataTable's structure, and then using LoadDataRow in a loop to add each row to the table copy. I then pass the copy to DataAdapter.Update.

All of these approaches seem like a kludge to me. Is there a better way?

Thanks for the help!

Dave

Scott M.

unread,
Feb 15, 2004, 5:12:01 PM2/15/04
to
The procedure is to call the DataAdapter's update method. The general
procedure is as follows:

To get the data in the first place...

Create a connection object (OleDBConection) and configure its connection
string
Create a command object (OledDBCommand) and configure its commandText
property (a SQL SELECT statement)
Create a DataAdapter object (OleDBDataAdapter)
Create a DataSet object (DataSet)
Call the .fill method of the DataAdapter and populate the DataSet with the
data.
A DataSet will now contain a DataTable with a copy of the original data.
Modify the data as you need to (the DataAdapter will know what records have
been modified or not)
Call the DataAdapter's .Update method (you can write your own update command
for the DataAdapter or configure the DataAdapter to create its own).


"Dave" <anon...@discussions.microsoft.com> wrote in message
news:7DFB64BA-87EA-4954...@microsoft.com...

Dave

unread,
Feb 15, 2004, 6:26:05 PM2/15/04
to
Sorry I don't think I stated my problem clearly enough.

I have a DataTable that is FILL'ed from Database#1. As the application is run, the data is changed, and the Update method is used to write the changes back to Database #1.

At some point, I want to write the contents of this DataTable to Database#2. The initial contents of this table in Database#2 is empty. At the time that I want to do this, all the rows in my table have had AcceptChanges called on them.

If I open a DataConnection to Database#2, create a DataAdapter, and call its Update method as you suggest, nothing happens because none of my table's rows are in the Added state.

What is the best way to copy this data to Database#2?

Thanks...

Dave

Chris Taylor

unread,
Feb 15, 2004, 6:42:11 PM2/15/04
to
Hi,

A sollution might be to insert the records from initial DataTable into a new
DataTable, the DataRows states will be 'Added' and passing this new
DataTable to the DataAdapter should insert the records into Database#2.

Hope this helps

--
Chris Taylor
http://dotnetjunkies.com/WebLog/chris.taylor/


"Dave" <anon...@discussions.microsoft.com> wrote in message

news:9DBD809F-2C06-451A...@microsoft.com...

Cor

unread,
Feb 16, 2004, 3:28:39 AM2/16/04
to
Hi Dave,

Dataset 2 does need the shema from database 2.
If you want to update it to database2.

You can get that with a sqldataAdapter.fillschema

I hope this helps?

Cor


Dave

unread,
Feb 16, 2004, 2:31:06 PM2/16/04
to
Thanks for the reply, Chris.

If you go back to my original post that started this thread you
will see that is exactly what I am doing. My original questions
(still not answered) were:

a) Is it better to make the DataTable copy as you suggest, or
b) Is it better use SQL INSERTs?, and
c) Is there a better alternative to (a) or(b)?

Below is my code for (a) and (b). I am wondering if the underlying
implementation of Update in approach (a) could be more effecient
(i.e. is it implemented in Native code?) than the loop of INSERTs
in approach (b) so as to make up the overhead of copying the table?
Or is there some still better approach not yet discussed???

Any comments would be appreciated!

Thanks...

Dave

// Approach (a):
DataTable copyTable = origTable.Clone();
copyTable.BeginLoadData();
foreach (DataRow row in origTable.Rows)
{
copyTable.LoadDataRow(row.ItemArray, false);
}
copyTable.EndLoadData();
dataAdapter.Update(copyTable);

// Approach (b):
OleDbCommand insertCommand = dataAdapter.InsertCommand;
foreach (DataRow row in origTable.Rows)
{
foreach (OleDbParameter param in insertCommand.Parameters)
{
param.Value = row[param.ParameterName];
}
insertCommand->ExecuteNonQuery();
}

----- Chris Taylor wrote: -----

0 new messages