SqlHelper.UpdateDataSet with transaction

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

Guest

Hi,
I'm trying to use the SqlHelper.UpdateDataSet method with transactions. The
DAAB 2.0 documentaion only say: "SqlHelper.UpdateDataset does not include
transaction support. If you need this, you can implement it yourself by a
preceding call to the helper method SqlHelper.ExecuteScalar("begin
transaction")." but I didn't find any matching overload in the ExecuteScalar
method.
What would be the correct way of using the UpdateDataSet with transaction?

Thanks,
Gwenda
 
Gwenda:

6 of the ExecuteScalar methods have a SqlTransaction as their first
parameter

SqlTransaction tran = con.BeginTransaction();

try

{

// use a parameter array of SqlParameter objects

oCat1 = SqlHelper.ExecuteScalar(tran, CommandType.StoredProcedure,

"UpdateAndGetAverage", new SqlParameter("@CategoryID", 1));

// use a SqlParameter array

SqlParameter[] arParams = new SqlParameter[1];

arParams[0] = new SqlParameter("@CategoryID", 2);

oCat2 = SqlHelper.ExecuteScalar(tran, CommandType.StoredProcedure,

"UpdateAndGetAverage", arParams);

// commit the transaction if there are no errors

tran.Commit();

MessageBox.Show("Transaction completed successfully");

}
 
Hi,
Sorry for not making myself clear: I'm aware that some ExecuteScalar
overload contain the transaction parameter but I'm not sure how to combine it
with the UpdateDataset method.
Thanks,
Gwenda

W.G. Ryan eMVP said:
Gwenda:

6 of the ExecuteScalar methods have a SqlTransaction as their first
parameter

SqlTransaction tran = con.BeginTransaction();

try

{

// use a parameter array of SqlParameter objects

oCat1 = SqlHelper.ExecuteScalar(tran, CommandType.StoredProcedure,

"UpdateAndGetAverage", new SqlParameter("@CategoryID", 1));

// use a SqlParameter array

SqlParameter[] arParams = new SqlParameter[1];

arParams[0] = new SqlParameter("@CategoryID", 2);

oCat2 = SqlHelper.ExecuteScalar(tran, CommandType.StoredProcedure,

"UpdateAndGetAverage", arParams);

// commit the transaction if there are no errors

tran.Commit();

MessageBox.Show("Transaction completed successfully");

}


--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
gwenda said:
Hi,
I'm trying to use the SqlHelper.UpdateDataSet method with transactions. The
DAAB 2.0 documentaion only say: "SqlHelper.UpdateDataset does not include
transaction support. If you need this, you can implement it yourself by a
preceding call to the helper method SqlHelper.ExecuteScalar("begin
transaction")." but I didn't find any matching overload in the ExecuteScalar
method.
What would be the correct way of using the UpdateDataSet with transaction?

Thanks,
Gwenda
Click to expand...
Click to expand...
 
Hi Gwenda,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to implement UpdateDataset
with transaction support. If there is any misunderstanding, please feel
free to let me know.

I think there are many ways to implement it. The simpliest is to use
ExecuteScalar to call BEGIN TRAN to start a transaction. After the
dataAdapter.Update is called successfully, we call a COMMIT TRAN to finish
it. If it fails with exception, we just catch that exception and call
ROLLBACK TRAN.

All the capitalized are SQL statements. We just use ExecuteScalar to call
them, use them as command text.

Or we can use SqlConnection.BeginTransaction to get a SqlTransaction object
and assign it to each command's SqlCommand.Transaction property
(updateCommand, insertCommand and deleteCommand). After the Update process,
we call SqlTransaction.Commit to finish the transaction. If exceptions were
thrown, we call SqlTransaction.Rollback.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top