by
0 0
Read Time:10 Minute, 5 Second

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

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.

Perform a Simple CRUD Operation Using GridView

www.blog.softhubsolution.com

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.

Default.aspx Markup Page

<!DOCTYPE html>
<html>
<head runat="server">
    <title>Performing CRUD Operation using GridView</title>
    <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 
         {
             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>

JavaScript to Validate Textbox in GridView

 // 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;
                }
            }
        }
    }

Default.aspx.cs (Code Behind Markup) Using 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;
    }
}

Default.aspx.vba (Code Behind Markup) Using Visual Basic

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

Thanks for reading this article for more topics comments and let me know your thoughts which topic you guys wants to read.

About Post Author

Gaurav Dixit

dgaurav300@gmail.com
http://blog.softhubsolution.com
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %
Post Views: 150

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%
(Add your review)

Leave a Comment