Update ado.net DataSet

[skyjoker1]

I think what I'm trying to do is something relatively simple. I have
created a DataTable using the SQLDataAdapter.Fill method:

Dim da As New SqlDataAdapter("SELECT * FROM Trans", cn)
Dim ds As New DataSet
da.Fill(ds, "AllTrans")

I am currently editing the table records individually by selecting a
subset of the data using a DataView and using the DataRowView.BeginEdit
and DataRowView.EndEdit methods. For example:

Dim dv As New DataView(ds.Tables("AllTrans"))
dv.RowFilter = "A1 = 'ABC'"
Dim drv As DataRowView
For Each drv In dv
drv.BeginEdit()
drv("S1") = "NC"
drv.EndEdit()
Next

However I would like to instead update the DataSet using the equivalent
of an update statement, e.g. "UPDATE AllTrans SET S1 = 'NC' WHERE A1 =
'ABC'".

I am not trying to update the original data source.

Thanks.

 

[Miha Markic [MVP C#]]

Hi,

No, there is no such functionality in ado.net.
I think someone posted a notice in this newsgroup about a product that
supports sql statements over DataSet few days or weeks ago.

 

[Cor Ligthert [MVP]]

Skyjoker,

This looks strange to me, why are you not just getting a datatable that you
have to update using a "where" clause in the Select. Seems for me much
easier and cost much less time on the server, on the used network and in the
used computer..

Just my thought,

Cor

 

[Rick]

Thanks Miha. It's unfortunate that it can't be done through ado.net.

 

[Rick]

Hi Cor,

I'm currently doing it this way because I have numerous updates that I
want to do to the data, based on more complex criteria. I want to end
up with one DataTable that contains all of the original data with the
various updates applied.

 

[Cor Ligthert [MVP]]

Rick,

However what do you think that the processing behind the scene will be with
that SQL select command that you propose.

It will probably be very much less efficient as what you are doing now.

Just my thought,

Cor

 

[Steve Gerrard]

I think what I'm trying to do is something relatively simple. I have
created a DataTable using the SQLDataAdapter.Fill method:

Dim da As New SqlDataAdapter("SELECT * FROM Trans", cn)
Dim ds As New DataSet
da.Fill(ds, "AllTrans")

I am currently editing the table records individually by selecting a
subset of the data using a DataView and using the DataRowView.BeginEdit
and DataRowView.EndEdit methods. For example:

Dim dv As New DataView(ds.Tables("AllTrans"))
dv.RowFilter = "A1 = 'ABC'"
Dim drv As DataRowView
For Each drv In dv
drv.BeginEdit()
drv("S1") = "NC"
drv.EndEdit()
Next

However I would like to instead update the DataSet using the equivalent
of an update statement, e.g. "UPDATE AllTrans SET S1 = 'NC' WHERE A1 =
'ABC'".

I am not trying to update the original data source.

Thanks.

Maybe you can write yourself a sub, something like
UpdateTable(DTable, Filter, FieldName, NewValue)
with the same code structure you have above.

If there was such a SQL function for a DataTable, it would be doing essentially
the same thing.

 

[Nilesh.Desh]

Use case in select statement, it will make your code more efficient.

SELECT Case WHEN A1 = 'ABC' THEN 'NC' ELSE A1 END as A1
FROM Trans

This way you don't have to update the datatable.

Thanks

Nilesh