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

[Stren Assaf]

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 Vaughn]

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)
____________________________________________________________________________________________

 

[Cor Ligthert[MVP]]

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

 

[Stren Assaf]

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.

 

[Stren Assaf]

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?

 

[Kerry Moorman]

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

 

[Cor Ligthert[MVP]]

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 Vaughn]

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)
____________________________________________________________________________________________