Programming

CRUD operations in ASP.Net Web Forms using C#

The complete operation for CRUD operations in ASP.Net Web Forms using C#.

  • Consider a below database table
CustomerId int
CustomerName varchar(50)
Country varchar(50)
  • Code for design the HTML page(aspx page)
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
    <ContentTemplate>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="OnRowDataBound"
            DataKeyNames="CustomerId" OnRowEditing="OnRowEditing" OnRowCancelingEdit="OnRowCancelingEdit" PageSize = "3" AllowPaging ="true" OnPageIndexChanging = "OnPaging"
            OnRowUpdating="OnRowUpdating" OnRowDeleting="OnRowDeleting" EmptyDataText="No records has been added."
            Width="450">
            <Columns>
                <asp:TemplateField HeaderText="Name" ItemStyle-Width="150">
                    <ItemTemplate>
                        <asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtName" runat="server" Text='<%# Eval("Name") %>' Width="140"></asp:TextBox>
                    </EditItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Country" ItemStyle-Width="150">
                    <ItemTemplate>
                        <asp:Label ID="lblCountry" runat="server" Text='<%# Eval("Country") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtCountry" runat="server" Text='<%# Eval("Country") %>' Width="140"></asp:TextBox>
                    </EditItemTemplate>
                </asp:TemplateField>
                <asp:CommandField ButtonType="Link" ShowEditButton="true" ShowDeleteButton="true"
                    ItemStyle-Width="150" />
            </Columns>
        </asp:GridView>
        <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse">
            <tr>
                <td style="width: 150px">
                    Name:<br />
                    <asp:TextBox ID="txtName" runat="server" Width="140" />
                </td>
                <td style="width: 150px">
                    Country:<br />
                    <asp:TextBox ID="txtCountry" runat="server" Width="140" />
                </td>
                <td style="width: 150px">
                    <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Insert" />
                </td>
            </tr>
        </table>
    </ContentTemplate>
</asp:UpdatePanel>

 

Namespaces
You will need to import the following namespaces.
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
Binding the GridView with records from SQL Database Table
The GridView is populated from the database inside the Page Load event of the page.
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindGrid();
    }
}
 
private void BindGrid()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT * FROM Customers";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter(query, con))
        {
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }
    }
}

 

Inserting records to GridView
The following event handler is executed when the Add Button is clicked. The name and the country values are fetched from their respective TextBoxes and then passed to the SQL Query for inserting the record in the database.
Finally the GridView is again populated with data by making call to the BindGrid method.
protected void Insert(object sender, EventArgs e)
{
    string name = txtName.Text;
    string country = txtCountry.Text;
    txtName.Text = "";
    txtCountry.Text = "";
    string query = "INSERT INTO Customers VALUES(@CustomerName, @Country)";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Parameters.AddWithValue("@CustomerName", name);
            cmd.Parameters.AddWithValue("@Country", country);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    this.BindGrid();
}

 

Editing and Updating GridView records
Edit
When the Edit Button is clicked, the GridView’s OnRowEditing event handler is triggered. Here simply the EditIndex of the GridView is updated with the Row Index of the GridView Row to be edited.
protected void OnRowEditing(object sender, GridViewEditEventArgs e)
{
    GridView1.EditIndex = e.NewEditIndex;
    this.BindGrid();
}
Update
When the Update Button is clicked, the GridView’s OnRowUpdating event handler is triggered.
CustomerId which is the primary key is fetched from the DataKey property of GridView while the Name and Country fields are fetched from their respective TextBoxes and are passed to the SQL Query for updating the records in the database.
protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e)
{
    GridViewRow row = GridView1.Rows[e.RowIndex];
    int customerId = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]);
    string name = (row.FindControl("txtName") as TextBox).Text;
    string country = (row.FindControl("txtCountry") as TextBox).Text;
    string query = "UPDATE Customers SET CustomerName=@CustomerName, Country=@Country WHERE CustomerId=@CustomerId";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Parameters.AddWithValue("@CustomerId", customerId);
            cmd.Parameters.AddWithValue("@CustomerName", name);
            cmd.Parameters.AddWithValue("@Country", country);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    GridView1.EditIndex = -1;
    this.BindGrid();
}
Cancel Edit
When the Cancel Button is clicked, the GridView’s OnRowCancelingEdit event handler is triggered. Here the EditIndex is set to -1 and the GridView is populated with data.
protected void OnRowCancelingEdit(object sender, EventArgs e)
{
    GridView1.EditIndex = -1;
    this.BindGrid();
}
Deleting GridView records
When the Delete Button is clicked, the GridView’s OnRowDeleting event handler is triggered.
CustomerId which is the primary key is fetched from the DataKey property of GridView and is passed to the SQL Query for deletion of the record from the database.
Finally the GridView is again populated with data by making call to the BindGrid method.
protected void OnRowDeleting(object sender, GridViewDeleteEventArgs e)
{
    int customerId = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]);
    string query = "DELETE FROM Customers WHERE CustomerId=@CustomerId";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Parameters.AddWithValue("@CustomerId", customerId);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
 
    this.BindGrid();
}

 

Also Read  5 Ways To Become A Professional Programmer

In order to display a confirmation message when deleting row, I have made use of OnRowDataBound event handler where I have first determined the Delete Button and then I have attach the JavaScript Confirm to its client side Click event handler.

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow && e.Row.RowIndex != GridView1.EditIndex)
    {
        (e.Row.Cells[2].Controls[2] as LinkButton).Attributes["onclick"] = "return confirm('Do you want to delete this row?');";
    }
}

 

Paging
The OnPageIndexChanging event handler is triggered when a Page Number is clicked. Here, the PageIndex property of the GridView is updated.
Finally the GridView is again populated with data by making call to the BindGrid method.
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    this.BindGrid();
}

 

About the author

Avtar

I am a blogger and I always believes in sharing a knowledge and happiness because happiness is the last journey of life.

Leave a Comment

Share
Tweet
Pin
+1
Share
0 Shares