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?

thanksIt 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

[sonicsqwirl]

ok figured that part out... and it is 0 as far as i can tell

int count = sda.Update(ds, "NamesTest");

int count = 0 when you mouse over it in debug mode

[sonicsqwirl]

hee hee... scratch that... it equals 11

[jmcilhinney]

It's just a function returning a value like any other, so you simply assign it to a variableint rowsAffected = myAdapter.Update(myTable);or you can display it immediatelyMessageBox.Show("Rows affected: " + myAdapter.Update(myTable).ToString());

[sonicsqwirl]

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.

I tried to transfer the same data with the same insert statement to an identical table named NamesTest2 in the same database and the data transfered flawlessly..... i'm confused now....

thanks

[jmcilhinney]

Well the number returned by Update is the number of rows affected, so if it returns 11 then that should mean that it's inserted 11 rows. If it is genuinely working within the same database but not between databases then I am confused. Like I said, never done it myself but I don't see why it shouldn't be possible.

The other option would be to create a DataTable and get the schema from the second database using FillSchema. You could then get the data from the first database with a DataReader and create the new rows in the DataTable yourself. Then you should be able to Update the new data because it should know that the table is from that same database. Seems like a workaround for a problem that shouldn't exist but necessity and invention.

[sonicsqwirl]

well i appreciate the help... i'lll prolly asking more questions in the future... this project seems to keep getting bigger.

thanks a bunch

[exterminator]

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");This is wrong code! And hence will not do anything that you are trying to achieve. The correct and easier way of using the connection, datareader and dataset object in this particular scenario follows this order:
1. Open connection with database 1
2. Retrieve data into DataSet from this datasource
3. Close connection with database 1
and then
1. Open connection with database 2
2. Make a new copy of the earlier dataset. Make changes here. and call update with the second dataadapter.
3. Close connection with database 2
Look at your code above - I see both the connection.open calls at the end of the code just before the update call. How is the dataset going to fill up with data if the connection has not been initially opened?

Now, for the case where you would need to merge the data from the tables in these two datasets - you could use the merge() method with the dataset class. you could go ahead with that.

[jmcilhinney]

Look at your code above - I see both the connection.open calls at the end of the code just before the update call. How is the dataset going to fill up with data if the connection has not been initially opened?You'll note that I commented on the fact that these calls were unnecessary in a previous post. They make no difference to whether the data gets retrieved though, as the call to Fill is made several lines earlier, which implicitly opens and closes the associated connection. There is no call to Close the connection after the Update but this should not be an issue because the Update is not within a transaction so changes should be committed immediately.