How can I update a an entire table in a dataset?

Hello, any help will be appreciated :)

I have a dataset filled with data from a database table. Now I want to do an
update command that will effect all rows in a specified table in this dataset
(something like: "UPDATE TableName SET RowName = RowName + 10")

How can I do it without executing it individually for each row?

Thanks.

William
Mon Feb 04 12:05:11 CST 2008

You can't. But there is nothing to stop you from doing this on the DBMS
engine--where it should be executed. Bringing rows to the client--especially
all the rows is ... challenged. Keep the data on the server and let the
engine do it's work. This avoids 2 round trips per row to fetch and post the
updates.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhikerâ??s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Stren Assaf" <StrenAssaf@discussions.microsoft.com> wrote in message
news:8CBCD5F3-B6C7-4846-8C68-BD2D9C88F4B5@microsoft.com...
> Hello, any help will be appreciated :)
>
> I have a dataset filled with data from a database table. Now I want to do
> an
> update command that will effect all rows in a specified table in this
> dataset
> (something like: "UPDATE TableName SET RowName = RowName + 10")
>
> How can I do it without executing it individually for each row?
>
> Thanks.
>

Cor
Mon Feb 04 12:30:06 CST 2008

Stren,

What is a RowName?

Rows don't have names, they are an enumeration inside the DataTable from the
type DataRow.
(DataRow Items have names).

Cor

StrenAssaf
Tue Feb 05 00:56:00 CST 2008

Cor,

First thanks for your replay.

I was mistaken writing RowName when irt should be ColumnName.
In short i just want to execute an update operation on all the rows in the
dataset table.

"Cor Ligthert[MVP]" wrote:

> Stren,
>
> What is a RowName?
>
> Rows don't have names, they are an enumeration inside the DataTable from the
> type DataRow.
> (DataRow Items have names).
>
> Cor
>

StrenAssaf
Tue Feb 05 01:02:00 CST 2008

William,

First thanks for your reply.

The table information in the dataset table is coming from an XML and there
is no DBMS engine involved in the application...

So if there is no way to update all rows at once what will be the best way
doing it one by one?

"William Vaughn" wrote:

> You can't. But there is nothing to stop you from doing this on the DBMS
> engine--where it should be executed. Bringing rows to the client--especially
> all the rows is ... challenged. Keep the data on the server and let the
> engine do it's work. This avoids 2 round trips per row to fetch and post the
> updates.
>
> --
> __________________________________________________________________________
> William R. Vaughn
> President and Founder Beta V Corporation
> Author, Mentor, Dad, Grandpa
> Microsoft MVP
> (425) 556-9205 (Pacific time)
> Hitchhikerâ??s Guide to Visual Studio and SQL Server (7th Edition)
> ____________________________________________________________________________________________
> "Stren Assaf" <StrenAssaf@discussions.microsoft.com> wrote in message
> news:8CBCD5F3-B6C7-4846-8C68-BD2D9C88F4B5@microsoft.com...
> > Hello, any help will be appreciated :)
> >
> > I have a dataset filled with data from a database table. Now I want to do
> > an
> > update command that will effect all rows in a specified table in this
> > dataset
> > (something like: "UPDATE TableName SET RowName = RowName + 10")
> >
> > How can I do it without executing it individually for each row?
> >
> > Thanks.
> >
>

KerryMoorman
Tue Feb 05 11:40:03 CST 2008

Stren,

You can iterate over the rows in the datatable with a For Each statement. Or
you could use a For Next statement and loop from 0 to the number of rows - 1.

Inside the loop, you will need to change some column's data in each row.

Kerry Moorman





"Stren Assaf" wrote:

> William,
>
> First thanks for your reply.
>
> The table information in the dataset table is coming from an XML and there
> is no DBMS engine involved in the application...
>
> So if there is no way to update all rows at once what will be the best way
> doing it one by one?
>
> "William Vaughn" wrote:
>
> > You can't. But there is nothing to stop you from doing this on the DBMS
> > engine--where it should be executed. Bringing rows to the client--especially
> > all the rows is ... challenged. Keep the data on the server and let the
> > engine do it's work. This avoids 2 round trips per row to fetch and post the
> > updates.
> >
> > --
> > __________________________________________________________________________
> > William R. Vaughn
> > President and Founder Beta V Corporation
> > Author, Mentor, Dad, Grandpa
> > Microsoft MVP
> > (425) 556-9205 (Pacific time)
> > Hitchhikerâ??s Guide to Visual Studio and SQL Server (7th Edition)
> > ____________________________________________________________________________________________
> > "Stren Assaf" <StrenAssaf@discussions.microsoft.com> wrote in message
> > news:8CBCD5F3-B6C7-4846-8C68-BD2D9C88F4B5@microsoft.com...
> > > Hello, any help will be appreciated :)
> > >
> > > I have a dataset filled with data from a database table. Now I want to do
> > > an
> > > update command that will effect all rows in a specified table in this
> > > dataset
> > > (something like: "UPDATE TableName SET RowName = RowName + 10")
> > >
> > > How can I do it without executing it individually for each row?
> > >
> > > Thanks.
> > >
> >

Cor
Tue Feb 05 11:51:03 CST 2008

Stren,

You cannot, the database is forever working row by row even if you do an
update of a datatable using the DBDataAdapter.

(The same probably with all utilitys. In fact it is processed byte by byte
as every process with computers)

However in this case probably the sample as Kerry wrote is the most simple.

(I would fill a datatable, with a dataadapter, change all rows in the
datatable and then do an update from the datatable).

If you do it simple then you can use the commandbuilder and you probably
need maximal 20 rows of code.

We don't see what database you use, so a sample is difficult to give.

Cor

William
Wed Feb 06 12:49:49 CST 2008

Nope, I would still export the data to SQL Server (even express) via
SqlBulkCopy and then do the global or other operations there. Anything that
can be exposed with a DataReader can be exported and with 2.0 you can create
a DataReader on more objects than ever before.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhikerâ??s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Stren Assaf" <StrenAssaf@discussions.microsoft.com> wrote in message
news:CF121979-3BFC-4DDE-B179-08C4CEA67527@microsoft.com...
> William,
>
> First thanks for your reply.
>
> The table information in the dataset table is coming from an XML and there
> is no DBMS engine involved in the application...
>
> So if there is no way to update all rows at once what will be the best way
> doing it one by one?
>
> "William Vaughn" wrote:
>
>> You can't. But there is nothing to stop you from doing this on the DBMS
>> engine--where it should be executed. Bringing rows to the
>> client--especially
>> all the rows is ... challenged. Keep the data on the server and let the
>> engine do it's work. This avoids 2 round trips per row to fetch and post
>> the
>> updates.
>>
>> --
>> __________________________________________________________________________
>> William R. Vaughn
>> President and Founder Beta V Corporation
>> Author, Mentor, Dad, Grandpa
>> Microsoft MVP
>> (425) 556-9205 (Pacific time)
>> Hitchhikerâ??s Guide to Visual Studio and SQL Server (7th Edition)
>> ____________________________________________________________________________________________
>> "Stren Assaf" <StrenAssaf@discussions.microsoft.com> wrote in message
>> news:8CBCD5F3-B6C7-4846-8C68-BD2D9C88F4B5@microsoft.com...
>> > Hello, any help will be appreciated :)
>> >
>> > I have a dataset filled with data from a database table. Now I want to
>> > do
>> > an
>> > update command that will effect all rows in a specified table in this
>> > dataset
>> > (something like: "UPDATE TableName SET RowName = RowName + 10")
>> >
>> > How can I do it without executing it individually for each row?
>> >
>> > Thanks.
>> >
>>