In this article I will focus on buiding the data access layer in ASP.NET 3.5 Web applications with LINQ to SQL and SQL Server.

Lots of articles introduce the LINQ technology, the LINQ to SQL object-relational mapping technology and ASP.NET 3.5 so I will skips these introduction and will explain the main problem I found when I started using LINQ to SQL in ASP.NET web applications. The problem was standard: “How to manage the database session?”.

Managing LINQ to SQL Database Session (the DataContext) in ASP.NET Web Applications

As an experienced Java developer when I started using LINQ to SQL with ASP.NET I had rich experience with Hibernate, NHibernate and other object-relational mapping (ORM) engines like OpenJPA. I was familiar with the common patterns and approaches used to manage the database session and transaction in Web applications (mostly in Java) and I wanted to leverage my existing experience.

The “Session-per-Request” Pattern

The first idea I came upon was to implement the classical “session-per-request” pattern. This pattern opens the database sesion in the beginning of the HTTP request and closes it at the end of the HTTP request. Thus the dynamic Web pages that interract with the database assume the connection is already open. Usually I also start a transaction along with the session and commit it when closing the session. If I need multiple transactions, I start and end them manually. In the most common case the developer do not write any additional code to handle the database connection and session. This worksvery wells for lots of my projects.

The “Session-per-Request” Pattern in Java (with Servlet Filter)

In Java the “session-per-request” pattern is best implemented with so called “servlet filter” which is just an HTTP request interceptior. Inside the intercepted HTTP request I open the Hibernate session, process the HTTP request and finally close the HTTP session. I can go further and catch the exceptions and rollback transaction as well. Typically after opening the session I put it in the current thread’s local context so all my database access objects (DAO classes) do not need to take it as a parameter. The DAO objects just get the session from the local context of the currently running thread. This works great in Java for many projects and many years.

Typical Java code for this can look like the following:

public class SessionPerRequestFilter implements Filter {
  public void doFilter(ServletRequest request, ServletResponse response,
  FilterChain chain) throws IOException, ServletException {
    try {
      HibernateSessionManager.openSession();
      chain.doFilter(request, response);
    } finally {
      HibernateSessionManager.closeSession();
    }
  }
  public void init(FilterConfig config) throws ServletException { }
  public void destroy() { }
}

In the above code the openSession() and closeSession() handle the transaction as well.

The “Session-per-Request” Pattern in ASP.NET

I wanted to implement the same pattern in ASP.NET with LINQ to SQL. In ASP.NET we don’t have servlet filters and HTTP request interception functionality. We have something similar, but with limited functionality and flexibility, the so called HTTP handlers. In ASP.NET the HTTP handlers can catch specific events that are produced during the execution lifecycle of the ASP.NET pages. We can catch events like BeginRequest and EndRequest but we can’t completely override the request processing logic (we don’t have chain.doFilter(request, response) which can be called or not).

In HTTP Module BeginRequest and EndRequest Execute in Different Threads

I don’t know if this is an optimization of architectural issue, but unlike in Java EE in ASP.NET the events BeginRequest and EndRequest for the same HTTP request can execute in different threads. ASP.NET actually uses the same thread for executing several concurrent requests. Really! Thus we cannot open the database connection in BeginRequest, put it in the thread’s local context, use it from the DAO classes when rendering the application’s dynamic web pages and finally in EndRequest remove it from the thread’s local context. No, this does not work with HttpHandler. I found this by debugging.

I tried to fix this by using the Application_BeginRequest and Application_EndRequest in Global.asax, but the result was the same: sometimes ASP.NET runs Application_BeginRequest and Application_EndRequest event handlers belonging to concurrent requests in the same thread.

After lost of experiments I fixed this.

In Global.asax Application_PreRequestHandlerExecute and Application_PostRequestHandlerExecutein Execute in the Same Thread

Following the documentation for the ASP.NET execution lifecycle I found that I can intercept the execution of the HTTP requests in many other ways, in the different stages of the request procesing. I found that the events Application_PreRequestHandlerExecute and Application_PostRequestHandlerExecute are called in the same thread for each request. If we have two concurrent HTTP requests, ASP.NET wil run two parallel threads and will execute each request in separate thread. The same applies for the Application_PreRequestHandlerExecute and Application_PostRequestHandlerExecute events.

Implementing Session-Per-Request Pattern in ASP.NET with LINQ to SQL

Finally I put the following code in Global.asax:

void Application_PreRequestHandlerExecute(object sender, EventArgs e)
{
  DataContextManager.CreateMMDataContext();
}

void Application_PostRequestHandlerExecute(object sender, EventArgs e)
{
  DataContextManager.DisposeMMDataContext();
}

This is what I have in the DataContextManager class which handles my DataContext (database session) by few static methods:

</pre>
public class DataContextManager
{
 private static LocalDataStoreSlot dataContextSlot = Thread.GetNamedDataSlot("DataContext");

/// <summary></summary>
 /// Creates new data context and stores it in the current thread's context.
 /// If data context already exists, do nothing.
 ///
 public static void CreateMMDataContext()
 {
 MMDataContext mmDataContext = (MMDataContext)Thread.GetData(dataContextSlot);
 if (mmDataContext == null)
 {
 mmDataContext = new MMDataContext();
 Thread.SetData(dataContextSlot, mmDataContext);
 }
 }

/// <summary></summary>
 /// Return the data context associated with the current thread.
 /// Throws and InvalidOperationException if no data context is associated with the current thread.
 ///
 public static MMDataContext MMDataContext
 {
 get
 {
 MMDataContext mmDataContext = (MMDataContext)Thread.GetData(dataContextSlot);
 if (mmDataContext != null)
 {
 return mmDataContext;
 }
 else
 {
 throw new InvalidOperationException(
 "The data context is not found in the current thread's context.");
 }
 }
 }

/// <summary></summary>
 /// Disposes the data context currently associated with the current thread
 /// and removes it from the thread context. If no data context is asssociated
 /// with the current thread, does nothing.
 ///
 public static void DisposeMMDataContext()
 {
 MMDataContext mmDataContext = (MMDataContext)Thread.GetData(dataContextSlot);
 if (mmDataContext != null)
{
 mmDataContext.Dispose();
 Thread.SetData(dataContextSlot, null);
 }
 }
}

In the examples my data context (.dbml mapping file) is called MMDataContext.

Implementing the Data Access Logic (DAO Classes)

Finally I implemented the data access logic as set of DAO classes and a generic basic DAO parent class:

</pre>
public class GenericDAO<tentity></tentity> where TEntity : class
{
protected static MMDataContext MMDataContext
{
get
{
return DataContextManager.MMDataContext;
}
}

public static List<tentity></tentity> FindAll()
{
List<tentity></tentity> enitities = MMDataContext.GetTable<tentity></tentity>().ToList<tentity></tentity>();
return enitities;
}

public static void SubmitChanges()
{
MMDataContext.SubmitChanges();
}
}

I wanted to implement the method:

public static T FindByPrimaryKey(int countryId)

… but I failed. I think there is no way to do this as generic. This is a weakness of the LINQ to SQL framework.

Once I have the generic baseic DAO class I can use it to define my DAP classes, e.g.

public class EventLogDAO : GenericDAO<eventlog></eventlog>
{
public static EventLog[] GetAllLogs(string userEmail)
{
var logs = from l in MMDataContext.EventLogs
where l.User.Email == userEmail
orderby l.EventDate descending
select l;
List<eventlog></eventlog> results = new List<eventlog></eventlog>();
if (logs.Count() != 0)
{
results = logs.ToList();
}
return results.ToArray();
}

public static EventLog FindById(int id)
{
EventLog log = (from l in MMDataContext.EventLogs
where l.EventLogId == id
select l).SingleOrDefault();
return log;
}

public static void MarkAsRead(int id)
{
EventLog log = FindById(id);
log.MessageRead = true;
MMDataContext.SubmitChanges();
}
...
}

Implementing the ASPX Pages that Use the DAO Classes

Once I have implemented the DAO classes I can use them directly in an .aspx page or ascx user control by ObjectDataSource or at event handler. All methods are static and developers do not need to care about thesession management. A sample ASPX page is given below:

< %@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true"
    CodeFile="BrowseCities.aspx.cs" Inherits="BrowseCities" Title="All Cities" %>
<asp :Content ID="ContentBrowseCities" ContentPlaceHolderID="ContentPlaceHolderBody"
    runat="Server">
    <div style="text-align: center">
        <asp :ListView ID="ListViewCities" runat="server" ItemPlaceholderID="PlaceHolderCity"
            DataSourceID="ObjectDataSourceCities">
            <layouttemplate>
                <asp :PlaceHolder ID="PlaceHolderCity" runat="server" />
            </layouttemplate>
            <itemtemplate>
                <asp :LinkButton ID="LinkButtonCity" runat="server" OnCommand="LinkButtonCity_Click"
                Text="<%# Container.DataItem.ToString() %>" CommandName="Click"/>
                <br />
                <br />
            </asp></itemtemplate>
        </asp>
        <asp :DataPager ID="DataPagerCities" runat="server" PagedControlID="ListViewCities">
                    <fields>
                        <asp :NumericPagerField NextPageText=">>" PreviousPageText="< <" />
                    </asp></fields>
                </asp>
        <asp :ObjectDataSource ID="ObjectDataSourceCities" runat="server" EnablePaging="true"
            SelectCountMethod="GetNumberOfCities" SelectMethod="GetCities" StartRowIndexParameterName="startIndex" TypeName="MM.Core.DAO.AddressDAO"></asp>
    </div>
</asp>