Data Access Layer Question

AgentSmith · December 12th, 2008, 09:20 AM

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?

dparsons · December 12th, 2008, 09:52 AM

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
         //Process 
         //close connection
         //return
   }
}

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

hth.
-Doug

Bob Bedell · December 13th, 2008, 12:50 AM

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:

Code:

public static SomeList GetSomeList()
{
 ... 
 using (SqlConnection connection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                connection.Open();
                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:

Code:

try
  {
    SqlConnection connection = new SqlConnection(AppConfiguration.ConnectionString)
    connection.Open();
  }
  finally
  {
    if (connection != null)
      ((IDisposable)connection).Dispose();
  }

So connection.Dispose gets called even if an exception occurs. Module level connection objects are old school. Check out MSDN on using statments.

HTH,

Bob

Bob Bedell · December 13th, 2008, 01:32 AM

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".

msellers · December 16th, 2008, 05:49 AM

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.