Basic paging and calling of a stored procedure

1 Stored Procedure:
Create procedure c_pf_getrecordfrompage
@ Tblname varchar (255), -- table name
@ Fldname varchar (255), -- field name
@ Pagesize Int = 10, -- page size
@ Pageindex Int = 1, -- page number
@ Iscount bit = 0, -- returns the total number of records. If the value is not 0, the system returns
@ Ordertype bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ Strwhere varchar (1000) = ''-- Query condition (Note: Do not add where)
As

Declare @ strsql varchar (2000) -- subject sentence
Declare @ strtmp varchar (1000) -- Temporary Variable
Declare @ strorder varchar (1000) -- sort type

If @ ordertype! = 0
Begin
Set @ strtmp = '<(select min'
Set @ strorder = 'ORDER BY' + @ fldname + 'desc'
End
Else
Begin
Set @ strtmp = '> (select Max'
Set @ strorder = 'ORDER BY' + @ fldname + 'asc'
End

Set @ strsql = 'select top '+ STR (@ pagesize) +' * from'
+ @ Tblname + 'where' + @ fldname + ''+ @ strtmp + '('
+ @ Fldname + ') from (select top' + STR (@ PageIndex-1) * @ pagesize) +''
+ @ Fldname + 'from' + @ tblname + ''+ @ strorder + ') as tbltmp )'
+ @ Strorder

If @ strwhere! =''
Set @ strsql = 'select top '+ STR (@ pagesize) +' * from'
+ @ Tblname + 'where' + @ fldname + ''+ @ strtmp + '('
+ @ Fldname + ') from (select top' + STR (@ PageIndex-1) * @ pagesize) +''
+ @ Fldname + 'from' + @ tblname + 'where ('+ @ strwhere + ')'
+ @ Strorder + ') as tbltmp) and (' + @ strwhere + ')' + @ strorder

If @ pageindex = 1
Begin
Set @ strtmp =''
If @ strwhere! =''
Set @ strtmp = 'where ('+ @ strwhere + ')'

Set @ strsql = 'select top '+ STR (@ pagesize) +' * from'
+ @ Tblname + ''+ @ strtmp +'' + @ strorder
End

If @ iscount! = 0
Set @ strsql = 'select count (*) as total from '+ @ tblname + 'where (' + @ strwhere + ')'
Exec (@ strsql)

Go

2 User ControlCode:
Front-end:
[% @ Control Language = "C #" autoeventwireup = "false" codebehind = "pagecontrol. ascx. cs" inherits = "clubmannage. modules. pagecontrol" targetschema = "http://schemas.microsoft.com/intellisense/ie5" %]
& Nbsp; [/font]
[Table id = "Table1" cellspacing = "1" cellpadding = "1" width = "100%" border = "0"]
[Tr]
[Td]
[Asp: Label id = "lb_pagemes" runat = "server"] label [/ASP: Label] [/td]
[TD align = "right"]
[Asp: button id = "btn_f" runat = "server" cssclass = "button1" text = "Homepage"] [/ASP: button]
[Asp: button id = "btn_p" runat = "server" cssclass = "button1" text = ""] [/ASP: button] & nbsp;
Go to [Asp: textbox id = "tb_pageindex" runat = "server" cssclass = "txtinput" width = "40px"] [/ASP: textbox]
[Asp: button id = "btn_go" runat = "server" cssclass = "button1" text = ""] [/ASP: button] & nbsp;
[Asp: button id = "btn_n" runat = "server" cssclass = "button1" text = "Next"] [/ASP: button]
[Asp: button id = "btn_l" runat = "server" cssclass = "button1" text = ""] [/ASP: button] [/td]
[/Tr]
[/Table]

Background:
Namespace clubmannage. Modules
{
Using system;
Using system. Data;
Using system. drawing;
Using system. Web;
Using system. Web. UI. webcontrols;
Using system. Web. UI. htmlcontrols;
Using system. Data. sqlclient;

/// <Summary>
/// Summary of pagecontrol.
/// </Summary>
Public class pagecontrol: system. Web. UI. usercontrol
{
Protected system. Web. UI. webcontrols. Label lb_pagemes;
Protected system. Web. UI. webcontrols. textbox tb_pageindex;
Protected system. Web. UI. webcontrols. Button btn_f;
Protected system. Web. UI. webcontrols. Button btn_p;
Protected system. Web. UI. webcontrols. Button btn_go;
Protected system. Web. UI. webcontrols. Button btn_n;
Protected system. Web. UI. webcontrols. Button btn_l;

Protected static string strsql;
Protected static string strconn = system. configuration. configurationsettings. deleettings ["strconnection"];

Private int _ pagesize; // pagesize
Public int pagesize
{
Get
{
Return _ pagesize;
}
Set
{
_ Pagesize = value;
}
}

private int _ pageindex; // _ pageindex
Public int pageindex
{< br> Get
{< br> return _ pageindex;
}< br> set
{< br> _ pageindex = value;
}< BR >}

private int _ pagecount;
private int _ recordcount;
private string _ pf_name; // name of the stored procedure
Public String pf_name
{< br> Get
{< br> return _ pf_name;
}< br> set
{< br> _ pf_name = value;
}< BR >}

private string _ tablename; // table name
Public String tablename
{< br> Get
{< br> return _ tablename;
}< br> set
{< br> _ tablename = value;
}< BR >}< br> private string _ strwhere; // condition, without where
Public String strwhere
{< br> Get
{< br> return _ strwhere;
}< br> set
{< BR >_strwhere = value;
}< BR >}

Private string _ orderfieldname; // name of the sorted Field
Public String orderfieldname
{
Get
{
Return _ orderfieldname;
}
Set
{
_ Orderfieldname = value;
}
}

Private int _ ordertype;
// Sorting method
Public int ordertype
{
Get
{
Return _ ordertype;
}
Set
{
_ Ordertype = value;
}
}

Private DataGrid _ bindcontrol; // bind the control
Public DataGrid bindcontrol
{
Get
{
Return this. _ bindcontrol;
}
Set
{
This. _ bindcontrol = value;
}
}

Private void page_load (Object sender, system. eventargs E)
{
If (! Page. ispostback)
{
Binderdg ();
}

}

# Code generated by region web Form Designer
Override protected void oninit (eventargs E)
{
//
// Codegen: This call is required by the ASP. NET web form designer.
//
Initializecomponent ();
Base. oninit (E );
}

/// <Summary>
/// The designer supports the required methods-do not use the code editor
/// Modify the content of this method.
/// </Summary>
Private void initializecomponent ()
{
This. btn_f.click + = new system. eventhandler (this. btn_f_click );
This. btn_p.click + = new system. eventhandler (this. btn_p_click );
This. btn_go.click + = new system. eventhandler (this. btn_go_click );
This. btn_n.click + = new system. eventhandler (this. btn_n_click );
This. btn_l.click + = new system. eventhandler (this. btn_l_click );
This. Load + = new system. eventhandler (this. page_load );

}
# Endregion
Public void rebinderdatasource ()
{
Binderdg ();
}

Protected static dataset executesql4pf (string commtext, string [] paramsarr, object [] valuesarr)
{
Sqlconnection mycn = new sqlconnection (strconn );
Sqlcommand mycmd = new sqlcommand ();
Mycmd. Connection = mycn;
Mycmd. commandtext = commtext;
Mycmd. commandtype = commandtype. storedprocedure;
If (paramsarr! = NULL)
{
For (INT I = 0; I <paramsarr. length; I ++)
Mycmd. Parameters. Add (paramsarr [I], valuesarr [I]);
}
Try
{
Mycn. open ();
Sqldataadapter SDA = new sqldataadapter (mycmd );
Dataset DS = new dataset ("ds ");
SDA. Fill (DS );
Return Ds;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
Mycn. Close ();
}
}
Public void search (string wherestring)
{
Viewstate ["_ strwhere"] = wherestring;
Viewstate ["_ pageindex"] = "1 ";
Binderdg ();
}
// Obtain the number of records
Private int getrecordcount ()
{
String [] sqlparameterarray = new string [7];
String [] sqlparametervaluesarray = new string [7];

Sqlparameterarray [0] = "@ tblname ";
Sqlparametervaluesarray [0] = viewstate ["_ tablename"]. tostring ();

Sqlparameterarray [1] = "@ fldname ";
Sqlparametervaluesarray [1] = viewstate ["_ orderfieldname"]. tostring ();

Sqlparameterarray [2] = "@ pagesize ";
Sqlparametervaluesarray [2] = viewstate ["_ pagesize"]. tostring ();

Sqlparameterarray [3] = "@ pageindex ";
Sqlparametervaluesarray [3] = viewstate ["_ pageindex"]. tostring ();

Sqlparameterarray [4] = "@ iscount"; // The number of records returned by 1
Sqlparametervaluesarray [4] = "1 ";

Sqlparameterarray [5] = "@ ordertype ";
Sqlparametervaluesarray [5] = "1 ";

Sqlparameterarray [6] = "@ strwhere ";
Sqlparametervaluesarray [6] = viewstate ["_ strwhere"]. tostring ();
_ Recordcount = int. parse (executesql4pf (viewstate ["_ pf_name"]. tostring (), sqlparameterarray, sqlparametervaluesarray ). tables [0]. rows [0] [0]. tostring ());
Viewstate ["_ recordcount"] = _ recordcount. tostring ();
Return _ recordcount;
}
// Obtain the page number
Private int getpagecount ()
{
_ Recordcount = getrecordcount ();
Int ipagesize = int. parse (viewstate ["_ pagesize"]. tostring ());

If (_ recordcount % ipagesize = 0)
{
_ Pagecount = _ recordcount/ipagesize;

}
Else
{
_ Pagecount = _ recordcount/ipagesize + 1;
}
Viewstate ["_ pagecount"] = _ pagecount. tostring ();
Return _ pagecount;
}
// Public void resetpagecount (INT npagecount)
//{
// _ Recordcount = getrecordcount ();
// Int ipagesize = npagecount;
//
// If (_ recordcount % ipagesize = 0)
//{
// _ Pagecount = _ recordcount/ipagesize;
//
//}
// Else
//{
// _ Pagecount = _ recordcount/ipagesize + 1;
//}
// Viewstate ["_ pagecount"] = _ pagecount. tostring ();
//}
Private void binderdg ()
{
String [] sqlparameterarray = new string [7];
String [] sqlparametervaluesarray = new string [7];

sqlparameterarray [0] = "@ tblname";
If (object. equals (viewstate ["_ tablename"], null)
{< br> sqlparametervaluesarray [0] = _ tablename. tostring ();
viewstate ["_ tablename"] = _ tablename. tostring ();
}< br> else
{< br> sqlparametervaluesarray [0] = viewstate ["_ tablename"]. tostring ();
}

sqlparameterarray [1] = "@ fldname";
If (object. equals (viewstate ["_ orderfieldname"], null)
{< br> sqlparametervaluesarray [1] = _ orderfieldname. tostring ();
viewstate ["_ orderfieldname"] = _ orderfieldname. tostring ();
}< br> else
{< br> sqlparametervaluesarray [1] = viewstate ["_ orderfieldname"]. tostring ();
}

sqlparameterarray [2] = "@ pagesize";
If (object. equals (viewstate ["_ pagesize"], null)
{< br> sqlparametervaluesarray [2] = _ pagesize. tostring ();
viewstate ["_ pagesize"] = _ pagesize. tostring ();
}< br> else
{< br> sqlparametervaluesarray [2] = viewstate ["_ pagesize"]. tostring ();
}

Sqlparameterarray [3] = "@ pageindex ";
If (object. Equals (viewstate ["_ pageindex"], null ))
{
Sqlparametervaluesarray [3] = _ pageindex. tostring ();
Viewstate ["_ pageindex"] = _ pageindex. tostring ();
}
Else
{
Sqlparametervaluesarray [3] = viewstate ["_ pageindex"]. tostring ();
}

Sqlparameterarray [4] = "@ iscount ";
Sqlparametervaluesarray [4] = "0 ";

sqlparameterarray [5] = "@ ordertype";
If (object. equals (viewstate ["_ ordertype"], null)
{< br> sqlparametervaluesarray [5] = _ ordertype. tostring ();
viewstate ["_ ordertype"] = _ ordertype. tostring ();
}< br> else
{< br> sqlparametervaluesarray [5] = viewstate ["_ ordertype"]. tostring ();
}

Sqlparameterarray [6] = "@ strwhere ";
If (object. Equals (viewstate ["_ strwhere"], null ))
{
Sqlparametervaluesarray [6] = _ strwhere. tostring ();
Viewstate ["_ strwhere"] = _ strwhere. tostring ();
}
Else
{
Sqlparametervaluesarray [6] = viewstate ["_ strwhere"]. tostring ();
}
If (object. Equals (viewstate ["_ pf_name"], null ))
{
Viewstate ["_ pf_name"] = _ pf_name;
}

_ Bindcontrol. datasource = executesql4pf (viewstate ["_ pf_name"]. tostring (), sqlparameterarray, sqlparametervaluesarray );
_ Bindcontrol. databind ();

Showpagemes ();

btn_p.enabled = true;
btn_f.enabled = true;
btn_n.enabled = true;
btn_l.enabled = true;
If (viewstate ["_ pageindex"]. tostring () = "1") {btn_p.enabled = false; btn_f.enabled = false;}
If (viewstate ["_ pageindex"]. tostring () = viewstate ["_ pagecount"]. tostring () {btn_n.enabled = false; btn_l.enabled = false; }< BR >}< br> private void showpagemes ()
{< br> getpagecount ();
string MEs = "Page Times " + viewstate ["_ pageindex"]. tostring () + " /" + viewstate ["_ pagecount"]. tostring () + " " + viewstate ["_ pagesize"]. tostring () + " Number of records " + viewstate ["_ recordcount"]. tostring () + "";
lb_pagemes.text = MES;
}

Private void btn_f_click (Object sender, system. eventargs E)
{
_ Pageindex = 1;
Viewstate ["_ pageindex"] = "1 ";
Binderdg ();
}

Private void btn_p_click (Object sender, system. eventargs E)
{
Int ipageindex = int. parse (viewstate ["_ pageindex"]. tostring ());
If (ipageindex> 1)
{
Ipageindex --;
_ Pageindex = ipageindex;
Viewstate ["_ pageindex"] = _ pageindex;
Binderdg ();
}
}

Private void btn_n_click (Object sender, system. eventargs E)
{

Int ipageindex = int. parse (viewstate ["_ pageindex"]. tostring ());
Int ipagecount = int. parse (viewstate ["_ pagecount"]. tostring ());
If (ipageindex <ipagecount)
{
Ipageindex ++;
_ Pageindex = ipageindex;
Viewstate ["_ pageindex"] = _ pageindex;
Binderdg ();
}
}

Private void btn_l_click (Object sender, system. eventargs E)
{
Int ipagecount = int. parse (viewstate ["_ pagecount"]. tostring ());
_ Pageindex = ipagecount;
Viewstate ["_ pageindex"] = ipagecount. tostring ();
Binderdg ();
}

Private void btn_go_click (Object sender, system. eventargs E)
{
Int ipageindex = int. parse (viewstate ["_ pageindex"]. tostring ());
Int ipagecount = int. parse (viewstate ["_ pagecount"]. tostring ());
Int gotopage = 1;
Try
{
Gotopage = convert. toint32 (tb_pageindex.text.trim ());
}
Catch
{
Gotopage = 1;
}
If (gotopage> = 1 & gotopage <= ipagecount)
{
_ Pageindex = gotopage;
Viewstate ["_ pageindex"] = _ pageindex. tostring ();
Binderdg ();
}
If (gotopage <1)
{
_ Pageindex = 1;
Viewstate ["_ pageindex"] = _ pageindex. tostring ();
Binderdg ();
}
If (gotopage> ipagecount)
{
_ Pageindex = ipagecount;
Viewstate ["_ pageindex"] = _ pageindex. tostring ();
Binderdg ();
}
}

}
}

Application:
[Uc1: pagecontrol id = "pagecontrol1" runat = "server"] [/uc1: pagecontrol]
[Asp: button id = "button1" runat = "server" text = "button"] [/ASP: button]
[Asp: DataGrid id = "datagrid1" runat = "server"] [/ASP: DataGrid]

Public class webform1: system. Web. UI. Page
{
Protected system. Web. UI. webcontrols. DataGrid datagrid1;
Protected system. Web. UI. webcontrols. Button button1;
Protected clubmannage. modules. pagecontrol pagecontrol1;

private void page_load (Object sender, system. eventargs e)
{< br>
pagecontrol1.bindcontrol = datagrid1;
If (! Page. ispostback)
{< br> initpagecontrol ();
}< br>
}

# code generated by region web forms designer
override protected void oninit (eventargs e)
{< br> //
// codegen: this call is ASP.. NET web form designer.
//
initializecomponent ();
base. oninit (E );
}< br>
///

// The method required by the designer. Do not use the code editor to modify the method.
/// the content of this method.
//
private void initializecomponent ()
{< br> This. button1.click + = new system. eventhandler (this. button#click);
This. load + = new system. eventhandler (this. page_load);

}
# Endregion
Private void initpagecontrol ()
{

Pagecontrol1.tablename = "tab_aritclerecommend ";
Pagecontrol1.orderfieldname = "r_id ";
Pagecontrol1.pagesize = 25;
Pagecontrol1.ordertype = 1;
Pagecontrol1.pageindex = 1;
Pagecontrol1.pf _ name = "c_pf_getrecordfrompage ";
Pagecontrol1.strwhere = "1 = 1 ";

}