How to flush DataSet data back to the database

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
Wed Jan 04 13:35:49 CST 2006

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.

"Jaime Stuardo" <JaimeStuardo@discussions.microsoft.com> wrote in message
news:C69BE336-CF2F-4947-99B4-A52C65E6BA43@microsoft.com...
> 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

JaimeStuardo
Wed Jan 04 14:05:02 CST 2006

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" wrote:

> 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.
>
> "Jaime Stuardo" <JaimeStuardo@discussions.microsoft.com> wrote in message
> news:C69BE336-CF2F-4947-99B4-A52C65E6BA43@microsoft.com...
> > 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
Wed Jan 04 14:15:36 CST 2006

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.

"Jaime Stuardo" <JaimeStuardo@discussions.microsoft.com> wrote in message
news:C3298E85-2EDB-4BC8-B7F9-6085ED9B1D75@microsoft.com...
> 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" wrote:
>
>> 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.
>>
>> "Jaime Stuardo" <JaimeStuardo@discussions.microsoft.com> wrote in message
>> news:C69BE336-CF2F-4947-99B4-A52C65E6BA43@microsoft.com...
>> > 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
>>
>>
>>