How to flush DataSet data back to the database

[Guest]

Hi all

I have a SQL Server database where I need to save some data.

By using the VS.NET wizard, I created a typed dataset. To fill a table with
data, I use the corresponding DataAdapter Fill method. After Fill method
returns, I can retrieve DataTable.Rows.Count property and see that all rows
are present.

The problem is that the data is only memory. How can save those data in the
database? I tried using Update of DataAdapter, AcceptChanges of DataSet,
AcceptChanges of DataTable but none worked.

Any help would be greatly appreciated.

Thanks
Jaime

 

[Marina]

What changes? You just retrieved the data, it's exactly as it was in the
database. Nothing to save.

If you read the documentation, you will see that AcceptChanges has nothing
to do with going back to the database server. It marks all updated/inserted
rows as Unmodified, and removes all deleted rows. So you should not be
calling this right before trying to update anything.

Since you are using the design-time wizard, it should have generated
update/insert/delete commands for you. You can call the Update method of the
DataAdapter and pass it the dataset, and it should be able to update.

However, if you really want to learn ADO.NET I suggest you never use the
wizard again and write all your code yourself. Just my opinion.

 

[Guest]

Hi Marina,

I didn't tell that I load DataTable with data from an Oracle database by
using a complex query. I need to save resulting rows to my own SQL Server
model database. As I said, I tried using Update method of the generated data
adapter but no rows were saved to the database.

This is the code:

cmd.CommandText = query;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("?", cie_id);
cmd.Parameters.AddWithValue("?",
dtpFechaTope.Value.ToString("dd/MM/yyyy"));
cmd.Parameters.AddWithValue("?",
dtpFechaTope.Value.ToString("dd/MM/yyyy"));

OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter.Fill(tabla); // "tabla" is my SQL Server DataTable

tablaAdapter.Update(tabla);


I agree with you that if I want to learn ADO.NET I should get rid of wizard
codes, but in this case I think that way is faster to code.

Jaime

 

[Marina]

Well, it's not really faster, because the wizard is hiding a lot of stuff,
so you end up not really learning much that way. Had you coded everything
by hand, you would have a better understand of ADO.NET, and probably not
needed to post here at all.

Just because you call Update on an adapter pointing to a different dataset,
doesn't mean that the adapter knows what to do with the data in the dataset.
How would it know that you are really trying to add those rows? Maybe it is
supposed to somehow update the rows? But how? Or update some, but insert
others?

It can't know. And if you look at the row state of the rows after the Fill,
you will see they are all Unmodified. So in the eyes of the adapter, there
are no pending changes to the dataset. So there is nothing to do.

You would have to do something like re-insert all the rows into a new
dataset, so that they would have an inserted row state, and then Update
would try to insert them.