Update ado.net DataSet

5 Jul 2005 3:31 PM

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.

5 Jul 2005 4:26 PM

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.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

<skyjok***@hotmail.com> wrote in message

Show quoteHide quote

news:1120577462.029724.64240@g49g2000cwa.googlegroups.com...
>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.
>

5 Jul 2005 5:01 PM

Rick

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

5 Jul 2005 4:35 PM

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

5 Jul 2005 6:01 PM

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.

5 Jul 2005 8:24 PM

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

6 Jul 2005 3:39 PM

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

6 Jul 2005 1:05 AM

Steve Gerrard

<skyjok***@hotmail.com> wrote in message
Show quoteHide quote

news:1120577462.029724.64240@g49g2000cwa.googlegroups.com...
>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.

4 Aug 2005 4:16 AM

Kyle Foster

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();
}

*** Sent via Developersdex http://www.developersdex.com ***