none
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.

    Friday, October 26, 2012 3:58 PM

Answers

  • 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!

    Friday, October 26, 2012 4:17 PM
  • 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!

    Friday, October 26, 2012 8:00 PM

All replies

  • 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!

    Friday, October 26, 2012 4:17 PM
  • 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.

    Friday, October 26, 2012 7:45 PM
  • 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!

    Friday, October 26, 2012 8:00 PM