Saturday, 17 December 2016

LINQ to SQL: Calling Stored Procedure with select, insert, update and delete in ASP.Net

In this article, I will explain you how to create and execute stored procedure to select, add, edit, update and delete a record using LINQ to SQL using C# in asp.net.

Related Articles

  1. What is LINQ to SQL and how to create LINQ to SQL class in C# with example for beginners
  2. LINQ to SQL: GridView select, insert, update and delete using C# with asp.net
  3. 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)

Creating Stored Procedure

We need to create a stored procedure which will perform CRUD operations.

create proc usp_Employee
@Id int=null,
@Name varchar(50)=null,
@Gender varchar(10)=null,
@Salary int=null,
@Task varchar(50)=null
as
begin
      if(@Task='Select')
       begin
         select * from tblEmployee
       end
      if(@Task='Insert')
       begin
         insert into tblEmployee values(@Name,@Gender,@Salary)
       end
      if(@Task='Update')
       begin
         update tblEmployee set Name=@Name,Gender=@Gender,Salary=@Salary where Id=@Id
       end
       if(@Task='Delete')
       begin
         delete from tblEmployee where Id=@Id
       end
end

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 and usp_Employee procedure from connection to Employee.dbml surface. Now rename tblEmployee to Employee and procedure will created as usp_Employee() function.

Drag and drop linq to sql table and stored procedure

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

Executing Select Query

Below is the code to execute stored procedure. usp_Employee() function takes 5 input parameters. All the parameters are nullable. So, pass null values in first 4 parameters and select in 5th parameter to execute select query and converting output into List.

EmployeeDataContext employeeDbContext = new EmployeeDataContext();
 
var data = employeeDbContext.usp_Employee(null, null, null, null, "Select").ToList()
            .Select(item => new Employee
            {
                Id = item.Id,
                Name = item.Name,
                Gender = item.Gender,
                Salary = item.Salary
            }).ToList();

Executing Insert Query

Pass all parameters value except for Id since it is auto generated.

using (EmployeeDataContext employeeDbContext = new EmployeeDataContext())
{
    string Name = "Rahul";
    string Gender = "Male";
    int Salary = 55000;
    employeeDbContext.usp_Employee(null, Name, Gender, Salary, "Insert");
}

Executing Update Query

Pass all the parameters along with Id parameter to update the record.

using (EmployeeDataContext employeeDbContext = new EmployeeDataContext())
{
    int Id = 2;
    string Name = "Rahul";
    string Gender = "Male";
    int Salary = 50000;
    employeeDbContext.usp_Employee(Id, Name, Gender, Salary, "Update");
}

Executing Delete Query

Pass Id parameter to delete the record.

using (EmployeeDataContext employeeDbContext = new EmployeeDataContext())
{
    int Id = 2;
    employeeDbContext.usp_Employee(Id, null, null, null, "Delete");
}

Complete CRUD Operation With GridView

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 complete C# code with validation.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Linq;//
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();
 
        var data = employeeDbContext.usp_Employee(null, null, null, null, "Select").ToList()
                    .Select(item => new Employee
                    {
                        Id = item.Id,
                        Name = item.Name,
                        Gender = item.Gender,
                        Salary = item.Salary
                    }).ToList();
        if (data.Count() > 0)
        {
            grdEmployee.DataSource = data;
            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())
        {
            string Name = ftxtName.Text.Trim();
            string Gender = fddlGender.SelectedItem.ToString();
            int Salary = Convert.ToInt32(ftxtSalary.Text.Trim());
            employeeDbContext.usp_Employee(null, Name, Gender, Salary, "Insert");
        }
        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())
        {
            int Id = Convert.ToInt32(lblId.Text);
            string Name = etxtName.Text.Trim();
            string Gender = eddlGender.SelectedItem.ToString();
            int Salary = Convert.ToInt32(etxtSalary.Text.Trim());
            employeeDbContext.usp_Employee(Id, Name, Gender, Salary, "Update");
        }
        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())
        {
            int Id = Convert.ToInt32(lblId.Text);
            employeeDbContext.usp_Employee(Id, null, null, null, "Delete");
        }
        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:

0 comments:

Post a Comment

Email Subscription

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

Blog Archive

BUY FROM AMAZON