In this article, I will explain you how to select, add, edit, update and delete a record using LINQ to SQL using C# in asp.net. In the previous article, I have explained LINQ to SQL with simple example. This articles is similar to the previous one.
Related Articles
- What is LINQ to SQL and how to create LINQ to SQL class in C# with example for beginners
- Insert, update, delete, crud operation in asp.net gridview using stored procedure
Creating SQL Table
First of all, create a SQL table tblEmployee and insert some records. Below is the script to create table.
create table tblEmployee
(
Id int primary key identity,
Name varchar(50),
Gender varchar(10),
Salary int
)
insert into tblEmployee values('John','Male',50000)
insert into tblEmployee values('Mary','Female',55000)
insert into tblEmployee values('Rahul','Male',40000)
insert into tblEmployee values('Mathew','Male',80000)
Right click on the project menu and select Add New Item.. from visual studio as show below.
Now, the new window will open as show below. Go to Data tab and select LINQ to SQL classes and give its name Employee.dbml and click on the add button.
Now, go to Server Explorer right click on Data Connections and select Add Connection... and new window will open as shown below. Write server name and select your database from dropdown.
Now, drag and drop tblEmployee table from connection to Employee.dbml surface. Now rename tblEmployee to Employee.
You can check the all the classes are generated in Employee.designer.cs file. EmployeeDataContext is the entry point to database as shown below.
In designer file
Now, add a gridview control and add a label control to webform. Below is the designer code.
asp:Label ID="lblMessage" ForeColor="Red" runat="server" Text="">asp:Label>
asp:GridView ID="grdEmployee" runat="server" AutoGenerateColumns="False" AllowPaging="True"
ShowFooter="True" PageSize="5" OnPageIndexChanging="grdEmployee_PageIndexChanging"
OnRowCancelingEdit="grdEmployee_RowCancelingEdit" OnRowEditing="grdEmployee_RowEditing"
OnRowUpdating="grdEmployee_RowUpdating"
OnRowDeleting="grdEmployee_RowDeleting">
FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
HeaderStyle BackColor="#4A3C8C" ForeColor="#F7F7F7" Font-Bold="True" />
PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
HeaderStyle HorizontalAlign="Center" />
AlternatingRowStyle BackColor="#F7F7F7" />
Columns>
asp:TemplateField HeaderText="Employee Id" Visible="false">
ItemTemplate>
asp:Label ID="lblId" runat="server" Text=''>
asp:Label>
ItemTemplate>
asp:TemplateField>
asp:TemplateField HeaderText="Employee Name">
ItemTemplate>
asp:Label ID="lblName" runat="server" Text=''>
asp:Label>
ItemTemplate>
EditItemTemplate>
asp:TextBox ID="etxtName" runat="server" Text=''>
asp:TextBox>
EditItemTemplate>
FooterTemplate>
asp:TextBox ID="ftxtName" runat="server">asp:TextBox>
FooterTemplate>
asp:TemplateField>
asp:TemplateField HeaderText="Gender">
ItemTemplate>
asp:Label ID="lblGender" runat="server" Text=''>
asp:Label>
ItemTemplate>
EditItemTemplate>
asp:DropDownList ID="eddlGender" runat="server">
asp:ListItem Value="0">--Select--asp:ListItem>
asp:ListItem Value="Male">Maleasp:ListItem>
asp:ListItem Value="Female">Femaleasp:ListItem>
asp:DropDownList>
EditItemTemplate>
FooterTemplate>
asp:DropDownList ID="fddlGender" runat="server">
asp:ListItem>--Select--asp:ListItem>
asp:ListItem>Maleasp:ListItem>
asp:ListItem>Femaleasp:ListItem>
asp:DropDownList>
FooterTemplate>
asp:TemplateField>
asp:TemplateField HeaderText="Salary">
ItemTemplate>
asp:Label ID="lblSalary" runat="server" Text=''>
asp:Label>
ItemTemplate>
EditItemTemplate>
asp:TextBox ID="etxtSalary" runat="server" Text=''>
asp:TextBox>
EditItemTemplate>
FooterTemplate>
asp:TextBox ID="ftxtSalary" runat="server">asp:TextBox>
FooterTemplate>
asp:TemplateField>
asp:TemplateField HeaderText="Operations">
ItemTemplate>
asp:Button ID="btnEdit" Text="Edit" runat="server" CommandName="Edit" CssClass="button" />
asp:Button ID="btnDelete" Text="Delete" runat="server" CommandName="Delete" CssClass="button" />
ItemTemplate>
EditItemTemplate>
asp:Button ID="btnUpdate" Text="Update" runat="server" CommandName="Update" CssClass="button" />
asp:Button ID="btnCancel" Text="Cancel" runat="server" CommandName="Cancel" CssClass="button" />
EditItemTemplate>
FooterTemplate>
asp:Button ID="fbtnAdd" runat="server" Text="Add" OnClick="fbtnAdd_Click" CssClass="button" />
FooterTemplate>
asp:TemplateField>
Columns>
asp:GridView>
Below is CSS code. Add this code in head section of designer file.
style type="text/css">
.button
{
border: Solid 2px;
Font-weight: bold;
color: White;
Height: 25px;
width: 70px;
filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#5384BE', endColorstr='#4386D7'); /* for IE */
-ms-filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#5384BE', endColorstr='#4386D7'); /* for IE 8 and above */
background: -webkit-gradient(linear, left top, left bottom, from(#5384BE), to(#4386D7)); /* for webkit browsers */
background: -moz-linear-gradient(top, #5384BE, #4386D7); /* for firefox 3.6+ */
background: -o-linear-gradient(top, #5384BE, #4386D7); /* for Opera */
}
style>Below is the code to get all records from Employee table. GridView will be populated as shown below.
EmployeeDataContext employeeDbContext = new EmployeeDataContext();
if (employeeDbContext.Employees.Count() > 0)
{
grdEmployee.DataSource = employeeDbContext.Employees;
grdEmployee.DataBind();
}
Below is the code to add record into Employee table.
using (EmployeeDataContext employeeDbContext = new EmployeeDataContext())
{
Employee employeeObj = new Employee();
employeeObj.Name = "Manish";
employeeObj.Gender = "Male";
employeeObj.Salary = 55000;
//employee object to collection
employeeDbContext.Employees.InsertOnSubmit(employeeObj);
// calling submitchanges to save
employeeDbContext.SubmitChanges();
}
Below is the code to update record whose employee Id is 2.
using (EmployeeDataContext employeeDbContext = new EmployeeDataContext())
{
// loading single employee using Id
Employee employeeObj = employeeDbContext.Employees.SingleOrDefault(emp => emp.Id == 2);
employeeObj.Name = "Rahul";
employeeObj.Gender = "Male";
employeeObj.Salary = 60000;
employeeDbContext.SubmitChanges();
}
Below is the code to delete employee record whose employee Id is 2.
using (EmployeeDataContext employeeDbContext = new EmployeeDataContext())
{
// loading single employee using employee id
Employee employeeObj = employeeDbContext.Employees.SingleOrDefault(emp => emp.Id == 2);
employeeDbContext.Employees.DeleteOnSubmit(employeeObj);
employeeDbContext.SubmitChanges();
}
Below is the complete C# code with validation.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace WebApplication1
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
private void BindGrid()
{
EmployeeDataContext employeeDbContext = new EmployeeDataContext();
if (employeeDbContext.Employees.Count() > 0)
{
grdEmployee.DataSource = employeeDbContext.Employees;
grdEmployee.DataBind();
}
else
{
// adding a new empty row when table doesn't have record
DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(Int32));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Gender", typeof(string));
dt.Columns.Add("Salary", typeof(Int32));
ds.Tables.Add(dt);
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
grdEmployee.DataSource = ds;
grdEmployee.DataBind();
grdEmployee.Rows[0].Visible = false;
lblMessage.Text = "No record found.";
}
}
protected void fbtnAdd_Click(object sender, EventArgs e)
{
TextBox ftxtName = (TextBox)grdEmployee.FooterRow.FindControl("ftxtName");
DropDownList fddlGender = (DropDownList)grdEmployee.FooterRow.FindControl("fddlGender");
TextBox ftxtSalary = (TextBox)grdEmployee.FooterRow.FindControl("ftxtSalary");
if (ftxtName.Text.Trim() == string.Empty)
{
lblMessage.Text = "Please enter Name.";
return;
}
if (fddlGender.SelectedIndex == 0)
{
lblMessage.Text = "Please select Gender.";
return;
}
if (ftxtSalary.Text.Trim() == string.Empty)
{
lblMessage.Text = "Please enter salary.";
return;
}
using (EmployeeDataContext employeeDbContext = new EmployeeDataContext())
{
Employee employeeObj = new Employee();
employeeObj.Name = ftxtName.Text.Trim();
employeeObj.Gender = fddlGender.SelectedItem.ToString();
employeeObj.Salary = Convert.ToInt32(ftxtSalary.Text.Trim());
//employee object to collection
employeeDbContext.Employees.InsertOnSubmit(employeeObj);
// calling submit changes to save
employeeDbContext.SubmitChanges();
}
BindGrid();
lblMessage.Text = "Record saved successfully.";
}
protected void grdEmployee_RowEditing(object sender, GridViewEditEventArgs e)
{
// find gender of employee before edit
Label Gender = (Label)grdEmployee.Rows[e.NewEditIndex].FindControl("lblGender");
grdEmployee.EditIndex = e.NewEditIndex;
BindGrid();
//find the Gender DropDownList of EditItemTemplate
DropDownList eddlGender = (DropDownList)grdEmployee.Rows[grdEmployee.EditIndex].FindControl(
"eddlGender");
// set the employees gender dropdownlist selected as true using findbyvalue
eddlGender.Items.FindByValue(Gender.Text).Selected = true;
}
protected void grdEmployee_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
lblMessage.Text = string.Empty;
// you can use DataKeyNames to get Id
Label lblId = (Label)grdEmployee.Rows[e.RowIndex].FindControl("lblId");
TextBox etxtName = (TextBox)grdEmployee.Rows[e.RowIndex].FindControl("etxtName");
DropDownList eddlGender = (DropDownList)grdEmployee.Rows[e.RowIndex].FindControl("eddlGender");
TextBox etxtSalary = (TextBox)grdEmployee.Rows[e.RowIndex].FindControl("etxtSalary");
if (etxtName.Text.Trim() == string.Empty)
{
lblMessage.Text = "Please enter Name.";
return;
}
if (eddlGender.SelectedIndex == 0)
{
lblMessage.Text = "Please select Gender.";
return;
}
if (etxtSalary.Text.Trim() == string.Empty)
{
lblMessage.Text = "Please enter salary.";
return;
}
using (EmployeeDataContext employeeDbContext = new EmployeeDataContext())
{
// loading single employee using Id
Employee employeeObj = employeeDbContext.Employees.SingleOrDefault(emp => emp.Id == Convert.ToInt32(lblId.Text));
employeeObj.Name = etxtName.Text.Trim();
employeeObj.Gender = eddlGender.SelectedItem.ToString();
employeeObj.Salary = Convert.ToInt32(etxtSalary.Text.Trim());
employeeDbContext.
