Using LINQ in nTier ASP.NET Application with JQuery and JSON

• Download source - 66.18 KB

Introduction

Language-Integrated Query (LINQ)is a set of classes in the .NET Framework 3.5. Linq is introduced in Visual Studio 2008. Write the queries in the code to retrieve the data from data source (SQL, XML, XQuery), a new syntax for querying databases and objects from C# 3.0. Using Linq, you can map the data in XML, ADO.NET datasets, SQL, .NET Collections.

When anyone designs a project using 3-Tier/n-Tier, we have to create business classes and objects. Below is a simple class definition which is mapped to Users Table.

UsersBase Class

public class  UserBaseEntity
{ 
public Int ID { get;set } 
public string Fullname { get;set }
public string Fathername { get;set; }
}

SQL TABLE

If you create a table in SQL:

create table Users
(
ID nvarchar(50) primary key not null,
Fathername nvarchar(50) not null  
) 

In Linq, we have to define entity class using mapping attribute. Below is the example of how to map attribute in a class.

First, you have to import System.Data.Linq.Mapping at the top of the class page.

Entity Class

[Table(Name="Users")]
public classUsers{

[Column(DbType="nvarchar(32) not null",Id=true)]
public string ID;

[Column(DbType="nvarchar(50) not null")]
public string FullName;

[Column(DbType="nvarchar(50) not null")]
public string FatherName; 

Above you have noticed, class entity mapping with Users table stucture. [Table(Name="Users")] is mapped with the SQL Table Name.[Column(DbType="nvarchar(32) not null")] public string Customer; field mapped with the SQL Column name.

Linq query has three parts:

1. Get Data source
2. Create Query
3. Execute Query

n-tier Example

1. Create DataAccessLayer
2. Create Business AccessLayer
3. Presentation Layer

Create Project

Create Project with name Users in Visual Studio 2008 and select 3.5 Framework.

Business AccessLayer (Business Tier)

Create a user Base Class with name UsersBase.cs as shown in the figure below:

using System;
using System.Collections.Generic;
using System.Text;

namespace AamirHasan.BLL
{
    public class UsersBase
    {
        private Int64 _id;
        private string _fullName;


        public String FullName
        {
            get { return _fullName; }
            set { _fullName = value; }
        }
        public Int64 ID
        {
            get { return _id; }
            set { _id = value; }
        }
    }
}

Create a users Class, and create a function which name getUsers() which will get FullName from users table and return a user list as shown in the below figure:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AamirHasan.DAL;
namespace AamirHasan.BLL
{
  public class Users: UsersBase
    {
      public List<Users> getUsers()
      {
        DBConnection  d = new DBConnection ();
         
           using(var db = d.GetDBContext())
           {
               List<Users> patientRecord = (from x in db.users
                   select new Users
                   { FullName = x.username}).ToList<Users>();
                return patientRecord;
           }
      }
    }
} 

Presentation Layer

I have used web form, so create a web page and write a web method to get all users name List as shown below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Services;
using AamirHasan.BLL;//Add reference Business Tire
using System.Web.Security;
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    
    [WebMethod]
    public static List<Users> GetUsersList()
    {
        Users a = new Users();
        return a.getUsers();
    }
} 

Note: Function should be static to send request from jquery to server side.

HTML Page

Under the head tag, include jquery reference. You can download jquery-1.3.2.min.js (66.18 KB).

<script src="js/jquery-1.3.2.min.js" type="text/JavaScript"></script>

Add jquery request function which will send request and get json object as shown below:

function GetData() {
        var request ={};// if you want to send parameters
    $.ajax({
         type: "POST",
         url: "Default.aspx//GetUsersList",
         data: request,
         contentType: "application/json; charset=utf-8",
         dataType: "json",
         dataFilter: OnDataFilter,
         success: OnSuccess,
         error: OnError
     });
 } function OnDataFilter(data) {
     var dataObj = eval("(" + data + ")");
     if (dataObj.hasOwnProperty("d"))
         return dataObj.d;
     return dataObj;
 }

json object will return values to onSuccess function and in case of error, will return onError function.

  function OnSuccess(data) {
           
            alert(data);//write you logic how to display a data on screen.
// here write your logic to display record e.g show data in div 
        }
        function OnError(data) {
            var err = eval("(" + data.responseText + ")");
            alert(err.Message);
        }  

On Client Click, GetData() function will be called.

<input type="button" onclick="GetData()" value="get all users"/>
<div id="DivData" runat="server">&nbsp;</div>

Conclusion

Using n tiered, you can make your web base project and window form base. Now your code is also reduced because you don't need to write data access layer and business tier using Linq. Linq is a new technology introduced by Microsoft. It is faster to get data from database and jquery to send and get data by json object which will travel over the network with reduced chunks of packets.

For more help, visit Microsoft MSDN:

• 101 LINQ Samples
• LINQ And Its Impact On The Design

History

• 17^th June, 2011: Initial post