LINQ to SQL Example:
1) In Microsoft Visualstudio2008, Select FileàNew àProject and select ASP.NET Web Application and give name as LINQQQ.
2) In Default.aspx page design the webpage as given below.
3) Create the database named “linqsamples” and create the table employee with the required field given below.
Turn off the Identity Specification to “NO”.
4) After creating the Database next we have to create dbml in our application. To do this , following the steps given below.
a) choose Project menuà Add New Item and select “LINQ to SQL Classes” and give name as employee.dbml.
b) In Solution Explorer double click the “employee.dbml” and in that file drag oue employee table and drop it.
c) In Default.aspx.cs, type the following code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace LINQQQ
{
public partial class _Default : System.Web.UI.Page
{
DAL dal = new DAL();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGrid();
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
employee emp = new employee();
emp.ID = int.Parse( txtID.Text);
emp.name = txtName.Text;
emp.description = txtDesc.Text;
dal.SaveEmployee(emp);
BindGrid();
}
private void BindGrid()
{
gvEmployees.DataSource = dal.GetAllEmployees();
gvEmployees.DataBind();
}
protected void gvEmployees_RowCommand(object sender, GridViewCommandEventArgs e)
{
hdnID.Value = e.CommandArgument.ToString();
if (e.CommandName == "EditEmployee")
{
//employee emp = new employee();
employee emp = GetEmployee(Convert.ToInt32(hdnID.Value));
txtName.Text = emp.name;
txtDesc.Text = emp.description;
btnSave.Visible = false;
btnUpdate.Visible = true;
}
else if (e.CommandName == "DeleteEmployee")
{
dal.DeleteEmployee(Convert.ToInt32(hdnID.Value));
BindGrid();
}
}
private employee GetEmployee(int ID)
{
employee emp = dal.GetEmployees(ID);
return emp;
}
private void ClearScreen()
{
txtName.Text = string.Empty;
txtDesc.Text = string.Empty;
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
employee emp = new employee();
emp.ID = Convert.ToInt32(hdnID.Value);
emp.name = txtName.Text;
emp.description = txtDesc.Text;
dal.UpdateEmployee(emp);
BindGrid();
}
}
}
d) Now you have to create a new class file named DAL.cs.
Choose ProjectàAddNewItem and select class file and give name as “DAL.cs”
“DAL.cs” class file is mainly used to create the methods for Inserting, Updating and Deleting records in a nice manner.
BY way of creating object to the class we have to access the methods in the class.
Ex:
DAL dal=new DAL();
Now in DAL.cs Class file type the following code:
DAL.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace LINQQQ
{
public class DAL
{
employeeDataContext context = new employeeDataContext();
public List<employee> GetAllEmployees()
{
Open();
var Res = context.employees.ToList();
Close();
return Res;
}
private void Open()
{
context.Connection.Open();
}
private void Close()
{
context.Connection.Close();
}
public List<employee> GetEmployees(string Search)
{
Open();
var Res = (from emp in context.employees
where emp.name.Contains(Search)
select emp).ToList();
Close();
return Res;
}
public void SaveEmployee(employee emp)
{
Open();
this.context.employees.InsertOnSubmit(emp);
this.context.SubmitChanges();
Close();
}
public void UpdateEmployee(employee emp)
{
Open();
var employee = (from em in this.context.employees
where em.ID == emp.ID
select em).First();
employee.name = emp.name;
employee.description = emp.description;
this.context.SubmitChanges();
Close();
}
public void DeleteEmployee(int ID)
{
Open();
var emp = (from em in this.context.employees
where em.ID == ID
select em).First();
this.context.employees.DeleteOnSubmit(emp);
this.context.SubmitChanges();
Close();
}
public employee GetEmployees(int ID)
{
Open();
var emp = (from em in this.context.employees
where em.ID == ID
select em).First();
Close();
return emp;
}
}
}
e) Now run the project LINQ works perfect..
All the Best... J
