Question: Try Catch Finally

[VB Programmer]

I usually put my declarations for db connections, datasets, datareaders,
etc... above the Try portion. Inside the Try is where I open my connection,
executereader, etc... In the Catch portion I usually "clean it up", with
closing the datareaders, connections, etc... Is this the best way to do it?

The reason I ask is that if something fails (in the Try) and it didn't, get
to open the db connection yet, it will jump to Finally and that will give me
an error because I cannot close a db connection that is not open. Is there
another way around this?

 

[William Ryan]

No, this probably isn't what you want to do b/c if you
don't get an exception, the code won't ever execute.

I'm pretty sure close wont' throw an exception if you try
to close a closed connection unless something else is
happening. Nonetheless, put the close in the finally and
check for if (cn.State<> connectionState.Open) {cn.Close
();} ..you may want to do the reverse (check to see if
it's not open and then open it) on the open connection
line.

This will solve this problem , but if you post the code
snippet, I give you some more detailed responses.

Good Luck,

Bill

W.G. Ryan
(e-mail address removed)
www.knowdotnet.com

 

[Terry Burns]

Bill Gates once told his executives to never finish an operating system;
this has the effect of generating third party interest and an economy based
on it. I think they did this with .NET, by making it complex and versatile
enough to generate endless questions about how to achieve things thus
creating a sea of individuals who strive to conquer its intricacies and
anomalies.

However, that being the case, I still like it !!!



..

 

[Russell Mangel]

I use the "using" statement, check it out.

try
{
// "using" Automatically calls Dispose on the object being "used"
// when leaving the scope of the using statement. The braces.
// The object is disposed even if the an exception occurs.
// If you have other objects to dispose inside, use another using
statement.

using(m_SqlConnection = new SqlConnection(this.ConnectionString))
{
m_SqlCommand = new SqlCommand(this.SQLQuery, m_SqlConnection);

m_SqlConnection.Open();
m_SqlCommand.ExecuteNonQuery();
} // Connection closed after exiting brace.
}
catch(System.Data.SqlClient.SqlException Exc)
{
MessageBox.Show(Exc.ToString());
}

 

[Terry Burns]

accortding to what I understand you are supposed to explicitly close the
connection, regardless of how you dispose of it as the connection remains
active after disposition if you do not

 

[Russell Mangel]

Skeptical eh?

"using" should be used with all unmanaged resources, like database
connections, and file handles.

To make yourself feel better, make some tests on your connection using the
ConnectionState.
if(m_SqlConnection.State == ConnectionState.Open)
{
Console.WriteLine("Connection is Open");
}

Here is link from Microsoft using "using" with files, notice how the author
never explicitly closes the
filestream. The filestream is closed after the last surrounding brace.

Watch the wrap on this link.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemiofilestreamclasstopic.asp

 

[Russell Mangel]

Actually... This is for C# only, so for VB users, I haven't a clue....

 

[Steve Drake]

The dispose calls the close.

But... it wont work for VB, all the using statments does is genrate IL code
todo a try,Finally, IL Code below

Try Finally IL :

..method public hidebysig instance void TryFinnaly() cil managed
{
// Code size 22 (0x16)
.maxstack 1
.locals init (class [System.Data]System.Data.SqlClient.SqlConnection V_0)
IL_0000: newobj instance void
[System.Data]System.Data.SqlClient.SqlConnection::.ctor()
IL_0005: stloc.0
.try
{
IL_0006: ldloc.0
IL_0007: callvirt instance void
[System.Data]System.Data.SqlClient.SqlConnection::Open()
IL_000c: leave.s IL_0015
} // end .try
finally
{
IL_000e: ldloc.0
IL_000f: callvirt instance void
[System]System.ComponentModel.Component:ispose()
IL_0014: endfinally
} // end handler
IL_0015: ret
} // end of method TryCatchTest::TryFinnaly

Using IL :

..method public hidebysig instance void UsingTest() cil managed
{
// Code size 25 (0x19)
.maxstack 1
.locals init (class [System.Data]System.Data.SqlClient.SqlConnection V_0)
IL_0000: newobj instance void
[System.Data]System.Data.SqlClient.SqlConnection::.ctor()
IL_0005: stloc.0
.try
{
IL_0006: ldloc.0
IL_0007: callvirt instance void
[System.Data]System.Data.SqlClient.SqlConnection::Open()
IL_000c: leave.s IL_0018
} // end .try
finally
{
IL_000e: ldloc.0
IL_000f: brfalse.s IL_0017
IL_0011: ldloc.0
IL_0012: callvirt instance void
[mscorlib]System.IDisposable:ispose()
IL_0017: endfinally
} // end handler
IL_0018: ret
} // end of method TryCatchTest::UsingTest

 

[VB Programmer]

So, is this the concensus (VB.NET)?

- 'Using' can be used for C#, not VB.NET
- Close all my datareaders, datasets, connections, etc... in FINALLY and
just check for ConnectionState.Open before I 'do my stuff'.

 

[Terry Burns]

Actually, if you read the close method descrption, it does say that the
close can be called even when the connection is already closed without
generating an exception.

Remarks
The Close method rolls back any pending transactions. It then releases the
connection to the connection pool, or closes the connection if connection
pooling is disabled. If Close is called while handling a StateChange event,
no additional StateChange events are fired.

An application can call Close more than one time. No exception is generated.

 

[VB Programmer]

When I execute my datareader it generates an exception (because I had bad
syntax in the comandtext). Anyways, when the code gets to the FINALLY it
fails on "drReader.Close()" with this error:
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.

Any ideas?

 

[Terry Burns]

The error Object reference not set

to an instance of an object.

Means that your execute reader did not return an reader object. You need an
appropriate catch statement for each line of code, you can haqve several
following the try block. This might help tell you why it did not execute.
Most probably you didnt set the commandtext statement

InvalidOperationException Cannot execute a command within a
transaction context that differs from the context in which the connection
was originally enlisted.


try
dim rdr = cmd.executereader()
catch ex as InvalidOperationException

messageBox.show( ex.message )

end try

 

[Dave Stienessen]

we do this type of check - pseudo coded!

SqlConnection conn = new SqlConnection();
SqlDataReader rdr;
SqlCommand cmd;
try
{
// parms excluded ....
conn.Open();
cmd = new SqlCommand(...);
rdr = cmd.ExecuteReader();

}
catch (Exception ex)
{
// report exception
}
finally
{
// always cleanup...
if (rdr != null) { rdr.Close(); }
if (cmd != null) { cmd.Dispose(); }
if (conn != null) { conn.Dispose(); }
}

HTH
Dave

 

[Michael Lang]

Here's what I do and it has always worked for me...

=====================
myConnection.Open()
try
{
... code to use connection ...
}
catch{...handle error...}
finally
{
myConnection.Close();
}
=====================

If open fails, you don't have to worry about closing it. None of the
samples in help "Dispose" objects in System.Data. But then the samples
don't always include try-catch-finally blocks either. Is
Connection.Dispose() needed? Once you close a connection are there any
resources that need explicit disposal? I usually just let the garbage
collector do it's thing.

 

[Dave Stienessen]

myConnection.Open could throw an exception and crash your app ---
Dave

 

[Michael Lang]

Not crash the app, it just gets thrown to the caller of this method.

You can add another try catch around the Open(). My point is that the Open
is before the try{} containing the operation that could fail, and the close
is in the corresponding finally{}. If the crash is in an attempt to Open
the connection, then the finally block would not need to try and close the
connection, therefore a separate try-catch for the Open command.

Unless you want to return a custom exception for a failed open, there is no
point in enclosing it in an exception block...
===============================================
public void MyDBOperation(SqlConnection myConnection, ...)
{
try{
myConnection.Open()
}
catch (Exception ex)
{
throw ex; //pointless, unless you need to throw a custom exception
}
try
{
... code to use connection ...
}
catch{...handle error...}
finally
{
myConnection.Close();
}
}
===============================================

As a client I would propably want to receive a different exception for a
failed connection, and a failed database operation. Each warrants a
different response.

What I am not sure about is if calling Dispose is required? Some messages
in this group say yes, others say no.