By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,199 Members | 1,458 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,199 IT Pros & Developers. It's quick & easy.

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

P: n/a
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,

Nov 16 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

"charliewest" <ch*********@discussions.microsoft.com> wrote in message
news:77**********************************@microsof t.com...
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,


Nov 16 '05 #2

P: n/a
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?
"Dmitriy Lapshin [C# / .NET MVP]" wrote:
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

"charliewest" <ch*********@discussions.microsoft.com> wrote in message
news:77**********************************@microsof t.com...
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,


Nov 16 '05 #3

P: n/a
charliewest <ch*********@discussions.microsoft.com> wrote:
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...

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 16 '05 #4

P: n/a
Thanks John. Much cleaner w/out the Try/Catch statements...

"Jon Skeet [C# MVP]" wrote:
charliewest <ch*********@discussions.microsoft.com> wrote:
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...

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Nov 16 '05 #5

P: n/a
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_2...tion-class.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Nov 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.