Best Solution to Insert/Update Multiple Rows with BeginTransaction

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Back
Top