delete, insert and update all in one transaction

Question

I have a code snippet below that does a call and insert into a stored procedure...and its working fine...But I want to add a delete statement before and a update code snippet as well...but I want all this to happen completely or not...

meaning if the delete portion of the code runs ok...but there is an issue with the insert code, i want it to rollback and not delete the records anymore....all or nothing, I am certain this can be done with transactions, but I can't find any samples with the new framework of 4.5

Databasedb = DatabaseFactory.CreateDatabase("ConnectionString");

stringsqlCommand = "stp_insertBus";

DbCommanddbCommand = db.GetStoredProcCommand(sqlCommand);

db.AddInParameter(dbCommand,

"id", DbType.Int32, b_id);

db.AddInParameter(dbCommand,

"com_id", DbType.Int32, comValue);

DataSetds = null;

ds = db.ExecuteDataSet(dbCommand);

So I want to delete, insert, update, on 3 different stored procedures...but only if everything passes and no errors do I want this committed, if there is any issues I want the whole 3 steps process rolledback.

Answer 1

Hi!

Try with this:

Databasedb = DatabaseFactory.CreateDatabase("ConnectionString");

stringsqlCommand = "stp_insertBus";

DbCommanddbCommand = db.GetStoredProcCommand(sqlCommand);

db.AddInParameter(dbCommand,

"id", DbType.Int32, b_id);

db.AddInParameter(dbCommand,

"com_id", DbType.Int32, comValue);

DataSetds = null;


 using (DbConnection conn = db.CreateConnection())
  {
    conn.Open();
    DbTransaction trans = conn.BeginTransaction();

    try
    {
      ds = db.ExecuteDataSet(dbCommand);

      //Here will go your other calls to SP

      trans.Commit();

      result = true;
    }
    catch
    {

      trans.Rollback();
    }
    conn.Close();

Regards!

Answer 2

Thanks for the post, but I'm not sure how I am supposed to integrate the other 2 stored procedures in the location you are refering...

The other stored procedure (this one runs first) is called "stp_clearCat" that also takes one parameter, then the stored procedure above is next (with 2 parameters), then the last stored procedure is stp_updateList which also takes 2 parameters.

not sure how this gets incorporated with regards to using/creating dbcommand objects and datasets when there is already on in there.

Thanks.

Answer 3

You just have to define the dbcommands and call them inside the using passing the transaction as parameter:

Databasedb = DatabaseFactory.CreateDatabase("ConnectionString");

stringsqlCommand = "stp_clearCa";
DbCommand dbClearCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "id", DbType.Int32, b_id);

stringsqlCommand = "stp_insertBus";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "id", DbType.Int32, b_id);
db.AddInParameter(dbCommand, "com_id", DbType.Int32, comValue);

stringsqlCommand = "stp_updateList";
DbCommand dbUpdateCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "id", DbType.Int32, b_id);
db.AddInParameter(dbCommand, "com_id", DbType.Int32, comValue);

DataSetds = null;


 using (DbConnection conn = db.CreateConnection())
  {
    conn.Open();
    DbTransaction trans = conn.BeginTransaction();

    try
    {

      db.ExecuteNonQuery(dbClearCommand, trans);

      ds = db.ExecuteDataSet(dbCommand, trans);

      db.ExecuteNonQuery(dbUpdateCommand, trans);

      trans.Commit();

      result = true;
    }
    catch
    {

      trans.Rollback();
    }
    conn.Close();

Regards!