Saturday, 10 December 2016

LINQ to SQL: GridView select, insert, update and delete using C# with ASP.Net

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

  1. What is LINQ to SQL and how to create LINQ to SQL class in C# with example for beginners
  2. 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.

add new item

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.

add linq to sql class

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.

add data connection

Now, drag and drop tblEmployee table from connection to Employee.dbml surface. Now rename tblEmployee to Employee.

drag and drop linq to sql class

You can check the all the classes are generated in Employee.designer.cs file. EmployeeDataContext is the entry point to database as shown below.

entry point to database

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='<%#Eval("Id") %>'>
            </asp:Label>
        </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Employee Name">
        <ItemTemplate>
            <asp:Label ID="lblName" runat="server" Text='<%#Eval("Name") %>'>
            </asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="etxtName" runat="server" Text='<%#Eval("Name") %>'>
            </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='<%#Eval("Gender") %>'>
            </asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:DropDownList ID="eddlGender" runat="server">
                <asp:ListItem Value="0">--Select--</asp:ListItem>
                <asp:ListItem Value="Male">Male</asp:ListItem>
                <asp:ListItem Value="Female">Female</asp:ListItem>
            </asp:DropDownList>
        </EditItemTemplate>
        <FooterTemplate>
            <asp:DropDownList ID="fddlGender" runat="server">
                <asp:ListItem>--Select--</asp:ListItem>
                <asp:ListItem>Male</asp:ListItem>
                <asp:ListItem>Female</asp:ListItem>
            </asp:DropDownList>
        </FooterTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Salary">
        <ItemTemplate>
            <asp:Label ID="lblSalary" runat="server" Text='<%#Eval("Salary") %>'>
            </asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="etxtSalary" runat="server" Text='<%#Eval("Salary") %>'>
            </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" />
            &nbsp;
           <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" />
            &nbsp;
            <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.SubmitChanges();
        }
        grdEmployee.EditIndex = -1;
        BindGrid();
        lblMessage.Text = "Record updated successfully.";
    }
 
    protected void grdEmployee_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        // you can use DataKeyNames to get Id
        Label lblId = (Label)grdEmployee.Rows[e.RowIndex].FindControl("lblId");
 
        using (EmployeeDataContext employeeDbContext = new EmployeeDataContext())
        {
            // loading single employee using employee id
            Employee employeeObj = employeeDbContext.Employees.SingleOrDefault(emp => emp.Id == Convert.ToInt32(lblId.Text));
            employeeDbContext.Employees.DeleteOnSubmit(employeeObj);
            employeeDbContext.SubmitChanges();
        }
        BindGrid();
        lblMessage.Text = "Record deleted successfully.";
    }
 
    protected void grdEmployee_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        grdEmployee.PageIndex = e.NewPageIndex;
        BindGrid();
    }
 
    protected void grdEmployee_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        lblMessage.Text = string.Empty;
        grdEmployee.EditIndex = -1;
        BindGrid();
    }
}
}
Share:

1 comment:

  1. Hi everybody, here every person is sharing these kinds of experience, thus it's good to read this blog, and I used to pay a visit this webpage every day. outlook email login

    ReplyDelete

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON