asp.net database best practice

  • Thread starter Thread starter charlie
  • Start date Start date
C

charlie

Hi there,

My asp.net application uses methods in classes to do database updates.
So for example on any given page when a user clicks the submit button, my
click event might call three different methods each of which opens a
database connection, updates a table, and closes the connection. This
doesn't seem very efficient. I think I've been told not to worry about it,
that the connections are pooled or something. Can I create a connection and
pass it to multiple class methods one after the other so that they all use
the same connection? If I could would it be worth it?
 
The basic idea behind an asp.net application is that method calls to
the database are used once, and thrown away. The application does not
maintain state between calls. If it did, you would have all kinds of
problems with dropped connections, locks being held for long periods
of time, etc. Connection pooling is what makes this efficient as the
asp.net worker process uses the same credentials and identical
connection string each time. To see what's going on under the hood,
open a Profiler trace on the app and look at the traffic. For details
on the coding end, see "Creating ASP.NET Web Applications" in the help
file:
ms-help://MS.VSCC.2003/MS.MSDNQTR.2004APR.1033/cpguide/html/cpconCreatingASPWebApplications.htm

--Mary
 
I'm really not talking about connections across mutiple page submissions.
Let me restate my question with an example. Suppose I have a page which
submits an order, In my submit event I create a customer object, set the
last order date property, and call a method to update the customer table. I
also create an order object and call a method to output records to the order
tables. What I was worried about is that each method is going to open a
database connection, perform database operations and close the connection.
Is this ok? Is it standard practice? Is there a better practice?
 
Connection pooling is useful across your entire application as well as
between method calls. You should read the following articles (particularly
the second one) to learn about the benefits that connection pooling provides
to your application.

http://msdn.microsoft.com/library/d...vsentpro/html/veconusingconnectionpooling.asp
http://msdn.microsoft.com/library/d...nectionpoolingforsqlservernetdataprovider.asp
(This article is for SqlConnections but the information holds true for
OleDbConnections as well)

Some key points to remember are:
1.) When you call Connection.Open(), the provider checks its connection
pools for matching, available connections. If a matching, available
connection is found, it is used otherwise a new connection pool and
connection are created and used.
2.) When you call Connection.Close() or Connection.Dispose(), the connection
(as opposed to the connection object) is released back into the pool
(depending on a couple of criteria) but not actually destroyed until the
active process ends.

This being said, opening and closing connections between methods on the same
page allows the method to do any work that does not need a connection to the
database without tying up a connection. When a connection is finally
needed, one is grabbed from the pool, database actions are then performed on
the connection, and the connection is released back into the pool, freeing
the connection for use by another process. This methodology is far more
efficient than keeping a persistent connection open for the duration of the
page/application.

HTH,
Dave
 
Charlie,

ADO.NET is really really cool - that it pools your connections as long as
the connection string remains EXACTLY the same and the connections are
closed diligently. If you really wanna know how that all works under the
hood - I invite you to read this blogpost I posted a few days back -
(Dispose/Close/Connection pooling - Here is how it all works !!! )
http://dotnetjunkies.com/WebLog/sahilmalik/archive/2004/11/12/31798.aspx

The above delves into whats inside ADO.NET under the hood and explains - how
in the world does pooling work afterall? Because there still is a confusion
about "Should I dispose?" "Should I close"? "Is my connection object
pooled"? "Is the physical connection pooled"? .. well .. as long as the
connectionstring remains unchanged - and you do close the connection -
pooling will work automagically. So in essence the connection object itself
isn't pooled - the connection is pooled.

So what is the recommended approach?

Easy - create a Data Access Layer. (DAL = Data Access Layer to save my hands
lots of typing)

This data access layer is nothing but a class or a bunch of classes that
abstract database access for the rest of the application. This way, your DAL
makes sure you end up closing connections - because unless you close a
connection - it will not be pooled. (So what does Dispose do? Should you
dispose or should you close? --- For answers - Read the above blogpost).

Just remember to - a) Make sure connections are opened as late as
possible/closed soon as they can be.
b) Nobody circumvents the DAL.
c) Don't store the DAL instance in session and write a stateless DAL.

One good starting point of a sample DAL is the Microsoft Data Access
Application Block. I personally don't like it for these reasons -
http://dotnetjunkies.com/WebLog/sahilmalik/archive/2004/11/24/33412.aspx .

Apparently it is good for a quickie job; because it very well masks the
complexity of ADO.NET but for an enterprise app' especially a high demand
asp.net app - I'd recommend writing your very own stateless DAL. I explain
on how to write a sample database agnostic DAL in my book chapter #10.

Hope this helped.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
How is transactional integrity being handled in theese cases? The first
update may succeed, the second fails and the first should be rolled back. I
have seen very little code samples doing anything with transactions.

Olav
 
Back
Top