Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > ASP.NET 3.5 Professionals
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
ASP.NET 3.5 Professionals If you are an experienced ASP.NET programmer, this is the forum for your 3.5 questions. Please also see the Visual Web Developer 2008 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 3.5 Professionals section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 12th, 2008, 09:20 AM
Authorized User
Join Date: Jun 2003
Location: , , .
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default Data Access Layer Question

This is an architecture question. Which is a better method for database access?

1. Open and close the connection with each Data Access Layer method call?

2. Have a connection object at the page level that only gets opened once and is used for all Data Access Layer method calls during that page life cycle, and dispose of it in the page_unload?
  #2 (permalink)  
Old December 12th, 2008, 09:52 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons

As a general rule of thumb you should open your connection, process your request, and then close your connection directly after. By doing this it is less likely that you are going to run into connection leaks and other unsavory things that can grind your application to a halt.

However, there is nothing wrong with this code:

CSharp Code:
public static class DataAccess
   private static SqlConnection con;
   private static SqlCommand cmd;

   private static DataRowCollection DoSomething()
         //Open connection
         //close connection

Also, if you are using SQL Server you may want to look into using the Enterprise Libraries for data access: http://www.codeplex.com/entlib

Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
  #3 (permalink)  
Old December 13th, 2008, 12:50 AM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

I'm a big fan of nested using statments to implement your option 1 for each of your Select, Insert, Update, Delete, etc. methods. Like:

public static SomeList GetSomeList()
 using (SqlConnection connection = new SqlConnection(AppConfiguration.ConnectionString))
                using (SqlCommand command = connection.CreateCommand())
                    // Initialize command
                    using (SqlDataReader reader = command.ExecuteReader())
                        /// Load collection
            return collection;
etc., etc....

That way your IDisposable objects (like your connection object) will be disposed automatically when the using statment exits.

Using statemens actually compile to:

    SqlConnection connection = new SqlConnection(AppConfiguration.ConnectionString)
    if (connection != null)
So connection.Dispose gets called even if an exception occurs. Module level connection objects are old school. Check out MSDN on using statments.


  #4 (permalink)  
Old December 13th, 2008, 01:32 AM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Oh, and check out Imar's N-Layered Web App articles at http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=476 if you havn't already. He also takes the nested using statement approach, as does CSLA, as do many methods in the SqlHelper class in the older version of the Data Access Application Block (havn't checked out the new version in Enterprise Library 4.1 that Doug mentioned yet), as does probably every other high profile data access library you're likely to run into out there. I think its pretty fair to say that using statement syntax is a prevailing data access layer "best practice".
  #5 (permalink)  
Old December 16th, 2008, 05:49 AM
Wrox Author
Join Date: Jul 2008
Location: Des Moines, IA, USA.
Posts: 74
Thanks: 1
Thanked 8 Times in 8 Posts
Send a message via MSN to msellers

I am another big believer in using the APplication Blocks as it really simplifies the management on the application side, but the key is as others have mentioned to ensure that the connections are always closed. Also, I've found that keeping the connections open for as little time as possible is a good thing, especially as applications begin to grow in size and load.
Mitchel Sellers
Microsoft C# MVP, MCITP
Director of Development
IowaComputerGurus Inc.

My blog for .NET and DotNetNuke info

Author of "Professional DotNetNuke Module Programming"

Tech Editor on "Visual Studio 2010 six-in-one" and "Pro C# 4.0"

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Handle error from database layer to applican layer khatu_jec ASP.NET 2.0 Basics 1 November 9th, 2008 03:51 PM
Independent Data Access Layer Sumith Asanka .NET Framework 2.0 2 September 28th, 2007 02:20 AM
Data Abstraction Layer kcraft Pro PHP 6 March 18th, 2006 04:29 PM
Question about Business Layer hasanali00 BOOK: ASP.NET Website Programming Problem-Design-Solution 3 March 21st, 2005 06:49 PM

All times are GMT -4. The time now is 06:34 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.