Last Updated: 11th March 2023
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, deleting etc. Here, in this article I am going show you how to perform a simple and basic CRUD operation using a paging enabled GridView in Asp.Net.
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 in both C# and Vb.
We first need a table, on which we do the CRUD operation using the GridView control. Therefore, we create a table with few columns in it. 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 in our web page.
The Markup
<!DOCTYPE html>
<html>
<head runat="server">
<!--add style to the gridview control-->
<style>
.grid { height:100px;
width:100%;
background-color:#FFF;
border:solid 1px #525252;
}
.grid td { padding:2px;
border:solid 1px #C1C1C1;
color:#333;
text-align:center;
}
.grid th { padding:3px;
color:#FFF;
background:#424242;
border-left:solid 1px #525252;
text-align:center;
}
input { padding:3px 2px; }
.txt {
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 first column). The templates are <ItemTemplate>, <EditItemTemplate> and the <FooterTemplate>. Each template is inside the <asp:TemplateField> attribute.
1) <ItemTemplate>: It will show values in each row. You cannot edit values in it.
2) <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.
3) <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 is clicked. The function takes a parameter, which is a reference of the button. Using the reference, I’ll first find out its "parent" control, 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