Best Solution to Insert/Update Multiple Rows with BeginTransaction

[Guest]

Dear All,

I would like to know the best practice to insert or update multiple DataSet
or DataTable into SQL database by using begintrans.

I think DataAdapter.Update method does not support BeginTransaction method
to update more than one DataSet within one SqlConnection.

Will it be performance issue or network traffic if I use Stored Procedure
and ExecuteNonQuery method within a loop?

Thanks.
Kyaw Soe Lin

 

[Cor Ligthert [MVP]]

Kyaw,

Ther are thousands of methods supported by dotNet. Do you really think that
there is than a best practice. You should make your own decissions given the
tools and the situation at your place.

If there is a best practise than the only solution that should have been
given is that.

Just my thought,

Cor

 

[W.G. Ryan MVP]

The big problem is rowstate. If you call update directly on the dataset, it
will call AcceptChanges row by row as they are sent to the db - but if the
transaction is rolled back, then the dataset and the db will be out of sync.
To get around this , you should use

NewDataSEt = OriginalDataSet.GetChanges();
try{
commit;
OriginalDataSet.AcceptChanges();
}
catch(SqlException){
Rollback
}
finally{
connection.close()
}

At least until the 2.0 framework where you can set the UpdateBatchSize
property to something greater than 1
http://msmvps.com/williamryan/archive/2004/05/13/6383.aspx
then each "Update" the adapter does is row by row - this is for all crud
operations so in that respect, calling it manually on your own or letting
the adapter do it is the same thing - you just end up writing more code if
you call it over and over yourself.

HTHl,

Bill