Sunday, June 28, 2009

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


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.

Diagrammatic representation of N-Tier  Architecture Using LINQ to SQL
Figure 1.1:N-Tier Architecture Using LINQ to SQL

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


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.

DataLinq Layer - Detailed View
Figure 1.2:DataLinq Layer - Detailed View

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.

Add New LINQ to SQL Class
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.

Designer with Tables - (.dbml file)
Figure 1.4: Designer with Tables - (.dbml file)

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

Data LINQ - Class Diagram
Figure 1.5: Data LINQ - Class Diagram

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.

Data Access - Detailed View
Figure 2.1:Data Access - Detailed View

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

No comments: