Load more records in Asp.Net Gridview on button click from sql server table ~ Asp.Net,C#.Net,VB.Net,MVC,jQuery,JavaScipt,AJAX,WCF,Sql Server example

Introduction: In this article, I am going to demonstrate how to get more data on demand i.e. On every click of button fetch more records from SQL Server database table and load in GridView with wait/progress/loading image as shown in image in ASP.NET using both C# and VB languages.

Description: Basically you will learn the following through this article:

How to initially bind specified number of records/data from sql server database table to Gridview.
How to bind some data in Gridview and on every click of "Load More Data button" fetch more data and load in Gridview.
How to show wait/progress image using Ajax "UpdateProgress" and "ProgressTemplate" while fetching more data from sql server table and binding to gridview.

In previous articles i explained How to Load more data on button click in Repeater and Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView and Bind and Export GridView data to PDF file and Implement searching in gridview records and Highlight gridview row on mouse over using CSS and Upload and store image in binary format in Sql server database and retrieve, bind to Gridview

Implementation: Let's create a demo website to demonstrate the concept.

First of all create a DataBase in Sql server and name it e.g. "DB_Student" and in this database create a table with the following Columns and Data type as shown below and name this table "Tbl_Student".

Column Name	Data Type
StudentId	Int(Primary Key. So set is identity=true)
StudentName	varchar(100)
Class	varchar(50)
Age	Int
Gender	varchar(50)
Address	varchar(500)

Create a stored procedure to get student details to be filled in GridView Data Control.

CREATE PROCEDURE [dbo].[GetStudentDetails_SP]

@topVal INT

BEGIN

--if @topVal=2 then the below query will become SELECT top (2) * FROM dbo.Tbl_Student and get 2 records

SELECT top (@topVal) * FROM dbo.Tbl_Student

END

Create another stored procedure to count the number of rows present in table:

CREATE PROCEDURE [dbo].[GetStudentDetailsCount_SP]

BEGIN

SELECT COUNT(*) FROM dbo.Tbl_Student

END

Now let's connect our asp.net application with Sql Server database

So In the <configuration> tag of web.config file create the connection string as:

</connectionStrings>

Note: Replace the Data Source and Initial Catalog as per your database settings.

Asp.Net C# Section

Below is the HTML Source of the Default.aspx page.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<asp:ScriptManager ID="ScriptManager1" runat="server">

</asp:ScriptManager>

<asp:UpdatePanel ID="UpdatePanel1" runat="server">

<legend>Load more data on demand in GridView</legend>

<table>

<tr>

<td>

<asp:GridView ID="grdStudentDetails" runat="server" AutoGenerateColumns="False"

CellPadding="4" ForeColor="#333333" GridLines="None">

<asp:BoundField HeaderText="Student Name" DataField="StudentName" />

<asp:BoundField HeaderText="Class" DataField="Class" />

<asp:BoundField HeaderText="Age" DataField="Age" />

<asp:BoundField HeaderText="Gender" DataField="Gender" />

<asp:BoundField HeaderText="Address" DataField="Address" />

</Columns>

</asp:GridView>

</td>

</tr>

<tr>

<asp:Button ID="btnLoadMore" runat="server" Text="Load More Data"

onclick="btnLoadMore_Click" />

</td>

</tr>

<tr>

<asp:UpdateProgress ID="UpdateProgress1" runat="server" ClientIDMode="Static" DisplayAfter="10">

</ProgressTemplate>

</asp:UpdateProgress>

</td></tr>

</table>

</fieldset>

</ContentTemplate>

</asp:UpdatePanel>

</div>

</form>

</body>

</html>

Note: You need to search on internet and download any wait/progress gif image of your choice and name it "ajax-loader.gif" and paste in root folder of your project

Asp.Net C# Code Section:

In code behind file (default.aspx.cs) write the code as;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

public partial class _Default: System.Web.UI.Page

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

int num = 0;

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

//Set the num variable equal to the value that you want to load data in gridView.

//e.g if initially you want to load 2 rows in repeater then set num=2.

num = 2;

//store this num value in ViewState so that we can get this value on Load more data button click

ViewState["num"] = num;

BindGridView(num);

}

private void BindGridView(int numOfRows)

{

DataTable dt = new DataTable();

SqlCommand cmd = null;

SqlDataAdapter adp = null;

try

{

//get number rows in table by calling the rowCount function i created.

int rCount = rowCount();

// hide the "Load More Data button" if the number of requested rows becomes greater than the rows in table

if (numOfRows > rCount)

{

btnLoadMore.Visible = false;

}

cmd = new SqlCommand("GetStudentDetails_SP", con);

//Passs numOfRows variable value to stored procedure to get desired number of rows

cmd.Parameters.AddWithValue("@topVal", numOfRows);

cmd.CommandType = CommandType.StoredProcedure;

adp = new SqlDataAdapter(cmd);

adp.Fill(dt);

if (dt.Rows.Count > 0)

{

grdStudentDetails.DataSource = dt;

grdStudentDetails.DataBind();

}

else

{

grdStudentDetails.DataSource = null;

grdStudentDetails.DataBind();

}

catch (Exception ex)

{

ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured: " + ex.Message.ToString() + "');", true);

}

finally

{

con.Close();

cmd.Dispose();

adp = null;

dt.Clear();

dt.Dispose();

}

protected int rowCount()

{

int NoOfRows = 0;

SqlCommand cmd = new SqlCommand("GetStudentDetailsCount_SP", con);

cmd.CommandType = CommandType.StoredProcedure;

try

{

con.Open();

NoOfRows = Convert.ToInt32(cmd.ExecuteScalar());

}

catch (Exception ex)

{

ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured: " + ex.Message.ToString() + "');", true);

}

finally

{

con.Close();

cmd.Dispose();

}

return NoOfRows;

}

protected void btnLoadMore_Click(object sender, EventArgs e)

{

//On every click of this button it will add 2 to the ViewState["num"] whose value was set to 2 initially on page load. So numval is 4 now.

int numVal = Convert.ToInt32(ViewState["num"]) + 2;

//Now pass numVal whose value is 4 to the BindGridView function to get 4 rows.

BindGridView(numVal);

//Set ViewState["num"] is equal to the numVal i.e. 4 so that when we again click this button it will be 4 + 2= 6 and so on.

ViewState["num"] = numVal;
}

}

Asp.Net VB Section:

Design the page (default.aspx) as in above Asp.net C# section but replace the lines

<asp:Button ID="btnLoadMore" runat="server" Text="Load More Data"

onclick="btnLoadMore_Click" />

with following line:

<asp:Button ID="btnLoadMore" runat="server" Text="Load More Data" />

In the code behind file (e.g. default.aspx.vb) write the code as:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Partial Class _Default

Inherits System.Web.UI.Page

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

Dim num As Integer = 0

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

If Not Page.IsPostBack Then

'Set the num variable equal to the value that you want to load data in GridView.

'e.g if initially you want to load 2 rows in repeater then set num=2.

num = 2

'store this num value in ViewState so that we can get this value on Load more data button click

ViewState("num") = 2

BindGridView(num)

End If

End Sub

Private Sub BindGridView(numOfRows As Integer)

Dim dt As New DataTable()

Dim cmd As SqlCommand = Nothing

Dim adp As SqlDataAdapter = Nothing

Try

'get number rows in table by calling the rowCount function i created.

Dim rCount As Integer = rowCount()

'hide the "Load More Data button" if the number of requested rows becomes greater than the rows in table

If numOfRows > rCount Then

btnLoadMore.Visible = False

End If

cmd = New SqlCommand("GetStudentDetails_SP", con)

'Passs numOfRows variable value to stored procedure to get desired number of rows

cmd.Parameters.AddWithValue("@topVal", numOfRows)

cmd.CommandType = CommandType.StoredProcedure

adp = New SqlDataAdapter(cmd)

adp.Fill(dt)

If dt.Rows.Count > 0 Then

grdStudentDetails.DataSource = dt

grdStudentDetails.DataBind()

Else

grdStudentDetails.DataSource = Nothing

grdStudentDetails.DataBind()

End If

Catch ex As Exception

ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Oops!! Error occured: " + ex.Message.ToString() + "');", True)

Finally

con.Close()

cmd.Dispose()

adp = Nothing

dt.Clear()

dt.Dispose()

End Try

End Sub

Protected Function rowCount() As Integer

Dim NoOfRows As Integer = 0

Dim cmd As New SqlCommand("GetStudentDetailsCount_SP", con)

cmd.CommandType = CommandType.StoredProcedure

Try

con.Open()

NoOfRows = Convert.ToInt32(cmd.ExecuteScalar())

Catch ex As Exception

ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Oops!! Error occured: " + ex.Message.ToString() + "');", True)

Finally

con.Close()

cmd.Dispose()

End Try

Return NoOfRows

End Function

Protected Sub btnLoadMore_Click(sender As Object, e As System.EventArgs) Handles btnLoadMore.Click

'On every click of this button it will add 2 to the ViewState("num") whose value was set to 2 initially on page load. So numval is 4 now.

Dim numVal As Integer = Convert.ToInt32(ViewState("num")) + 2

'Now pass numVal whose value is 4 to the BindGridView function to get 4 rows.

BindGridView(numVal)

'Set ViewState("num") is equal to the numVal i.e. 4 so that when we again click this button it will be 4 + 2= 6 and so on.

ViewState("num") = numVal
End Sub

End Class

Now over to you:

" I hope you have got the way to load more data on demand in GridView in Asp.Net and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."

6 comments

Click here for comments

Unknown

admin

June 01, 2014 ×

Good Post Lalit Sir, I like it.
Keep it up....

Lalit Raghuvanshi

Thanks Shaiwal Tripathi for appreciating my work..stay connected and keep reading for more useful articles like this...:)

Bhavik

August 18, 2014 ×

Great Job.. Learn new thing

Thanks Bhavik..i am glad you liked this articles..stay connected and keep reading:)

Anonymous

September 16, 2014 ×

good article,am learned new concept..... :)

I am glad you found this article useful for you..stay connected and keep reading..:)

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..

Load more records in Asp.Net Gridview on button click from sql server table

Related Post

6 comments