CRUD Operation In ASP.NET Using LINQ To SQL

CRUD Operation In ASP.NET Using LINQ To SQL
Article:	Viewed: 2684 Posted On: 23/12/2013 09:11:03 How we can use LINQ To SQL to perform CRUD operation in ASP.NET ?

CRUD_Using_LINQ_2_SQL.zip

In this article I am going to show how we can perform CRUD (Create/ Read/ Update/ Delete) operations in ASP.NET using LINQ to SQL.

Below is my Data Table. . .

LINQ To SQL 1.png

Image 1.

Now Open Visual Studio -> Create New WebSite ->Right Click on Solution Explorer -> Add New Item.

LINQ To SQL 2.png

Image 2.

View -> Server Explorer -> Right Click -> Add Connection

LINQ To SQL 3.png

Image 3.

Here Give Your Server Name-> Select Your DB

LINQ To SQL 4.png

Image 4.

Drag And Drop Table to Object Relational Designer.

LINQ To SQL 5.png

Image 5.

This will add connection string in you web.config file.

LINQ To SQL 6.png

Image 6.

Now I am going to add a new page ManageEmployee from where I will perform CRUD operations.

ManageEmployee.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ManageEmployee.aspx.cs" Inherits="CRUD_Using_LINQ_2_SQL.ManageEmployee" %>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title></title>
</head>
<body>
  <form id="form1" runat="server">
  <div>
  <table style="width: 70%; vertical-align: middle;">
  <tr>
  <td>
  <asp:GridView ID="GridViewEmployee" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" AutoGenerateColumns="false"
  OnRowCommand="GridViewEmployee_RowCommand">
  <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
  <EditRowStyle BackColor="#999999" />
  <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
  <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
  <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
  <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
  <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
  <SortedAscendingCellStyle BackColor="#E9E7E2" />
  <SortedAscendingHeaderStyle BackColor="#506C8C" />
  <SortedDescendingCellStyle BackColor="#FFFDF8" />
  <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
  <Columns>
  <asp:TemplateField HeaderText="Name">
  <ItemTemplate>
  <%# Eval("First_NM") %> <%# Eval("Last_NM") %>
  </ItemTemplate>
  </asp:TemplateField>
  <asp:BoundField DataField="Country" HeaderText="Country" />
  <asp:BoundField DataField="City" HeaderText="City" />
  <asp:BoundField DataField="State" HeaderText="State" />
  <asp:BoundField DataField="Email" HeaderText="Email" />
  <asp:BoundField DataField="Joining_DT" HeaderText="Joining_DT" />

  <asp:TemplateField HeaderText="Edit">
  <ItemTemplate>
  <asp:Button runat="server" ID="btnEdit"
  CommandName="EditRecord" CommandArgument=' <%# Eval("ID") %>' Text="Edit" />

  </ItemTemplate>
  </asp:TemplateField>

  <asp:TemplateField HeaderText="Delete">
  <ItemTemplate>
  <asp:Button runat="server" ID="btnDelete"
  CommandName="DeleteRecord" CommandArgument=' <%# Eval("ID") %>' Text="Delete" />
  </ItemTemplate>
  </asp:TemplateField>

  </Columns>
  </asp:GridView>
  </td>
  </tr>
  <tr>
  <td></td>
  </tr>

  </table>
  <table>
  <tr>
  <td style="height: 10px;"></td>
  </tr>
  </table>
  <table>
  <tr>
  <td width="20%">First Name</td>
  <td>
  <asp:TextBox ID="txtFName" runat="server" Width="250px"></asp:TextBox>
  </td>
  </tr>
  <tr>
  <td>Last Name</td>
  <td>
  <asp:TextBox ID="txtLName" runat="server" Width="250px"></asp:TextBox>
  </td>
  </tr>
  <tr>
  <td>Country</td>
  <td>
  <asp:TextBox ID="txtCountry" runat="server" Width="250px"></asp:TextBox>
  </td>
  </tr>
  <tr>
  <td>City</td>
  <td>
  <asp:TextBox ID="txtCity" runat="server" Width="250px"></asp:TextBox>
  </td>
  </tr>
  <tr>
  <td>State</td>
  <td>
  <asp:TextBox ID="txtState" runat="server" Width="250px"></asp:TextBox>
  </td>
  </tr>
  <tr>
  <td>Email</td>
  <td>
  <asp:TextBox ID="txtEmail" runat="server" Width="250px"></asp:TextBox>
  </td>
  </tr>
  <tr>
  <td></td>
  <td>
  <asp:Button ID="btnAdd" runat="server" Text="ADD New Record" Visible="false" OnClick="btnAdd_Click" /> 
  <asp:Button ID="btnUpdate" runat="server" Text="Update Record" Visible="false" OnClick="btnUpdate_Click" />
  </td>

  </tr>
  </table>
  </div>
  </form>
</body>
</html>

ManageEmployee.aspx.cs

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

namespace CRUD_Using_LINQ_2_SQL
{
  public partial class ManageEmployee : System.Web.UI.Page
{
  EmployeeDBDataContext empDB = new EmployeeDBDataContext();

  protected void Page_Load(object sender, EventArgs e)
{
  if (!Page.IsPostBack)
{
BindEmployee();
btnAdd.Visible = true;
btnUpdate.Visible = false;
}
}

  private void BindEmployee()
{
  var emp = from employee in empDB.Employees
  select new
{
employee.ID,
employee.First_NM,
employee.Last_NM,
employee.Country,
employee.City,
employee.Email,
employee.Joining_DT,
employee.State
};

GridViewEmployee.DataSource = emp;
GridViewEmployee.DataBind();
}

  protected void GridViewEmployee_RowCommand(object sender, GridViewCommandEventArgs e)
{
  if (e.CommandName == "DeleteRecord")
{
  int emp_ID = Convert.ToInt32(e.CommandArgument);
  EmployeeDBDataContext empDB = new EmployeeDBDataContext();
  var emp = empDB.Employees.Single(employee => employee.ID == emp_ID);
empDB.Employees.DeleteOnSubmit(emp);
empDB.SubmitChanges();
BindEmployee();
}
  else if (e.CommandName == "EditRecord")
{
  int emp_ID = Convert.ToInt32(e.CommandArgument);
btnAdd.Visible = false;
btnUpdate.Visible = true;
empDB = new EmployeeDBDataContext();
  var emp_TB = empDB.Employees.Single(employee => employee.ID == emp_ID);
txtFName.Text = emp_TB.First_NM.ToString();
txtLName.Text = emp_TB.Last_NM.ToString();
txtCountry.Text = emp_TB.Country.ToString();
txtCity.Text = emp_TB.City.ToString();
txtEmail.Text = emp_TB.Email.ToString();
txtState.Text = emp_TB.State.ToString();
ViewState["Emp_ID"] = emp_ID;
}
}

  protected void btnAdd_Click(object sender, EventArgs e)
{
empDB = new EmployeeDBDataContext();
  Employee emp_TB = new Employee();
emp_TB.First_NM = txtFName.Text.ToString();
emp_TB.Last_NM = txtLName.Text.ToString();
emp_TB.Country = txtCountry.Text.ToString();
emp_TB.City = txtCity.Text.ToString();
emp_TB.Email = txtEmail.Text.ToString();
emp_TB.State = txtState.Text.ToString();

empDB.Employees.InsertOnSubmit(emp_TB);
empDB.SubmitChanges();

txtFName.Text = "";
txtLName.Text = "";
txtCountry.Text = "";
txtCity.Text = "";
txtEmail.Text = "";
txtState.Text = "";
BindEmployee();
}

  protected void btnUpdate_Click(object sender, EventArgs e)
{
empDB = new EmployeeDBDataContext();
  var emp_TB = empDB.Employees.Single(employee => employee.ID == Convert.ToInt32(ViewState["Emp_ID"].ToString()));
emp_TB.First_NM = txtFName.Text.ToString();
emp_TB.Last_NM = txtLName.Text.ToString();
emp_TB.Country = txtCountry.Text.ToString();
emp_TB.City = txtCity.Text.ToString();
emp_TB.Email = txtEmail.Text.ToString();
emp_TB.State = txtState.Text.ToString();
empDB.SubmitChanges();
txtFName.Text = "";
txtLName.Text = "";
txtCountry.Text = "";
txtCity.Text = "";
txtEmail.Text = "";
txtState.Text = "";
BindEmployee();
}
}
}

When run the application: All Record are binding in a Grid

Image 7.

To add a new record fill information and click on Add New Record Button

LINQ To SQL 8.png

Image 8.

To update a record click on Edit button and update information.

LINQ To SQL 9.png

Image 9.

Now you can delete a record by clicking on Delete button.

LINQ To SQL 10.png

Image 10.

Comment:

HOME \| Submit Article \| Contact Us \| About Us \| Terms & Condition \| Advertise With us

Copyright © 2014 www.dotnetcodesg.com All Rights Reserved (Best Viewed In IE at 1024 * 768)