close SqlConnection when Page is disposed of

[Steve Richter]

I really miss c++ ....

I have an SqlConnection object within my Web.UI.Page object. The
thinking is that the connection to the database server is opened once
when the page starts to do its thing, then when everything is rendered
down to the browser, and all references to the page object are ended,
the connection to the server can be closed.

First of all, are the SqlConnection Open and Close methods quick
performers and I dont need to concern myself with caching the
SqlConnection. I am not doing a lot of IO.

Is there an event I can hook that is fired at the end of a pages life
that can be used to close the SqlConnection?

But basically, I dont follow the functioning of IDisposable and the
Dispose method. In the context of a web page object, who calls Dispose
and when is it called?

thanks,

-Steve

 

[Brock Allen]

I really miss c++ ....

.NET is a lonley world for the die-hard C++ programmer. But, the more
you resist, the more you're going to have a hard time. Sorry...

Is there an event I can hook that is fired at the end of a pages life
that can be used to close the SqlConnection?

I'd not take this approach (even in a C++ app). The best programming model
for ASP.NET for shared resources that are limited (IOW, expensive) is to
acquire late and release early. I tend to acquire the Connection within a
method, use it (open, do reader, etc), then close it within the same method.
This insures that I won't leak it the connection (assuming finally block
or using statement within the method). If you hold a Connection reference
the lifetime of your page, you're guarenteed that it won't be used for certain
parts of your execution (when all you non-DB work is being done) and thus
no one else can use it. Also, you leave yourself open to leaks because there's
always the possibility that odd circumstances arise and your Dispose isn't
called. You proabably don't want to leave the connection leaked until the
Connection's Finalizer is called.

Now, the performance argument always rears its ugly head here, since opening
and closing the connection would normally be expensive, especially if you're
doing this 3, 4, 5 or 20 times within your page. But these connections are
pooled, so this Opening/Closing pattern is fairly efficient. So again, acquire
late and release early is the general recommendation.

 

[Steve Richter]

.NET is a lonley world for the die-hard C++ programmer. But, the more
you resist, the more you're going to have a hard time. Sorry...

I love the reference types. Why the great one never allowed reference
counting or smart pointers or whatever to be built into C++ is a
decision our team will regret for all time. But you can keep your
concept of boxing and string - StringBuilder - jeez!

On another totally unrelated note ... I am using the .net mutex for the
first time and I am forced to read and actually make a decision on the
following description of a parameter of the WaitOne method:
exitContext
true to exit the synchronization domain for the context before
the wait (if in a synchronized context), and reacquire it; otherwise,
false.

Are their no default values in this f-ing language? This is
programming for the masses? I had these finely crafted syncronization
classes in C++/Win32. To block on a mutex you simply needed one
statement to be place within a scoped block of code. the constructor
and destructor did it all. ah what the hell!!

I'd not take this approach (even in a C++ app). The best programming model
for ASP.NET for shared resources that are limited (IOW, expensive) is to
acquire late and release early. I tend to acquire the Connection within a
method, use it (open, do reader, etc), then close it within the same method.
This insures that I won't leak it the connection (assuming finally block
or using statement within the method).

sounds good. I will conform.

If you hold a Connection reference
the lifetime of your page, you're guarenteed that it won't be used for certain
parts of your execution (when all you non-DB work is being done) and thus
no one else can use it. Also, you leave yourself open to leaks because there's
always the possibility that odd circumstances arise and your Dispose isn't
called. You proabably don't want to leave the connection leaked until the
Connection's Finalizer is called.

Now, the performance argument always rears its ugly head here, since opening
and closing the connection would normally be expensive, especially if you're
doing this 3, 4, 5 or 20 times within your page. But these connections are
pooled, so this Opening/Closing pattern is fairly efficient.

makes sense. SqlServer is probably not as stupid as it looks.

So again, acquire
late and release early is the general recommendation.

thanks,

-Steve

 

[Brock Allen]

I love the reference types. Why the great one never allowed reference

counting or smart pointers or whatever to be built into C++ is a
decision our team will regret for all time. But you can keep your
concept of boxing and string - StringBuilder - jeez!

Well, it's not my concept, but boxing is a necessary evil if you are to have
a singly rooted OO type system where you optimize the primitive types (value
types) by allocating them inline on the stack (when that's the declaration
context). StringBuilder is a necessary evil also when your string types are
immutable.

On another totally unrelated note ... I am using the .net mutex for
the
first time and I am forced to read and actually make a decision on the
following description of a parameter of the WaitOne method:
exitContext
true to exit the synchronization domain for the context before
the wait (if in a synchronized context), and reacquire it;
otherwise,
false.

This has to do with COM+'s activities, IIRC. Yeah, I agree this is an icky
parameter.

Are their no default values in this f-ing language?

Experience has shown that default values are a horrible design, as the implementation
embeds the default value into the client code. This is a huge problem when
my component changes it semantics (IOW, I want a new default value as the
parameter), because I version my component, but I am unable to rebuild your
client app. This worked fine in monolithic C++ apps of the days of old, but
no longer in today's world. As a side note, Clemens Syperski has a great
book called "Component Software - Beyond Object-Oriented Programming " which
does a great job of showing why OOP is quite dead for modern applications.
It has its place, of course, but for modern applications the promise of OOP
has fallen short. I'll leave it at that so we don't get into a religious
debate, as that's not my intent

This is programming for the masses?

Ummm, that's hard to say. That's what the x86 ASM die-hards said of C, and
then what the die-hard C programmers said of C++.

I had these finely crafted syncronization
classes in C++/Win32. To block on a mutex you simply needed one
statement to be place within a scoped block of code. the constructor
and destructor did it all. ah what the hell!!

Hmm, sounds like you designed an API for the masses, no?

This gets back to the notion of deterministic finalization and how we don't
necessarily have it in the CLR. This is a trade off. The CLR uses garbage
collection as the framework for memory management, rather than the C runtime
library (or C++). The CLR memory management architecture (with GC) is much
faster than how it is done in C++. And as icing on the cake, you no longer
have the problems of memory leaks and double deletes, among other subtle
memory related bugs. The tradeoff? Well, for non-memory resources you still
need to explicity manage the resource, such as closing a file handle, closing
a DB connection, releasing a mutex, etc. The best thing we have is the IDisposable
and using statement. I agree, it's not as clean feeling as the stack instance
wrapper class that we all used to build and use.

sounds good. I will conform.

Ah ha! Microsoft will pay handsomly for yet another convert. You hear that
Bill? ((heh, j/k))

Thanks for the post -- these are the fun issues to discuss. Good luck.

 

[Guest]

Steve,

You should dispose of the connection as soon as you're done using it.
Anything that implements IDisposable probably holds some underlying resource
that you want to let go of as soon as possible. SqlConnection is pooled by
the framework creating then an disposing of them is inexpensive. You want to
dispose of it ASAP just so that you don't accidentally forget to dispose of
one.

Whenever you use a class that implements IDisposable I suggest doing
something like this;

using( SqlConnection conn = new SqlConnection( myConnectionString ) )
{
conn.Open();

// Do something with the connection
}

Some people prefer;

SqlConnection conn = null;
try
{
SqlConnection conn = new SqlConnection( myConnectionString );
conn.Open();

// Do something with the connection
}
finally
{
if ( conn != null ) conn.Close();
}

I would caution against this, since you may create several IDisposable
objects inside the try block and then you need to explicity release them
inside the finally block. I find that occasionally a developer will forget
to explicitly release one, especially if there are many.