When dealing with large data paging efficient paging is a must for us. Half the paging challenge is database SQL for the selected range of rows. With SQL Server 2005 we have Row Number function, which existed in Oracle for long. However, there is a difference in how we can use this to our need. Let’s take an example of we wanted to list purchase history of a user on the page that can run into hundreds or thousands of records. Here is an idea on how we can generate a set of records between given rownumbers:
SQL Server
SELECT Column1, Column2, Column3
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Purchase_Date Desc)AS RowN,
Column1, Column2, Column3 FROM myPurchaseHistory Where username = 'myUserName') As PH
WHERE RowN between 26 and 50
Oracle
Select *
From (Select Row_Number() Over (Partition By 'kigo' Order By Purchase_Date Desc) myTops,
Column1, Column2, Column3
From myPurchaseHistory Where username = 'myUserName')
Where myTops Between 26 And 50
--Another Old way
Select *
From (Select RowNum RowN, PH.*
From (Select Column1, Column2, Column3
From myPurchaseHistory Where username = 'myUserName'
Order By Purchase_Date Desc) PH)
Where RowN Between 26 And 50
Having figured out how to go about querying the range, we can create a stored procedure that can do this work for us. Here is an example on how this can be built with dynamic ObjectDataSource:
First, let’s build a class that has two methods (using Oracle here) GetPurchasesCount, GetPurchases and GetAllPurchases.
public class Purchases
{
public int purhcaseID = 0;
public string description = "";
public DateTime purchaseDate;
///
/// Returns the total purchase count
///
///
public static int GetPurchasesCount()
{
string sql = "Select Count(PurchaseID) PurchaseCount From myPurchaseHistory Where username = :username";
OracleParameter[] p = { new OracleParameter(":username", OracleDbType.Varchar2, 50) };
p[0].Value = HttpContext.Current.Profile.UserName;
object obj = myDataHelper.ExecuteScalar(sql, p, CommandType.Text);
int PolicyCount = 0;
int.TryParse(obj.ToString(), out PolicyCount);
return PolicyCount;
}
///
/// Returns range of purchases as list
///
///
public static List<Purchases> GetPurchases(int fromCount, int toCount)
{
string sql = "Select *" +
" From (Select RowNum RowN, PH.*" +
" From (select Column1, Column2, Column3" +
" From myPurchaseHistory Where username = :username " +
" Order By Purchase_Date Desc) PH)" +
" Where RowN Between :fromCount And :toCount ";
OracleParameter[] p = { new OracleParameter(":username", OracleDbType.Varchar2, 50),
new OracleParameter(":fromCount", OracleDbType.Int32),
new OracleParameter(":toCount", OracleDbType.Int32)};
p[0].Value = HttpContext.Current.Profile.UserName;
p[1].Value = fromCount;
p[2].Value = toCount;
OracleDataReader rdr = myDataHelper.ExecuteReader(sql, p, CommandType.Text);
List<Purchases> purchaseHistory = new List<Purchases>();
//Fill purchaseHistory here...
return purchaseHistory;
}
}
Now, let’s say on our ASP.Net page we have a drop down list that the user can choose to change the page size and other standard stuff. We can code a method called BindList and code it like this:
private int CurrentPage
{
get
{
// look for current page in ViewState
object o = this.ViewState["CurrentPage"];
if (o == null)
return 0; // default page index of 0
else
return (int)o;
}
set
{
this.ViewState["CurrentPage"] = value;
}
}
private int PurchasesCount
{
get { return (this.ViewState["PurchasesCount"] != null ?
int.Parse(this.ViewState["PurchasesCount"].ToString()) : GetPurchasesCount());
}
set { this.ViewState["PurchasesCount"] = value; }
}
private int GetPurchasesCount()
{
int myPCount = Purchases.GetPurchasesCount();
PurchasesCount = myPCount;
return myPCount;
}
public void BindList()
{
PagedDataSource objPds = new PagedDataSource();
// Set paging info
objPds.AllowPaging = true;
objPds.AllowCustomPaging = true;
objPds.VirtualCount = PurchasesCount;
objPds.CurrentPageIndex = CurrentPage;
// ddlistPageSize is the dropdown list on our page for choosing page size
switch (ddlistPageSize.SelectedValue)
{
case "":
objPds.PageSize = 5;
break;
case "*":
objPds.AllowPaging = false;
break;
default:
objPds.PageSize = int.Parse(ddlistPageSize.SelectedValue);
break;
}
//
if (objPds.AllowPaging)
objPds.DataSource =
Purchases. GetPurchases((((CurrentPage + 1) * objPds.PageSize) - objPds.PageSize), ((CurrentPage + 1) * objPds.PageSize));
else
objPds.DataSource = Purchases.GetAllPurchases;
//
myGridView.DataSource = objPds;
myGridView.DataBind();
}
Love coding!
Posts
No comments:
Post a Comment