|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to flush DataSet data back to the databaseHi 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 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. Show quote "Jaime Stuardo" <JaimeStua***@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 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 Show quote "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" <JaimeStua***@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 > > > 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. Show quote "Jaime Stuardo" <JaimeStua***@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" <JaimeStua***@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 >> >> >>  |
|||||||||||||||||||||||
Other interesting topics