How to: implement "using" statement and "SqlTransation" class

[Guest]

I've implemented the USING statement to ensure that my newly created sql
connection closes when my method is finished using it. The USING statement is
wrapped in try/catch error handling statement. This works fine.

When i try to implement the "SqlTransation" class, the code does not work as
the SqlTransation object is out-of-scope in the catch statement, and/or,
after the USING statement ends, the sql connection object is automatically
closed ??

For example...

try
{
using (SqlConnection sqlCn = new SqlConnection())
{
// establish sqlCn params
SqlTransaction myTrans;
sqlTrans = sqlCn.BeginTransaction();
// code to execute (SqlCommands)
myTrans.Commit();
}
}
catch (SqlException ex)
{
myTrans.RollBack();
// error handling code
}

.... Is it not possible to implement both the USING statment and the
SqlTransation class at the same time?

Thanks,

 

[Dmitriy Lapshin [C# / .NET MVP]]

SqlConnection sqlCn = new SqlConnection()
try
{
using (sqlCn)
{
// establish sqlCn params
SqlTransaction myTrans;
sqlTrans = sqlCn.BeginTransaction();
// code to execute (SqlCommands)
myTrans.Commit();
}
}
catch (SqlException ex)
{
myTrans.RollBack();
// error handling code
}

 

[Guest]

Thanks Dimitriy. I had to declare and initiate the sqlTransaction variable
before initiating the TRY statement as well to get this to work...

One question: If i close the sql connection using the FINALLY statement, is
it then not necessary (or redundant) to implement the USING statement?

SqlConnection sqlCn = new SqlConnection()
try
{
using (sqlCn)
{
// establish sqlCn params
SqlTransaction myTrans;
sqlTrans = sqlCn.BeginTransaction();
// code to execute (SqlCommands)
myTrans.Commit();
}
}
catch (SqlException ex)
{
myTrans.RollBack();
// error handling code
}

--
Sincerely,
Dmitriy Lapshin [C# / .NET MVP]
Bring the power of unit testing to the VS .NET IDE today!
http://www.x-unity.net/teststudio.aspx

I've implemented the USING statement to ensure that my newly created sql
connection closes when my method is finished using it. The USING statement
is
wrapped in try/catch error handling statement. This works fine.

When i try to implement the "SqlTransation" class, the code does not work
as
the SqlTransation object is out-of-scope in the catch statement, and/or,
after the USING statement ends, the sql connection object is automatically
closed ??

For example...

try
{
using (SqlConnection sqlCn = new SqlConnection())
{
// establish sqlCn params
SqlTransaction myTrans;
sqlTrans = sqlCn.BeginTransaction();
// code to execute (SqlCommands)
myTrans.Commit();
}
}
catch (SqlException ex)
{
myTrans.RollBack();
// error handling code
}

... Is it not possible to implement both the USING statment and the
SqlTransation class at the same time?

Thanks,

 

[Jon Skeet [C# MVP]]

I've implemented the USING statement to ensure that my newly created sql
connection closes when my method is finished using it. The USING statement is
wrapped in try/catch error handling statement. This works fine.

When i try to implement the "SqlTransation" class, the code does not work as
the SqlTransation object is out-of-scope in the catch statement, and/or,
after the USING statement ends, the sql connection object is automatically
closed ??

For example...

try
{
using (SqlConnection sqlCn = new SqlConnection())
{
// establish sqlCn params
SqlTransaction myTrans;
sqlTrans = sqlCn.BeginTransaction();
// code to execute (SqlCommands)
myTrans.Commit();
}
}
catch (SqlException ex)
{
myTrans.RollBack();
// error handling code
}

... Is it not possible to implement both the USING statment and the
SqlTransation class at the same time?

There's a better way than Dmitri showed:

using (SqlConnection sqlCn = new SqlConnection())
{
using (SqlTransaction myTrans = sqlCn.BeginTransaction())
{
...
myTrans.Commit();
}
}

SqlTransaction.Dispose will be called whatever happens, and if the
transaction hasn't been committed yet, it will be rolled back. (You
don't want a transaction hanging around just because an exception other
than SqlException was thrown, do you?)

Note the lack of need for explicit try/catch blocks...

 

[Guest]

Thanks John. Much cleaner w/out the Try/Catch statements...

 

[Greg Johnson]

In MSDN, it states that the using statement wraps the try/finally block, which automatically disposes of the object if it has an IDisposable interface. When opening a database connection with the using statement, if the connection fails, how do you execute a catch block with a custom message? It would seem that one or more catch blocks would have follow the using block to catch and report exceptions that might occur.

From http://www.developmentnow.com/g/36_...t-using-statement-and-SqlTransation-class.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com