Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP .Net (http://www.velocityreviews.com/forums/f29-asp-net.html)
-   -   SqlTransaction issue (http://www.velocityreviews.com/forums/t92805-sqltransaction-issue.html)

Piotr Strycharz 12-15-2004 03:22 PM

SqlTransaction issue
 
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 12-15-2004 04:01 PM

Re: SqlTransaction issue
 
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" <Piotr.Strycharz@antispam-account.com> wrote in message
news:cppl34$96$1@nemesis.news.tpi.pl...
> 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
>




David Browne 12-15-2004 04:30 PM

Re: SqlTransaction issue
 

"Piotr Strycharz" <Piotr.Strycharz@antispam-account.com> wrote in message
news:cppl34$96$1@nemesis.news.tpi.pl...
> 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




bruce barker 12-15-2004 04:59 PM

Re: SqlTransaction issue
 

"Piotr Strycharz" <Piotr.Strycharz@antispam-account.com> wrote in message
news:cppl34$96$1@nemesis.news.tpi.pl...
| 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 12-15-2004 05:11 PM

Re: SqlTransaction issue
 
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" <Piotr.Strycharz@antispam-account.com> wrote in message
news:cppl34$96$1@nemesis.news.tpi.pl...
| 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 12-16-2004 08:28 AM

Re: SqlTransaction issue
 

Użytkownik "bruce barker" <nospam_brubar@safeco.com> napisał w wiadomości
news:ObWNTks4EHA.1596@tk2msftngp13.phx.gbl...
> 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.



All times are GMT. The time now is 02:00 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.