Friday, 8 April 2016

Insert, update, delete, crud operation in ASP.Net GridView with C# using Stored Procedure

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 separated tasks for select all record, add new record, update existing record and delete a record.

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='Add')
     begin
      --to add a new record
      insert into tblEmployee values (@EmpName,@EmpAddress) 
     end

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

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

In Web.config File

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

<configuration>
  <connectionStrings>
<add name="MyConnString" connectionString="server=XXXX;database=XXXX;uid=XXXX;password=XXXX;" 
    providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>

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">
<div>
<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" BorderColor="SkyBlue">
<HeaderStyle BackColor="#00A6DC" ForeColor="White" />
<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="lblEmpName" runat="server" Text='<%#Eval("EmpName") %>'>
          </asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="etxtEmpName" runat="server" Text='<%#Eval("EmpName") %>'>
          </asp:TextBox>
        </EditItemTemplate>
        <FooterTemplate>
            <asp:TextBox ID="ftxtEmpName" runat="server"></asp:TextBox>
        </FooterTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Employee Address">
        <ItemTemplate>
            <asp:Label ID="lblEmpAddress" runat="server" Text='<%#Eval("EmpAddress") %>'>
          </asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="etxtEmpAddress" runat="server" Text='<%#Eval("EmpAddress") %>'>
          </asp:TextBox>
        </EditItemTemplate>
        <FooterTemplate>
            <asp:TextBox ID="ftxtEmpAddress" runat="server"></asp:TextBox>
        </FooterTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Operations">
        <ItemTemplate>
            <asp:Button ID="btnEdit" Text="Edit" runat="server" CommandName="Edit" />
            &nbsp;
            <asp:Button ID="btnDelete" Text="Delete" runat="server" CommandName="Delete" />
        </ItemTemplate>
        <EditItemTemplate>
            <asp:Button ID="btnUpdate" Text="Update" runat="server" CommandName="Update" />
            &nbsp;
            <asp:Button ID="btnCancel" Text="Cancel" runat="server" CommandName="Cancel" />
        </EditItemTemplate>
        <FooterTemplate>
            <asp:Button ID="fbtnAdd" runat="server"  Text="Add" OnClick="fbtnAdd_Click" />
            <asp:Button ID="fbtnSave" runat="server" Text="Save" OnClick="fbtnSave_Click" />
            <asp:Button ID="fbtnCancel" runat="server" Text="Cancel" OnClick="fbtnCancel_Click" />
        </FooterTemplate>
    </asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>

Namespaces Used

Include the below Namespaces.

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

Complete C# 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.
crud operations page load

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

crud operations on add click

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

crud operations on add save

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

crud operations on edit click

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

crud operations on edit save

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

Share:

2 comments:

Email Subscription

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

Blog Archive

BUY FROM AMAZON