CRUD operation using GridView in ASP.NET

CRUD operation in Gridview:

CRUD is the short form of Create, Retrieve, Update, Delete operation. Create, Retrieve, Update, Delete operation used in database data management. Using these 4 operations we can perform insert, select, update, delete SQL Server Database table data. We will use these 4 operations in Gridview control in ASP.Net.

Gridview:

Gridview control helps to show all SQL server table data in the list format. After getting the data from SQL server you have to bind that list with Gridview control. For binding data with Gridview use these following 2 lines:

Gridview1.DataSource = dt;
Gridview1.DataBind();

For CRUD operation with Gridview you have to follow below steps:

Step-1:

Now create the one sample application "GridView_soln" as:

• "Start" - "All Programs" - "Microsoft Visual Studio 2010".
• "File" - "New Project" - "C#" - "Empty Web Application" (to avoid adding a master page).
• Provide the web site a name such as  "GridView_soln" or another as you wish and specify the location.
• Then right-click on Solution Explorer - "Add New Item" - "Default.aspx page".
• Drag and drop one GridView on the <form> section of the Default.aspx page.

Step-2:

Then add SQL Server Database. Then this database will save in "App_Data". And this database name is "Database1.mdf".

Step-3:

Now Open Server Explorer and create new table  "dbo.Tbl_Info"  in your database. E.G.:

           CREATE TABLE [dbo].[Tbl_Info] (
                    [Id]    INT           IDENTITY (1, 1) NOT NULL,
                   [Name]  VARCHAR (100) NULL,
                   [State] VARCHAR (50)  NULL,
                   [City]  VARCHAR (50)  NULL,
                   PRIMARY KEY CLUSTERED ([Id] ASC)
             );

Step-4:

Write Below Code for Gridview in your Design Page:

Now use the following GridView event properties to perform events such as update, delete, edit cancel and so on. Let us see what the properties are:

• DataKeyNames: This property I have used to the row index of GridView  
• OnRowEditing: This property is used to handle the event when the user clicks on the edit button
• OnRowCancelingEdit: This property is used to handle the event when the user clicks on the Cancel button that exists after clicking on the edit button
• OnRowDeleting: This property is used to handle the event when the user clicks on the delete button that deletes the row of the GridView
• OnRowUpdating: This property is used to handle the event when the user clicks on the update button that updates the Grid Record 

 <asp:gridview ID="Gridview1" runat="server" AutoGenerateColumns="False" OnPageIndexChanging="Gridview1_PageIndexChanging" OnRowCancelingEdit="Gridview1_RowCancelingEdit" OnRowCommand="Gridview1_RowCommand" OnRowDeleting="Gridview1_RowDeleting" OnRowEditing="Gridview1_RowEditing" OnRowUpdating="Gridview1_RowUpdating" ShowFooter="True" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" ShowHeaderWhenEmpty="True" Width="100%">
      <Columns>
          <asp:TemplateField HeaderText="Id">
              <EditItemTemplate>
                  <asp:Label ID="lbleditid" runat="server" Text='<%# Bind("Id") %>'></asp:Label>
              </EditItemTemplate>
              <ItemTemplate>
                  <asp:Label ID="lblid" runat="server" Text='<%# Bind("Id") %>'></asp:Label>
              </ItemTemplate>
          </asp:TemplateField>
          <asp:TemplateField HeaderText="Name">
              <EditItemTemplate>
                  <asp:TextBox ID="txtname" runat="server" Text='<%# Bind("Name") %>'></asp:TextBox>
              </EditItemTemplate>
              <FooterTemplate>
                  <asp:TextBox ID="txtname1" runat="server"></asp:TextBox>
              </FooterTemplate>
              <ItemTemplate>
                  <asp:Label ID="lblname" runat="server" Text='<%# Bind("Name") %>'></asp:Label>
              </ItemTemplate>
          </asp:TemplateField>
          <asp:TemplateField HeaderText="State">
              <EditItemTemplate>
                  <asp:TextBox ID="txtstate" runat="server" Text='<%# Bind("State") %>'></asp:TextBox>
              </EditItemTemplate>
              <FooterTemplate>
                  <asp:TextBox ID="txtstate1" runat="server"></asp:TextBox>
              </FooterTemplate>
              <ItemTemplate>
                  <asp:Label ID="lblstate" runat="server" Text='<%# Bind("State") %>'></asp:Label>
              </ItemTemplate>
          </asp:TemplateField>
          <asp:TemplateField HeaderText="City">
              <EditItemTemplate>
                  <asp:TextBox ID="txtcity" runat="server" Text='<%# Bind("City") %>'></asp:TextBox>
              </EditItemTemplate>
              <FooterTemplate>
                  <asp:TextBox ID="txtcity1" runat="server"></asp:TextBox>
              </FooterTemplate>
              <ItemTemplate>
                  <asp:Label ID="lblcity" runat="server" Text='<%# Bind("City") %>'></asp:Label>
              </ItemTemplate>
          </asp:TemplateField>
          <asp:TemplateField HeaderText="Edit" ShowHeader="False">
                <EditItemTemplate>
                  <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True"
                        CommandName="Update" Text="Update" ></asp:LinkButton>
                    &nbsp;
                  <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton>
                </EditItemTemplate>
                <FooterTemplate>
                  <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False"
                        CommandName="AddNew" Text="Add New" ></asp:LinkButton>
                </FooterTemplate>
                <ItemTemplate>
                  <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False"
                        CommandName="Edit" Text="Edit"></asp:LinkButton>
                </ItemTemplate>
                </asp:TemplateField>
                <asp:CommandField HeaderText="Delete" ShowDeleteButton="True"  ShowHeader="True" />
                <asp:CommandField HeaderText="Select" ShowSelectButton="True"  ShowHeader="True" />
      </Columns>
      <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
      <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
      <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
      <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
      <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
      <SortedAscendingCellStyle BackColor="#FFF1D4" />
      <SortedAscendingHeaderStyle BackColor="#B95C30" />
      <SortedDescendingCellStyle BackColor="#F1E5CE" />
      <SortedDescendingHeaderStyle BackColor="#93451F" />
  </asp:gridview>

Step-5:

 Write Below Code in "Default.aspx.cs" page:

Add Namespace :

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
Then Write these codes:

 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
        SqlDataAdapter ad = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand();
        DataTable dt;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                bind();
                Gridview1.ShowFooter = true;
            }
        }
        public void bind()
        {
            ad = new SqlDataAdapter("select * from Tbl_Info", conn);
            dt=new DataTable();
            ad.Fill(dt);
            Gridview1.DataSource = dt;
            Gridview1.DataBind();
        }
        protected void Gridview1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            Gridview1.EditIndex = e.NewEditIndex;
            bind();
        }
        protected void Gridview1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            TextBox txtName = (TextBox)Gridview1.Rows[e.RowIndex].FindControl("txtname");
            TextBox txtState = (TextBox)Gridview1.Rows[e.RowIndex].FindControl("txtstate");
            TextBox txtCity = (TextBox)Gridview1.Rows[e.RowIndex].FindControl("txtcity");
            Label lblid = (Label)Gridview1.Rows[e.RowIndex].FindControl("lbleditid");
          
            ad = new SqlDataAdapter("UPDATE Tbl_Info SET Name ='" + txtName.Text + "',State ='" + txtState.Text + "',City ='" + txtCity.Text + "'  WHERE Id='" + Convert.ToInt32(lblid.Text) + "'",conn);
            dt = new DataTable();
            ad.Fill(dt);
            Gridview1.EditIndex = -1;
            bind();
        }
        protected void Gridview1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            ad = new SqlDataAdapter("DELETE FROM Tbl_Info WHERE Id=" +Convert.ToInt32(((Label)Gridview1.Rows[e.RowIndex].FindControl("lblid")).Text) + "", conn);
            dt = new DataTable();
            ad.Fill(dt);
            bind(); 
        }
        protected void Gridview1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName.Equals("AddNew"))
            {
                TextBox txtName = (TextBox)Gridview1.FooterRow.FindControl("txtname1");
                TextBox txtState = (TextBox)Gridview1.FooterRow.FindControl("txtstate1");
                TextBox txtCity = (TextBox)Gridview1.FooterRow.FindControl("txtcity1");
                ad = new SqlDataAdapter("INSERT INTO Tbl_Info(Name,State,City) Values('" + txtName.Text + "', '" + txtState.Text + "', '" + txtCity.Text + "')",conn);
                dt = new DataTable();
                ad.Fill(dt);
                bind(); 
            }
        }
        protected void Gridview1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            Gridview1.EditIndex = -1;
            bind();
        }

        protected void Gridview1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            Gridview1.PageIndex = e.NewPageIndex;
            bind();
        }
    }

OUTPUT: