Update ado.net DataSet

  • Thread starter Thread starter skyjoker1
  • Start date Start date
S

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.
 
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.
 
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
 
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.
 
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
 
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.
Click to expand...

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.
 
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
 
SURE THERE IS A WAY! I do it all of the time.

call the function below with your update statement.

ex. UpdateSQLData("UPDATE CUSTOMERS SET ADDRESS='100 S. Street' WHERE
ID=100")



public void UpdateSQLData(string sqlString)
{
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(connectionString);

SqlCommand myCommand = new SqlCommand(sqlString, myConnection);

// Execute the command
myConnection.Open();

//HERE IS THE TRICK - This call only returns the number of records
updated!
myCommand.ExecuteNonQuery();
}
 
Back
Top