> This is my first post in the mailing list but I read it all the time :)
>
> I have the following scenarion :
>
> Super stored proc :
>
> begin tran T1;
> proc1;
> proc2;
> ...
> commit tran T1;
>
> I would like to call the procedure from ADO.NET but I want to finish
> some other work in C# code before committing the whole data to the
> database. So Is it possible to the Super stored procedure in Ado.Net
> transaction and what will be the impact if it fails ?
>
> Lets write a simple example :
>
> SQLConnection db = new SQLConnection("...");
> SQLCommand comm = new SQLCommand();
> comm = SuperStoredProc;
> ....
> db.Open();
> db.BeginTransaction();
> try
> {
> comm.execute();
> StartC#Proc();
> db.CommitTransaction();
> }
>
> catch Exception
> {
> db.Rollback();
> }
SqlServer actually has 1 active transaction per connection. So if you
start multiple nested transactions, they're not really nested: if you rollback
an inner transaction, all are rolled back.
So if you call db.BeginTransaction, what's done is that the SqlClient
executes BEGIN TRANSACTION name
Then your proc is called, which also does BEGIN TRANSACTION somename.
The commit you execute IN the proc is ignored by sqlserver, because there's
already an outer transaction active on the connection (namely the one started
by db.BeginTransaction).
So your code works fine. Check 'Nested transactions' in BOL of
sqlserver for details.
FB
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
===================================
This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
