using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Collections.Generic;
/// <summary>
/// Author: Tom Arnaldo E. Bauto (tom.bauto@gmail.com)
/// Date: May 27, 2009
/// Remarks: This is a custom paging for Asp.Net Gridview; data is retrieved through an SQL 2005 database
/// </summary>
public partial class home : System.Web.UI.Page
{
#region Load
protected void Page_Load(object sender, EventArgs e)
{
/* Set the events for Object : You can do this on GUI instead */
this.btnPrevious.Click += new EventHandler(btnPrevious_Click);
this.btnNext.Click += new EventHandler(btnNext_Click);
this.btnJump.Click += new EventHandler(btnJump_Click);
this.txtJumpToPage.SelectedIndexChanged += new EventHandler(txtJumpToPage_SelectedIndexChanged);
/* From first load of the page */
if (!IsPostBack)
{
this.txtPage.Text = "1"; /* Set the start row index */
this.txtTotalRowsPerPage.Text = "5"; /* Set a minimum rows per page */
ReloadGridview(1,5); /* Call the paging function */
}
}
protected void Page_LoadComplete(object sender, EventArgs e)
{
/* This one handles the drop down JUMP TO PAGE */
for (int i = 0; i < this.txtJumpToPage.Items.Count; i++)
{
if (txtJumpToPage.Items[i].Value == SelectedPageIndex.ToString())
{
txtJumpToPage.Items[i].Selected = true;
break;
}
}
}
#endregion
#region Property
/* Create a constant values */
const string CONSTANT_VIEWSTATE_CURRENT_ROW = "VIEWSTATE_CURRENT_ROW_INDEX";
const string CONSTANT_VIEWSTATE_TOTAL_RESULT_ROW = "VIEWSTATE_TOTAL_RESULT_ROW";
const string CONSTANT_VIEWSTATE_SELECTED_PAGE_INDEX = "VIEWSTATE_SELECTED_PAGE_COUNT";
/* This will store the Selected Page Index into ViewState */
int SelectedPageIndex
{
get
{
if (ViewState[CONSTANT_VIEWSTATE_SELECTED_PAGE_INDEX] == null)
{
ViewState[CONSTANT_VIEWSTATE_SELECTED_PAGE_INDEX] = 1;
}
int _currentRow = Convert.ToInt32(ViewState[CONSTANT_VIEWSTATE_SELECTED_PAGE_INDEX]);
return _currentRow;
}
set
{
ViewState[CONSTANT_VIEWSTATE_SELECTED_PAGE_INDEX] = value;
}
}
/* This will store the Current total Result Row into ViewState */
int CurrentTotalResultRow
{
get
{
if (ViewState[CONSTANT_VIEWSTATE_TOTAL_RESULT_ROW] == null)
{
ViewState[CONSTANT_VIEWSTATE_TOTAL_RESULT_ROW] = 0;
}
int _currentRow = Convert.ToInt32(ViewState[CONSTANT_VIEWSTATE_TOTAL_RESULT_ROW]);
return _currentRow;
}
set
{
ViewState[CONSTANT_VIEWSTATE_TOTAL_RESULT_ROW] = value;
}
}
/* This will store the Current Row into ViewState */
int CurrentRow
{
get
{
if (ViewState[CONSTANT_VIEWSTATE_CURRENT_ROW] == null)
{
ViewState[CONSTANT_VIEWSTATE_CURRENT_ROW] = 1;
}
int _currentRow = Convert.ToInt32(ViewState[CONSTANT_VIEWSTATE_CURRENT_ROW]);
return _currentRow;
}
set
{
if (value > 0 && value < CurrentTotalResultRow)
ViewState[CONSTANT_VIEWSTATE_CURRENT_ROW] = value;
}
}
/* This will store the Total Rows Per Page which is entered on textbox into ViewState */
int RowPerPage
{
get
{
return Convert.ToInt32((string.IsNullOrEmpty(this.txtTotalRowsPerPage.Text)) ? "1" : this.txtTotalRowsPerPage.Text);
}
}
/* This will store the Selected Value of the JUMP TO PAGE into ViewState */
int JumpPage
{
get
{
return Convert.ToInt32((string.IsNullOrEmpty(this.txtPage.Text)) ? "1" : this.txtPage.Text);
}
}
#endregion
#region Paging
void btnNext_Click(object sender, EventArgs e)
{
int rows_start;
int rows_per_page;
rows_per_page = RowPerPage;
rows_start = CurrentRow + rows_per_page;
ReloadGridview(rows_start, rows_per_page);
}
void btnPrevious_Click(object sender, EventArgs e)
{
int rows_start;
int rows_per_page;
rows_per_page = RowPerPage;
if (CurrentRow > CurrentTotalResultRow)
CurrentRow = CurrentTotalResultRow;
rows_start = CurrentRow - rows_per_page;
ReloadGridview(rows_start, rows_per_page);
}
void btnJump_Click(object sender, EventArgs e)
{
int j = JumpPage * RowPerPage;
ReloadGridview(j, RowPerPage);
}
#endregion
#region Data binding
/* Setting up the paging method */
void ReloadGridview(int rows_start, int rows_per_page)
{
CurrentRow = rows_start; /* Set the current row value into the rowStart */
int total_rows = 0; /* Set to default since we will call a out type parameter in LoadGridData function */
DataTable DT = LoadGridData(out total_rows, rows_start, rows_per_page); /* Call the function and retrieve datatable, assign the rows to be selected */
this.GridView1.DataSource = DT; /* Binding of datatable to GridView */
this.GridView1.DataBind(); /* Refresh the GUI */
CurrentTotalResultRow = total_rows; /* Assign the retrieved totalrows into viewState */
bindJumpToPage(total_rows / rows_per_page); /* Bind the JUMP INTO PAGE dropdown control */
this.div_total_records.InnerText = string.Format("{0} Record(s) / {1} Page(s)", total_rows, total_rows / rows_per_page); /* Display the information about the result */ /* Display the information about the result */
}
/* Get records from the database and return a datatable */
private DataTable LoadGridData(out int total_rows, int rows_start, int rows_per_page)
{
string SQL = GetSQL(); /* Get the SQL Statement, in your program you may use the text from this method on your Stored Procedure instead */
/* Get the connection string */
/* For ouw demo, since I have Northwind on my database, I used it for better familliarity */
string con_str = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(con_str);
con.Open(); /* Open connection */
/* Setting up the parameters */
/* Here, we assign the Object or the main Query, we can use SQL VIEWS, but for this example, we assign plain query*/
string MAIN_SQL = @"
Select
A.ProductId, A.ProductName, A.UnitPrice, A.UnitsInStock, B.CompanyName
From Products AS A
Inner Join Suppliers AS B on (B.SupplierId = A.SupplierId)
";
/* Here, we assign the fields to be displayed on our GridView */
string FIELDS_TO_DISPLAY = " ProductId, ProductName, UnitPrice, UnitsInStock, CompanyName ";
/* Here, we assign the Fields to be sorted, on our example its just one field and sorted Ascending */
string FIELDS_TO_BE_SORT = " ProductName ASC ";
/*
* No we simple put the variable on its index assignment,
* remember, if you are using Stored Procedure it will be much easier and much descriptive
* because in our example we simple used the string format we have from .Net */
SQL = string.Format(SQL, MAIN_SQL, FIELDS_TO_DISPLAY, FIELDS_TO_BE_SORT, rows_start, rows_per_page);
/* basic assignment of SqlClient */
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.Text;
com.CommandText = SQL;
/* Set the parameter get, to get the Total rows therein in our main Query */
com.Parameters.Add("@TOTAL", SqlDbType.Int).Direction = ParameterDirection.Output;
/* Create an adapter object */
SqlDataAdapter ada = new SqlDataAdapter(com);
DataTable DT = new DataTable();
ada.Fill(DT); /* Bind the filtered result to our datatable */
/* Set the total rows to ViewState */
total_rows = Convert.ToInt32(com.Parameters["@TOTAL"].Value);
/* Remember to properly disposed your objects */
con.Close();
com.Dispose();
ada.Dispose();
/* return the collection */
return DT;
}
#endregion
#region Method
/* Method to bind the dropdown box */
void bindJumpToPage(int total)
{
this.txtJumpToPage.Items.Clear();
for (int i = 1; i <= total; i++)
{
this.txtJumpToPage.Items.Add(i.ToString());
}
}
/* Method that fires up when drop down box index has changed */
void txtJumpToPage_SelectedIndexChanged(object sender, EventArgs e)
{
SelectedPageIndex = Convert.ToInt32(this.txtJumpToPage.SelectedValue);
int j = SelectedPageIndex * RowPerPage;
ReloadGridview(j, RowPerPage); /* Calls the reloading of GridView based from the JUMP TO PAGE value */
}
string GetSQL()
{
/* My Generated SQL Paging */
return @"
DECLARE
@START AS INT ,
@MAX AS INT ,
@SORT AS VARCHAR(100) ,
@FIELDS AS VARCHAR(MAX) ,
@OBJECT AS VARCHAR(MAX)
SELECT
@START = {3} ,
@MAX = {4} ,
@SORT = '{2}' ,
@FIELDS = '{1}' ,
@OBJECT = '{0}'
/* CLEANING PARAMETER VALUES */
IF (ISNULL(@SORT , '') = '') BEGIN SET @SORT = 'SELECT 1' END
IF (@START < 1) BEGIN SET @START = 1 END
IF (@MAX < 1) BEGIN SET @MAX = 1 END
/* SET THE LENGTH OF RESULT */
DECLARE @END AS INT
SET @END = (@START + (@MAX - 1))
/* GET THE TOTAL PAGE COUNT */
DECLARE
@SQL_COUNT AS NVARCHAR(MAX)
SET @TOTAL = 0
SET @SQL_COUNT = 'SELECT @GET_TOTAL = COUNT(*) FROM (' + @OBJECT + ') AS [TABLE_COUNT]'
EXEC sp_executesql @SQL_COUNT, N'@GET_TOTAL INT OUTPUT', @GET_TOTAL = @TOTAL OUTPUT
/* GET THE RECORDS BASED FROM THE GIVEN STATEMENT AND CONDITION */
DECLARE @SQL AS NVARCHAR(MAX)
SET @SQL =
'
SELECT ' + @FIELDS + '
FROM
(
SELECT
(ROW_NUMBER() OVER(ORDER BY ' + @SORT + ')) AS [ROWNUM] , *
FROM ( SELECT ' + @FIELDS + ' FROM (' + @OBJECT + ') AS [SOURCE_TABLE]) AS [SOURCE_COLLECTION]
) AS TMP
WHERE
[ROWNUM] BETWEEN ' + CAST(@START AS VARCHAR(10)) + ' AND ' + CAST(@END AS VARCHAR(10)) + '
'
EXEC(@SQL)
";
}
#endregion
}
Submit an article or tip