add delete update inside gridview using store procedure in ASP.NET

Introduction: Hello friends, in this article i will explain that how we can insert,delete,update the data in the gridview. Here i am using the store procedure to insert,delete and update the record. With the help of the store procedure the execution of the sql  query make fast. And less  the code of the aspx.cs page. So we can say  the use of the store procedure in the sql server is very important and also very helpful.

Implementation: create a new website abb a page named gallery.aspx. Drag and drop the two textboxes, a fileuploader and a button from the toolbox named txt_album_name, txt_caption, fileuploader1 and btn_insert respectively. Here i am giving the complete code for the html page. 

Code for .aspx page:

<table class="style1">

        <tr>

            <td class="style2">

                Album Name</td>

            <td>

                <asp:TextBox ID="txt_album_name" runat="server"></asp:TextBox>

                <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"

                    ControlToValidate="txt_album_name" ErrorMessage="Please enter the album name"></asp:RequiredFieldValidator>

            </td>

        </tr>

        <tr>

            <td class="style2">

                Caption</td>

            <td>

                <asp:TextBox ID="txt_caption" runat="server"></asp:TextBox>

                <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"

                    ControlToValidate="txt_caption" ErrorMessage="Please enter the caption"></asp:RequiredFieldValidator>

            </td>

        </tr>

        <tr>

            <td class="style2">

                Image</td>

            <td>

                <asp:FileUpload ID="FileUpload1" runat="server" />

                <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server"

                    ControlToValidate="FileUpload1" ErrorMessage="Please browse the image"></asp:RequiredFieldValidator>

            </td>

        </tr>

        <tr>

            <td class="style3">

                </td>

            <td class="style4">

                <asp:Button ID="btn_insert" runat="server" onclick="btn_insert_Click"

                    Text="Insert" />

            </td>

        </tr>

        <tr>

            <td class="style2" colspan="2">

                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

                    onrowcancelingedit="GridView1_RowCancelingEdit"

                    onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"

                    onrowupdating="GridView1_RowUpdating"

                    onselectedindexchanging="GridView1_SelectedIndexChanging">

                    <Columns>

                    <%--here i am using templatefields to for binding the gridview--%>

                        <asp:TemplateField HeaderText="Album_name">

                            <EditItemTemplate>

                           

                     <%--here i am using the textbox in the edititmtemplatefield to upadate the data--%>

                                <asp:TextBox ID="txt_album_name" runat="server"

                                    Text='<%# Eval("album_name") %>'></asp:TextBox>

                                <asp:Label ID="Label4" runat="server" Text='<%# Eval("id") %>' Visible="False"></asp:Label>

                            </EditItemTemplate>

                            <ItemTemplate>

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

                                <asp:Label ID="Label2" runat="server" Text='<%# Eval("id") %>' Visible="False"></asp:Label>

                            </ItemTemplate>

                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="Caption">

                            <EditItemTemplate>

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

                            </EditItemTemplate>

                            <ItemTemplate>

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

                            </ItemTemplate>

                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="Image">

                        <ItemTemplate>

                        <%--for displaying the image inside the gidview here i'm using the <img>tag

                        and specify the path of the folder where image is stored--%>

                        <img alt ="" src ='images/<%#Eval("image") %>' height="50px" width="50px"/>

                        </ItemTemplate>

                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="Delete">

                        <%--here i am using the linkbutton to delete the record and specify the command name

                        of this linkbutton is delete--%>

                            <ItemTemplate>

                                <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False"

                                    CommandName="Delete"

                                    onclientclick="return confirm('are you sure you want to delet this column')">Delete</asp:LinkButton>

                            </ItemTemplate>

                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="Update">

                            <EditItemTemplate>

                           <%-- here i am using the s linkbuttons to update the record and a  cancel button

                           and set the commandname of update button is update and the cancel button is cancel --%>

                                <asp:LinkButton ID="LinkButton3" runat="server" CausesValidation="False"

                                    CommandName="Update">Update</asp:LinkButton>

                                <asp:LinkButton ID="LinkButton4" runat="server" CausesValidation="False"

                                    CommandName="Cancel">Cancel</asp:LinkButton>

                            </EditItemTemplate>

                            <ItemTemplate>

                               <%--here i am using the linkbutton for edit and specify the command name

                        of this linkbutton is Edit--%>

                                <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False"

                                    CommandName="Edit">Edit</asp:LinkButton>

                            </ItemTemplate>

                        </asp:TemplateField>

                    </Columns>

                </asp:GridView>

            </td>

        </tr>

        </table>

Code for aspx.cs page:

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

// use below two namespaces for this program

using System.Data.SqlClient;

using System.IO;

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

{

    // here i declare some variables that will be used below inside the code.

    String fn;

    String path;

    SqlConnection cnn = new SqlConnection();

    SqlCommand cmd = new SqlCommand();

    SqlDataAdapter adp;

    DataTable dt;

    Int32 id;// id as a integer variable it will be used to catch the id at the time of

    //the deletion and updation inside the gridview.

    // below  the two string variables will be used to update the record , inside the gridvbiew

    String album_name;

    String caption;

    // the image this string type variaple will be used to delete the image from the folder where

    //the image will be stored after the record insertion.

    String image;

    protected void Page_Load(object sender, EventArgs e)

    {

        //here i declare the connection of the connectionstring to attach the database with the application

        cnn.ConnectionString = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;

        cnn.Open();

        // if the connection will be closed the below code poen the connection when the page will be loaded.

        if (cnn.State == ConnectionState.Closed)

        {

            cnn.Open();

        }

        cnn.Close();

        if (IsPostBack == false)

        {

            // here i am calling the function that will bind the gridview

            grd_bind();

        }

    }

    protected void btn_insert_Click(object sender, EventArgs e)

    {

        // inside the first if condition i am declaring the code for the uploading the image.

        if (FileUpload1.PostedFile.ContentLength > 0)

        {

            fn = Path.GetFileName(FileUpload1.FileName);

            path = Server.MapPath("images") + "/" + fn;

            FileUpload1.SaveAs(path);

        }

        if (cnn.State == ConnectionState.Closed)

        {

            cnn.Open();

        }

        // here i am using the store procedure named tb_gallery_insert to insert the record inside the database.

        SqlCommand cmd = new SqlCommand("tb_gallery_insert", cnn);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Connection = cnn;

        // here i am passing the parameters to insert the record

        cmd.Parameters.AddWithValue("@album_name", txt_album_name.Text);

        cmd.Parameters.AddWithValue("@caption", txt_caption.Text);

        cmd.Parameters.AddWithValue("@image", fn);

        cmd.ExecuteNonQuery();

        cmd.Dispose();

        grd_bind();

        cnn.Close();

        //here i am calling the function that  will be used after the insertion of the record

        //insert the record inside the database.

        clr();

    }

    // this function will used after the insertion of the record insert the record inside the database.

    private void clr()

    {

        txt_album_name.Text = "";

        txt_caption.Text = "";

    }

    //this function will used to bind the gridview

    private void grd_bind()

    {

        if (cnn.State == ConnectionState.Closed)

        { cnn.Open(); }

        // here i am using the sql query to select the gecord from the database and

        adp = new SqlDataAdapter("SELECT * FROM tb_gallery ", cnn);

        // here i declare the datatable to fill the record

        dt = new DataTable();

        // here  i am filling the dqldataadapter withe the datatable dt

        adp.Fill(dt);

        // here i am disposing the apd after filling the record

        adp.Dispose();

        // here i am binding the ghridview with the datatable dt

        GridView1.DataSource = dt;

        GridView1.DataBind();

   

   

    }

    // this is a gridview's rowdeleting event that will be used to delete of the row  record from the database

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

    {

       

        try

        {

            if (cnn.State == ConnectionState.Closed)

            {

                cnn.Open();

            }

            //inside the id variable i am finding the label from the gridview and with the help of this

            //label i will fetch the id of the record that i want to delete

            id = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl("label2"))).Text);

            // here i am using the tb_gallery_delete store procedure ti delete the record from the database

            SqlCommand cmd = new SqlCommand("tb_gallery_delete", cnn);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Connection = cnn;

            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;

            // this code will be used to delete the image from the folder too

            // here i am using the sql query to select the record that will be selected by the user for deletion

            SqlDataAdapter adp = new SqlDataAdapter("select * from tb_gallery where id=@id", cnn);

            // here i am passing the parameter that will be used by the above sql query named as id

            adp.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = id;

            DataSet ds = new DataSet();

            // here i am filling the sqldataadapter with the dataset dt

            adp.Fill(ds);

            try

            {

                // here i am using the try catch exception becoz if the image will be not available

                // in side the folder. it does not creates the error.

                // inside the image variable i am fetching the image path from the database

                image = Convert.ToString(ds.Tables[0].Rows[0]["image"]);

                // this line will used to delete the image from the folder

                // here i am also giving the filder name where you image was stored.

                File.Delete(Server.MapPath("images") + "\\" + image);

            }

            catch { }

            cmd.ExecuteNonQuery();

            cmd.Dispose();

            grd_bind();

        }

        catch {

       

        }

    }

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

    {

        GridView1.EditIndex = -1;

        grd_bind();

    }

    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

    {

        GridView1.EditIndex = e.NewEditIndex;

        grd_bind();

    }

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

    {

        if (cnn.State == ConnectionState.Closed)

        {

            cnn.Open();

        }

        //inside the id variable i am finding the label from the gridview and with the help of this

        //label i will fetch the id of the record that i want to update

        id = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl("label4"))).Text);

        //inside the album_name variable i am finding the textbox from the gridview and with the help of this

        //textbox i will find the album name thatthe user want to update

        album_name = (((TextBox)(GridView1.Rows[e.RowIndex].FindControl("txt_album_name"))).Text);

        //inside the caption  variable i am finding the textbox from the gridview and with the help of this

        //textbox i will find caption that the user want to update

        caption = (((TextBox)(GridView1.Rows[e.RowIndex].FindControl("txt_caption"))).Text);

        // here i am using the tb_gallery_update to update the record from the database inside the gridview

        SqlCommand cmd = new SqlCommand("tb_gallery_update", cnn);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Connection = cnn;

        // here i am passing the three variables that will be used to update the record by the store orocedure

        cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;

        cmd.Parameters.Add("@album_name", SqlDbType.VarChar, 50).Value = album_name;

        cmd.Parameters.Add("@caption", SqlDbType.VarChar, 50).Value = caption;

        cmd.ExecuteNonQuery();

        cmd.Dispose();

        GridView1.EditIndex = -1;

        // here i am also calling the function taht will bind the gridview after fetching the record

        //from the database

        grd_bind();

    }

    protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)

    {

        GridView1.PageIndex = e.NewSelectedIndex;

        grd_bind();

    }

}

See output in this image:

Sql Script for database:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_Gallery]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[tb_Gallery](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [Album_name] [varchar](50) NULL,

      [Caption] [varchar](50) NULL,

      [Image] [varchar](50) NULL

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_gallery_insert]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[tb_gallery_insert]

       @album_name varchar(50),

       @caption varchar(50),

     @image varchar(50)

AS

      insert into tb_gallery values(@album_name,@caption,@image)

      RETURN

'

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_gallery_delete]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[tb_gallery_delete]

      @id int

AS

      delete from tb_gallery where id=@id

      RETURN

'

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_gallery_update]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[tb_gallery_update]

      @id int,

      @album_name varchar(50),

      @caption varchar(50)

AS

      update tb_gallery set album_name=@album_name, caption=@caption where id=@id

      RETURN

'

END