October 17, 2021

Programmingempire

This article describes Database Manipulation Using DataGrid in ASP.NET. Basically, DtaGrid is a data-bound control in ASP.NET. In order to perform database manipulation operations, the DataGrid control is quite useful as it provides all the necessary features in a single control. In other words, DataGrid control is a feature-rich and flexible control for carrying out the CRUD (create, read, update, and delete) operations.

Example of Database Manipulation Using DataGrid

The following code example in ASP.NET demonstrates how to use DataGrid control. At first, we need to create a Web Application in ASP.NET. Hence, we need to create a .aspx file as well as the corresponding code-behind file.

WebForm2.aspx

The following ASPX file shows the DataGrid control tag. As can be seen, we need to specify the key of the table in the DataKeyField property. Also, it includes various properties to specify event handler methods such as OnEditCommand, OnUpdateCommand, OnCancelCommand, OnDeleteCommand, and OnItemCommand.

Further, DataGrid also requires defining different types of columns that hold the corresponding fields in the database table. For instance, here we are using TemplateColumn, EditCommandColumn, and ButtonColumn. Further, within the TemplateColumn we need to specify HeaderTemplate, ItemTemplate, EditItemTemplate, and FooterTemplate.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="DataGridExamples.WebForm2" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:DataGrid ID="DataGrid1" runat="server"
                 AutoGenerateColumns="false" DataKeyField="item_id" 
                OnCancelCommand="DataGrid1_CancelCommand" 
                OnEditCommand="DataGrid1_EditCommand" 
                OnUpdateCommand="DataGrid1_UpdateCommand"
                 BackColor="Lavender" Font-Bold="true" BorderColor="Navy"
                 BorderStyle="Outset" BorderWidth="4"
                  CellPadding="10" CellSpacing="10"
                 Font-Size="Large" OnDeleteCommand="DataGrid1_DeleteCommand" 
                 OnItemCommand="DataGrid1_ItemCommand" ShowFooter="true">
                <Columns>
                    <asp:TemplateColumn>
                        <HeaderStyle BackColor="LightBlue" ForeColor="Navy"/>
                        <HeaderTemplate>
                            Item ID
                        </HeaderTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server"
                                Text='<%#DataBinder.Eval(Container,"DataItem.item_id") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:Label ID="Label5" runat="server"
                                Text='<%#DataBinder.Eval(Container,"DataItem.item_id") %>'></asp:Label>
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:Button ID="Button1" runat="server" Text="Insert"
                                 CommandName="AddNew"/>
                            <asp:TextBox ID="t11" runat="server"></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateColumn>
                    <asp:TemplateColumn>
                        <HeaderStyle BackColor="LightBlue" ForeColor="Navy"/>
                        <HeaderTemplate>
                            Item Name
                        </HeaderTemplate>
                        <ItemTemplate>
                           <asp:Label ID="Label2" runat="server" 
                               Text='<%#DataBinder.Eval(Container,"DataItem.item_name") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="t1" runat="server"
                                 Text='<%#DataBinder.Eval(Container,"DataItem.item_name") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="t4" runat="server"></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateColumn>
                    <asp:TemplateColumn>
                        <HeaderStyle BackColor="LightBlue" ForeColor="Navy"/>
                        <HeaderTemplate>
                            Price
                        </HeaderTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label3" runat="server" 
                                Text='<%#DataBinder.Eval(Container,"DataItem.price") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="t2" runat="server"
                                 Text='<%#DataBinder.Eval(Container,"DataItem.price") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="t5" runat="server"></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateColumn>
                    <asp:TemplateColumn>
                        <HeaderStyle BackColor="LightBlue" ForeColor="Navy"/>
                        <HeaderTemplate>
                            Quantity
                        </HeaderTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label4" runat="server" 
                                Text='<%#DataBinder.Eval(Container,"DataItem.quantity") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                              <asp:TextBox ID="t3" runat="server"
                                   Text='<%#DataBinder.Eval(Container,"DataItem.quantity") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="t6" runat="server"></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateColumn>
                    <asp:EditCommandColumn HeaderText="Edit" EditText="Edit"
                         CancelText="Cancel" UpdateText="Update">
                    </asp:EditCommandColumn>
                    <asp:ButtonColumn HeaderText="Delete" CommandName="Delete" Text="[Remove]">

                    </asp:ButtonColumn>

                </Columns>
            </asp:DataGrid>

        </div>
    </form>
</body>
</html>

Once we create the ASPX file, we need to define the corresponding methods in the code-behind file.

WebForm2.aspx.cs

The following code shows that we are using a disconnected approach in ADO.NET to display the data in the BindGrid() method. In order to find explanation of the various operations, click here.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

namespace DataGridExamples
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        SqlConnection c1;
        SqlDataAdapter da;
        DataSet ds;
        public void Connect()
        {
            c1 = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\path to database file\d1.mdf;Integrated Security=True;Connect Timeout=30");
            c1.Open();
        }
        public void BindGrid()
        {
            Connect();
            da = new SqlDataAdapter("select * from Items", c1);
            ds = new DataSet("ItemsData");
            da.Fill(ds, "Items");
            DataGrid1.DataSource = ds;
            DataGrid1.DataBind();
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
            }
        }

        protected void DataGrid1_ItemCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
        {
            TextBox tx, ty, tz, tw;
            if (e.CommandName.Equals("AddNew"))
            {

                tx = (TextBox)e.Item.FindControl("t11");
                ty = (TextBox)e.Item.FindControl("t4");
                tz = (TextBox)e.Item.FindControl("t5");
                tw = (TextBox)e.Item.FindControl("t6");

                int k = Int32.Parse(tx.Text);
                String s1 = ty.Text.Trim();
                int a = Int32.Parse(tz.Text);
                int b = Int32.Parse(tw.Text);

                String str = "insert into Items values(@iid, @in, @pr, @qu)";
                Connect();
                SqlCommand cmd = new SqlCommand(str, c1);
                cmd.Parameters.AddWithValue("@iid", k);
                cmd.Parameters.AddWithValue("@in", s1);
                cmd.Parameters.AddWithValue("@pr", a);
                cmd.Parameters.AddWithValue("@qu", b);

                int n = cmd.ExecuteNonQuery();
                if (n > 0)
                {
                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "inserted", "<script>alert('Record Inserted Successfully')</script>");
                    DataGrid1.EditItemIndex = -1;
                    c1.Close();
                    BindGrid();
                }
                else
                {
                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "inserted", "<script>alert('Record Not Inserted')</script>");
                    DataGrid1.EditItemIndex = -1;
                    c1.Close();
                    BindGrid();
                }
            }
        }

        protected void DataGrid1_CancelCommand(object source, DataGridCommandEventArgs e)
        {
            DataGrid1.EditItemIndex = -1;
            BindGrid();
        }

        protected void DataGrid1_EditCommand(object source, DataGridCommandEventArgs e)
        {
            DataGrid1.EditItemIndex = e.Item.ItemIndex;
            BindGrid();
        }

        protected void DataGrid1_UpdateCommand(object source, DataGridCommandEventArgs e)
        {
            int k = (int)DataGrid1.DataKeys[e.Item.ItemIndex];
            TextBox tx, ty, tz;
            tx = (TextBox)e.Item.FindControl("t1");
            ty = (TextBox)e.Item.FindControl("t2");
            tz = (TextBox)e.Item.FindControl("t3");

            String s1 = tx.Text.Trim();
            int a = Int32.Parse(ty.Text);
            int b = Int32.Parse(tz.Text);

            String str = "update Items set item_name=@in, price=@pr, quantity=@qu where item_id=@iid";
            Connect();
            SqlCommand cmd = new SqlCommand(str, c1);
            cmd.Parameters.AddWithValue("@iid", k);
            cmd.Parameters.AddWithValue("@in", s1);
            cmd.Parameters.AddWithValue("@pr", a);
            cmd.Parameters.AddWithValue("@qu", b);
            int n = cmd.ExecuteNonQuery();
            if (n > 0)
            {
                Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "updated", "<script>alert('Record Updated Successfully')</script>");
                DataGrid1.EditItemIndex = -1;
                c1.Close();
                BindGrid();
            }
            else
            {
                Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "updated", "<script>alert('Record Not Updated')</script>");
                DataGrid1.EditItemIndex = -1;
                c1.Close();
                BindGrid();
            }

        }

        protected void DataGrid1_DeleteCommand(object source, DataGridCommandEventArgs e)
        {
            int k = (int)DataGrid1.DataKeys[e.Item.ItemIndex];

            String str = "delete Items where item_id=@iid";
            Connect();
            SqlCommand cmd = new SqlCommand(str, c1);


            cmd.Parameters.AddWithValue("@iid", k);

            int n = cmd.ExecuteNonQuery();
            if (n > 0)
            {
                Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "deleted", "<script>alert('Record Deleted Successfully')</script>");
                DataGrid1.EditItemIndex = -1;
                c1.Close();
                BindGrid();
            }
            else
            {
                Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "deleted", "<script>alert('Record Not Deleted')</script>");
                DataGrid1.EditItemIndex = -1;
                c1.Close();
                BindGrid();
            }
        }
    }
}

Output