In this article, we will learn crud operations that is insert, select, edit, update and delete in asp.net Gridview control using sql stored procedure.

Data Source:

First, we need to create a table named as tblEmployee. Exceute the below script to create table.

CREATE TABLE tblEmployee
(
Id int IDENTITY(1,1) NOT NULL,
EmpName varchar (20) NULL,
EmpAddress varchar (50) NULL
)

And also, we need to create a procedure proc_Employee with seperated tasks.

CREATE PROCEDURE proc_Employee
@Action varchar(20)=null,
@Id int=null,
@EmpName varchar(20)=null,
@EmpAddress varchar(50)=null
as
begin

if(@Action='Select')
begin
--to display all the record
select Id,EmpName,EmpAddress from tblEmployee 
end

else if(@Action=       class="str">'Add')
begin
--to add a new record
insert into tblEmployee           class="kwrd">values (@EmpName,@EmpAddress) 
end

else if(@Action=       class="str">'Update')
begin
--to update the existing record
update tblEmployee set EmpName=@EmpName,EmpAddress=@EmpAddress           class="kwrd">where id=@Id 
end

else if(@Action=       class="str">'Delete')
begin
--to delete a record
delete from  tblEmployee           class="kwrd">where id=@Id 
end
end

In web.config file:

Write your database connection string in web.config file within tag.

add name="MyConnString" connectionString=    class='string'>"server=XXXX;database=XXXX;uid=XXXX;password=XXXX;" 
    providerName="System.Data.SqlClient"/>

In designer file:

In designer file, create a asp.net GridView grdEmployee control to display, edit, update and delete the record. Create a Label control is used to display the status of every operation performed. Disable the footer while page is loading.
Below is the complete gridview designer code.

form id="form1" runat="server">

Label ID="lblMessage" ForeColor="Red" runat="server" Text="">Label>
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" BorderColor="SkyBlue">
HeaderStyle BackColor="#00A6DC" ForeColor="White" />

    TemplateField HeaderText="Employee Id" Visible="false">

            Label ID="lblId" runat="server" Text=''>
Label>

TemplateField>
    TemplateField HeaderText="Employee Name">

            Label ID="lblEmpName" runat="server" Text=''>
Label>


            TextBox ID="etxtEmpName" runat="server" Text=''>
TextBox>


            TextBox ID="ftxtEmpName" runat="server">TextBox>

TemplateField>
    TemplateField HeaderText="Employee Address">

            Label ID="lblEmpAddress" runat="server" Text=''>
Label>


            TextBox ID="etxtEmpAddress" runat="server" Text=''>
TextBox>


            TextBox ID="ftxtEmpAddress" runat="server">TextBox>

TemplateField>
    TemplateField HeaderText="Operations">

            Button ID="btnEdit" Text="Edit" runat="server" CommandName="Edit" />
             
            Button ID="btnDelete" Text="Delete" runat="server" CommandName="Delete" />


            Button ID="btnUpdate" Text="Update" runat="server" CommandName="Update" />
             
            Button ID="btnCancel" Text="Cancel" runat="server" CommandName="Cancel" />


            Button ID="fbtnAdd" runat="server"  Text="Add" OnClick="fbtnAdd_Click" />
            Button ID="fbtnSave" runat="server" Text="Save" OnClick="fbtnSave_Click" />
            Button ID="fbtnCancel" runat="server" Text="Cancel" OnClick="fbtnCancel_Click" />

TemplateField>

GridView>

form>

Namespaces used:

Include the below Namespaces.

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

Complete Code:

Code explanation is commented within code.

//retrieving connection string from web.config file
string MyConnString = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;
SqlConnection con = null;
SqlCommand cmd = null;

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
    {
        BindGrid();
    }
}

private void BindGrid()
{
    con = new SqlConnection(MyConnString);
    cmd = new SqlCommand("proc_Employee", con);
    cmd.CommandType = CommandType.StoredProcedure;
    con.Open();
// call the select task
    cmd.Parameters.AddWithValue("@Action", "Select");
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
    sda.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
    {
        grdEmployee.DataSource = ds;
        grdEmployee.DataBind();
    }
else
    {
// add new row when the dataset is having zero record
        ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
        grdEmployee.DataSource = ds;
        grdEmployee.DataBind();
        grdEmployee.Rows[0].Visible = false;
    }
// method to set controls status after gridview binding
    setControls();

}

private void setControls()
{
//disable the footer EmpName and EmpAddress textboxes
TextBox ftxtEmpName = (TextBox)grdEmployee.FooterRow.FindControl("ftxtEmpName");
TextBox ftxtEmpAddress = (TextBox)grdEmployee.FooterRow.FindControl("ftxtEmpAddress");
    ftxtEmpName.Enabled = ftxtEmpAddress.Enabled = false;

Button fbtnSave = (Button)grdEmployee.FooterRow.FindControl("fbtnSave");
Button fbtnCancel = (Button)grdEmployee.FooterRow.FindControl("fbtnCancel");
    fbtnSave.Visible = fbtnCancel.Visible = false;
}

protected void fbtnAdd_Click(object sender, EventArgs e)
{
    lblMessage.Text = "";
//enable the footer EmpName and EmpAddress textboxes
TextBox ftxtEmpName = (TextBox)grdEmployee.FooterRow.FindControl("ftxtEmpName");
TextBox ftxtEmpAddress = (TextBox)grdEmployee.FooterRow.FindControl("ftxtEmpAddress");
    ftxtEmpName.Enabled = ftxtEmpAddress.Enabled = true;

Button btnAdd = (Button)grdEmployee.FooterRow.FindControl("fbtnAdd");
Button fbtnSave = (Button)grdEmployee.FooterRow.FindControl("fbtnSave");
Button fbtnCancel = (Button)grdEmployee.FooterRow.FindControl("fbtnCancel");
    fbtnSave.Visible = fbtnCancel.Visible = true;
    btnAdd.Visible = false;
}

protected void fbtnSave_Click(object sender, EventArgs e)
{
TextBox ftxtEmpName = (TextBox)grdEmployee.FooterRow.FindControl("ftxtEmpName");
TextBox ftxtEmpAddress = (TextBox)grdEmployee.FooterRow.FindControl("ftxtEmpAddress");
    con = new SqlConnection(MyConnString);
    cmd = new SqlCommand("proc_Employee", con);
    cmd.CommandType = CommandType.StoredProcedure;
    con.Open();
// call the Add task
    cmd.Parameters.AddWithValue("@Action", "Add");
    cmd.Parameters.AddWithValue("@EmpName", ftxtEmpName.Text.Trim());
    cmd.Parameters.AddWithValue("@EmpAddress", ftxtEmpAddress.Text.Trim());
    cmd.ExecuteNonQuery();
    con.Close();
    BindGrid();
    grdEmployee.EditIndex = -1;
    lblMessage.Text = "Record saved successfully.";
}

protected void fbtnCancel_Click(object sender, EventArgs e)
{
    grdEmployee.EditIndex = -1;
    BindGrid();
    lblMessage.Text = "";
}

protected void grdEmployee_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    grdEmployee.PageIndex = e.NewPageIndex;
    BindGrid();
}

protected void grdEmployee_RowEditing(object sender, GridViewEditEventArgs e)
{
// set the gridview to edit mode
    grdEmployee.EditIndex = e.NewEditIndex;
    BindGrid();
}

protected void grdEmployee_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
Label lblId = (Label)grdEmployee.Rows[e.RowIndex].FindControl("lblId");
TextBox etxtEmpName = (TextBox)grdEmployee.Rows[e.RowIndex].FindControl("etxtEmpName");
TextBox etxtEmpAddress = (TextBox)grdEmployee.Rows[e.RowIndex].FindControl("etxtEmpAddress");
    con = new SqlConnection(MyConnString);
    cmd = new SqlCommand("proc_Employee", con);
    cmd.CommandType = CommandType.StoredProcedure;
    con.Open();
// call the Update task
    cmd.Parameters.AddWithValue("@Action", "Update");
    cmd.Parameters.AddWithValue("@Id", lblId.Text);
    cmd.Parameters.AddWithValue("@EmpName", etxtEmpName.Text.Trim());
    cmd.Parameters.AddWithValue("@EmpAddress", etxtEmpAddress.Text.Trim());
    cmd.ExecuteNonQuery();
    con.Close();
    grdEmployee.EditIndex = -1;
    BindGrid();
    lblMessage.Text = "Record updated successfully.";
}

protected void grdEmployee_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Label lblId = (Label)grdEmployee.Rows[e.RowIndex].FindControl("lblId");
    con = new SqlConnection(MyConnString);
    cmd = new SqlCommand("proc_Employee", con);
    cmd.CommandType = CommandType.StoredProcedure;
    con.Open();
// call the Delete task
    cmd.Parameters.AddWithValue("@Action", "Delete");
    cmd.Parameters.AddWithValue("@Id", lblId.Text);
    cmd.ExecuteNonQuery();
    con.Close();
    BindGrid();
    lblMessage.Text = "Record deleted successfully.";

}

protected void grdEmployee_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
//set gridview in initial mode
    grdEmployee.EditIndex = -1;
    BindGrid();
}

When page loads, gridview will be shown as below without any record.

When you click on add button, footer will be enable and insert values in textbox as shown below.

After filling footer textboxes, when you click on save button, data will saved into database and save successfully message will be displayed.

When you click on edit button, edit mode will be enable and insert values in textbox as shown below.

After filling textboxes, when you click on update button, data will saved into database and record updated successfully message will be displayed.

When you click on delete button, record will be deleted.