executing sql against a dataset

Hi all


is there a way to issue sql commands against a dataset.

i mean can we execute an update command on the dataset.

like.. update <datatable> set <somecol> = <somevalue> where <someothercol> =
<someothervalue>


Im not asking about the exact synatax as mentioned above.. but i m just
asking if there is a way to update multiple rows of single col in one go.

thanks

Noor

Miha
Thu Jan 22 11:00:49 CST 2004

Hi Noor,

"Noor" <noorrocks@softhome.net> wrote in message
news:eZOrZhQ4DHA.1592@TK2MSFTNGP10.phx.gbl...
> Hi all
>
>
> is there a way to issue sql commands against a dataset.
>
> i mean can we execute an update command on the dataset.
>
> like.. update <datatable> set <somecol> = <somevalue> where <someothercol>
=
> <someothervalue>

No.

>
> Im not asking about the exact synatax as mentioned above.. but i m just
> asking if there is a way to update multiple rows of single col in one go.

No, but you might consider filtering rows by using either DataTable.Select
method or DataView and then loop on rows and change them as you want.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Mohamoss
Thu Jan 22 11:13:55 CST 2004

Hi Noor

The answer is no you can not do that directly to the dataset .
you have only the clear method that clear all the data in the dataset.

However there are 2 workarounds to that

1 ?

a- write the dataset to an Xml file using the WriteXml method of the
dataset

b- Use the classes of the namespace System.Xml which does allow you to
queries on the xml file

c- clear the dataset using the clear method

d- refill the dataset with the modified Xml file using the ReadXml method
of the dataset

2- do a function that does the querying depending on the input parameters
that are sent to it.



i hope this would be of any help



for example this code do deletion from a table in a dataset depending
on values inside the datarow

myset = new DataSet();

mytable = new DataTable("trial");

mycolumn = new DataColumn("col1" , Type.GetType("System.Decimal"));

mytable.Columns.Add(mycolumn);

DataRow myrow = mytable.NewRow();

myrow["col1"]= 11.1;

mytable.Rows.Add(myrow);

mytable.Rows.Add(new object[]{12.2});

mytable.Rows.Add(new object[]{14.7});

myset.Tables.Add(mytable);

DataRowCollection r = mytable.Rows;




for( int idx = 0; idx < r.Count; idx++ )

{

DataRow row = mytable.Rows[ idx ];

if( row.RowState == DataRowState.Deleted )

{

if( row[ mycolumn, DataRowVersion.Original ].ToString()== "12.2" )

{

r.RemoveAt( idx );

idx--;

}

}

else

{

if( row[ mycolumn].ToString() == "12.2" )

{

r.RemoveAt( idx );

idx--;

}

}

}




--
Mohamed Mossad
Microsoft GTSC Developer support for Middle East


"Noor" <noorrocks@softhome.net> wrote in message
news:eZOrZhQ4DHA.1592@TK2MSFTNGP10.phx.gbl...
> Hi all
>
>
> is there a way to issue sql commands against a dataset.
>
> i mean can we execute an update command on the dataset.
>
> like.. update <datatable> set <somecol> = <somevalue> where <someothercol>
=
> <someothervalue>
>
>
> Im not asking about the exact synatax as mentioned above.. but i m just
> asking if there is a way to update multiple rows of single col in one go.
>
> thanks
>
> Noor
>
>

Cor
Thu Jan 22 11:39:32 CST 2004

Hi Noor,

If you want that you can make your own procedure by instance
executedataset(mydataset,selectcolumn, selectstring)

And process that than in the way Miha said.

Cor

> is there a way to issue sql commands against a dataset.
> i mean can we execute an update command on the dataset.
> like.. update <datatable> set <somecol> = <somevalue> where <someothercol>
=
> <someothervalue>

Maxim
Thu Jan 22 12:02:39 CST 2004

Noor,
This will be possible to do with ADO.NET 2.0 Whidbey release. Batch
Updates. I would not use Data Adpater for this purpose, just write you own
database mapper to process the updates!

Maxm

[www.ipattern.com do you?]
"Cor" <non@non.com> wrote in message
news:uGsd57Q4DHA.2348@TK2MSFTNGP09.phx.gbl...
> Hi Noor,
>
> If you want that you can make your own procedure by instance
> executedataset(mydataset,selectcolumn, selectstring)
>
> And process that than in the way Miha said.
>
> Cor
>
> > is there a way to issue sql commands against a dataset.
> > i mean can we execute an update command on the dataset.
> > like.. update <datatable> set <somecol> = <somevalue> where
<someothercol>
> =
> > <someothervalue>
>
>

Miha
Thu Jan 22 12:39:58 CST 2004

Hi Maxim,

"Maxim V. Karpov" <max@ipattern.com> wrote in message
news:uN3FRDR4DHA.2572@TK2MSFTNGP09.phx.gbl...
> Noor,
> This will be possible to do with ADO.NET 2.0 Whidbey release. Batch
> Updates. I would not use Data Adpater for this purpose, just write you own
> database mapper to process the updates!

Batch updates mean that adapter will create all necessary (or at least some)
sql commands and batch fire them to server as opposite to now, when one
statament is issued at the time..

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com