First, define the stored procedure in the database, and easily implement pagination of millions of data records: // @ pagesize: page size, pageindex: page number, @ pagecount: total page number, @ recordcount: create procedure getcustomdatapage @ pagesize int, @ pageindex int, @ pagecount int output, @ recordcount int output asdeclare @ SQL varchar (1000) Select @ recordcount = count (*) from productsset @ pagecount = ceiling (@ recordcount * 1.0/@ pagesize) If @ pageindex = 0 or @ pagecount <= 1 Set @ SQL = 'select top '+ STR (@ pagesize) + 'productid, productname, unitprice from products order by productid ASC 'else if @ pageindex = @ pagecount-1 Set @ SQL = 'select * from (select top '+ STR (@ recordcount-@ pagesize * @ pageindex) + 'productid, productname, unitprice from products order by productid DESC) temptable order by productid ASC ′
Else set @ SQL = 'select top '+ STR (@ pagesize) +' * from (select top '+ STR (@ recordcount-@ pagesize * @ pageindex) +' productid, productname, unitprice from products order by productid DESC) temptable order by productid ASC ′
Exec (@ SQL) Go is ready and the stored procedure is created. How can I use it in. Net? See the following code: Private uint pagecount; // the total number of pages.
Private uint recordcount; // total number of records
Private dataset getpagedata (uint pagesize, uint pageindex)
{
String strconn = system. configuration. configurationsettings. etettings ["connectionstring"];
Sqlconnection conn = new sqlconnection (strconn );
Conn. open ();
Sqlcommand command = new sqlcommand ("getcustomdatapage", Conn); // The first parameter is the name of the stored procedure.
Command. commandtype = commandtype. storedprocedure; // declare that the command type is stored procedure
Command. Parameters. Add ("@ pagesize", sqldbtype. INT );
Command. Parameters ["@ pagesize"]. value = pagesize;
Command. Parameters. Add ("@ pageindex", sqldbtype. INT );
Command. Parameters ["@ pageindex"]. value = pageindex;
Command. Parameters. Add ("@ pagecount", sqldbtype. INT );
Command. Parameters ["@ pagecount"]. value = pagecount;
Command. Parameters ["@ pagecount"]. Direction = parameterdirection. output; // output parameter in the Stored Procedure
Command. Parameters. Add ("@ recordcount", sqldbtype. INT );
Command. Parameters ["@ recordcount"]. value = recordcount;
Command. Parameters ["@ recordcount"]. Direction = parameterdirection. output; // output parameters in the Stored Procedure
Sqldataadapter adapter = new sqldataadapter (command );
Dataset DS = new dataset ();
Adapter. Fill (DS );
// Obtain the output parameter value
Pagecount = convert. touint32 (command. Parameters ["@ pagecount"]. value );
Recordcount = convert. touint32 (command. Parameters ["@ recordcount"]. value );
Conn. Close ();
Return Ds;
}
// Bind data to the DataGrid
Private void binddatagrid ()
{
Dataset DS = getpagedata (uint) dgproduct. pagesize, (uint) dgproduct. currentpageindex );
Dgproduct. virtualitemcount = (INT) recordcount;
Dgproduct. datasource = Ds;
Dgproduct. databind ();
}
// The DataGrid is bound when the page is loaded.
Private void page_load (Object sender, system. eventargs E)
{
If (! Page. ispostback)
{
Binddatagrid ();
}
}
// Process the user's flip current events
Private void dgproduct_pageindexchanged (Object source, system. Web. UI. webcontrols. datagridpagechangedeventargs E)
{
Dgproduct. currentpageindex = E. newpageindex;
Binddatagrid ();
}
// Click Edit button to process the current event
Private void dgproduct_editcommand (Object source, system. Web. UI. webcontrols. datagridcommandeventargs E)
{
Dgproduct. edititemindex = E. Item. itemindex;
Binddatagrid ();
}
// The user clicks cancel button to process the current event
Private void dgproduct_cancelcommand (Object source, system. Web. UI. webcontrols. datagridcommandeventargs E)
{
Dgproduct. edititemindex =-1;
Binddatagrid ();
}
// The user clicks Update button to process the current event
Private void dgproduct_updatecommand (Object source, system. Web. UI. webcontrols. datagridcommandeventargs E)
{
String strconn = system. configuration. configurationsettings. etettings ["connectionstring"];
Sqlconnection conn = new sqlconnection (strconn );
Conn. open ();
// String strsql = "update from products set productname = @ productname, set unitprice = @ unitprice where productid = @ productid ";
String strsql = "update products set productname = @ productname where productid = @ productid ";
Sqlcommand command = new sqlcommand (strsql, Conn );
Command. Parameters. Add ("@ productname", sqldbtype. nvarchar, 40 );
Command. Parameters ["@ productname"]. value = (textbox) (E. Item. cells [1]. controls [0]). Text. Trim ();
// Command. Parameters. Add ("@ unitprice", sqldbtype. INT );
// Command. parameters ["@ unitprice"]. value = convert. toint32 (textbox) (E. item. cells [2]. controls [0]). text. trim ());
Command. Parameters. Add ("@ productid", sqldbtype. INT );
Command. Parameters ["@ productid"]. value = dgproduct. datakeys [E. Item. itemindex];
Command. executenonquery ();
Conn. Close ();
Dgproduct. edititemindex =-1;
Binddatagrid ();
}
// The user clicks delete button to process the current event
Private void dgproduct_deletecommand (Object source, system. Web. UI. webcontrols. datagridcommandeventargs E)
{
String strconn = system. configuration. configurationsettings. etettings ["connectionstring"];
Sqlconnection conn = new sqlconnection (strconn );
Conn. open ();
Sqlcommand command = new sqlcommand ("deleteproduct", Conn );
Command. commandtype = commandtype. storedprocedure;
Command. Parameters. Add ("@ productid", sqldbtype. INT );
Command. Parameters ["@ productid"]. value = dgproduct. datakeys [E. Item. itemindex];
Command. executenonquery ();
Binddatagrid ();
}
// Implement deletion confirmation and alternate Color Display
Private void dgproduct_itemdatabound (Object sender, system. Web. UI. webcontrols. datagriditemeventargs E)
{
If (E. Item. itemtype = listitemtype. Item | E. Item. itemtype = listitemtype. alternatingitem)
{
Button btndelete = (button) (E. Item. cells [4]. controls [0]);
Btndelete. Attributes. Add ("onclick", "javascript: Return confirm ('Are you sure you want to delete? ')");
E. Item. Attributes. Add ("onmouseover", "This. style. backgroundcolor = '# ffcc66 '");
E. Item. Attributes. Add ("onmouseout", "This. style. backgroundcolor = '# ffff '");
}
}
