Display Data in Gridview with Pagination and Search

In this article we will cover how to display data in Gridview Control with pagination and search using C# and SQL. Live Example

SQL Procedure

I have created sql procedure to retrieve data based on search text. Change Procedure, Table name and columns as per your requirement and run SQL Procedure.

CREATE PROCEDURE [PROC_COUNTRIES] 
	@SEARCH VARCHAR(100)=''
AS
BEGIN
	
	SET NOCOUNT ON;

    IF(@SEARCH != '')
	
	SELECT [COUNTRIES_ID] AS ID,[COUNTRIES_NAME] AS NAME,
	[COUNTRIES_ISO_CODE] AS ISO_CODE,[COUNTRIES_ISD_CODE] AS ISD_CODE
	FROM COUNTRIES WHERE [COUNTRIES_NAME] LIKE '%'+ @SEARCH +'%' OR 
	   [COUNTRIES_ISO_CODE] LIKE '%'+ @SEARCH +'%' OR
	   [COUNTRIES_ISD_CODE] LIKE '%'+ @SEARCH +'%'

	ELSE

	SELECT [COUNTRIES_ID] AS ID
      ,[COUNTRIES_NAME] AS NAME
      ,[COUNTRIES_ISO_CODE] AS ISO_CODE
      ,[COUNTRIES_ISD_CODE] AS ISD_CODE
	FROM COUNTRIES; 

END

Countries.aspx

Create a Web Form named "Countries.aspx"

Add below mentioned html code to search and display all the Countries with pagination inside Gridview Control

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
    <ContentTemplate>
        <asp:UpdateProgress ID="up1" AssociatedUpdatePanelID="UpdatePanel1" runat="server" DisplayAfter="10">
            <ProgressTemplate>
                <div class="loading" style="display: block !important">
                    <div class="spinner-border" role="status">
                        <span class="sr-only">Loading...</span>
                    </div>
                </div>
            </ProgressTemplate>
        </asp:UpdateProgress>
        <div class="container">
            <div class="row">
                <div class="col-lg-8 col-md-10 mx-auto">
                    <div class="form-row">
                        <div class="control-group col-md-12">
                            <asp:HiddenField ID="hdnTotRecords" runat="server"></asp:HiddenField>
                            <div class="row">
                                <div class="col-sm-6 form-group">
                                    <asp:TextBox ID="txtSearch" runat="server" CssClass="form-control" placeholder="Search here"></asp:TextBox>
                                </div>
                                <div class="col-sm-6 form-group">
                                    <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" CssClass="btn btn-primary" />
                                    <asp:Button ID="btnClear" runat="server" Text="Clear" OnClick="btnClear_Click" CssClass="btn btn-primary" />
                                </div>
                            </div>
                            <asp:GridView ID="GridView1" runat="server" Width="100%" AutoGenerateColumns="false" AllowPaging="true" ShowFooter="true" OnPageIndexChanging="OnPaging" EmptyDataText="No Records Found" CellPadding="10" OnRowDataBound="GridView1_RowDataBound">
                                <Columns>
                                    <asp:BoundField DataField="ID" HeaderText="Country ID" />
                                    <asp:BoundField DataField="Name" HeaderText="Country Name" />
                                    <asp:BoundField DataField="iso_code" HeaderText="Country ISO Code" />
                                    <asp:BoundField DataField="isd_code" HeaderText="Country ISD Code" />
                                </Columns>
                                <FooterStyle HorizontalAlign="Center" />
                                <PagerStyle CssClass="pad10" />
                            </asp:GridView>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </ContentTemplate>
</asp:UpdatePanel>

<style>
    .pad10 td {
        padding-left: 10px;
    }
 
    .pad10 span {
        color: green;
        font-weight: bold
    }
</style>

Countries.aspx.cs

Add below mentioned code to .cs file

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
 
namespace HpBlogs
{
    public partial class Countries : System.Web.UI.Page
    {
        private const string constr = "Password=dbpassword;Persist Security Info=True;User ID=dbuserid;Initial Catalog=databasename;Data Source=datasource";
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                BindGrid();
            }
        }
        private void BindGrid()
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("PROCEDURE_NAME", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@SEARCH", txtSearch.Text);
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            hdnTotRecords.Value = dt.Rows.Count.ToString();
                            GridView1.DataSource = dt;
                            GridView1.DataBind();
 
                            GridView1.FooterRow.Cells[0].ColumnSpan = 4;
 
                            for (int i = 0; i < 3; i++)
                                GridView1.FooterRow.Cells.RemoveAt(1);
                        }
                    }
                }
            }
 
        }
        protected void OnPaging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            BindGrid();
        }
        protected void btnSearch_Click(object sender, EventArgs e)
        {
            BindGrid();
        }
        protected void btnClear_Click(object sender, EventArgs e)
        {
            txtSearch.Text = string.Empty;
            BindGrid();
        }
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.Footer)
            {
                var Count = int.Parse(hdnTotRecords.Value);
                var PageSize = 10;
                var showFrom = ((GridView1.PageIndex) * PageSize) + 1;
                var showTo = (showFrom + PageSize);
                var showToDisp = ((showTo - 1) > Count ? Count : (showTo - 1));
                var msg = "Showing " + showFrom + " to " + showToDisp + " of " + Count + " results";
 
                e.Row.Cells[0].Text = msg;
            }
        }
    }
}

Run the project. Output page would look like: Live Example