SQLDataAdapter Update method?

[Asaf]

Hi,

I have let's say, about 10,000 rows in a DataTable.
I am looping with foreach on each row and need to update each and every row
with new data.

After updating, for each row I am calling MySQLDataAdapter.Update(MyTable)
for the row to be updated to my DB.

* I know I can call one Update after updating all of the rows but I need to
update the DB for each row.

My question is, the way that I am calling Update for each row, is this right
way? or should I create a Connection object, open a connection to the DB,
update each row by SQLCommand and finally close the connection?

When using Update method, does for each call, SQLDataAdapter will open a new
connection to the server?


Thanks in advanced for any advice,
Asaf

 

[William Vaughn]

Ah, why are you doing this on the client? This sort of job needs to be done
on the server with a stored procedure.
Yes, the Update method will open and close the connection on each
operation--even if that operation includes several updates (as in a batch).
If you want to eliminate that extra overhead (besides doing the query on the
database), open the Connection first.

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

Asaf,

An update with the dataadapter is only done for rows that haschanges

Cor

 

[WenYuan Wang [MSFT]]

Thank for William's suggestion.

Hello Asaf,

The implement of DBDataAdapter.Update(Table) method do the following steps:
First, it filter rows (which is necessary for updating) from table by
DBTable.GetChanges() method.
Then, for each row, it checks the current connection state. If current
connection has been opened before, it will generate a SQLCommand and update
the changes to DB. Finally, it leaves the connection opening. If the
current state of connection is close, it open this connection, update the
row and close the connection.

Thereby, in your specail case, the connection will be open/close 10000
times.The way to eliminate this extra overhead is to open the Connection
first. Such as:

SQLDataAdapter.SelectCommand.Connection.Open();
Foreach(DataRow dr in dt)
{..//update each row..}
SQLDataAdapter.SelectCommand.Connection.Close();

Another issue in your case is that DBDataAdatpter.Update(Table) calls
DBTable.GetChanges to retrive rows modified. I'd like to suggest you pass
the row to DBDataAdatper directly, rather then pass the whole Table for
Update Method. Such as:
Foreach(DataRow dr in datatable)
{
// SQLDataAdapter.update(datatable)
SQLDataAdapter.Update(dr)
}

Hope this helps. Please feel free to update here again, if you have any
more concern. We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Asaf]

Hi WenYuan,

Thanks you very much for your detailed answer.

Kind Regards,
Asaf

 

[Cor Ligthert[MVP]]

Thank for William's suggestion.

??????

What did I write wrong?

Cor

 

[WenYuan Wang [MSFT]]

oops, sorry, Cor.
I miss your great help when I'm writing the reply, due to sync issue.
I'm totally agree with you. DBDataAdapter only update the rows which has
changes.
Thanks very much for your update.

Have a very Merry Christmas
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[WenYuan Wang [MSFT]]

You are welcome, Asaf.
It's my pleasure to assist you.

Have a very Merry Christmas
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Cor Ligthert[MVP]]

WenYuan,

Fine that Merry Christmas wish in your message, a lot more informal from
MSFT then it was in past.

A very Merry Christmas from me too.



Cor