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





33 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
  5. Hello sir. I read your article, its very helpful.you are very good at Asp.net. Sir Please give your email-id.

    ReplyDelete
  6. t is very good blog and useful for students and developer ,Thanks for sharing
    .Net Online Training
    Dot Net Online Training Bangalore
    .Net Online Course

    ReplyDelete
  7. Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you..Keep update more information..
    Data Science training in Chennai
    Data science online training

    ReplyDelete
  8. Really great post, Thank you for sharing This knowledge.Excellently written article, if only all bloggers offered the same level of content as you, the internet would be a much better place. Please keep it up!
    Data Science course in Indira nagar
    Data Science course in marathahalli
    Data Science Interview questions and answers
    Data science training in tambaram
    Data Science course in btm layout
    Data science course in kalyan nagar

    ReplyDelete
  9. This is a nice article here with some useful tips for those who are not used-to comment that frequently. Thanks for this helpful information I agree with all points you have given to us. I will follow all of them.
    Best Devops online Training
    Online DevOps Certification Course - Gangboard
    Best Devops Training institute in Chennai

    ReplyDelete
  10. Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
    Data Science Training in Indira nagar
    Data Science training in marathahalli
    Data Science Interview questions and answers
    Data Science training in btm layout
    Data Science Training in BTM Layout
    Data science training in bangalore

    ReplyDelete
  11. thanks for posting such an useful and informative stuff...

    amazon aws tutorial

    ReplyDelete
  12. Thanks a lot for sharing such a good source with all, i appreciate your efforts taken for the same. I found this worth sharing and must share this with all.


    Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery



    ReplyDelete
  13. Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work.
    hardware and networking training in chennai

    hardware and networking training in tambaram

    xamarin training in chennai

    xamarin training in tambaram

    ios training in chennai

    ios training in tambaram

    iot training in chennai

    iot training in tambaram

    ReplyDelete
  14. Such a helpful article. Interesting to peruse this article.I might want to thank you for the endeavors you had made for composing this wonderful article.
    data science coaching in hyderabad

    ReplyDelete
  15. Really good information and useful content. Thanks for sharing with us. Keep doing update blogs more.
    AI Patasala Data Science Training in Hyderabad

    ReplyDelete
  16. Very informative Blog by you for the people who are new to this industry. Your detailed blog solves all the queries with good explanation. Keep up the good work. Thanks for sharing! We have a website too. Feel free to visit anytime.

    machine learning training in bangalore

    machine learning course in bangalore

    ReplyDelete
  17. Very informative Blog by you for the people who are new to this industry. Your detailed blog solves all the queries with good explanation. Keep up the good work. Thanks for sharing! We have a website too. Feel free to visit anytime.

    web development course in bangalore

    website developer training in bangalore

    ReplyDelete
  18. Very informative Blog by you for the people who are new to this industry. Your detailed blog solves all the queries with good explanation. Keep up the good work. Thanks for sharing! We have a website too. Feel free to visit anytime.

    knee length western dress

    ReplyDelete
  19. Very informative Blog by you for the people who are new to this industry. Your detailed blog solves all the queries with good explanation. Keep up the good work. Thanks for sharing! We have a website too. Feel free to visit anytime.

    two piece dress western

    ReplyDelete
  20. Very informative Blog by you for the people who are new to this industry. Your detailed blog solves all the queries with good explanation. Keep up the good work. Thanks for sharing! We have a website too. Feel free to visit anytime.

    domestic packers and movers in Navi Mumbai

    packers and movers for local shifting in mumbai

    ReplyDelete
  21. Very informative Blog by you for the people who are new to this industry. Your detailed blog solves all the queries with good explanation. Keep up the good work. Thanks for sharing! We have a website too. Feel free to visit anytime.

    Car Transportation service in mumbai

    Car Transport service in Mumbai

    ReplyDelete
  22. I am exteremly impressed by your blog, because its very powerful for the new readers and have lot of information with proper explanation. Keep up the good work. Thanks for sharing this wonderful blog! We also have a website. Please check out whenever and wherever you see this comment.

    invitation card

    marriage invitation card

    ReplyDelete
  23. I am exteremly impressed by your blog, because its very powerful for the new readers and have lot of information with proper explanation. Keep up the good work. Thanks for sharing this wonderful blog! We also have a website. Please check out whenever and wherever you see this comment.

    Online Wedding Card Maker

    Ring ceremony invitation card

    ReplyDelete