twitter
    Using ASP.NET

Thursday, 31 March 2011

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





7 comments:

  1. 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();

    this thing is giving me error of nullreferenceexeption[Object reference not set to an instance of an object.] what alterations are to b made plese help

    ReplyDelete
    Replies
    1. //
      //
      //

      // Create connectionstring by using your localhsot sql server.

      Delete
  2. That was cool..Thanks published exactly on my Birthday!!!!!

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. how an error message from back-end be passed to the front-end

    ReplyDelete