Custom pages of ASP. NET

Using System;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. ComponentModel;
Using System. Data;
Using System. Data. SqlClient;
Namespace ControlLibs
{
/// <Summary>
/// Summary of CustomPager.
/// </Summary>
[DefaultProperty ("Text "),
ToolboxData ("<{0}: SqlPager runat = server> </{0}: SqlPager>")]
Public class SqlPager: System. Web. UI. WebControls. WebControl, System. Web. UI. INamingContainer
{
# Automatic region generation
Private string text;
 
[Bindable (true ),
Category ("Appearance "),
DefaultValue ("")]
Public string Text
{
Get
{
Return text;
}

Set
{
Text = value;
}
}

/// <Summary>
/// Present the control to the specified output parameter.
/// </Summary>
/// <Param name = "output"> HTML writer to be written </param>
Protected override void Render (HtmlTextWriter output)
{
If (Site! = Null & Site. DesignMode)
CreateChildControls ();

Base. Render (output );
}
# Endregion

# Region Constructor
Public SqlPager (): base ()
{
This. CurrentPageIndex = 0;
This. PageSize = 10;
This. PageCount =-1;
This. RecordCount =-1;
This. SortField = "";
This. ConnString = "";
This. ControlToPaginate = "";
This. PagingMode = PageMode. Cached;
This. CacheDuration = 60;
This. SelectCommand = "";

}
# Endregion

# Region overload method
Public override void DataBind ()
{
Base. DataBind ();
// Step 1: redraw the control **************
This. ChildControlsCreated = false;
// Step 2: whether the cooperator control exists and meets the conditions (ListControl, BaseDataList), selectcmd, connstr
If (this. ControlToPaginate = "")
Return;
This. m_ControlToPaginate = this. Page. FindControl (this. ControlToPaginate );
If (this. m_ControlToPaginate = null)
Return;
If (! (This. m_ControlToPaginate is ListControl | this. m_ControlToPaginate is BaseDataList ))
Return;

If (this. SelectCommand = "" | this. ConnString = "")
Return;

// Step 3: retrieve data
If (this. PagingMode = PageMode. Cached)
GetAllData ();
Else
GetPageData ();

// Step 4: bind data
ListControl listControl = null;
BaseDataList baseDataList = null;
If (this. m_ControlToPaginate is ListControl)
{
ListControl = (ListControl) this. m_ControlToPaginate;
ListControl. Items. Clear ();
ListControl. DataSource = this. m_DataSource;
ListControl. DataBind ();
Return;
}
If (this. m_ControlToPaginate is BaseDataList)
{
BaseDataList = (BaseDataList) this. m_ControlToPaginate;
BaseDataList. DataSource = this. m_DataSource;
BaseDataList. DataBind ();

Return;
}

}

Protected override void CreateChildControls ()
{
// TODO: Add CustomPager. CreateChildControls to implement
// Base. CreateChildControls ();
This. Controls. Clear ();
This. ClearChildViewState ();
InitControls ();
}
# Endregion

# Region event processing
Private void lb_Navigater_Click (object sender, EventArgs e)
{

String str = (LinkButton) sender). ID;
Switch (str)
{
Case "first ":
{
This. CurrentPageIndex = 0;
Break;
}
Case "prev ":
{
This. CurrentPageIndex --;
Break;
}
Case "next ":
{
This. CurrentPageIndex ++;
Break;
}
Case "last ":
{
This. CurrentPageIndex = this. PageCount-1;
Break;
}
}
This. DataBind ();
}
# Endregion

# Region paging information
Public class PageInfo
{
Public int pageCount;
Public int recordCountInLast;
Public int recordCount;

}
# Endregion

# Region Enumeration
Public enum PageMode
{
Cached,
NoCached
}
# Endregion

# Region private variable
Private string m_QueryPageCommandText = "SELECT * FROM" +
"(Select top {0} * FROM" +
"(Select top {1} * FROM ({2}) AS t0 order by {3} {4}) AS t1" +
"Order by {3} {5}) AS t2" +
"Order by {3 }";
Private string m_RecordCountSql = "select count (*) from ({0}) as t0 ";
Private System. Web. UI. WebControls. PagedDataSource m_DataSource;
Private System. Web. UI. Control m_ControlToPaginate;
Private string m_CacheKeyName
{
Get
{
Return this. Page. Request. FilePath + "_" + this. UniqueID + "_ Data ";
}
}
# Endregion

# Region attributes

Public int CacheDuration
{
Get
{
Return int. Parse (this. ViewState ["CacheDuration"]. ToString ());
}
Set
{
This. ViewState ["CacheDuration"] = int. Parse (value. ToString ());
}
}
/// <Summary>
/// Sort field name
/// </Summary>
Public string SortField
{
Get
{
Return this. ViewState ["SortField"]. ToString ();
}
Set
{
This. ViewState ["SortField"] = value. ToString ();
}
}
Public string SelectCommand
{
Get
{
Return this. ViewState ["SelectCommand"]. ToString ();
}
Set
{
This. ViewState ["SelectCommand"] = value. ToString ();
}
}

Public string ConnString
{
Get
{
Return this. ViewState ["ConnString"]. ToString ();
}
Set
{
This. ViewState ["ConnString"] = value. ToString ();
}
}
/// <Summary>
/// Paging Mode
/// </Summary>
Public PageMode PagingMode
{
Get
{
Return (PageMode) this. ViewState ["PagingMode"];
}
Set
{
This. ViewState ["PagingMode"] = (PageMode) value;
}
}
/// <Summary>
/// Cooperator Control name
/// </Summary>
Public string ControlToPaginate
{
Get
{
Return this. ViewState ["ControlToPaginate"]. ToString ();
}
Set
{
This. ViewState ["ControlToPaginate"] = value. ToString ();
}
}
Public int RecordCount
{
Get
{
Return int. Parse (this. ViewState ["RecordCount"]. ToString ());
}
Set
{
This. ViewState ["RecordCount"] = int. Parse (value. ToString ());

}
}

Public int PageSize
{
Get
{
Return int. Parse (this. ViewState ["PageSize"]. ToString ());
}
Set
{
This. ViewState ["PageSize"] = int. Parse (value. ToString ());

}
}
Public int CurrentPageIndex
{
Get
{
Return int. Parse (this. ViewState ["CurrentPageIndex"]. ToString ());
}
Set
{
This. ViewState ["CurrentPageIndex"] = int. Parse (value. ToString ());

}
}
Public int PageCount
{
Get
{
Return int. Parse (this. ViewState ["PageCount"]. ToString ());
}
Set
{
This. ViewState ["PageCount"] = int. Parse (value. ToString ());

}
}

# Endregion

# Region Method
Private void InitControls ()
{
Table tbl = new Table ();
TableRow row = new TableRow ();
Tbl. Rows. Add (row );

TableCell PrevNextCell = new TableCell ();
InitPrevNextInfo (PrevNextCell );
Row. Cells. Add (PrevNextCell );

TableCell PageInfoCell = new TableCell ();
InitPageInfo (PageInfoCell );
Row. Cells. Add (PageInfoCell );

This. Controls. Add (tbl );

}

Private void GetAllData ()
{
// Retrieve data from the cache
DataTable tbl;
Tbl = (DataTable) this. Page. Cache [this. m_CacheKeyName];
If (tbl = null)
{

Tbl = new DataTable ();
This. AdjustSelectCommand (true );
SqlConnection conn = new SqlConnection (this. ConnString );
SqlCommand cmd = conn. CreateCommand ();
Cmd. CommandText = this. SelectCommand;
SqlDataAdapter da = new SqlDataAdapter ();
Da. SelectCommand = cmd;
Da. Fill (tbl );
This. RecordCount = tbl. Rows. Count;
This. Page. Cache. Insert (this. m_CacheKeyName, tbl, null, System. DateTime. Now. AddSeconds (this. CacheDuration)
, System. Web. Caching. Cache. NoSlidingExpiration );

}

If (this. m_DataSource = null)
{
This. m_DataSource = new PagedDataSource ();
This. m_performance.datasource = tbl. DefaultView;
This. m_performance.allowpaging = true;
This. m_performance.pagesize = this. PageSize;
This. PageCount = this. m_performance.pagecount;
// This. RecordCount = this. m_performance.virtualcount;

This. ValidPageIndex ();
If (this. CurrentPageIndex =-1)
{
This. m_DataSource = null;
Return;
}

This. m_performance.currentpageindex = this. CurrentPageIndex;
}

}

Private void ValidPageIndex ()
{
If (! (This. CurrentPageIndex> = 0 & this. CurrentPageIndex <= this. PageCount-1 ))
This. CurrentPageIndex =-1;

}

Private void AdjustSelectCommand (bool isAddSortInfo)
{
String strTemp = this. SelectCommand. ToLower ();
Int pos = strTemp. IndexOf ("order ");
If (pos>-1)
{
This. SelectCommand = this. SelectCommand. Substring (0, pos );
}

If (isAddSortInfo & this. SortField! = "")
{
This. SelectCommand = "order by" + this. SortField;
}

}

Private PageInfo CalcPageInfo ()
{
PageInfo pInfo = new PageInfo ();

// Remove the Sort Field
This. AdjustSelectCommand (false );

// Obtain the number of records
SqlConnection conn = new SqlConnection (this. ConnString );
SqlCommand cmd = conn. CreateCommand ();

Cmd. CommandText = string. Format (this. m_RecordCountSql, this. SelectCommand );
Conn. Open ();
Int recordCount = (int) cmd. ExecuteScalar ();
Conn. Close ();
PInfo. recordCount = recordCount;
If (recordCount % this. PageSize> 0)
{
PInfo. pageCount = recordCount/this. PageSize + 1;
PInfo. recordCountInLast = pInfo. recordCount % this. PageSize;

}
Else
{
PInfo. pageCount = recordCount/this. PageSize;
PInfo. recordCountInLast = 0;
}

This. PageCount = pInfo. pageCount;
This. RecordCount = pInfo. recordCount;

Return pInfo;

}

Private void GetPageData ()
{
PageInfo p = this. CalcPageInfo ();
This. ValidPageIndex ();
If (this. CurrentPageIndex =-1)
{
Return;
}

SqlCommand cmd = this. PrepareCommand (p );
If (cmd = null)
Return;
SqlDataAdapter da = new SqlDataAdapter (cmd );
DataTable tbl = new DataTable ();
Da. Fill (tbl );

// Configures the paged data source component
If (this. m_DataSource = null)
This. m_DataSource = new PagedDataSource ();
// This. m_cece.allowcustompaging = true;
// This. m_performance.allowpaging = true;
// This. m_performance.currentpageindex = 0;
// If (p. recordCountInLast! = 0)
// This. m_performance.currentpageindex = p. pageCount-1;
// This. m_performance.pagesize = this. PageSize;
// This. m_performance.virtualcount = p. recordCount;
This. m_performance.datasource = tbl. DefaultView;

}

Private SqlCommand PrepareCommand (PageInfo p)
{
If (SortField = "")
{
// Get metadata for all columns and choose either the primary key
// Or
String text = "set fmtonly on;" + SelectCommand + "; set fmtonly off ;";
SqlDataAdapter da = new SqlDataAdapter (text, this. ConnString );
DataTable tbl = new DataTable ();
Da. MissingSchemaAction = MissingSchemaAction. AddWithKey;
Da. Fill (tbl );
DataColumn col = null;
If (tbl. PrimaryKey. Length> 0)
Col = tbl. PrimaryKey [0];
Else
Col = tbl. Columns [0];
SortField = col. ColumnName;
}
// Determines how many records are to be retrieved.
// The last page cocould require less than other pages
Int recsToRetrieve = this. PageSize;
If (CurrentPageIndex = p. pageCount-1)
RecsToRetrieve = p. recordCountInLast;

String plain text = String. Format (m_QueryPageCommandText,
RecsToRetrieve, // {0} --> page size
This. PageSize * (CurrentPageIndex + 1), // {1} --> size * index
This. SelectCommand, // {2} --> base query
This. SortField, // {3} --> key field in the query
"ASC", // Default to ascending order
"DESC ");

SqlConnection conn = new SqlConnection (this. ConnString );
SqlCommand cmd = new SqlCommand (plain text, conn );
Return cmd;
}

 

/// <Summary>
/// Initialize the top and bottom pages
/// </Summary>
/// <Param name = "cell"> </param>
Private void InitPrevNextInfo (TableCell cell)
{
Bool isValidPage = this. CurrentPageIndex> = 0 & this. CurrentPageIndex <= this. PageCount-1;
Bool canMovePrev = this. CurrentPageIndex> 0;
Bool canMoveNext = this. CurrentPageIndex <this. PageCount-1;

LinkButton lb_First = new LinkButton ();
Lb_First.ID = "first ";
Lb_First.Font.Name = "Webdings ";
Lb_First.Font.Size = FontUnit. Medium;
Lb_First.ForeColor = this. ForeColor;
Lb_First.ToolTip = "Homepage ";
Lb_First.Text = "7 ";
Lb_First.Click + = new EventHandler (lb_Navigater_Click );
Lb_First.Enabled = isValidPage & canMovePrev;

Cell. Controls. Add (lb_First );
Cell. Controls. Add (new LiteralControl ("& nbsp ;"));

LinkButton lb_Prev = new LinkButton ();
Lb_Prev.ID = "prev ";
Lb_Prev.Font.Name = "Webdings ";
Lb_Prev.Font.Size = FontUnit. Medium;
Lb_Prev.ForeColor = this. ForeColor;
Lb_Prev.ToolTip = "Previous Page ";
Lb_Prev.Text = "3 ";
Lb_Prev.Click + = new EventHandler (lb_Navigater_Click );
Lb_Prev.Enabled = isValidPage & canMovePrev;

Cell. Controls. Add (lb_Prev );
Cell. Controls. Add (new LiteralControl ("& nbsp ;"));

LinkButton lb_Next = new LinkButton ();
Lb_Next.ID = "next ";
Lb_Next.Font.Name = "Webdings ";
Lb_Next.Font.Size = FontUnit. Medium;
Lb_Next.ForeColor = this. ForeColor;
Lb_Next.ToolTip = "next page ";
Lb_Next.Text = "4 ";
Lb_Next.Click + = new EventHandler (lb_Navigater_Click );
Lb_Next.Enabled = isValidPage & canMoveNext;

Cell. Controls. Add (lb_Next );
Cell. Controls. Add (new LiteralControl ("& nbsp ;"));

LinkButton lb_Last = new LinkButton ();
Lb_Last.ID = "last ";
Lb_Last.Font.Name = "Webdings ";
Lb_Last.Font.Size = FontUnit. Medium;
Lb_Last.ForeColor = this. ForeColor;
Lb_Last.ToolTip = "last page ";
Lb_Last.Text = "8 ";
Lb_Last.Click + = new EventHandler (lb_Navigater_Click );
Lb_Last.Enabled = isValidPage & canMoveNext;

Cell. Controls. Add (lb_Last );
Cell. Controls. Add (new LiteralControl ("& nbsp ;"));

}

/// <Summary>
/// Initialize the page information
/// </Summary>
/// <Param name = "cell"> </param>
Private void InitPageInfo (TableCell cell)
{
Int pageIndex = this. CurrentPageIndex;
PageIndex ++;
String str = string. Format ("Page times: {0}/{1} {2} content/Page total {3} content ",
PageIndex. ToString (), this. PageCount. ToString (), this. PageSize. ToString (), this. RecordCount. ToString ());
Cell. Text = str;

}
# Endregion

}
}