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:
- Get Data source
- Create Query
- Execute Query
n-tier Example
- Create
DataAccessLayer
Create Business AccessLayer
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;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 ={}; $.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); }
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"> </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:
History
- 17th June, 2011: Initial post