CRUD Operation Using GridView Control and store procedure in ASP.Net

In this article, we will create a CRUD operation using grid view and store procedure in Asp.Net.So let us see step by step 

We will create a table

Create a stored procedure

USE [EmployeeDB]

GO

/****** Object:  StoredProcedure [dbo].[sp_AllCRUDOpration]    Script Date: 07-05-2018 11:26:31 ******/

Create proc [dbo].[sp_AllCRUDOpration]

(

@EmployeeNo int = 0,

@FirstName VARCHAR(50) = NULL

,@LastName VARCHAR(50) = NULL

,@Gender varchar(50) = Null

,@Address VARCHAR(50) = null

,@MobileNo varchar(50) = NULL,

@PostelCode varchar(50) = NULL

,@EmailId VARCHAR(50) = NULL

,@Status VARCHAR(50)

)

AS

BEGIN

set nocount on;

if @Status = 'INSERT'

begin

Insert into EmployeeInfo(FirstName,LastName,Gender,Address,MobileNo,PostelCode,EmailId)values(@FirstName,@LastName,@Gender,@Address,@MobileNo,@PostelCode,@EmailId)

END

if @Status = 'SELECT'

begin

select EmployeeNo,FirstName,LastName,Gender,Address,MobileNo,PostelCode,EmailId from EmployeeInfo

END

if @Status = 'UPDATE'

begin

UPDATE EmployeeInfo SET FirstName = @FirstName,LastName = @LastName,Gender = @Gender

,Address = @Address,MobileNo = @MobileNo,PostelCode = @PostelCode

,EmailId = @EmailId WHERE EmployeeInfo.EmployeeNo = @EmployeeNo

END

if @Status = 'DELETE'

begin

delete from EmployeeInfo WHERE EmployeeInfo.EmployeeNo = @EmployeeNo

End

SET NOCOUNT OFF

End

Add a web form and design the grid view 

<form id="form1" class="container" runat="server">

    <h1>Employee Details</h1>

    <div class="GridviewDiv col-md-12">

        <asp:GridView runat="server" ID="gvDetails" ShowFooter="true" AllowPaging="true" PageSize="10" AutoGenerateColumns="false" DataKeyNames="EmployeeNo" OnPageIndexChanging="gvDetails_PageIndexChanging" OnRowCancelingEdit="gvDetails_RowCancelingEdit"

                      OnRowEditing="gvDetails_RowEditing" OnRowUpdating="gvDetails_RowUpdating" OnRowDeleting="gvDetails_RowDeleting" OnRowCommand="gvDetails_RowCommand">

            <HeaderStyle CssClass="btn-primary" />

            <Columns>

                <asp:BoundField DataField="EmployeeNo" ControlStyle-CssClass="w-50" HeaderText="Emp No" ReadOnly="true" />

                <asp:TemplateField HeaderText="First Name">

                    <ItemTemplate>

                        <asp:Label ID="lblFirstName" runat="server" Text='<%# Eval("FirstName")%>' />

                    </ItemTemplate>

                    <EditItemTemplate>

                        <asp:TextBox ID="txtFirstName" runat="server" Text='<%# Eval("FirstName")%>' />

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox ID="txtFName" CssClass="form-control" runat="server" />

                    </FooterTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="Last Name">

                    <ItemTemplate>

                        <asp:Label ID="lblLastName" runat="server" Text='<%# Eval("LastName")%>'></asp:Label>

                    </ItemTemplate>

                    <EditItemTemplate>

                        <asp:TextBox ID="txtLastName" runat="server" Text='<%# Eval("LastName")%>' />

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox ID="txtLName" CssClass="form-control" runat="server" />

                    </FooterTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="Gender">

                    <ItemTemplate>

                        <asp:Label ID="lblGender" runat="server" Text='<%# Eval("Gender")%>'></asp:Label>

                    </ItemTemplate>

                    <EditItemTemplate>

                        <asp:TextBox ID="txtGender" runat="server" Text='<%# Eval("Gender")%>' />

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox ID="txtMGender" CssClass="form-control" runat="server" />

                    </FooterTemplate>

                </asp:TemplateField>

  

                <asp:TemplateField HeaderText="Address">

                    <ItemTemplate>

                        <asp:Label ID="lblAddress" runat="server" Text='<%# Eval("Address")%>'></asp:Label>

                    </ItemTemplate>

                    <EditItemTemplate>

                        <asp:TextBox ID="txtAddress" runat="server" Text='<%# Eval("Address")%>' />

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox ID="txtMAddress" CssClass="form-control" runat="server" />

                    </FooterTemplate>

                </asp:TemplateField> 

                <asp:TemplateField HeaderText="Mobile Number">

                    <ItemTemplate>

                        <asp:Label ID="lblMobileNo" runat="server" Text='<%# Eval("MobileNo")%>'></asp:Label>

                    </ItemTemplate>

                    <EditItemTemplate>

                        <asp:TextBox ID="txtMobileNo" runat="server" Text='<%# Eval("MobileNo")%>' />

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox ID="txtMMobileNo" CssClass="form-control" runat="server" />

                    </FooterTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="Postel Code">

                    <ItemTemplate>

                        <asp:Label ID="lblPostelCode" runat="server" Text='<%# Eval("PostelCode")%>'></asp:Label>

                    </ItemTemplate>

                    <EditItemTemplate>

                        <asp:TextBox ID="txtPostelCode" runat="server" Text='<%# Eval("PostelCode")%>' />

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox ID="txtMPostelCode" CssClass="form-control" runat="server" />

                    </FooterTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="EmailId">

                    <ItemTemplate>

                        <asp:Label ID="lblEmailId" runat="server" Text='<%# Eval("EmailId")%>'></asp:Label>

                    </ItemTemplate>

                    <EditItemTemplate>

                        <asp:TextBox ID="txtEmailId" runat="server" Text='<%# Eval("EmailId")%>' />

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox ID="txtMEmailId" CssClass="form-control" runat="server" />

                        <asp:Button ID="btnAdd" CssClass="btn btn-primary" CommandName="AddNew" runat="server" Text="Add" />

                    </FooterTemplate>

                </asp:TemplateField> 

                <asp:CommandField ShowEditButton="True" ShowDeleteButton="true" />

            </Columns>

        </asp:GridView>

        <asp:Label ID="lblMessage" runat="server"></asp:Label>

    </div>

</form>

Create a class and give the name Employee

public class Employee

       {

    public int EmployeeNo { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public string Gender { get; set; }

    public string Address { get; set; }

    public string MobileNo { get; set; }

    public string PostelCode { get; set; }

    public string EmailId { get; set; }

}

Write code-behind logic

using System;

using System.Data;

using System.Data.SqlClient;

using System.Web.UI.WebControls;

using System.Drawing;

public partial class Default1 : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BindGridDetails();

        }

    }

    public void BindGridDetails()

    {

        DataSet ds = new DataSet();

        using (SqlConnection con = new SqlConnection("Data Source=Mithilesh;Integrated Security=true;Initial Catalog=EmployeeDB"))

        {

            con.Open();

            SqlCommand cmd = new SqlCommand("sp_AllCRUDOpration", con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@Status", "SELECT");

            SqlDataAdapter da = new SqlDataAdapter();

            da.SelectCommand = cmd;

            da.Fill(ds);

            con.Close();

            if (ds.Tables[0].Rows.Count > 0)

            {

                gvDetails.DataSource = ds.Tables[0];

                gvDetails.DataBind();

            }

            else

            {

                ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());

                gvDetails.DataSource = ds;

                gvDetails.DataBind();

                int columnCount = gvDetails.Rows[0].Cells.Count;

                gvDetails.Rows[0].Cells.Clear();

                gvDetails.Rows[0].Cells.Add(new TableCell());

                gvDetails.Rows[0].Cells[0].ColumnSpan = columnCount;

                gvDetails.Rows[0].Cells[0].Text = "No Details Found";

            }

        }

    }

    protected void gvDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)

    {

        gvDetails.PageIndex = e.NewPageIndex;

        BindGridDetails();

    }

    protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

    {

        gvDetails.EditIndex = -1;

        BindGridDetails();

    }

    protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)

    {

        gvDetails.EditIndex = e.NewEditIndex;

        BindGridDetails();

    }

    protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)

    {

        Employee objEmp = new Employee();

        objEmp.EmployeeNo = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["EmployeeNo"].ToString());

        objEmp.FirstName = ((TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtFirstName")).Text;

        objEmp.LastName = ((TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtLastName")).Text;

        objEmp.Gender = ((TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtGender")).Text;

        objEmp.Address = ((TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtAddress")).Text;

        objEmp.MobileNo = ((TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtMobileNo")).Text;

        objEmp.PostelCode = ((TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtPostelCode")).Text;

        objEmp.EmailId = ((TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtEmailId")).Text;

        CrudOperations("UPDATE", objEmp);

    }

    protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)

    {

        Employee objEmp = new Employee();

        objEmp.EmployeeNo = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["EmployeeNo"].ToString());

        CrudOperations("DELETE", objEmp);

    }

    protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)

    {

        Employee objEmp = new Employee();

        if (e.CommandName.Equals("AddNew"))

        {

            objEmp.FirstName = ((TextBox)gvDetails.FooterRow.FindControl("txtFName")).Text;

            objEmp.LastName = ((TextBox)gvDetails.FooterRow.FindControl("txtLName")).Text;

            objEmp.Gender = ((TextBox)gvDetails.FooterRow.FindControl("txtMGender")).Text;

            objEmp.Address = ((TextBox)gvDetails.FooterRow.FindControl("txtMAddress")).Text;

            objEmp.MobileNo = ((TextBox)gvDetails.FooterRow.FindControl("txtMMobileNo")).Text;

            objEmp.PostelCode = ((TextBox)gvDetails.FooterRow.FindControl("txtMPostelCode")).Text;

            objEmp.EmailId = ((TextBox)gvDetails.FooterRow.FindControl("txtMEmailId")).Text;

            CrudOperations("INSERT", objEmp);

        }

    }

    protected void CrudOperations(string status, Employee objEmp)

    {

        using (SqlConnection con = new SqlConnection("Data Source=Mithilesh;Integrated Security=true;Initial Catalog=EmployeeDB"))

        {

            con.Open();

            SqlCommand cmd = new SqlCommand("sp_AllCRUDOpration", con);

            cmd.CommandType = CommandType.StoredProcedure;

            if (status == "INSERT")

            {

                cmd.Parameters.AddWithValue("@Status", status);

                cmd.Parameters.AddWithValue("@FirstName", objEmp.FirstName);

                cmd.Parameters.AddWithValue("@LastName", objEmp.LastName);

                cmd.Parameters.AddWithValue("@Gender", objEmp.Gender);

                cmd.Parameters.AddWithValue("@Address", objEmp.Address);

                cmd.Parameters.AddWithValue("@MobileNo", objEmp.MobileNo);

                cmd.Parameters.AddWithValue("@PostelCode", objEmp.PostelCode);

                cmd.Parameters.AddWithValue("@EmailId", objEmp.EmailId);

            }

            else if (status == "UPDATE")

            {

                cmd.Parameters.AddWithValue("@Status", status);

                cmd.Parameters.AddWithValue("@EmployeeNo", objEmp.EmployeeNo);

                cmd.Parameters.AddWithValue("@FirstName", objEmp.FirstName);

                cmd.Parameters.AddWithValue("@LastName", objEmp.LastName);

                cmd.Parameters.AddWithValue("@Gender", objEmp.Gender);

                cmd.Parameters.AddWithValue("@Address", objEmp.Address);

                cmd.Parameters.AddWithValue("@MobileNo", objEmp.MobileNo);

                cmd.Parameters.AddWithValue("@PostelCode", objEmp.PostelCode);

                cmd.Parameters.AddWithValue("@EmailId", objEmp.EmailId);

            }

            else if (status == "DELETE")

            {

                cmd.Parameters.AddWithValue("@Status", status);

                cmd.Parameters.AddWithValue("@EmployeeNo", objEmp.EmployeeNo);

            }

            cmd.ExecuteNonQuery();

            lblMessage.ForeColor = Color.Green;

            lblMessage.Text = "Details " + status.ToLower() + "ed successfully";

            gvDetails.EditIndex = -1;

            BindGridDetails();

        }

    }

}

Output:

 After inserting some details