SqlTransaction issue

Discussion in 'ASP .Net' started by Piotr Strycharz, Dec 15, 2004.

  1. Hi,

    The question is about how to check if the transaction is still active. Let's
    say, that I am using a 3rd party stored procedure, that cannot be changed.
    Sample is (Of course, the real one is much more complicated):
    CREATE PROC test AS IF @@trancount > 0 ROLBACK TRAN

    Now, I'm executing code:

    tran = connection.BeginTransaction();
    cmd = new SqlCommand("test", connection);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.ExecuteNonQuery();

    After that - how do I know if the transaction is still active?

    Regards
    Piotr Strycharz, Dec 15, 2004
    #1
    1. Advertising

  2. Piotr Strycharz

    Sahil Malik Guest

    For this same purpose, The Microsoft Data Access block checks to see if the
    transaction.connection is a valid connection or null.

    - Sahil Malik
    http://dotnetjunkies.com/weblog/sahilmalik

    "Piotr Strycharz" <> wrote in message
    news:cppl34$96$...
    > Hi,
    >
    > The question is about how to check if the transaction is still active.

    Let's
    > say, that I am using a 3rd party stored procedure, that cannot be changed.
    > Sample is (Of course, the real one is much more complicated):
    > CREATE PROC test AS IF @@trancount > 0 ROLBACK TRAN
    >
    > Now, I'm executing code:
    >
    > tran = connection.BeginTransaction();
    > cmd = new SqlCommand("test", connection);
    > cmd.CommandType = CommandType.StoredProcedure;
    > cmd.ExecuteNonQuery();
    >
    > After that - how do I know if the transaction is still active?
    >
    > Regards
    >
    Sahil Malik, Dec 15, 2004
    #2
    1. Advertising

  3. Piotr Strycharz

    David Browne Guest

    "Piotr Strycharz" <> wrote in message
    news:cppl34$96$...
    > Hi,
    >
    > The question is about how to check if the transaction is still active.
    > Let's
    > say, that I am using a 3rd party stored procedure, that cannot be changed.
    > Sample is (Of course, the real one is much more complicated):
    > CREATE PROC test AS IF @@trancount > 0 ROLBACK TRAN
    >
    > Now, I'm executing code:
    >
    > tran = connection.BeginTransaction();
    > cmd = new SqlCommand("test", connection);
    > cmd.CommandType = CommandType.StoredProcedure;
    > cmd.ExecuteNonQuery();
    >


    You can examine the @@trancount. If it is > 0 then the transaction is
    active. Otherwise the transaction has been rolled back.

    new SqlCommand("select @tc = @@trancount)

    and bind an output parameter to @tc.

    David
    David Browne, Dec 15, 2004
    #3
  4. Piotr Strycharz

    bruce barker Guest

    "Piotr Strycharz" <> wrote in message
    news:cppl34$96$...
    | Hi,
    |
    | The question is about how to check if the transaction is still active.
    Let's
    | say, that I am using a 3rd party stored procedure, that cannot be changed.
    | Sample is (Of course, the real one is much more complicated):
    | CREATE PROC test AS IF @@trancount > 0 ROLBACK TRAN
    |
    | Now, I'm executing code:
    |
    | tran = connection.BeginTransaction();
    | cmd = new SqlCommand("test", connection);
    | cmd.CommandType = CommandType.StoredProcedure;
    | cmd.ExecuteNonQuery();
    |
    | After that - how do I know if the transaction is still active?
    |
    | Regards
    |
    bruce barker, Dec 15, 2004
    #4
  5. Piotr Strycharz

    bruce barker Guest

    the commit will fail if no pending transaction exits - this will be your
    fallback.

    procs generally test @@trancount to avoid throwing an error on rollback when
    there is no nested transction. you need to look to see how the proc is
    returning failure - return value, raiserror, or paramter.

    you are going to use multiple batches be sure to use the same connection,
    and don't close it. at anytime you can select @@trancount to see a
    transaction is active. you could also use the follow sp to do the commit.

    create proc DoCommit as
    set nocount on
    if @@trancount > 0
    begin
    commit tran
    select 1 as CommitResult
    end
    return 0 as CommitResult

    then use

    cmd = new SqlCommand("DoComitt", connection);
    cmd.CommandType = CommandType.StoredProcedure;
    bool ok = ((int) cmd.ExecuteScaler()) ==1);

    -- bruce (sqlwork.com)




    "Piotr Strycharz" <> wrote in message
    news:cppl34$96$...
    | Hi,
    |
    | The question is about how to check if the transaction is still active.
    Let's
    | say, that I am using a 3rd party stored procedure, that cannot be changed.
    | Sample is (Of course, the real one is much more complicated):
    | CREATE PROC test AS IF @@trancount > 0 ROLBACK TRAN
    |
    | Now, I'm executing code:
    |
    | tran = connection.BeginTransaction();
    | cmd = new SqlCommand("test", connection);
    | cmd.CommandType = CommandType.StoredProcedure;
    | cmd.ExecuteNonQuery();
    |
    | After that - how do I know if the transaction is still active?
    |
    | Regards
    |
    bruce barker, Dec 15, 2004
    #5
  6. U¿ytkownik "bruce barker" <> napisa³ w wiadomo¶ci
    news:...
    > the commit will fail if no pending transaction exits - this will be your
    > fallback.


    Yes ! And it fails, indeed. However, my code is not aware of the ended
    transaction, so it executes consecutive commands **without** transaction
    (although transaction variable is not null).

    Seems, that the @@trancount sql variable is the one solution.

    Regards.
    Piotr Strycharz, Dec 16, 2004
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. .Net Newbie
    Replies:
    2
    Views:
    691
    =?Utf-8?B?RGVlcA==?=
    Jul 9, 2004
  2. Joe Rigley

    SqlTransaction Record Not Found

    Joe Rigley, Nov 11, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    369
    Joe Rigley
    Nov 11, 2005
  3. Neven Klofutar

    SqlTransaction problem

    Neven Klofutar, Nov 23, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    513
  4. Replies:
    0
    Views:
    1,642
  5. sloan
    Replies:
    2
    Views:
    1,106
Loading...

Share This Page