1. Resources
    2. Code Snippets
    3. ASP.NET GridView

How to use stored procedure for insert, update and delete using grid view control?

Posted Date: 23 Dec 2012 |Updated: 23-Dec-2012 |Category: ASP.NET GridView |Author: |Member Level: Gold |Points: 75 |


In this article I am going to explain you in detail all grid view operation like Insert, update, delete and select using stored procedures using ASP.net.

Description

Most frequently we are use gridview control to display data from the database but some time we are struggle to manipulate data operation through gridview using stored procedure. For this reason I am write here one simple article to explain all operation using stored procedure.

Initially I have create one table in SQL SERVER using below query

--create table
create table emp(eno int,empname varchar(50),sal bigint)

Then I have created four separate stored procedures to manipulate data in that table.

--insert procedure
create procedure insert_emp
@eno int,
@empname varchar(50),
@sal varchar(50)
AS
Begin
insert into emp(eno,empname,sal) values(@eno,@empname,@sal)
END
--update procedure
create procedure update_emp
@eno int,
@empname varchar(50),
@sal varchar(50)
AS
Begin
update emp set empname=@empname,sal=@sal where eno=@eno
END
--delete procedure
create procedure delete_emp
@eno int
AS
Begin
delete from emp where eno=@eno
END
--stored procedure for select
create procedure select_emp
AS
Begin
select * from emp
END


Select Record details using Stored Procedure

First step in the page load event I load that table data into the grid view using select_emp procedure like below

void LoadGrid()
{
    sqlcmd = new SqlCommand("select_emp", sqlcon);
    sqlcmd.CommandType = CommandType.StoredProcedure;
    try
    {
        sqlcon.Open();
        da = new SqlDataAdapter(sqlcmd);
        dt.Clear();
        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();

        if (dt.Rows.Count == 0)
        {
            Panel1.Visible = true;
        }
        else
        {
            Panel1.Visible = false;
        }
    }
    catch (Exception ex)
    {

    }
    finally
    {
        sqlcon.Close();
    }
}


How to insert new record using stored procedure in grid view?

Here I have insert new record details using grid view footer template. If user wants to add new record then he/ she use footer row to insert new data like below screen.

StoredProcedureInsertupdate

Here I am using insert_emp stored procedure to insert new record in the table.

Code for Insert


protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName == "Insert")
    {
        GridViewRow row = GridView1.FooterRow;
        TextBox txteno = (TextBox)row.FindControl("txteno");
        TextBox txtempname = (TextBox)row.FindControl("txtempname");
        TextBox txtsal = (TextBox)row.FindControl("txtsal");
        Insert(txteno.Text, txtempname.Text, txtsal.Text);
        lblmsg.Text = "Record inserted successfully....";
        //Load common method
        LoadGrid();
    }
}
void Insert(string eno, string empname, string sal)
{
    try
    {
        sqlcon.Open();
        sqlcmd = new SqlCommand("insert_emp", sqlcon);
        sqlcmd.CommandType = CommandType.StoredProcedure;
        sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = eno;
        sqlcmd.Parameters.Add("@empname", SqlDbType.VarChar).Value = empname;
        sqlcmd.Parameters.Add("@sal", SqlDbType.Int).Value = sal;
        sqlcmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
    }
    finally
    {
        sqlcon.Close();
    }
    LoadGrid();
}


How to update data using stored procedure in grid view?

Here I am going to explain how to update using stored procedure. I am call here previously created update_emp stored procedure to update existing record in the table.

StoredProcedureInsertupdate

Code for update


protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    try
    {

        GridViewRow row = GridView1.Rows[e.RowIndex];
        string eno;
        eno = GridView1.DataKeys[e.RowIndex].Value.ToString();
        TextBox empname = (TextBox)row.FindControl("txtEempname");
        TextBox sal = (TextBox)row.FindControl("txtEsal");
        sqlcon.Open();
        //update_emp is stored procedure name  
        sqlcmd = new SqlCommand("update_emp", sqlcon);
        sqlcmd.CommandType = CommandType.StoredProcedure;
        //Assign each stored procedure parameter value to update
        sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(eno);
        sqlcmd.Parameters.Add("@empname", SqlDbType.VarChar).Value = empname.Text;
        sqlcmd.Parameters.Add("@sal", SqlDbType.BigInt).Value = Convert.ToInt32(sal.Text);
        sqlcmd.ExecuteNonQuery();
        sqlcon.Close();
        GridView1.EditIndex = -1;
        LoadGrid();
    }
    catch (Exception ex)
    {
        lblmsg.Text = ex.ToString();
    }
}


How to delete data using stored procedure in grid view?

Here I am going to explain how to update using stored procedure. I am call here previously created update_emp stored procedure to update existing record in the table.

StoredProcedureInsertupdate

Code for delete


protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    try
    {

        GridViewRow row = GridView1.Rows[e.RowIndex];
        string eno;
        eno = GridView1.DataKeys[e.RowIndex].Value.ToString();
        TextBox empname = (TextBox)row.FindControl("txtEempname");
        TextBox sal = (TextBox)row.FindControl("txtEsal");
        sqlcon.Open();
        //update_emp is stored procedure name  
        sqlcmd = new SqlCommand("update_emp", sqlcon);
        sqlcmd.CommandType = CommandType.StoredProcedure;
        //Assign each stored procedure parameter value to update
        sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(eno);
        sqlcmd.Parameters.Add("@empname", SqlDbType.VarChar).Value = empname.Text;
        sqlcmd.Parameters.Add("@sal", SqlDbType.BigInt).Value = Convert.ToInt32(sal.Text);
        sqlcmd.ExecuteNonQuery();
        sqlcon.Close();
        GridView1.EditIndex = -1;
        LoadGrid();
    }
    catch (Exception ex)
    {
        lblmsg.Text = ex.ToString();
    }
}


Full Source Code


Client Side


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Insert, Update, Delete and Select using stored procedure</title>
</head>
<body>
    <form id="form1" runat="server">
    <h3>
        GridView Operations like Insert, Delete, Update and Select records using Stored Procedure</h3>
    <asp:Label ID="lblmsg" runat="server"></asp:Label><br />
    <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" ShowFooter="true"
        OnRowCommand="GridView1_RowCommand" OnRowCancelingEdit="GridView1_RowCancelingEdit"
        OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating"
        DataKeyNames="eno">
        <Columns>
            <asp:TemplateField HeaderText="Employee No.">
                <ItemTemplate>
                    <%#Eval("eno") %>
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:TextBox ID="txtEeno" runat="server" Enabled="false" Text=' <%#Eval("eno") %>'></asp:TextBox>
                </EditItemTemplate>
                <FooterTemplate>
                    <asp:TextBox ID="txteno" runat="server"></asp:TextBox>
                </FooterTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Name">
                <ItemTemplate>
                    <%#Eval("empname") %>
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:TextBox ID="txtEempname" runat="server" Text=' <%#Eval("empname") %>'></asp:TextBox>
                </EditItemTemplate>
                <FooterTemplate>
                    <asp:TextBox ID="txtempname" runat="server"></asp:TextBox>
                </FooterTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Salary">
                <ItemTemplate>
                    <%#Eval("sal") %>
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:TextBox ID="txtEsal" runat="server" Text=' <%#Eval("sal") %>'></asp:TextBox>
                </EditItemTemplate>
                <FooterTemplate>
                    <asp:TextBox ID="txtsal" runat="server"></asp:TextBox>
                </FooterTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Edit / Delete">
                <ItemTemplate>
                      
                    <asp:ImageButton ID="ImageButton1" runat="server" ImageUrl="~/img/edit.png" Width="25"
                        Height="25" CommandName="Edit" />  
                    <asp:ImageButton ID="ImageButton2" runat="server" ImageUrl="~/img/delete.png" CommandName="Delete"
                        OnClientClick="return confirm('Are you sure want to delete record?')" />
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:LinkButton ID="LinkButton1" runat="server" CommandName="Update">Update</asp:LinkButton>  
                    <asp:LinkButton ID="LinkButton2" runat="server" CommandName="Cancel">Cancel</asp:LinkButton>
                </EditItemTemplate>
                <FooterTemplate>
                    <asp:ImageButton ID="ImageButton2" runat="server" ImageUrl="~/img/add.png" CommandName="Insert" />
                </FooterTemplate>
                <FooterStyle HorizontalAlign="Center" />
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <br />
    <asp:Panel ID="Panel1" runat="server">
        Enter Eno
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
        <br />
        Enter Empname
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
        <br />
        Enter Salary
        <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />
        <br />
        <asp:Button ID="Button1" runat="server" Text="Add Record" OnClick="Button1_Click" />
    </asp:Panel>
    </form>
</body>
</html>


Code Behind


using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
    //change connection string as per your details
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString());
    SqlCommand sqlcmd = new SqlCommand();
    SqlDataAdapter da = new SqlDataAdapter();
    DataTable dt = new DataTable();

    protected void Page_Load(object sender, EventArgs e)
    {
        lblmsg.Text = "";
        if (!Page.IsPostBack)
        {
            GridView1.ShowFooter = true;
            //Load grid data using common method
            LoadGrid();
        }
    }
    void LoadGrid()
    {
        sqlcmd = new SqlCommand("select_emp", sqlcon);
        sqlcmd.CommandType = CommandType.StoredProcedure;
        try
        {
            sqlcon.Open();
            da = new SqlDataAdapter(sqlcmd);
            dt.Clear();
            da.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();

            if (dt.Rows.Count == 0)
            {
                Panel1.Visible = true;
            }
            else
            {
                Panel1.Visible = false;
            }
        }
        catch (Exception ex)
        {

        }
        finally
        {
            sqlcon.Close();
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        Insert(TextBox1.Text, TextBox2.Text, TextBox3.Text);
    }
    void Insert(string eno, string empname, string sal)
    {
        try
        {
            sqlcon.Open();
            sqlcmd = new SqlCommand("insert_emp", sqlcon);
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = eno;
            sqlcmd.Parameters.Add("@empname", SqlDbType.VarChar).Value = empname;
            sqlcmd.Parameters.Add("@sal", SqlDbType.Int).Value = sal;
            sqlcmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
        }
        finally
        {
            sqlcon.Close();
        }
        LoadGrid();
    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Insert")
        {
            GridViewRow row = GridView1.FooterRow;
            TextBox txteno = (TextBox)row.FindControl("txteno");
            TextBox txtempname = (TextBox)row.FindControl("txtempname");
            TextBox txtsal = (TextBox)row.FindControl("txtsal");
            Insert(txteno.Text, txtempname.Text, txtsal.Text);
            lblmsg.Text = "Record inserted successfully....";
            //Load common method
            LoadGrid();
        }
    }
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        LoadGrid();
    }
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        LoadGrid();
    }

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        try
        {
            string eno;
            eno = GridView1.DataKeys[e.RowIndex].Value.ToString();
            sqlcmd = new SqlCommand("delete_emp", sqlcon);
            sqlcon.Open();
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(eno);
            sqlcmd.ExecuteNonQuery();
            sqlcon.Close();
            LoadGrid();
        }
        catch (Exception ex)
        {
            lblmsg.Text = ex.ToString();
        }
    }

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        try
        {

            GridViewRow row = GridView1.Rows[e.RowIndex];
            string eno;
            eno = GridView1.DataKeys[e.RowIndex].Value.ToString();
            TextBox empname = (TextBox)row.FindControl("txtEempname");
            TextBox sal = (TextBox)row.FindControl("txtEsal");
            sqlcon.Open();
            //update_emp is stored procedure name  
            sqlcmd = new SqlCommand("update_emp", sqlcon);
            sqlcmd.CommandType = CommandType.StoredProcedure;
            //Assign each stored procedure parameter value to update
            sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(eno);
            sqlcmd.Parameters.Add("@empname", SqlDbType.VarChar).Value = empname.Text;
            sqlcmd.Parameters.Add("@sal", SqlDbType.BigInt).Value = Convert.ToInt32(sal.Text);
            sqlcmd.ExecuteNonQuery();
            sqlcon.Close();
            GridView1.EditIndex = -1;
            LoadGrid();
        }
        catch (Exception ex)
        {
            lblmsg.Text = ex.ToString();
        }
    }

}


Source Code

Here I have attached full source code for the same download it and test it.

Client Side : ASP.NET
Code Behind : C#

Conclusion

I hope this article is help you to know about all gridview operations using stored procedure.


Attachments

  • InsUpdDelStrdPrcodure (44430-04014-InsUpdDelStrdPrcodure.rar)

    • Comments

    Guest Author: Vishal17 Jan 2013

    Hi
    i have implement this code but problem is when i use the txtempname1 it accept null value so please explane me what is the problem
    TextBox txtempname1 = row.Cells[1].FindControl("txtempname") as TextBox;

    Guest Author: Indrajeet B17 Jul 2013

    Thanks! It worked and helped me a lot!



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
    • Name:
    Email:
    Submit Article
    Return to Article Index

    Top Contributors

    Today
      Last 7 Days

      more...

      About dotnetspider.com

      We believe in providing quality content to our readers. If you have any questions or concerns regarding any content published here, feel free to contact us using the Contact link below.

      Quick Links
      General
      Services

      Digital Marketing by SpiderWorks Technologies, Kochi - India. ©