How to Add Insert Update Delete rows ASP.NET GridView Control

GridView displays the values of a data source in a table where each column represents a field and each row represents a record.  It enables you to select, sort, and edit these items.  In this article, I am going to explain how to insert, update or delete a row from a grid view control. If you are new to using a grid control, I would recommend you to read my blogs “Using GridView in ASP.NET & C# —PART 1 & PART 2”.

Database Connection

Create a new ASP.NET Web Application project. Open web.config and add following entry to connection strings element

<add name="Sql" connectionString="Data Source=<SERVERNAME>;
     Initial Catalog= TEST;User=testuser;Password=testuser;" 
     providerName="System.Data.SqlClient"/> 

Page Design
Create a new aspx page and name it as ProductsEditView.aspx. Open the aspx file in design mode and add the following code in div tag. I will explain each setting in detail later.

<asp:gridview id="gvProducts"          
          autogeneratecolumns="False" emptydatatext="No data available."               
          runat="server" DataKeyNames="ProductID"
          OnRowEditing="gvProducts_RowEditing" OnRowCancelingEdit="gvProducts_RowCancelingEdit"
          OnRowCommand="gvProducts_RowCommand" onrowupdating="gvProducts_RowUpdating"
          OnRowDeleting="gvProducts_RowDeleting">
  <Columns>
  <asp:BoundField DataField="ProductID"  HeaderText="Product ID" ReadOnly="true"/>                           
      <asp:TemplateField headertext="Product Number">
          <ItemTemplate> <%#Eval("ProductNumber")%></ItemTemplate>    
          <EditItemTemplate>
               <asp:TextBox id="txtProductNumber" runat="server" text='<%#Eval("ProductNumber")%>'/>
          </EditItemTemplate>                 
          <FooterTemplate>
         <asp:TextBox ID="txtNewProductNumber" runat="Server"/>                     
    </FooterTemplate>
      </asp:TemplateField>                   
      <asp:TemplateField headertext="Product Name">
         <ItemTemplate> <%#Eval("Name")%></ItemTemplate>
         <EditItemTemplate>
                <asp:TextBox id="txtProductName" text='<%#Eval("Name")%>' runat="server"/>                                             
         </EditItemTemplate>
         <FooterTemplate>
                    <asp:TextBox ID="txtNewProductName" runat="Server"/> 
         </FooterTemplate>
     </asp:TemplateField>     
     <asp:TemplateField headertext="Price">
         <ItemTemplate> <%#Eval("ListPrice")%></ItemTemplate>
         <EditItemTemplate>
               <asp:TextBox id="txtListPrice" text='<%#Eval("ListPrice")%>' runat="server"/>                                                                         
         </EditItemTemplate>
         <FooterTemplate>
                <asp:TextBox ID="txtNewListPrice" runat="Server"> 
         </FooterTemplate>
     </asp:TemplateField>                                 
     <asp:TemplateField>
         <ItemTemplate>
              <asp:LinkButton ID="btnedit" runat="server" CommandName="Edit" Text="Edit"/>        
         </ItemTemplate>
          <EditItemTemplate>
              <asp:LinkButton ID="btnupdate" runat="server" CommandName="Update" Text="Update" />
              <asp:LinkButton ID="btncancel" runat="server" CommandName="Cancel" Text="Cancel"/>
              <asp:LinkButton ID="btnDelete" runat="server" CommandName="Delete" Text="Delete"/> 
          </EditItemTemplate>
          <FooterTemplate>
             <asp:Button ID="btnInsert" runat="Server" Text="Insert" CommandName="Insert" UseSubmitBehavior="False" />
          </FooterTemplate>
      </asp:TemplateField>                                          
  </Columns>
</asp:gridview>         
<asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="AddNewRecord" />

View Products
I am using ADO.NET API such as SQL Connection, SQL Command, SQL Adapters etc. to get the products from the database and to bind to gridview.

private void BindData()
{
    //Bind the grid view
    gvProducts.DataSource = RetrieveProducts();
    gvProducts.DataBind();
}
private DataSet RetrieveProducts()
{
    if (ViewState["Products"] != null)
        return (DataSet)ViewState["Products"];

    //fetch the connection string from web.config
    string connString = ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;

    //SQL statement to fetch entries from products
    string sql = @"Select top 10  P.ProductID, P.Name, P.ProductNumber, ListPrice  from tblProduct P";

    DataSet dsProducts = new DataSet();
    //Open SQL Connection
    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        //Initialize command object
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            //Fill the result set
            adapter.Fill(dsProducts);

        }
    }
    return dsProducts;
}

If you run the application now, you would see the following page listing all the products.

Create Product
Click on “Add” button and it would create a blank row.  All we did in the button click event is to make the footer row visible. In the footer design itself, we designed it in such a way that, it looks like a blank product  record.

protected void AddNewRecord(object sender, EventArgs e) 
{ 
     gvProducts.ShowFooter = true;
     BindData();             
}

After you fill in the data, click on the insert button to add the product to the back-end database. When user clicks on the “insert” button, gvProducts_RowCommand event handler is called. In the event handler, we get the data from the controls and insert them in the database.

protected void gvProducts_RowCommand(object sender, 
GridViewCommandEventArgs e)
{
    // If multiple ButtonField column fields are used, use the
    // CommandName property to determine if "Insert" button was clicked. 
    if (e.CommandName.Equals("Insert"))
    {   
        //fetch the values of the new product
        TextBox txtNewProductNumber = gvProducts.FooterRow.FindControl("txtNewProductNumber") as TextBox;
        TextBox txtNewProductName = gvProducts.FooterRow.FindControl("txtNewProductName") as TextBox;
        TextBox txtNewListPrice = gvProducts.FooterRow.FindControl("txtNewListPrice") as TextBox;
        //insert the new product into database
        InsertProduct(txtNewProductNumber.Text, txtNewProductName.Text, txtNewListPrice.Text);
        gvProducts.ShowFooter = false;
        //clear the view state so that latest list will be retrieved from db
        ViewState["Products"] = null;
        BindData(); // rebind the data
    }
}

private void InsertProduct(string productNumber, string productName, string listPrice)
{
    //fetch the connection string from web.config
    string connString = ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    //SQL statement to insert a product
    string sql = String.Format("Insert into tblProduct values('{0}','{1}',{2})",
                                    productName,
                                    productNumber,
                                    listPrice);
    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        //Initialize command object
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            cmd.ExecuteNonQuery();
        }
    }
}

Following are the screen shots of products page before and after clicking on “Insert” button

Update Product
Click on “Edit” and the grid would go into edit mode.  If you look at event handling function,  you would notice  that we are setting edit index of the grid to the row you trying to edit. Since it is a post back, we have to re-bind the grid control.

protected void gvProducts_RowEditing(object sender, GridViewEditEventArgs e)
{
gvProducts.EditIndex = e.NewEditIndex;
BindData();
}

Change product number to “product number updated” and product name to “product name updated” and price to 50. Clicking on “Update” would save your changes to DB.

When you click on “Update” button, gvProducts_RowUpdating event is fired. Based on the selected row, get the gridviewrow. Once you get the gridviewrow, you could search for the content controls and get the updated values. Once we have the latest values, we could simply persist them to the database using ADO.NET API.

protected void gvProducts_RowUpdating(Object sender, GridViewUpdateEventArgs e)
{
    
    // Get the product id of the selected product    
    string ID = gvProducts.DataKeys[e.RowIndex].Value.ToString();

    // Get the GridViewRow object that represents the row being edited
    // from the Rows collection of the GridView control.            
    GridViewRow row = gvProducts.Rows[e.RowIndex];

    // Get the controls that contain the updated values. In this
    // example, the updated values are contained in the TextBox 
    // controls declared in the edit item templates of each TemplateField 
    // column fields in the GridView control.
    TextBox txtProductNumber = (TextBox)row.FindControl("txtProductNumber");
    TextBox txtProductName = (TextBox)row.FindControl("txtProductName");
    TextBox txtListPrice = (TextBox)row.FindControl("txtListPrice");
    //update the product 
    UpdateProduct(ID, txtProductNumber.Text, txtProductName.Text, txtListPrice.Text);
    gvProducts.EditIndex = -1;
    BindData();
}

private void UpdateProduct(string productID, string productNumber, 
                    string productName, string listPrice)
{
    //fetch the connection string from web.config
    string connString = 
           ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    //SQL statement to update a product
    string sql = String.Format(@"Update tblProduct set Name='{0}'
                                    ,ProductNumber='{1}'
                                    ,ListPrice ={2} 
                                    where ProductID = {3}",
                                    productName,
                                    productNumber,
                                    listPrice, productID);
    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        //Initialize command object
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            cmd.ExecuteNonQuery();
        }
    }
}

Delete Product
 Clicking on “Delete” button would delete that record from the table. When you click on “Delete” button, gvProducts_RowDeleting event would be fired. In the event handler, we fetch the product ID from the row you were trying to delete. Based on the product ID, which is primary key for tblProduct table, that record is deleted using ADO.NET API.

protected void gvProducts_RowDeleting(Object sender, 
GridViewDeleteEventArgs e)
{
    // Get the product id of the selected product    
    string ProductID = gvProducts.DataKeys[e.RowIndex].Value.ToString();            
    //delete the product 
    DeleteProduct(ProductID);
    gvProducts.EditIndex = -1;
    BindData();
}
private void DeleteProduct(string productID)
{
    //fetch the connection string from web.config
    string connString = 
         ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    //SQL statement to delete from products
    string sql = String.Format(@"Delete from tblProduct 
                                    where ProductID = {0}",
                                    productID);
    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        //Initialize command object
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            cmd.ExecuteNonQuery();
        }
    }
}

Cancel Edit:
If you ever want to get out the edit mode, click on “Cancel” button and you would be back in view mode.

protected void gvProducts_RowCancelingEdit
        (object sender, GridViewCancelEditEventArgs e)
{
    gvProducts.EditIndex = -1;
    BindData();
}

SQL Statements:
I have created a tblProduct table and loaded it with some test data. If you would like to use the same table, you could use below scripts.

CREATE TABLE [dbo].[tblProduct](
 [ProductID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](255) NOT NULL,
 [ProductNumber] [varchar](255) NOT NULL,
 [ListPrice] [numeric](9, 2) NOT NULL,
 CONSTRAINT [PK_tblProduct_ProductID] PRIMARY KEY CLUSTERED 
(
 [ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

DECLARE @COUNTER INT, @productname VARCHAR(255), 
 @productnumber VARCHAR(255), @listprice DECIMAL
SET @COUNTER = 0
SET @productname = 'product name '
SET @productnumber = 'product number '
SET @listprice = 500
WHILE(@COUNTER <= 5)
BEGIN
 SET @listprice= @listprice + 100
 INSERT INTO [dbo].[tblProduct]
     VALUES
           (@productname + convert(VARCHAR, @COUNTER)
           ,@productnumber + convert(VARCHAR, @COUNTER)
           ,@listprice)
 SET @COUNTER = @COUNTER + 1           
END

Be Sociable, Share!

•