Chris,
In the old days the rule for DB connections was "Open Late, Close Early". This rule really still applies when building desktop/client applications. With regards to web applications however everything is happening in one fell swoop so it's kind of silly to implement that strategy when doing multiple calls to the same data store. From several database driven site implementations, I can offer the following suggestion as a feasible option.
My method consisted of having pages that all used common functionality (in ASP it was includes) which prepared and opened the database connection and closed it at the beginning and end (respectively) of page execution. This minimized the number of opens and closes of the DB connection down to just a single instance of each. I have working proof that this method works quite well. All your pages do is use the existing DB connection to execute their calls. The site framework (or "skeleton" as I have come to call it to not conflict with .Net's "Framework") does all the work involved with connecting to the DB. This saves a tremendous amount of code. This technique works in certain circumstances, but you need to keep a wary eye on how you use it. If you create a utility class that has data control functions in it, you may find that you need to add method parameters or properties to them to provide a reference to the DBConnection object because those classes won't necessarily always be running within the context of an executing page. This becomes even more apparent when you start building shared functions in generic class libraries. Adjusting for this is not difficult, you just have to be wary of it.
So anyway, here's something more tangible...
In the ASP.Net paradigm, building pages from a skeleton becomes much easier than with classic ASP. Here's my idea simplified: You need to create a base page class that all your aspx's will inherit from.
[u]BasePage.vb</u>
Code:
Public Class BasePage
Inherits System.Web.UI.Page
Private m_objDBConn As _
New System.Data.SqlClient.SqlConnection
Public ReadOnly Property DBConn() As _
System.Data.SqlClient.SqlConnection
Get
Return m_objDBConn
End Get
End Property
Private Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: Do all your setup for m_objDBConn here
End Sub
Private Sub Page_Unload(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Unload
'TODO: Close and cleanup m_objDBConn here
End Sub
End Class
Now, in all your aspx code behind files you change their inherits statement from
Code:
Inherits System.Web.UI.Page
to
and voila, you can now access the connection object...
Before you say "But wait a minute... what if I have user controls on my pages that need the DB?!" take a look at this:
[u]BaseUserControl.vb</u>
Code:
Public Class BaseUserControl
Inherits System.Web.UI.UserControl
Private m_objDBConn As _
New System.Data.SqlClient.SqlConnection
Public ReadOnly Property DBConn() As _
System.Data.SqlClient.SqlConnection
Get
Return m_objDBConn
End Get
End Property
Private Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
If Not Me.Page Is Nothing Then
m_objDBConn = CType(Me.Page, BasePage).DBConn
End If
End Sub
End Class
Now, all your user controls inherit from this class instead of System.Web.UI.UserControl. ** Note that this works only if the user controls inheriting from BaseUserControl are put onto pages that inherit from BasePage. Usually this is not a problem.
Now just like in the page codebehind, you can access the DB connection object with "Me.DBConn".
You can extend this technique to work with loads of common properties that you use site wide. The great part of something like this is that you can strongly type data that gets managed thru the viewstate, cookie or the session objects. The base page class loads then all up and populates the properties, and your calling code never has to worry about where the data comes from or where it needs to go to. If you build all your common properties/objects into a little class and make an instance of that class a member of the base page, then it gets even easier cause all you need to know is what the basepage property is. I use "Common". So in your code, you can just call "Me.Common." and you get all your common "stuff". Using the method above, you can make the common property a member of both the page and user control base classes and tie them together. When you add common properties to your common class, all pages/controls are updated.
I have implemented several variations of the example above and would be happy to share more details and ideas with people. Also, if you have any criticism/comments/suggestions or anything else regarding my examples, please let me know. I'm always looking for improvements.
Good luck!
Peter
SQL Access
