Database connections

[DeltaTech]

A newbie's dilemma

If opening a connection is resource intensive, and with ASP.Net we have to explicitly close a connection, when is it best to open and close?

Of course, if you have just one procedure that retrieves data for display, then this is a non-issue, as you would open, retrieve and close in that procedure.

Suppose you had several procedures in a page that needed access to the database?

Sub Page_Load()

  DisplayThis()

End Sub

Sub DisplayThis()

  Retrieve and display options

  If Option selected
    DisplayThat
  End If

End Sub

Sub DisplayThat
 
  Retrieve and display option values

End Sub

Well, you get the idea.

Would it be best to open and close once? If so, should this be done in the Page_Load/Page_Unload events?

Or would it be more efficient to open and close in each of the procedures that requires access, which would be resource intensive?

Then again, the code to open and close the connection should be common to all pages so that we need not write the code in every page. Is there a way to do this?

Any guidance or pointers would be very much appreciated.

Thanks in advance

 

[jbenson001]

You would be best to have a class that would open the connection, get the data or do whatever manipulation you need, then close the connection.

Look into Microsoft's Data AccessApplication block.

 

[jmeckley]

data access is a solved problem. implementing your own data access is a waist of time. check out 3rd party libraries like Active Record, Nhibernate, WilsonORMapper, LLBL, or MS Data Access Blocks. They solve this problem for you.

check out the concept of session per view and unit of work pattern. with web applications this is very simple. each request is a unit of work. In simplest terms open a connection/transaction at the beginning of the request and commit/close at the end.

with AR and NH the session object can be treated as the raw unit of work. session != db connection as the session is smart enough to know when/how to actually open a true db connection.

I have found that managing units of works beyond begin/end request events breaks the principle of separation of concerns and can lead to maintainability issues.

Jason Meckley
Programmer
Specialty Bakers, Inc.