commiting Transaction and underlauing connection

[Shimon Sim]

Hi I have following code

public void Functon1()
{
SqlTransaction trans=CreateTransaction();
//Do something with it

trans.Commit();

}

private SqlTransaction CreateTransaction()
{
SqlConnection con=new SqlConnection("some connection string");
return con.BeginTransaction();
}

I just wanted to know what will happen with my Connection. Will it be still
open or not?
trans.Connection property is always null after I commit the transaction.
Does it mean anything?
Thanks, Shimon.

 

[Val Mazur]

Hi,

It depends on a scope of the connection. Actual connection still should be
opened, but in your case connection is local to the function, which means as
soon as you commit transaction, then connection should be destroyed

 

[Shimon Sim]

Thanks,
so I can safely assume that it will be closed, since it is just a local
variable?
Shimon.

 

[David Browne]

Thanks,
so I can safely assume that it will be closed, since it is just a local
variable?
Shimon.

No. Your code is not correct. It leaks connections and will cause your
application to fail.

Commiting the transaction will cause the Connection to fly away into the
unreachable spaces of the managed heap where it will remain open until it is
garbage collected and finalized. Which may be a very long time.

David

 

[Shimon Sim]

Thank you for your comment.
I guess I will have to drop the notion of creating SqlConnection as local
variable to open a Transaction object with bigger scope.

Shimon.