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

[Guest]

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.]

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).

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.

 

[Guest]

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]

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/

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.

 

[Cor]

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

 

[Guest]

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?, an
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..

Dav

// 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: ----

Hi

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

Hope this help

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

Sorry I don't think I stated my problem clearly enough

run, the data is changed, and the Update method is used to write the change
back to Database #1Database#2. The initial contents of this table in Database#2 is empty. A
the time that I want to do this, all the rows in my table have ha
AcceptChanges called on themits Update method as you suggest, nothing happens because none of my table'
rows are in the Added state