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

http://www.tolib.com/66555.html

Introduction

In the new version (.NET framework 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 called 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.

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 spend on writting the code for the components than writting the code for the Web Pages. Sometimes, organizing the components itself can become a time consuming process, you may run into architectural issues concerning which is the best way to design the web application.

My intension of writting 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 Application. Because, developers always have their own passionate opinions about Architectures. So any statement about proper architecture is controversial. However, It's always a good practice to seperate the code into logical segments as shown in the figure 1.1. Organizing the code in such a way makes it easier to maintain and extend the application.

In the Figure 1.1, you can see that Business Components are seperated into layers. The best method to organize the code as shown in the figure is to create seperate 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 corressponding 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 more easier to modify/update 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 screen shots and explaining how to do such a thing.

I presume that you already have some experience working on classic 3-Tier application and I'll just give you a tip on which layer should reference the assembly of which other layers. and after that you are on your own to add the assembly references with the above given details. The direction of the arrows in the 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 the Data Linq layer to creat the instances of the Table entities (disscussed in detail in the below section) and call the methods available in the Data Access Layer.
• Presentation Layer should have the reference of Data Linq layer and Business Facade.

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 writting stored procedures and calling them from the DataAccess layer. It automatically generates the appropriate SQL statements needed for the DB operation at run time. You can add LINQ to SQL files to projects by using the 'Add New item' in the visual studio. The below figure 1.4 is a representation of SampleDB Database shown in the Object Relational Designer. when this file is saved to the project, the LINQ To SQL automatically creates a vb class file with DataContext Class to access the Database and creates the neccessary enity Classes for the Tables presented in the Desginer. Each table presented in the designer will have a corresponding enity class in the class file.

The tables added to the designer adds 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 Entity class for each table inserted in the designer layout (.dbml file). These entity classes contains collection of strongly- Typed properties which are mapped to the columns of the corressponding table in the Database. LINQ To SQL uses these entity classes to generate the SQL statements for the DB operation. And also we can create instances of this enity class and add data to it and use them as data transfer objects between the layers.

Mapping

Classes and Properties defined within the DataContext Class are directly mapped to the corressponding Tables and Columns in the database. By default, In the DataContext Class 'LINQ uses AttributeMappingSource class which inherits an abstract class called MappingSource to stores the information about the Table and Clolumn mapping. This information is required while retrieving records from the database and submitting the chages to the database.

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.

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.

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.

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

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

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.

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

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

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.

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

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

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.

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.

Figure 2.2:Data Access - Class Diagram

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.

Code Snippet 1.2: Business Facade Layer

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.

Figure 3.2:Business Facade - Class Diagram

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.

Figure 4.1:Presentation - Detailed View

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.

Code Snippet 1.3: User Interface - HTML

<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

   '--------------------------------------------------------------------------
'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 Data Context 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.

Figure 4.2:User-Interface - 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