How to Perform a Simple CRUD Operation using a Paging Enabled GridView Control in Asp.Net – C#, Vb.Net

CRUD is an acronym (short form) for create, read, update and delete. We often use these four basic functions to perform database operations such as, inserting, retrieving, modifying etc. Here, in my article I am going show you how to perform a simple and basic CRUD operation using a paging enabled GridView in Asp.Net.

Perform CRUD Operation on a Database using GridView control

I have previously written few articles on How to Edit and Delete rows in GridView and How to Insert GridView rows in a Database table using SqlDataSource control. I am sure you will like it. However, here I am not using SqlDataSource for data retrieving or manipulation, but using standard code behind procedures. It is very simple.

I have written the code here in both C# and Vb.net.

We first need a table, on which we do the CRUD operation using our GridView control. Therefore, we create a table with few columns in it. Just keep the table blank, since we will populate the table with data from the GridView.

CREATE TABLE [dbo].[Books](
    [BookID] [int] IDENTITY(1,1) NOT NULL,
    [BookName] [varchar](50) NULL,
    [Category] [varchar](50) NULL,
    [Price] [numeric](18, 2) NULL
    PRIMARY KEY CLUSTERED ( [BookID] ASC )
) ON [PRIMARY]

The dbo.Books table has four columns. Create the table in your SQL Server database.

Now, let’s add the a GridView control on our web page.

The Markup
<!DOCTYPE html>
<html>
<head runat="server">
    <title>Performing CRUD Operation using GridView</title>
    <style>
        .grid {
            height:100px;
            width:100%;
            font:12px Verdana;
            background-color:#FFF;
            border:solid 1px #525252;
        }
        .grid td {
            font:inherit;
            padding:2px;
            border:solid 1px #C1C1C1;
            color:#333;
            text-align:center;
        }
        .grid th {
            font:inherit;
            padding:3px;
            color:#FFF;
            background:#424242 url(grd.png) repeat-x top;
            border-left:solid 1px #525252;
            text-align:center;
        }
         input 
         {
             font:inherit;
             padding:3px 2px;
        }
        .txt {
            font:inherit;
            text-align:center;
            border:solid 1px #4F94CD;
            color:#000;
            padding:5px 2px;
        }
    </style>
</head>
<body>
    <form runat="server">
    <div class="page">

        <div style="width:500px;">
            <asp:GridView ID="GridView1" runat="server" 
                AutoGenerateColumns="False" 
                CssClass="grid"
                ShowFooter="True"
                AutoGenerateEditButton="false"
                AllowPaging="True" PageSize="5"
                
                OnRowEditing="GridView_RowEditing" 
                OnRowCancelingEdit="GridView_RowCancelingEdit"
                OnPageIndexChanging="GridView1_PageIndexChanging"
                OnRowUpdating="GridView1_RowUpdating"
                OnRowDeleting="GridView1_RowDeleting">

                <Columns>
                    <asp:TemplateField HeaderText="Book ID">
                        <ItemTemplate><asp:Label ID="lblBookID" Width="70px"  
                            Text='<%#Eval("BookID")%>' runat="server"></asp:Label></ItemTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Book Name">
                        <ItemTemplate> <%#Eval("BookName")%> </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="tbEd_Book" Width="200px" Text='<%#Eval("BookName")%>' CssClass="txt" runat="server" />
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="tbBookName" Width="200px" runat="server" />
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Category">
                        <ItemTemplate> <%#Eval("Category")%> </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="tbEd_Cate" Width="100px" Text='<%#Eval("Category")%>' CssClass="txt" runat="server" />
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="tbCategory" Width="100px" runat="server" />
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Price">
                        <ItemTemplate> <%#Eval("Price")%> </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="tbEd_Price" Width="100px" Text='<%#Eval("Price")%>' CssClass="txt" runat="server" />
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="tbPrice" Width="100px" runat="server" />
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField>
                        <ItemTemplate></ItemTemplate>
                        <FooterTemplate>
                            <asp:Button ID="btInsert" runat="server" Text="Insert Record" 
                                OnClientClick="return validate(this);"
                                OnClick="InsertRecord" />
                        </FooterTemplate>
                    </asp:TemplateField>

                    < <%--SHOW THE EDIT AND DELETE BUTTON IN EVERY ROW.--%>
                    <asp:CommandField ShowEditButton="true" ShowDeleteButton="true" />
                </Columns>
            </asp:GridView>
        </div>
    </div>
    </form>
</body>

If you look at the markup carefully, I have defined three different templates in the GridView for each column (except the 1st column). The templates are <ItemTemplate>, <EditItemTemplate> and the <FooterTemplate>. Each template is inside the <asp:TemplateField> attribute.

<ItemTemplate>: It will show values in each row. You cannot edit values in it.

<EditItemTemplate>: This template allows you to edit existing values in a row, when the GridView is in Edit mode. However, you have to add a <asp:TextBox /> control inside each template to allow users to edit the value. It gets values from the database table.

<FooterTemplate>: This template too has an <asp:TextBox /> control and will allow users to enter values when the GridView is in Insert mode.

Along with the templates, I have added five events while defining the GridView properties. These events are OnRowEditing, OnRowCancelingEdit, OnPageIndexChanging, OnRowUpdating and OnRowDeleting respectively. Each event has a role to play while do the CRUD operations. I have written code for each event in the Code behind section.

JavaScript to Validate Textbox in GridView

I have also written a small code in JavaScript to validate if the user has entered some value (or text) in the textboxes. This is important as you can do some critical validations at the client side, before submitting the data to the server.

The function validate() is called when the Insert button in the GridView is clicked. The function takes a parameter, which is a reference of button. Using the reference, I’ll first find out its “parent” control, and later loop through all the controls inside the parent to find the textbox named tbBookName. I am only validating one field. You can write on multiple fields or textboxes.

<script>
    // JAVASCRIPT FOR VALIDATION.
    function validate(val) {
        var grdRow = val.parentNode.parentNode;
        var grdControl = grdRow.getElementsByTagName("*");

        // GET ALL THE CONTROLS IN THE GRIDVIEW.
        for (var i = 0; i < grdControl.length; i++) {

            // CHECK IF BOOK IS EMPTY.
            if (grdControl[i].id.indexOf("tbBookName") != -1) {
                var tbBook = grdControl[i];
                if (tbBook.value === "") {
                    alert("Fields are empty");
                    return false;
                }
            }
        }
    }
</script>
</html>

Code Behind (C#)
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class SiteMaster : System.Web.UI.MasterPage
{

    string sCon = "Data Source=DNA;Persist Security Info=False;" + 
        "Initial Catalog=DNA_Classified;User Id=sa;Password=;Connect Timeout=30;";

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

    private void BindGrid_With_Data()
    {
        using (SqlConnection con = new SqlConnection(sCon))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.Books"))
            {

                SqlDataAdapter sda = new SqlDataAdapter();
                try
                {
                    cmd.Connection = con;
                    con.Open();
                    sda.SelectCommand = cmd;

                    DataTable dt = new DataTable();
                    sda.Fill(dt);

                    // BIND DATABASE WITH THE GRIDVIEW.
                    if (dt.Rows.Count != 0)         // CHECK IF THE BOOKS TABLE HAS RECORDS.
                    {
                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                    }
                    else
                    {
                        // CREATE A BLANK ROW IF THE BOOKS TABLE IS EMPTY.

                        DataRow aBlankRow = dt.NewRow();
                        dt.Rows.Add(aBlankRow);
                        GridView1.DataSource = dt;
                        GridView1.DataBind();

                        // SHOW A SINGLE COLUMN WITH A MESSAGE.
                        int col = GridView1.Rows[0].Cells.Count;
                        GridView1.Rows[0].Cells.Clear();
                        GridView1.Rows[0].Cells.Add(new TableCell());
                        GridView1.Rows[0].Cells[0].ColumnSpan = col;
                        GridView1.Rows[0].Cells[0].Text = "Table is Empty";
                    }
                }
                catch (Exception ex)
                {
                    //
                }
                finally
                {
                    con.Close();
                }
            }
        }
    }

    // INSERT A NEW RECORD.
    protected void InsertRecord(object sender, EventArgs e)
    {
        // GET THE ACTIVE GRIDVIEW ROW.
        Button bt = (Button)sender;
        GridViewRow grdRow = (GridViewRow)bt.Parent.Parent;

        // NOW GET VALUES FROM FIELDS FROM THE ACTIVE ROW.
        TextBox tbBookName = (TextBox)grdRow.Cells[0].FindControl("tbBookName");
        TextBox tbCategory = (TextBox)grdRow.Cells[0].FindControl("tbCategory");
        TextBox tbPrice = (TextBox)grdRow.Cells[0].FindControl("tbPrice");

        if (!string.IsNullOrEmpty(tbBookName.Text.Trim()))
        {
            if (Perform_CRUD(0, tbBookName.Text, tbCategory.Text, double.Parse(tbPrice.Text), "INSERT"))
            {
                BindGrid_With_Data();    // REFRESH THE GRIDVIEW.
            }
        }
    }

    protected void GridView1_PageIndexChanging(object sender, System.Web.UI.WebControls.GridViewPageEventArgs e)
    {
        // GRIDVIEW PAGING.
        GridView1.PageIndex = e.NewPageIndex;
        BindGrid_With_Data();
    }

    protected void GridView_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        BindGrid_With_Data();
    }

    protected void GridView_RowCancelingEdit(object sender, System.Web.UI.WebControls.GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        BindGrid_With_Data();
    }

    // EXTRACT DETAILS FOR UPDATING.
    protected void GridView1_RowUpdating(object sender, System.Web.UI.WebControls.GridViewUpdateEventArgs e)
    {
        Label lblBookID = (Label)GridView1.Rows[e.RowIndex].FindControl("lblBookID");
        TextBox tbBookName = (TextBox)GridView1.Rows[e.RowIndex].FindControl("tbEd_Book");
        TextBox tbCategory = (TextBox)GridView1.Rows[e.RowIndex].FindControl("tbEd_Cate");
        TextBox tbPrice = (TextBox)GridView1.Rows[e.RowIndex].FindControl("tbEd_Price");

        if (int.Parse(lblBookID.Text) != 0)
        {
            if (Perform_CRUD(int.Parse(lblBookID.Text), tbBookName.Text, tbCategory.Text, double.Parse(tbPrice.Text), "UPDATE"))
            {
                BindGrid_With_Data();       // REFRESH THE GRIDVIEW.
            }
        }
    }

    protected void GridView1_RowDeleting(object sender, System.Web.UI.WebControls.GridViewDeleteEventArgs e)
    {
        Label lblBookID = (Label)GridView1.Rows[e.RowIndex].FindControl("lblBookID");

        if (int.Parse(lblBookID.Text) != 0)
        {
            if (Perform_CRUD(int.Parse(lblBookID.Text), "", "", 0, "DELETE"))
            {
                BindGrid_With_Data();   // REFRESH THE GRIDVIEW.
            }
        }
    }

    // PRIVATE FUNCTION THAT WILL DO "CRUD" OPERATION.
    // IT TAKES FOUR PARAMETERS FOR UPDATE, DELETE AND INSERT.
    // THE LAST PARAMETER "sOperation" IS THE TYPE OF OPERATION.

    private bool Perform_CRUD(int iBookID, string sBookName, string sCategory, double dPrice, string sOperation)
    {

        using (SqlConnection con = new SqlConnection(sCon))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.Books"))
            {

                cmd.Connection = con;
                con.Open();

                switch (sOperation)
                {
                    case "INSERT":
                        cmd.CommandText = "INSERT INTO dbo.Books (BookName, Category, Price) " + "VALUES(@BookName, @Category, @Price)";

                        cmd.Parameters.AddWithValue("@BookName", sBookName.Trim());
                        cmd.Parameters.AddWithValue("@Category", sCategory.Trim());
                        cmd.Parameters.AddWithValue("@Price", dPrice);

                        break;
                    case "UPDATE":
                        cmd.CommandText = "UPDATE dbo.Books SET BookName = @BookName, Category = @Category, " + "Price = @Price WHERE BookID = @BookID";

                        cmd.Parameters.AddWithValue("@BookName", sBookName.Trim());
                        cmd.Parameters.AddWithValue("@Category", sCategory.Trim());
                        cmd.Parameters.AddWithValue("@Price", dPrice);
                        cmd.Parameters.AddWithValue("@BookID", iBookID);

                        break;
                    case "DELETE":
                        cmd.CommandText = "DELETE FROM dbo.Books WHERE BookID = @BookID";
                        cmd.Parameters.AddWithValue("@BookID", iBookID);
                        break;
                }

                cmd.ExecuteNonQuery();
                GridView1.EditIndex = -1;
            }
        }

        return true;
    }
}
Code behind (Vb.Net)
Option Explicit On
Imports System.Data
Imports System.Data.SqlClient

Partial Class Site
    Inherits System.Web.UI.MasterPage

    Dim sCon As String = "Data Source=DNA;Persist Security Info=False;" & _
        "Initial Catalog=DNA_Classified;User Id=sa;Password=;Connect Timeout=30;"

    Protected Sub frm_Load(sender As Object, e As System.EventArgs) Handles frm.Load
        If Not Page.IsPostBack Then
            BindGrid_With_Data()
        End If
    End Sub

    Private Sub BindGrid_With_Data()
        Using con As SqlConnection = New SqlConnection(sCon)
            Using cmd As SqlCommand = New SqlCommand("SELECT *FROM dbo.Books")

                Dim sda As SqlDataAdapter = New SqlDataAdapter
                Try
                    cmd.Connection = con : con.Open()
                    sda.SelectCommand = cmd

                    Dim dt As DataTable = New DataTable
                    sda.Fill(dt)

                    ' BIND DATABASE WITH THE GRIDVIEW.
                    If dt.Rows.Count <> 0 Then          ' CHECK IF THE BOOKS TABLE HAS RECORDS.
                        GridView1.DataSource = dt
                        GridView1.DataBind()
                    Else
                        ' CREATE A BLANK ROW IF THE BOOKS TABLE IS EMPTY.

                        Dim aBlankRow As DataRow = dt.NewRow()
                        dt.Rows.Add(aBlankRow)
                        GridView1.DataSource = dt
                        GridView1.DataBind()

                        ' SHOW A SINGLE COLUMN WITH A MESSAGE.
                        With GridView1
                            Dim col As Integer = .Rows(0).Cells.Count
                            .Rows(0).Cells.Clear()
                            .Rows(0).Cells.Add(New TableCell())
                            .Rows(0).Cells(0).ColumnSpan = col
                            .Rows(0).Cells(0).Text = "Table is Empty"
                        End With
                    End If
                Catch ex As Exception
                    '
                Finally
                    con.Close()
                End Try
            End Using
        End Using
    End Sub

    ' INSERT A NEW RECORD.
    Protected Sub InsertRecord(ByVal sender As Object, ByVal e As EventArgs)
        ' GET THE ACTIVE GRIDVIEW ROW.
        Dim bt As Button = DirectCast(sender, Button)
        Dim grdRow As GridViewRow = DirectCast(bt.Parent.Parent, GridViewRow)

        ' NOW GET VALUES FROM FIELDS FROM THE ACTIVE ROW.
        Dim tbBookName As TextBox = DirectCast(grdRow.Cells(0).FindControl("tbBookName"), TextBox)
        Dim tbCategory As TextBox = DirectCast(grdRow.Cells(0).FindControl("tbCategory"), TextBox)
        Dim tbPrice As TextBox = DirectCast(grdRow.Cells(0).FindControl("tbPrice"), TextBox)

        If Trim(tbBookName.Text) <> "" Then
            If Perform_CRUD(0, tbBookName.Text, tbCategory.Text, tbPrice.Text, "INSERT") Then
                BindGrid_With_Data()   ' REFRESH THE GRIDVIEW.
            End If
        End If
    End Sub

    Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) 

        ' GRIDVIEW PAGING.
        GridView1.PageIndex = e.NewPageIndex
        BindGrid_With_Data()
    End Sub

    Protected Sub GridView_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
        GridView1.EditIndex = e.NewEditIndex
        BindGrid_With_Data()
    End Sub

    Protected Sub GridView_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs)
        GridView1.EditIndex = -1
        BindGrid_With_Data()
    End Sub

    ' EXTRACT DETAILS FOR UPDATING.
    Protected Sub GridView1_RowUpdating(sender As Object, e As System.Web.UI.WebControls.GridViewUpdateEventArgs)

        Dim lblBookID As Label = DirectCast(GridView1.Rows(e.RowIndex).FindControl("lblBookID"), Label)
        Dim tbBookName As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("tbEd_Book"), TextBox)
        Dim tbCategory As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("tbEd_Cate"), TextBox)
        Dim tbPrice As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("tbEd_Price"), TextBox)

        If Val(lblBookID.Text) <> 0 Then
            If Perform_CRUD(lblBookID.Text, tbBookName.Text, tbCategory.Text, tbPrice.Text, "UPDATE") Then
                BindGrid_With_Data()   ' REFRESH THE GRIDVIEW.
            End If
        End If
    End Sub

    Protected Sub GridView1_RowDeleting(sender As Object, e As System.Web.UI.WebControls.GridViewDeleteEventArgs)
        Dim lblBookID As Label = DirectCast(GridView1.Rows(e.RowIndex).FindControl("lblBookID"), Label)

        If Val(lblBookID.Text) <> 0 Then
            If Perform_CRUD(lblBookID.Text, "", "", 0, "DELETE") Then
                BindGrid_With_Data()   ' REFRESH THE GRIDVIEW.
            End If
        End If
    End Sub

    ' PRIVATE FUNCTION THAT WILL DO "CRUD" OPERATION.
    ' IT TAKES FOUR PARAMETERS FOR UPDATE, DELETE AND INSERT.
    ' THE LAST PARAMETER "sOperation" IS THE TYPE OF OPERATION.
    Private Function Perform_CRUD(
                ByVal iBookID As Integer, ByVal sBookName As String,
                ByVal sCategory As String, ByVal dPrice As Double,
                ByVal sOperation As String) As Boolean

        Using con As SqlConnection = New SqlConnection(sCon)
            Using cmd As SqlCommand = New SqlCommand("SELECT *FROM dbo.Books")

                ' FINALLY INSERT ROW VALUES IN THE TABLE.
                With cmd
                    .Connection = con : con.Open()

                    Select Case sOperation
                        Case "INSERT"
                            .CommandText = "INSERT INTO dbo.Books (BookName, Category, Price) " & _
                                "VALUES(@BookName, @Category, @Price)"

                            .Parameters.AddWithValue("@BookName", Trim(sBookName))
                            .Parameters.AddWithValue("@Category", Trim(sCategory))
                            .Parameters.AddWithValue("@Price", dPrice)

                        Case "UPDATE"
                            .CommandText = "UPDATE dbo.Books SET BookName = @BookName, Category = @Category, " & _
                                "Price = @Price WHERE BookID = @BookID"

                            .Parameters.AddWithValue("@BookName", Trim(sBookName))
                            .Parameters.AddWithValue("@Category", Trim(sCategory))
                            .Parameters.AddWithValue("@Price", dPrice)
                            .Parameters.AddWithValue("@BookID", iBookID)

                        Case "DELETE"
                            .CommandText = "DELETE FROM dbo.Books WHERE BookID = @BookID"
                            .Parameters.AddWithValue("@BookID", iBookID)
                    End Select

                    .ExecuteNonQuery()
                    GridView1.EditIndex = -1
                End With
            End Using
        End Using

        Return True
    End Function
End Class

That’s it. Hope you find this example on CRUD operation useful. Thanks for reading.

← PreviousNext →


Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner

Related Posts: