HI WELCOME TO KANSIRIS

LOAD MORE RECORDS IN ASP.NET GRIDVIEW ON BUTTON CLICK FROM SQL SERVER TABLE

Leave a Comment

Load more records in asp.net gridview on button clickIntroduction: 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.

 

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
AS
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]               
AS
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>
    <add name="conStr" connectionString="Data Source=LALIT;Initial Catalog=DB_Student;Integrated Security=True"/>
  </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">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>       
<fieldset style="width:370px;">
    <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">
         <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        <Columns>
        <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>
         <EditRowStyle BackColor="#999999" />
         <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
         <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
         <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
         <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
         <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
         <SortedAscendingCellStyle BackColor="#E9E7E2" />
         <SortedAscendingHeaderStyle BackColor="#506C8C" />
         <SortedDescendingCellStyle BackColor="#FFFDF8" />
         <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
        </asp:GridView>        
    </td>
    </tr>
    <tr>

    <td align="center">
        <asp:Button ID="btnLoadMore" runat="server" Text="Load More Data"
            onclick="btnLoadMore_Click" />
            </td>
            </tr>
            <tr>
            <td align="center">
            <asp:UpdateProgress ID="UpdateProgress1" runat="server" ClientIDMode="Static" DisplayAfter="10">
    <ProgressTemplate>
        <img src="ajax-loader.gif" alt="wait image" />   
    </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(thisthis.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(thisthis.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.EventArgsHandles 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(MeMe.[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(MeMe.[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.EventArgsHandles 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

0 comments:

Post a Comment

Note: only a member of this blog may post a comment.