Inserting a DataSet into a Database table

[sonicsqwirl]

the problem:
I'm trying to get information from one database table and load it into a dataset using OleDbDataAdapters. I can do that part successfully. I think need to take that DataSet and insert the WHOLE thing into a different database. How do i get the 2nd DataAdapter to get the data from the dataset?

Eventually to add to my frustration, i'll need to merge 2 datasets and do the same thing?

Any suggestion as to how to accomplish this?

Thanks in advance,
Scott

[jmcilhinney]

First off, set the AcceptChangesDuringFill property of the DataAdapter to False. This will leave all retrieved rows marked as Added when you call Fill. Now create an appropriate Command object with the required SQL statement and a connection to the correct database and assign it to the InsertCommand property of the DataAdapter. Then you just call Update on the adapter and pass the DataSet (if there is only one table) or the appropriate DataTable (if there are more than one). Because the rows are still marked as Added, they will be inserted into the new database.

[sonicsqwirl]

Ok I think I've done what you suggest, and it builds fine... and runs... but no data is written to the second dataset...here is the code

string Conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\db1.mdb";
string Conn3 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\db3.mdb";

private void button1_Click(object sender, System.EventArgs e)
{
OleDbConnection xlConn = new OleDbConnection(Conn);
OleDbCommand selCMD = new OleDbCommand("Select * from NamesTest", xlConn);
OleDbDataAdapter sda = new OleDbDataAdapter();
sda.AcceptChangesDuringFill = false;
sda.SelectCommand = selCMD;
DataSet ds = new DataSet();
sda.Fill(ds, "NamesTest");
xlDataGrid.DataSource=ds.Tables["NamesTest"].DefaultView;
//xlDataGrid.DataBind();
//xlConn.Close();

OleDbConnection xlConn3 = new OleDbConnection(Conn3);
OleDbCommand insCmd = new OleDbCommand("INSERT INTO NamesTest (ID, FirstName, LastName) VALUES (?, ?, ?)", xlConn3);

insCmd.Parameters.Add("@ID", OleDbType.Integer, 5, "ID");
insCmd.Parameters.Add("@FirstName", OleDbType.Char, 20, "FirstName");
insCmd.Parameters.Add("@LastName", OleDbType.Char, 20, "LastName");

sda.InsertCommand = insCmd;
xlConn.Open();
xlConn3.Open();
sda.Update(ds, "NamesTest");

[deesan]

If you need to transfer big amount of data you should use DataReader instead of DataSet.

[sonicsqwirl]

how would a dataReader be more advantageous... this program will be transfering about 1000 records every time its ran... so i need pretty quick and efficient.

thanks

[torrud]

1.000 records handles a dataset without any problems. If you had 1.000.000 records then I would prefer DataReader and DataWriter.

[sonicsqwirl]

ok so DataSets will work with the data, but why is the data not being updated in the second database (xlConn3)?

thanks

[jmcilhinney]

Your code looks fairly good. I'd say that your going to have to do some debugging. Confirm that there are new rows in the table by calling GetChanges. Use the return value of Update to see how many rows were affected by the call.

Forget the calls to Open the Connections. They are not needed. When calling Fill or Update, the DataAdapter will open and close the connection if it is not already open. The only reason to do it yourself is if you are doing multiple Fills or Updates over the same connection. Opening the connection yourself saves it being closed and reopened between calls.

As for a DataReader, using a DataReader is faster than using a DataAdapter. The problem is that you have to write the code to populate the DataTable yourself, and you will have to use a DataTable or else you'll have to insert each row into the new database individually. This would more than offset any speed gain from using a DataReader.

On the point of DataSets, it seems that everyone loves them. A DataSet is a way of grouping multiple DataTables and the DataRelations between them. If there are no relations, and especially if there is only one table, just use a DataTable directly .

[jmcilhinney]

1.000 records handles a dataset without any problems. If you had 1.000.000 records then I would prefer DataReader and DataWriter.

I just repped you because I thought you were a genius for suggesting the DataWriter that I've never heard of. Then I had a look in the help to read about it and I discover that the reason I haven't heard of it is because it doesn't exist. Gimme that rep back!

[sonicsqwirl]

when i use
xlDataGrid3.DataSource=ds.GetChanges(DataRowState.Added);

the datagrid will show the rows that needed to be added... so its gotta be something with my insert command to the 2nd database (xlconn3) aye?

thanks

[jmcilhinney]

when i use
xlDataGrid3.DataSource=ds.GetChanges(DataRowState.Added);

the datagrid will show the rows that needed to be added... so its gotta be something with my insert command to the 2nd database (xlconn3) aye?

thanks

It would appear so. Without knowing the database structure the code looks good to me. What value does Update return?

[sonicsqwirl]

i'm kind of new to this... what does

"What value does Update return?" mean?

sorry for the ignorance... my first C# project and it's kinda a big one

thanks

[sonicsqwirl]

the database structure i'm using for the test databases

db1 has a table named NamesTest, with ID, FirstName, LastName as field names

db3 is exactly the same just with no data at all in the database... db1 has 10 records in the NamesTest table.

[jmcilhinney]

The Update method of the DataAdapter is actually a function, i.e. it returns a value, specifically the number of rows that were affected, i.e. deleted or inserted or updated, by the call. If there are rows to be affected and it returns zero then that is odd. With Added rows and a valid InsertCommand, I would think that the Call to Update should return a value greater than zero or throw an exception. Anything else is unacceptable, **** it!

Maybe you could try the same thing with two tables in the same database. If that works but it doesn't between databases then that must be the issue. I've never actually tried this myself but I can't see why there should be a problem.

[sonicsqwirl]

ok i feel really dumb now, but how do i know what number Update returns?

sorry again