How to do CRUD operation in Asp.net using LINQ to SQL ? Part #5


These are the few steps to do this task

step1:Create the tblEmp in database with Id,EmpName, EmpSal field where Id is

auto increment.

Step2: Create a DataClassesDataContext using  “LINQ to SQL Class” and fill the desire table in that class

Step3: Design the aspx page as shown in Fig

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”CRUID.aspx.cs” Inherits=”CRUID” %>

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;

<html xmlns=”http://www.w3.org/1999/xhtml”&gt;
<head runat=”server”>
<title></title>
<style type=”text/css”>
.style1
{
width: 38%;
}
.style2
{
width: 127px;
}
.style3
{
width: 230px;
}
</style>
</head>
<body>
<form id=”form1″ runat=”server”>
<div style=”margin-left:150px”>

<table>
<tr>
<td>
EmpName</td>
<td>
<asp:TextBox ID=”txtEmpName” runat=”server”></asp:TextBox>
</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td>
EmpSal</td>
<td>
<asp:TextBox ID=”txtEmpSal” runat=”server”></asp:TextBox>
</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td>
&nbsp;</td>
<td>
<asp:Button ID=”BtnSubmit” runat=”server” Text=”Submit”
onclick=”BtnSubmit_Click” style=”height: 26px” />
</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td>
&nbsp;</td>
<td>
<asp:Label ID=”lblmsg” runat=”server”></asp:Label>
</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td>
&nbsp;</td>
<td>
<asp:HiddenField ID=”HiddenField1″ runat=”server” />
</td>
<td>
&nbsp;</td>
</tr>
</table>

<asp:GridView ID=”GridView1″  AllowPaging=”true” PageSize=”10″ runat=”server” AutoGenerateColumns=”False”
CellPadding=”4″ ForeColor=”#333333″ GridLines=”None”
onpageindexchanging=”GridView1_PageIndexChanging”
onrowcommand=”GridView1_RowCommand” onrowdeleting=”GridView1_RowDeleting”
onrowediting=”GridView1_RowEditing” Width=”341px”>
<RowStyle BackColor=”#EFF3FB” />
<Columns>
<asp:TemplateField HeaderText=”SI”>
<ItemTemplate>

<%#Container.DataItemIndex+1 %>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText=”EmpName” DataField=”EmpName” />
<asp:BoundField HeaderText=”EmpSal” DataField=”EmpSal” />
<asp:TemplateField HeaderText=”Action”>

<ItemTemplate>

<asp:LinkButton ID=”lnkEdit” runat=”server” Text=”Edit” CausesValidation=”false” CommandName=”Edit” CommandArgument='<%# Eval(“Id”) %>’ OnClientClick=”return confirm(‘Are you sure?’)” />
<asp:LinkButton runat=”server” ID=”lnk_Delete” Text=”Delete” CausesValidation=”false” CommandName=”Delete” CommandArgument='<%# Eval(“Id”) %>’ OnClientClick=”return confirm(‘Are you sure?’)”/>

</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor=”#507CD1″ Font-Bold=”True” ForeColor=”White” />
<PagerStyle BackColor=”#2461BF” ForeColor=”White” HorizontalAlign=”Center” />
<SelectedRowStyle BackColor=”#D1DDF1″ Font-Bold=”True” ForeColor=”#333333″ />
<HeaderStyle BackColor=”#507CD1″ Font-Bold=”True” ForeColor=”White” />
<EditRowStyle BackColor=”#2461BF” />
<AlternatingRowStyle BackColor=”White” />
</asp:GridView>

</div>
</form>
</body>
</html>

Step 4: write the code behind like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class CRUID : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
fillGrid();
}
protected void BtnSubmit_Click(object sender, EventArgs e)
{
using (DataClassesDataContext dc = new DataClassesDataContext())
{

if (BtnSubmit.Text == “Submit”)
{
tblEmp empobj = new tblEmp();
empobj.EmpName = txtEmpName.Text;
empobj.EmpSal = txtEmpSal.Text;

// Insert the new Employee object
dc.tblEmps.InsertOnSubmit(empobj);

//Submit the change into database
dc.SubmitChanges();

lblmsg.Text = “Data has been inserted sucessfully”;
txtEmpName.Text = “”;
txtEmpSal.Text = “”;

fillGrid();
}
else
{

//Code update operation

int Id1=Convert.ToInt32(HiddenField1.Value);

// for fetching single rows of tblEmp
var query = (from m in dc.tblEmps
where m.Id == Id1
select m).Single();

query.Id = Id1;
query.EmpName = txtEmpName.Text;
query.EmpSal = txtEmpSal.Text;
dc.SubmitChanges();
lblmsg.Text = “Data has been updated Sucessfully”;

BtnSubmit.Text = “Submit”;
txtEmpName.Text = “”;
txtEmpSal.Text = “”;
fillGrid();

}

}
}

protected void fillGrid()
{
using (DataClassesDataContext dc = new DataClassesDataContext())
{

var query = from m in dc.tblEmps orderby m.Id descending
select m;
GridView1.DataSource = query;
GridView1.DataBind();

}

}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{

}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{

}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == “Delete”)
{

using (DataClassesDataContext dc = new DataClassesDataContext())
{

//code for Delete operation

int id1 = Convert.ToInt32(e.CommandArgument);

tblEmp objEmp = dc.tblEmps.Single(m => m.Id == id1);

dc.tblEmps.DeleteOnSubmit(objEmp);

dc.SubmitChanges();

lblmsg.Text = “Data has been deleted sucessfully”;

fillGrid();

}

}
else if (e.CommandName == “Edit”)
{
using (DataClassesDataContext dc = new DataClassesDataContext())
{

// code for fetching particular row of gridview

int id1 = Convert.ToInt32(e.CommandArgument);

var Empinfo  =  GetEmpInfo(id1);
txtEmpName.Text = Empinfo[0].EmpName.ToString();
txtEmpSal.Text = Empinfo[0].EmpSal.ToString();
HiddenField1.Value = Empinfo[0].Id.ToString();
BtnSubmit.Text = “Update”;

}

}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
fillGrid();

}

private List<tblEmp> GetEmpInfo(int Id)
{
using (DataClassesDataContext dc = new DataClassesDataContext())
{
return (from m in dc.tblEmps
where m.Id == Id
select m).ToList();
}
}

}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: