SqlConnections, abstraction etc

[Ben]

Hi I have a question concerning SqlConnections and when they should be
opened and closed.

Right now I have a database utilities class. I instantiate it when in
page_load and dispose it in page_prerender, which opens and closes class
level sqlconnection.

Now, say I need to run a SqlCommand, all I do is:
SqlCommand oCmd = DBUtils.CreateSqlCommand("spName");

And I get a SqlCommand, type storedprocedure, with the connection set to the
classes database. I add parameters etc, and execute. Everything seems
great. I've been told (#mscorlib and #asp.net on efnet) that this isn't a
good approach. I should be using late open and early close on my database
connection. Especially to take advantage of connection pooling. This means
I need code like...

using (SqlCommand oCmd = DBUtils.CreateSqlCommand("spName"))
{
oCmd.Connection.Open();
oCmd.ExecuteScalar();
oCmd.Connection.Close();
}

Assuming all things are equal except the connection in dbutils is closed.
Is that a better approach? To me that doesn't guarantee the connection
closes or anything.

Maybe I should use...

using (SqlConnection oConn = DBUtils.DatabaseReference)
{
oCmd.Connection = oConn
oCmd.Connection.Open();
oCmd.ExecuteScalar();
oCmd.Connection.Close();
}

Or something like that??? Basically I'm trying to farm out as much of the
database coding as possible. Is this possible? What is everyone else
doing?

What if I have a class in my page that needs to access the database? What
do I do now? Pass reference to my database class?

Please, any help / advise will be greatly appreciated!

Thanks,
Ben

 

[David Browne]

Hi I have a question concerning SqlConnections and when they should be
opened and closed.

Right now I have a database utilities class. I instantiate it when in
page_load and dispose it in page_prerender, which opens and closes class
level sqlconnection.

Now, say I need to run a SqlCommand, all I do is:
SqlCommand oCmd = DBUtils.CreateSqlCommand("spName");

And I get a SqlCommand, type storedprocedure, with the connection set to the
classes database. I add parameters etc, and execute. Everything seems
great. I've been told (#mscorlib and #asp.net on efnet) that this isn't a
good approach.

Whoever told you was wrong. Except possibly that page_prerender is possibly
not the best place to close the connection. Instead override Page.Dispose
and close the connection there. If an exception occurs somewhere you still
need to close the connection, and Page.Dispose will run even if another page
method throws an exception.

But the basic idea of tying the life of the connection to the life of the
page instance is fine. If you open and close your connections around each
command you may get slightly fewer connections in your connection pool, but
I wouldn't worry about it. You will never have more connections than
ASP.NET worker threads. No matter what you do you may have that many, so
it's just fine.

David

 

[Steven Cheng[MSFT]]

Hi Ben,

As for the connection management problem, I agree with David that it dosn't
matter much which coding style you use, as long as we remember to close the
connection after using it. Also, the
========================
I should be using late open and early close on my database
connection.
========================
you mentioned is also right, that'll help the connection pool to better
determine how to adjust the pooled connections. And since the actual
connections are managed by the connectdion pool which is apparent to us(how
many acutal instances of connection will it maintain), we don't need to
care about it.

#Connection Pooling for the .NET Framework Data Provider for SQL Server
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconconnectionpoolingf
orsqlservernetdataprovider.asp?frame=true

So, as for your situation, you'd like to use connection in a certain page,
just open it and be sure to close it before the page is disposed, it's
enough.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Get Preview at ASP.NET whidbey
http://msdn.microsoft.com/asp.net/whidbey/default.aspx

 

[Ben]

Thanks,

I'll digest everything and see what fits my senerio.

 

[Ben]

Whoever told you was wrong. Except possibly that page_prerender is possibly
not the best place to close the connection. Instead override Page.Dispose
and close the connection there. If an exception occurs somewhere you still
need to close the connection, and Page.Dispose will run even if another page
method throws an exception.

But the basic idea of tying the life of the connection to the life of the
page instance is fine. If you open and close your connections around each
command you may get slightly fewer connections in your connection pool, but
I wouldn't worry about it. You will never have more connections than
ASP.NET worker threads. No matter what you do you may have that many, so
it's just fine.

David

Thanks for the pointers, I will move my class dispose to page.dispose.