Thanks. All is clear now :)
On 18/09/2007, Frans Bouma <[EMAIL PROTECTED]> wrote:
> > 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(r) http://www.develop.com
>
> View archives and manage your subscription(s) at http://discuss.develop.com
>
--
To dive or not to dive, that's an easy question ...
><((((º>`·.¸¸´¯`·.¸.·´¯`·...¸>(((º>
.·´¯`·.><((((º>`·.¸¸.·´¯`·.¸.·´¯`·...¸><((((º>
===================================
This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
