Building a Multi-Tier Web Application in the .NET 3.5 Framework Using LINQ to SQL

• Download Demo Application (VS2008 Beta2) with DB Script - 298,885 KB

Contents

• Introduction
• Architectural Considerations
• LINQ to SQL
  • DataContext Class
  • Entity Classes
  • Mapping
  • Regenerating DataContext
• Creating the Data Linq layer
• Implementing the Data Access Layer Using LINQ
• Building the Business Facade Layer
• Designing and Coding the Presentation Layer
• Summary and Conclusion
• References

Introduction

In the latest version of the .NET framework (version 3.5), Microsoft have provided a way to define the queries in the programming language (C# 3.0, VB.NET 9.0) using LINQ, which is stands for Language-Integrated query. It's basically a programming concept to query the data against the following :

• database (LINQ to SQL),
• dataset (LINQ to Dataset),
• xml (LINQ to XML) and
• objects (LINK to Entities).

In this article I will show you an approach on how to build multi-tier web application in ASP.NET 3.0 Using LINQ to SQL. In the present programming world, creating N-Tier application has become a common practice. and .NET framework is more flexible in providing a greater support to do the same. In general N-Tier applications have the following layers or tiers : 1. Presentation 2. Business Logic 3. Data Access and 4. Database Layer. Each of these layers serves a dinstinct task. The architecture explained in this article is similar to the classic N-Tire but will replace the Database layer with a new layer called DataLinq Layer that Uses LINQ to SQL to perform the database operation.

Architectural Considerations

If you have worked on large ASP.NET projects, you might have noticed that more time is spent on writing the code for the components than writing the code for the web pages. Sometimes the process of organizing the components itself can become time-consuming. You may run into architectural issues concerning which is the best way to design the web application. This article explains about the N-tier design model using LINQ.

For your information, my intention in writing this article is to show you a good design model, not to come up with the conclusion or to claim that this is the best design model for N-tier applications. Developers always have their own passionate opinions about architecture, so any statement about proper architecture is controversial. However, it's always good practice to separate the code into logical segments as shown in Figure 1.1. Organizing the code in such a way makes it easier to maintain and extend the application.

In Figure 1.1, you can see that business components are separated into layers. The best method to organize the code, as shown in the figure, is to create a separate class library project for each business component. Visual Studio allows us to create multiple projects under the same solution. So, we have the provision to add ASP.NET application and class library projects to the same solution. When you build the solution, a new assembly is created for each project in the corresponding project's bin folder under the solution.

There are two different ways of referencing the assemblies in the ASP.NET application. Either 1. we can copy the DLL files to the application's bin folder manually or 2. we can select the library project from the Add Reference option of the website menu. The second method automatically updates the assemblies in the application's bin folder when you build the solution. This way of organizing the code makes it easier to modify/update a particular part of the project and also to move the application from one server to another server. I don't want to go much deeper in this section by showing the screenshots and explaining how to do such a thing because I have other important topics to discuss in further sections.

I presume that you already have some experience working on classic 3-tier applications and I'll just give you a tip on which layer should reference the assembly of which other layers. After that, you are on your own to add the assembly references with the above given details. The direction of the arrows in Figure 1.1 depicts the communication that happens between the layers. So, here it goes:

• Data Access Layer should have the reference of Data LINQ Layer and Business Facade Layer since it interacts only with these two layers.
• Business Facade Layer should have the reference of Data LINQ Layer and Data Access Layer since it uses the Entity classes available in Data LINQ Layer to create the instances of the table Entities (discussed in detail in the below section) and call the methods available in Data Access Layer.
• Presentation Layer should have the reference of Data LINQ Layer and Business Facade Layer.

LINQ to SQL

LINQ to SQL provides a way to access the database and allows us to do all the database related operations such as select, insert, update and delete. It eliminates the process of writing stored procedures and calling them from the Data Access Layer. LINQ to SQL automatically generates the appropriate SQL statements needed for the DB operation at run time. The above figure is a representation of the SampleDB database shown in the designer. When this (designer.layout) file is saved to the project, LINQ to SQL creates a VB class file with the DataContext class to access the database. It also creates the necessary Entity classes for the tables presented in the designer. Each table presented in the designer will have a corresponding Entity class in the class file.

The tables added to the designer add all the properties of the database, including the table relationships. The direction of the arrows in the designer represents the Primary Key/Foreign Key relationship in the database.

DataContext Class

A DataContext class will be generated for each LINQ to SQL designer added to the solution. It is the main class used to query the database, retrieve the records and transmit the changes back to the database. It contains properties that represent the each table within the database.

Entity Classes

LINQ to SQL creates an Entity class for each table inserted into the designer layout (DBML file). These Entity classes contain a collection of strongly-typed properties which are mapped to the columns of the corresponding table in the database. LINQ to SQL uses these Entity classes to generate the SQL statements for the DB operation. Also, we can create instances of these Entity class and add data to them, using them as data transfer objects between the layers.

Mapping

Classes and properties defined within the DataContext class are directly mapped to the corresponding tables and columns in the database. By default, in the DataContext class LINQ uses is the AttributeMappingSource class. It inherits an abstract class called MappingSource to store information about the table and column mapping. This information is required while retrieving records from the database and submitting changes to the database.

Regenerating DataContext

If any changes are made to the database table design, updating of the DataContext class is also required. It is recommended to regenerate the entire DataContext class in case of any modifications or updates. To do this, delete the DataContext class (VB file) and then remove the corresponding Entity (table) from the designer layout and insert it again from the database. After doing this, just saving the file will automatically create the DataContext class. Alternatively, if you right-click on the DBML file, you will find the "Run Custom Tool" option. Clicking on it will regenerate the DataContext class.

Regenerating DataContext

If any changes made to the Database table Design then updating the DataContext class is also required. It is recommended to regenerate the enitre DataContext Class in case of any modification/updation. To do this, delete the DataContext class(.vb file), then Remove the corressponding entity (Table) from the designer layout and insert it again from the Database. After doing this, just saving the file would automatically create the DataContext class or if you right-click on the .dbml file, you will find the "Run Custom Tool" option. Click on it will regenerate the DataContext Class.

Creating Data Linq layer

The Figure 1.2 shows the detailed view of a Data Linq Layer. It contains DataContext class and Entity classes. The code related to these classes are completely generated by the Visual Studio tool.

To create a Data Linq Layer, as i said earlier Create a class library project under the project solution and Add a New item LINQ to SQL to the project. Below shown figure is the snap-shot of Add New Item window from Visual Studio 2008 designer and It high lights the LINQ to SQL item in the window.

Figure 1.3: Add New LINQ to SQL Class

Adding this item to the project automatically creates a blank designer surface (.dbml file) with a link to server explorer and also creates the other related files such as dbml.layout file (xml file) and designer.vb file (DataContext Class file). Open the server explore, then establish a connection with your database and navigate to the appropriate Database, tables in your database. Then drag and drop the respetive tables to the designer surface. Finally, Save the file. You are done, The DataContext Class is updated with the appropriate properties, methods and related entity classes are added to the class file with the relevant properties, methods and events.

For demonstration purpose I'm going to use a sample Database with three related tables named tblEmployees, tblDepartment, tblDesignation. with this table structure let us see how to build the layers for a Empolyee Details Screen to perform the CURD operations. The figure 1.4 shows the dbml file with these tables.

Below shown Figure 1.5 is the class Diagram of DataContext Class with Entity Classes.

Note: DataContext Class automatically takes care of opening the Database Connetion, Performing the Transaction and Closing the connection.

Implementing the Data Access Layer Using LINQ

Data Access Layer contains the code to communicate with the Data Linq layer. It uses Language-Integrated query for interacting with the Data Linq layer. The figure 2.1 show the details view of the Data Access Layer. Basically, It contains all the relevant methods to communicate with the above layer and acheives the Database related operation.

In the sample Demo Application, the Data Access Layer contains a single component named DALEmployees. The code snippet of the component is shown below.

Code Snippet 1.1: Data Access Layer

Collapse

'----------------------------------------------------------------------------
'Class Name       : DALEmployees
  'Description      : This module handles Data Access Logic of tblEmployees 
'                   Table Using LINQ
  '---------------------------------------------------------------------------
Imports System.Data
Imports System.Data.Linq
  Imports Sample.DataLinq

Public Class DALEmployees

    Private objDataContext As New DBLinqDataContext
  
   '------------------------------------------------------------------------
    'Name    : SelectRecordAll
      'Desc    : Accesses Table Property of DataContext Class, that
    'Calls GetTable() Method of the DataContext Class to retrieve all records
      'Returns : Table Collection
    '------------------------------------------------------------------------
      Public Function SelectRecordAll() As Table(Of tblEmployee)
        Try
              Return objDataContext.tblEmployees
        Catch ex As Exception
            Throw ex
          End Try
    End Function

    '------------------------------------------------------------------------
      'Name    : SelectRecordByID
    'Desc    : Executes a LINQ Query to retrieve one record from table
      'Input   : EmployeeID - Integer
    'Returns : Table Entity
    '------------------------------------------------------------------------
      Public Function SelectRecordByID(ByVal EmployeeID As Integer) As _
             tblEmployee
        Try
            Return (From Emp In objDataContext.tblEmployees Where 
            Emp.EmployeeID = EmployeeID Select Emp).Single
          Catch ex As Exception
            Throw ex
        End Try
      End Function

    '--------------------------------------------------------------------
      'Name    : SelectRecordByIDListable
    'Desc    : Executes a LINQ Query to retrieve one record from table
      'Input(s): EmployeeID - Integer
    'Returns : List Colletion
    '----------------------------------------------------------------------
      Public Function SelectRecordByIDListable(ByVal EmployeeID As Integer) _ 
          As List(Of tblEmployee)
        Dim LocalTable As List(Of tblEmployee)
          Try
            LocalTable = (From Emp In objDataContext.tblEmployees Where 
        Emp.EmployeeID = EmployeeID Select Emp).ToList
              Return LocalTable
        Catch ex As Exception
            Throw ex
          End Try
    End Function

    '------------------------------------------------------------------------
      'Name    : InsertRecord
    'Desc    : Adds a New Record to the Data Collection and submits changes to 
      '          the Database
    'Input   : Table Entity
    'Returns : Integer
      '------------------------------------------------------------------------
    Public Function InsertRecord(ByVal LocalTable As tblEmployee) As Integer
          Try
            objDataContext.tblEmployees.Add(LocalTable)
            objDataContext.SubmitChanges()
            Return LocalTable.EmployeeID
          Catch ex As Exception
            Throw ex
        End Try
      End Function

    '------------------------------------------------------------------------
      'Name    : UpdateRecord
    'Desc    : Updates an existing Record in the Data Collection and submits 
      '          changes to the Database
    'Input    : Table Entity
    'Returns : None
      '-------------------------------------------------------------------------
    Public Sub UpdateRecord(ByVal LocalTable As tblEmployee)
          Try
            objDataContext.tblEmployees.Attach(LocalTable)
            objDataContext.Refresh(RefreshMode.KeepCurrentValues, LocalTable)
            objDataContext.SubmitChanges(ConflictMode.ContinueOnConflict)
          Catch ex As Exception
            Throw ex
        End Try
      End Sub

    '-------------------------------------------------------------------------
      'Name    : DeleteRecord
    'Desc    : Deletes a Record from the Data Collection and Submits the 
      '          Changes back to the database
    'Input    : EmployeeID - Integer
      'Returns : None
    ------------------------------------------------------------------------
    Public Sub DeleteRecord(ByVal EmployeeID As Integer)
          Try
            objDataContext.tblEmployees.Remove( _
              (From Emp In objDataContext.tblEmployees _
               Where Emp.EmployeeID = EmployeeID).Single)
              objDataContext.SubmitChanges()
        Catch ex As Exception
            Throw ex
        End Try
      End Sub

    '-------------------------------------------------------------------------
      'Name    : SelectAllDepartment
    'Desc    : Accesses Table Property of DataContext Class, that
      '         Calls GetTable() Method of the DataContext Class to retrieve all records
    'Returns : Table Collection
      '------------------------------------------------------------------------
    Public Function SelectAllDepartment() As Table(Of tblDepartment)
          Try
            Return objDataContext.tblDepartments
        Catch ex As Exception
              Throw ex
        End Try
    End Function
  
    '--------------------------------------------------------------------------
    'Name    : SelectAllDesignation
      'Desc    : Accesses Table Property of DataContext Class, that
    '          Calls GetTable() Method of the DataContext Class to retrieve all records
      'Returns : Table Collection
    '--------------------------------------------------------------------------
      Public Function SelectAllDesignation() As Table(Of tblDesignation)
        Try
              Return objDataContext.tblDesignations
        Catch ex As Exception
            Throw ex
          End Try
    End Function

End Class
  

An Instance of the DataContext class is created in this layer to access the properties and methods to perform the database related operation. AS we disscussed earlier the DataContext class contains properties that represent the each table within the Database and methods to generate the dynamic SQL statements to perform the database related operation. For each table there is a respective Entity class created and all the properties are mapped to the actual database. Accessing the properties of the DataContext Class returns a collection of Table entities.

The DALEmployee component contains the list of public methods show in the below class diagram. The selectAll method access the property of the DataContext class to retrieve the records. Say, here the statement

Collapse

Return objDataContext.tblEmployees

in the SelectRecordAll method actually calls the GetTable method of the DataContext class to retrieve the records. and those retrieved records can be stored in a table object and queried using LINQ. Notice that SelectRecordById method Queries the collection of Table Entities returned by objDataContext.tblEmployees to get the single record.

Collapse

Return (From Emp In objDataContext.tblEmployees _
        Where Emp.EmployeeID = EmployeeID _
          Select Emp).Single 

The Insert method receives an entity calss object as a prameter which contains the new record to be inserted to the database. The following statements

Collapse

objDataContext.tblEmployees.Add(LocalTable)
objDataContext.SubmitChanges()

adds the new record to the data collection and the call to the SubmitChanges method transmits the changes back to the database.

The update method receives an entity calss object as a prameter which contains the modified record to be updated in the database. The following statements

Collapse

objDataContext.tblEmployees.Attach(LocalTable)
objDataContext.Refresh(RefreshMode.KeepCurrentValues, LocalTable)
objDataContext.SubmitChanges(ConflictMode.ContinueOnConflict)

attaches the modified record to the data collection and the Refresh method defines how to handle the attached record and the SubmitChanges method with the appropriate parameter value (ConflictMode.ContinueOnConflict) determines what changes need to be updated to the database.

The DeleteRecord() takes the ID as parameter and queries the Data collection to select the single record and call the appropriate Remove() method of the DataContext Class deletes the corressponding record from the Data Collection. Finally, SubmitChanges() method transmits the changes back to the database.

Collapse

objDataContext.tblEmployees.Remove( _
              (From Emp In objDataContext.tblEmployees _
               Where Emp.EmployeeID = EmployeeID).Single)
  objDataContext.SubmitChanges()

The class Diagram of the DALEmployees component is shown below.

Building the Business Facade Layer

All the Application related business logics are implemented in this layer. Basically, this layer process the data and moves the data between presentation and data access. This layer provides a hign-level interface by physically separating the presentation code and data access code. The ASP.NET pages should not directly communicate with the the Data Access Layer. Instead, the pages should call the method contained in the Business Facade Layer. The Figure 3.1 shows the detailed view of Business Facade layer.

Figure 3.1:Business Facade - Detailed View

In our sample Demo application, the Business Facade Layer consists of a single component named BFLEmployees. The code snippet of the component is shown below.

Collapse

'-------------------------------------------------------------------------
'Class Name       : BFLEmployees
  'Description        : This module handles business logic of tblEmployees Table
'-------------------------------------------------------------------------
  
Imports System.Data
Imports System.Data.Linq
Imports Sample.DataAccess
Imports Sample.DataLinq
  
Public Class BFLEmployees

    Private MobjDataAccess As New DALEmployees
  
    '----------------------------------------------------------------------
    'Name    : SelectRecordAll
      'Purpose : Call Data Access Method to retrieve all records from table
    'Returns : Table Collection
      '----------------------------------------------------------------------
    Public Function SelectRecordAll() As Table(Of tblEmployee)
          Try
            Return MobjDataAccess.SelectRecordAll()
        Catch ex As Exception
              Throw ex
        End Try
    End Function
  
    '----------------------------------------------------------------------
    'Name    : SelectRecordByID
      'Purpose : Call Data Access Method to retrieve One record from table
    'Input   : EmployeeID - Integer
      'Returns : Table Entity
    '----------------------------------------------------------------------
      Public Function SelectRecordByID(ByVal EmployeeID As Integer) As _
          tblEmployee
        Try
            Return MobjDataAccess.SelectRecordByID(EmployeeID)
        Catch ex As Exception
              Throw ex
        End Try
    End Function
  
    '-------------------------------------------------------------------
    'Name    : SelectRecordByIDListable
      'Purpose : Call Data Access method to retrieve One record from table
    'Input   : EmployeeID - Integer
      'Returns : List Collection
    '---------------------------------------------------------------------
       Public Function SelectRecordByIDListable(ByVal EmployeeID As Integer) _ 
          As List(Of tblEmployee)
        Try
            Return MobjDataAccess.SelectRecordByIDListable(EmployeeID)
          Catch ex As Exception
            Throw ex
        End Try
      End Function

    '----------------------------------------------------------------------
      'Name    : InsertRecord
    'Purpose : Call Data Access method to insert one record in table
      'Input  : Table Entity
    'Returns : Integer
    '----------------------------------------------------------------------
      Public Function InsertRecord(ByVal LocalTable As tblEmployee) As Integer
          Try
            Return MobjDataAccess.InsertRecord(LocalTable)
        Catch ex As Exception
              Throw ex
        End Try
    End Function
  
    '----------------------------------------------------------------------
    'Name    : UpdateRecord
      'Purpose : Call Data Access method to update existing record in table
    'Input  : Table Entity
      'Returns : None
    '----------------------------------------------------------------------
      Public Sub UpdateRecord(ByVal LocalTable As tblEmployee)
        Try
              MobjDataAccess.UpdateRecord(LocalTable)
        Catch ex As Exception
            Throw ex
        End Try
      End Sub

    '----------------------------------------------------------------------
      'Name    : DeleteRecord
    'Purpose : Call Data Access method to delete existing record in table
      'Input  : EmployeeID - Integer
    'Returns : None
    '----------------------------------------------------------------------
      Public Sub DeleteRecord(ByVal EmployeeID As Integer)
          Try
            MobjDataAccess.DeleteRecord(EmployeeID)
        Catch ex As Exception
            Throw ex
          End Try
    End Sub

    '----------------------------------------------------------------------
      'Name    : SelectAllDepartment
    'Purpose : Call Data Access method to retrieve all records from 
      '          tblDepartment table
    'Returns : Table Collection
    '----------------------------------------------------------------------
      Public Function SelectAllDepartment() As Table(Of tblDepartment)
        Try
              Return MobjDataAccess.SelectAllDepartment()
        Catch ex As Exception
            Throw ex
          End Try
    End Function

    '----------------------------------------------------------------------
      'Name    : SelectAllDesignation
    'Purpose : Call Data Access method to retrieve all records from 
      '          tblDesination table
    'Returns : Table Collection
    '------------------------------------------------------------------------
      Public Function SelectAllDesignation() As Table(Of tblDesignation)
        Try
              Return MobjDataAccess.SelectAllDesignation()
        Catch ex As Exception
            Throw ex
          End Try
    End Function
   
End Class
  

The BFLEmployees component contains the list of public methods shown in the below class diagram. These methods are used to interact with the Data Access Layer.

Note : BusinessFacade layer does not creates an instance of the DataContext class and so it cannot implement any Data Access Logic.

Here is the class diagram of BFLEmployees Component.

Designing and Coding the Presentation Layer

Presentation Layer deals with the User Interface of the application. The shown figure 4.1 is the detailed view of the presenter. It contains the Web Forms, Web User-Contro1s, Code-Behind and Shared common User-Interface code. It is responsible for delivery of information to the end user and to the Application. In a .NET based web application the following type of files resides in this Layer - .aspx, .ascx, .asax, .vb (code behind), web.config, .xml, .js, .resx, .css etc. All these files integrated together comprise the presentation logic and provide a friendly Interface to the user.

ASP.NET Provides rich set of controls that specifically designed to work with web forms. using those controls we can develop an application with high visual apperance and functionality. In the uploaded sample demo application, the Presentation layer has a WebForm and a Web-User control that uses GridView and DetailsView control to provide a friendly user-interface to the user to perform the basic Select, Insert, Update and Delete against the Employee details. let me shown you a part of HTML Code and Code-Behind that handles displaying the Data in the GridView. You will get a little hint on how to Query the Table Object using LINQ.

Collapse

<asp:GridView ID="gvEmployee" runat="server" Width="100%" 
       AutoGenerateColumns="False" AllowPaging="True" 
       DataKeyNames="EmployeeID" PageSize="5">
  <FooterStyle CssClass="GridFooter"></FooterStyle>
  <RowStyle CssClass="GridItem"></RowStyle>
  <AlternatingRowStyle CssClass="GridAltItem"></AlternatingRowStyle>
  <HeaderStyle CssClass="GridHeader" HorizontalAlign="Left"></HeaderStyle>
  <Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="Employee Code"/>
  <asp:BoundField DataField="FullName" HeaderText="Name"/>
  <asp:BoundField DataField="Designation" HeaderText="Designation" />
  <asp:BoundField DataField="Department" HeaderText="Department" />
  <asp:BoundField DataField="DateOfJoining" HeaderText="Date Of Joining" 
      DataFormatString="{0:dd/MM/yyyy}" />
  <asp:BoundField DataField="Salary" HeaderText="Salary" 
      DataFormatString="{0:c}" />
 <asp:CommandField ButtonType="Link" HeaderText="Select" 
  ShowEditButton="true" EditText="Select" CausesValidation="false" /> 
  </Columns>
</asp:GridView>
  

Code Snippet 1.4: Code-Behind

Collapse

   '--------------------------------------------------------------------------
    'Name              : BindGridView
      'Purpose           : BindGridView procedure calls method of Bussines 
    '                    layer and bind data to GridView control.
      '----------------------------------------------------------------------
    Private Sub BindGridView()
          Dim LocalDataTable As Table(Of tblEmployee)
        'Call to retrieve all records
          LocalDataTable = mobjEmployees.SelectRecordAll()
        gvEmployee.DataSource = (From tbl In LocalDataTable _
                               Select tbl.EmployeeID, _
                                        tbl.FullName, _
                                      tbl.tblDepartment.Department, _
                                      tbl.tblDesignation.Designation, _
                                      tbl.DateOfJoining, _
                                        tbl.Salary)
        gvEmployee.DataBind()
    End Sub

The above shown Code-snippet is a procedure written in the code-behind that calls the SelectRecordAll() method in the Business Layer that returns the records in the form of Table collection. If you notice the database design that Employee table is related to other two tables - Department and Designation. In the Table(Of tblEmployee) collection it will have the columns DepartmentID and DesignationID. since these tables are related the NAME of department and designation will also be available as sub-enity. So, Inorder to get those details I query the Table Collection and bind the results to the GridView.

Note: Presentation Layer Doesn't Implements any Business Logic or Data Access Logic and thus it contains only the User-Interface Code. When you code the layers, Make sure that no Instances of the DataContext Class are created in the Presentation and Business Facade. Only the instances of the Entity Classes should be created in these layers.

When you do all this and finally, here at a stage where you can see what exactly you have done. Here is the screen shot of Employee Screen.

Summary and Conclusion

I hope you have learned how to build an application using LINQ. This article is the result of my quick analysis and experiment on the N-Tire Design Model using LINQ. I discovered that Using LINQ to SQL reduces enormous lines of code to do DB related task and obviously that will reduce the overall time needed for the application development, reduces the coding error and will make the maintainance of the application easier.

Finally, New LINQ To SQL Says NO To Traditional ADO.NET

References

MSDN - LINQ
Scott Guthrie's Blog

Labels: ASP.NET