none
MS Enterprise library, stored procedures and transactions RRS feed

  • Question

  •  

    Hi there people! I have a question: I use ms enterprise library to access to mssql stored procedures. The example is:

    Code Snippet

    Database db = DatabaseFactory.CreateDatabase();

    db.ExecuteNonQuery("SOME_STORED_PROCEDURE", some_param);

     

     

     

    Now I need to add transactions support to this structure. Thats what I found in enterpise docs:

    Code Snippet
    using (DbConnection connection = db.CreateConnection())
    connection.Open();  
    DbTransaction transaction = connection.BeginTransaction();  
    try  {    
    // Credit the first account.    
    db.ExecuteNonQuery(creditCommand, transaction);    
    // Debit the second account.    
    db.ExecuteNonQuery(debitCommand, transaction);    
    // Commit the transaction.    
    transaction.Commit();    
    result = true;  
    }  catch  {    
    // Roll back the transaction.     
    transaction.Rollback();  }  
    connection.Close();  
    return result;
    }

     

     

    As you see, in second example (what microsoft offers) ExecuteNonQuery method receives DBCommand variables instead of stored procedure's names. So the question should I use this structure and simple call ExecuteNonQuery methods with stored procedure's names or following structure also will work:

     

    Code Snippet

    Database db = DatabaseFactory.CreateDatabase();

    DbTransaction transaction = conn.BeginTransaction();

    try

    {

    db.ExecuteNonQuery(transaction, "SAVE_WORK_DATA", userId, workId, operationState);

     

    transaction.Commit();

     

    }

    catch (Exception err)

    {

    transaction.Rollback();

    }

     

     

     

    Thanks!

    Wednesday, February 20, 2008 10:41 AM

Answers

  • Both ways will work and both should participate in transaction properly as long as both commands use same previously opened database connection that holds transaction. That is the only condition to participate in transaction.

    Wednesday, February 20, 2008 10:50 AM
    Moderator
  • It is valid and you do not need to pass it by reference, since it is class and function will get reference to the transaction class. So inside of the function it will be same transaction class instance, not a copy of it.

    Thursday, February 21, 2008 11:02 AM
    Moderator

All replies

  • Both ways will work and both should participate in transaction properly as long as both commands use same previously opened database connection that holds transaction. That is the only condition to participate in transaction.

    Wednesday, February 20, 2008 10:50 AM
    Moderator
  •  

    Thanks
    Wednesday, February 20, 2008 11:02 AM
  • One more question: can I send a defined DbTransaction variable to another functions and also use it there? Like this:

    void Update1(int id)

    {

    here I define variable and write a code for update (like in my first post)

     

    Update2(1, transaction);

    }

    void Update2(int id, DbTransaction t)

    {

    here I want to call an ExecuteNonQuery method and pass it the received DbTransaction variable

    }

     

    is it valid? Or I should to send it by reference? Thanks

    Wednesday, February 20, 2008 11:11 AM
  • It is valid and you do not need to pass it by reference, since it is class and function will get reference to the transaction class. So inside of the function it will be same transaction class instance, not a copy of it.

    Thursday, February 21, 2008 11:02 AM
    Moderator
  •  

    Thanks alot!
    Thursday, February 21, 2008 1:04 PM