Programming - Best Practice

[bigHairy]

Hello.

I have been teaching myself .NET over the last few months and have had
some success. I would like to ask a question though...

A number of examples I have followed have the following in their
finally statement

Try
......
Catch
......
Finally

If (Not IsNothing(dbCon n)) Then
dbConn.Close()
End If

End Try

Will my data connection always close using this method or should I
just use dbConn.close?

I have a program that seems to be holding onto its connection - am I
missing something?

[pvdg42]

Re: Programming - Best Practice


"bigHairy" <mthomas1973@gm ail.comwrote in message
news:1171633711 .929140.133200@ m58g2000cwm.goo glegroups.com.. .

Hello.
>
I have been teaching myself .NET over the last few months and have had
some success. I would like to ask a question though...
>
A number of examples I have followed have the following in their
finally statement
>
Try
.....
Catch
.....
Finally
>
If (Not IsNothing(dbCon n)) Then
dbConn.Close()
End If
>
End Try
>
Will my data connection always close using this method or should I
just use dbConn.close?
>
I have a program that seems to be holding onto its connection - am I
missing something?
>

If you just use dbConn.Close(), you'll get an exception if the connection in
question is not open.
The code in finally first checks to see if dbConn is an empty reference (no
active connection), and only attempts to close it is there is an active
connection to close.
As code in the finally block is "guaranteed to execute", the code you posted
closes the connection *if* it is open.
The question is, what makes you think the connection remains active after
this code executes?

[bigHairy]

Re: Programming - Best Practice

Thanks for your prompt reply, I appreciate the help.

I am currently in discussions with a 3rd party data provider as there
seem to be timeout issues using a linked SQL Server with a Progress
Database. There are suggestions that it may be our program leaving
connections open but I can find no evidence of this - thought I would
check that I am using the best practice for closing these connections
as a first port of call before I go investigating the rest of the
process.

[Zim Babwe]

Re: Programming - Best Practice

You could use something like this to close your connection:

If objConn.State = Data.Connection State.Open Then
objConn.Close()
End If




"bigHairy" <mthomas1973@gm ail.comwrote in message
news:1171633711 .929140.133200@ m58g2000cwm.goo glegroups.com.. .

Hello.
>
I have been teaching myself .NET over the last few months and have had
some success. I would like to ask a question though...
>
A number of examples I have followed have the following in their
finally statement
>
Try
.....
Catch
.....
Finally
>
If (Not IsNothing(dbCon n)) Then
dbConn.Close()
End If
>
End Try
>
Will my data connection always close using this method or should I
just use dbConn.close?
>
I have a program that seems to be holding onto its connection - am I
missing something?
>

[Brian Gideon]

Re: Programming - Best Practice

On Feb 16, 11:07 am, "Zim Babwe"
<zimba...@doyou reallythinkthis isreal.comwrote :

You could use something like this to close your connection:
>
If objConn.State = Data.Connection State.Open Then
objConn.Close()
End If
>
"bigHairy" <mthomas1...@gm ail.comwrote in message
>
news:1171633711 .929140.133200@ m58g2000cwm.goo glegroups.com.. .
>

You could call Dispose instead of Close. Dispose will not throw
exceptions. If you're using VB 2005 the Using keyword can clean up
the code quite a bit as well.

[Cor Ligthert [MVP]]

Re: Programming - Best Practice

Hi,

AS you ask for Best Practice than you don't definitly not need a test to see
if your connection is open.

You control your program, so you know if it is open or not and can close it
therefore at the right time without any error.

Cor

"Zim Babwe" <zimbabwe@doyou reallythinkthis isreal.comschre ef in bericht
news:OaAUlzeUHH A.192@TK2MSFTNG P04.phx.gbl...

You could use something like this to close your connection:
>
If objConn.State = Data.Connection State.Open Then
objConn.Close()
End If
>
>
>
>
"bigHairy" <mthomas1973@gm ail.comwrote in message
news:1171633711 .929140.133200@ m58g2000cwm.goo glegroups.com.. .

>Hello.
>>
>I have been teaching myself .NET over the last few months and have had
>some success. I would like to ask a question though...
>>
>A number of examples I have followed have the following in their
>finally statement
>>
>Try
>.....
>Catch
>.....
>Finally
>>
>If (Not IsNothing(dbCon n)) Then
>dbConn.Close ()
>End If
>>
>End Try
>>
>Will my data connection always close using this method or should I
>just use dbConn.close?
>>
>I have a program that seems to be holding onto its connection - am I
>missing something?
>>

>
>

[Stephany Young]

Re: Programming - Best Practice

The issue here has nothing to do with whether or not the database connection
is open. It is to do with whether or not the database connection object has
been instantiated.

You will probabaly note that the 'examples' have the dbConn variable
declared before the Try. This means that the dbConn variable is 'scoped' so
that it is available to the Finally block.

With both the SqlConnection and OleDbConnection classes, calling Close on an
object of those types that is not open will NOT cause an exception. However,
if you call Close on an object of those types that has NOT been instantiated
(Is Nothing) then an exception WILL be thrown.

In the Try block, if an exception is thrown before dbConn is instantiated
(let alone opened), the logic will 'jump' directly to the Catch block and
then fall through to the Finally block. At this point dbConn would be
Nothing and a call to dbConn.Close would fail. The conditional execution of
dbConn.Close handles this situation.

Because the code Finally block is executed regardless of whether an
exception was thrown or not, having the conditional execution of
dbConn.Close means that you do not need to worrying about closing the
connection in either the Try or Catch blocks.


"bigHairy" <mthomas1973@gm ail.comwrote in message
news:1171633711 .929140.133200@ m58g2000cwm.goo glegroups.com.. .

Hello.
>
I have been teaching myself .NET over the last few months and have had
some success. I would like to ask a question though...
>
A number of examples I have followed have the following in their
finally statement
>
Try
.....
Catch
.....
Finally
>
If (Not IsNothing(dbCon n)) Then
dbConn.Close()
End If
>
End Try
>
Will my data connection always close using this method or should I
just use dbConn.close?
>
I have a program that seems to be holding onto its connection - am I
missing something?
>

[pfc_sadr@hotmail.com]

Re: Programming - Best Practice

I didn't think that you could even leave a connection open.

it's one of my biggest complaints about ADO.net; I used to use @@SPID
similiar to sessionID in ASP in order to build some simple apps...

but now there is nothing like that in .NET from what I understand

I just don't understand; why do you even need to close a connection if
you can't leave a connection OPEN?


like I'm being serious and honest here.

Thanks





On Feb 16, 12:04 pm, "Stephany Young" <noone@localhos twrote:

The issue here has nothing to do with whether or not the database connection
is open. It is to do with whether or not the database connection object has
been instantiated.
>
You will probabaly note that the 'examples' have the dbConn variable
declared before the Try. This means that the dbConn variable is 'scoped' so
that it is available to the Finally block.
>
With both the SqlConnection and OleDbConnection classes, calling Close on an
object of those types that is not open will NOT cause an exception. However,
if you call Close on an object of those types that has NOT been instantiated
(Is Nothing) then an exception WILL be thrown.
>
In the Try block, if an exception is thrown before dbConn is instantiated
(let alone opened), the logic will 'jump' directly to the Catch block and
then fall through to the Finally block. At this point dbConn would be
Nothing and a call to dbConn.Close would fail. The conditional execution of
dbConn.Close handles this situation.
>
Because the code Finally block is executed regardless of whether an
exception was thrown or not, having the conditional execution of
dbConn.Close means that you do not need to worrying about closing the
connection in either the Try or Catch blocks.
>
"bigHairy" <mthomas1...@gm ail.comwrote in message
>
news:1171633711 .929140.133200@ m58g2000cwm.goo glegroups.com.. .
>

Hello.

>

I have been teaching myself .NET over the last few months and have had
some success. I would like to ask a question though...

>

A number of examples I have followed have the following in their
finally statement

>

Try
.....
Catch
.....
Finally

>

If (Not IsNothing(dbCon n)) Then
dbConn.Close()
End If

>

End Try

>

Will my data connection always close using this method or should I
just use dbConn.close?

>

I have a program that seems to be holding onto its connection - am I
missing something?

[Brian Gideon]

Re: Programming - Best Practice

On Feb 16, 3:00 pm, pfc_s...@hotmai l.com wrote:

I didn't think that you could even leave a connection open.
>
it's one of my biggest complaints about ADO.net; I used to use @@SPID
similiar to sessionID in ASP in order to build some simple apps...
>
but now there is nothing like that in .NET from what I understand
>
I just don't understand; why do you even need to close a connection if
you can't leave a connection OPEN?
>
like I'm being serious and honest here.
>
Thanks
>

Hi,

What made you think you couldn't leave a connection open?

Brian

[PFC Sadr]

Re: Programming - Best Practice

uh a half dozen MS press books???
uh a half dozen MS press books???
uh a half dozen MS press books???


get a spid; get another SPID.

DO THEY MATCH?

because if they do then I'll accept a written apology from Microsoft
and new editions of their books that are CORRECTED.


On Feb 16, 1:48 pm, "Brian Gideon" <briangid...@ya hoo.comwrote:

On Feb 16, 3:00 pm, pfc_s...@hotmai l.com wrote:
>

I didn't think that you could even leave a connection open.

>

it's one of my biggest complaints about ADO.net; I used to use @@SPID
similiar to sessionID in ASP in order to build some simple apps...

>

but now there is nothing like that in .NET from what I understand

>

I just don't understand; why do you even need to close a connection if
you can't leave a connection OPEN?

>

like I'm being serious and honest here.

>

Thanks

>
Hi,
>
What made you think you couldn't leave a connection open?
>
Brian

[Chris Mullins [MVP]]

Re: Programming - Best Practice

I think you misunderstood what the MS-Press books were saying.

The opening and closing of ADO.NET connections, along with the implications
this has on the connection pool, can catch people by surprise.

The general rule for your code is open / execute / close - this is how best
you're able to work with the connection pool. There's nothing stopping you
from doing other things though - like holding connections open, or any of a
dozen other (bad) things.

Be aware that, under the hood, open / close don't actually open or close
database connections. IT's more "Check out from pool", "Check back into
pool". It's up the pool as to when the connections are actually opened and
closed.

--
Chris Mullins, MCSD.NET, MCPD:Enterprise , Microsoft C# MVP

http://www.coversant.com/blogs/cmullins

"PFC Sadr" <pfc_sadr@hotma il.comwrote in message
news:1171663581 .468393.248680@ a75g2000cwd.goo glegroups.com.. .

uh a half dozen MS press books???
uh a half dozen MS press books???
uh a half dozen MS press books???
>
>
get a spid; get another SPID.
>
DO THEY MATCH?
>
because if they do then I'll accept a written apology from Microsoft
and new editions of their books that are CORRECTED.
>
>
On Feb 16, 1:48 pm, "Brian Gideon" <briangid...@ya hoo.comwrote:

>On Feb 16, 3:00 pm, pfc_s...@hotmai l.com wrote:
>>

I didn't think that you could even leave a connection open.

>>

it's one of my biggest complaints about ADO.net; I used to use @@SPID
similiar to sessionID in ASP in order to build some simple apps...

>>

but now there is nothing like that in .NET from what I understand

>>

I just don't understand; why do you even need to close a connection if
you can't leave a connection OPEN?

>>

like I'm being serious and honest here.

>>

Thanks

>>
>Hi,
>>
>What made you think you couldn't leave a connection open?
>>
>Brian

>
>

[aaron.kempf@gmail.com]

Re: Programming - Best Practice

uh sorry about changing logins; most people think that I do it to be
dastardly; when in fact I only do it to get around this 'your ip has
posted too many ~~ crap on google groups'

I really don't try to be an asshole; I am just continously
flabberghasted by the poor architecture choices by the designers for
ADO.net.

I do not appreciate or grasp _ANY_ of these changes.
so I only use datareaders; because datasets seem overly complex to
me.. and I never cared for anything in vb6 except forward only, read-
only

(and of course, performance wins every decision in my book)

I've been writing SQL Server and Vb for a decade.
I started with Basic in 1982 on my commodore 64; and I'll never give
it up.

It just blows my mind; a bunch of drunk monkeys sitting around and
putting architecture solutions into a hat and randomly pulling out
solutions would end up a VASTLY SUPEROR architecture to this DOTNET
crap.

Did they purposefully handicap ADO.net???
I am DEAD SERIOUS.

http://www.startvbdotnet.com/ado/default.aspx

ADO was a connected data access, which means that when a connection to
the database is established the connection remains open until the
application is closed. Leaving the connection open for the lifetime of
the application raises concerns about database security and
network traffic. Also, as databases
are becoming increasingly important and as they are serving more
people, a connected data access model makes us think about its
productivity. For example, an application with connected data access
may do well when connected to two clients, the same may do poorly when
connected to 10 and might be unusable when connected to 100 or more.
Also, open database connections use system resources to a maximum
extent making the system performance less effective.

Why ADO.NET?

To cope up with some of the problems mentioned above, ADO .NET came
into existence. ADO .NET addresses the above mentioned problems by
maintaining a disconnected database access model which means, when an
application interacts with the database, the connection is opened to
serve the request of the application and is closed as soon as the
request is completed.




On Feb 16, 1:48 pm, "Brian Gideon" <briangid...@ya hoo.comwrote:

On Feb 16, 3:00 pm, pfc_s...@hotmai l.com wrote:
>

I didn't think that you could even leave a connection open.

>

it's one of my biggest complaints about ADO.net; I used to use @@SPID
similiar to sessionID in ASP in order to build some simple apps...

>

but now there is nothing like that in .NET from what I understand

>

I just don't understand; why do you even need to close a connection if
you can't leave a connection OPEN?

>

like I'm being serious and honest here.

>

Thanks

>
Hi,
>
What made you think you couldn't leave a connection open?
>
Brian

[Herfried K. Wagner [MVP]]

Re: Programming - Best Practice

"Cor Ligthert [MVP]" <notmyfirstname @planet.nlschri eb:

AS you ask for Best Practice than you don't definitly not need a test to
see if your connection is open.
>
You control your program, so you know if it is open or not and can close
it therefore at the right time without any error.

Not really. Imagine an exception gets thrown when opening the connection.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

[Herfried K. Wagner [MVP]]

Re: Programming - Best Practice

"bigHairy" <mthomas1973@gm ail.comschrieb:

A number of examples I have followed have the following in their
finally statement
>
Try
.....
Catch
.....
Finally
>
If (Not IsNothing(dbCon n)) Then
dbConn.Close()
End If
>
End Try
>
Will my data connection always close using this method or should I
just use dbConn.close?

It will always close because the code inside the 'Finally' branch will be
executed even if the the procedure is left using 'Return' or 'Exit *' inside
'Try' and 'Catch'.

However, I'd prefer this way to write down the code in the 'Finally' block:

\\\
If dbConn IsNot Nothing Then
dbConn.Close()
End If
///

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

[aaron.kempf@gmail.com]

Re: Programming - Best Practice

I didn't ask for an essay on whether it is good or bad. Leaving a
connection OPEN is a requirment for any data access layer that I use;
and I'm not willing to budge on it. I'm not short-sighted; I am
_PRACTICAL_.

For an office with a half dozen users; using a single connection for
each user is hard to beat.

Yes; there are some places I don't want to reuse the same connection
for a single user all day.

But ADO allowed the flexibility to use either strategy.
UNNECESSARY CHANGE IS NOT SEXY.
I DO NOT ACCEPT THE PREMISE THAT IT IS ACCEPTABLE FOR MICROSOFT TO
DICTATE HOW WE WRITE CODE.

WHEN SOMETHING _WORKS_ AND IT HAS WORKED FOR A DECADE, WHY CHANGE IT?

IF SOMETHING IS NOT BROKEN THEN DO NOT CHANGE IT.

from what I understand; it is impossible to get the same SPID from a
single connection in ADO.net that I leave open.

IS THAT CORRECT?

because if it is; then it is just another day in the HOLY WAR AGAINST
DOTNET _CRAP_.
because if it is; then it is just another day in the HOLY WAR AGAINST
DOTNET _CRAP_.
because if it is; then it is just another day in the HOLY WAR AGAINST
DOTNET _CRAP_.
because if it is; then it is just another day in the HOLY WAR AGAINST
DOTNET _CRAP_.
because if it is; then it is just another day in the HOLY WAR AGAINST
DOTNET _CRAP_.


if MS stops introducing 'unnecessary change' for NO PRACTICAL REASON
(and fixes this transgression) then maybe.. just _MAYBE_ I'll lay down
my arms.

-Aaron






On Feb 16, 2:13 pm, "Chris Mullins [MVP]" <cmull...@yahoo .comwrote:

I think you misunderstood what the MS-Press books were saying.
>
The opening and closing of ADO.NET connections, along with the implications
this has on the connection pool, can catch people by surprise.
>
The general rule for your code is open / execute / close - this is how best
you're able to work with the connection pool. There's nothing stopping you
from doing other things though - like holding connections open, or any of a
dozen other (bad) things.
>
Be aware that, under the hood, open / close don't actually open or close
database connections. IT's more "Check out from pool", "Check back into
pool". It's up the pool as to when the connections are actually opened and
closed.
>
--
Chris Mullins, MCSD.NET, MCPD:Enterprise , Microsoft C# MVPhttp://www.coversant.c om/blogs/cmullins
>
"PFC Sadr" <pfc_s...@hotma il.comwrote in message
>
news:1171663581 .468393.248680@ a75g2000cwd.goo glegroups.com.. .
>

uh a half dozen MS press books???
uh a half dozen MS press books???
uh a half dozen MS press books???

>

get a spid; get another SPID.

>

DO THEY MATCH?

>

because if they do then I'll accept a written apology from Microsoft
and new editions of their books that are CORRECTED.

>

On Feb 16, 1:48 pm, "Brian Gideon" <briangid...@ya hoo.comwrote:

On Feb 16, 3:00 pm, pfc_s...@hotmai l.com wrote:

>

I didn't think that you could even leave a connection open.

>

it's one of my biggest complaints about ADO.net; I used to use @@SPID
similiar to sessionID in ASP in order to build some simple apps...

>

but now there is nothing like that in .NET from what I understand

>

I just don't understand; why do you even need to close a connection if
you can't leave a connection OPEN?

>

like I'm being serious and honest here.

>

Thanks

>

Hi,

>

What made you think you couldn't leave a connection open?

>

Brian