asp.net data paging controls (without stored procedures)

ASP tutorials. NET Data paging controls (without stored procedures)

The principle of this page control

Many methods of paging, useful stored procedures, there is no stored procedures, there are in C # with DataGridView virtual mode, the purpose of only one, the large amount of data processing, so that the user experience has been improved, software speed has been upgraded. This page control is mainly used by the following SQL statement, I believe you can understand, stored procedure paging is also in the same SQL code:

1/*tablename: Table name: Tbtestdata


2 sqlwherestatement:sql where expression such as: where expression is empty


3 PrimaryKey: Primary key such as: UniqueID


4 pagesize: Paging size such as: 50


5 pageindex: Current page such as: 8


6 OrderField: Sort fields such as: Insetdatatime


7 */


8


9 Select top * from Tbtestdata


Ten where UniqueID not in


11 (


Select Top ($ * 8) UniqueID from Tbtestdata ORDER BY insetdatatime Desc


13)


ORDER BY insetdatatime Desc

The principle is so simple.

Page-Control Code

(i), instance database tutorial code

Create an instance database.

CREATE TABLE [Tbtestdata] (
[UniqueID] [bigint] NOT NULL,
[CompanyName] [varchar] (MB) NULL,
[Companycode] [varchar] (m) NULL,
[Address] [varchar] () NULL,
[owner] [varchar] (MB) NULL,
[Memo] [varchar] (a) NULL,
[Insetdatatime] [DateTime] NULL,
constraint [Pk_tbtestdata] primary key clustered
(
[UniqueID] ASC
With (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, Allow_page_locks = O n) on [primary]
) on [primary]

Go

ALTER TABLE [dbo]. [Tbtestdata] Add constraint [df_tbtestdata_insetdatatime] Default (GETDATE ()) for [Insetdatatime]
Go

--Generate instance data
declare @intrownumber int;
Select @intrownumber = 1;
While @intrownumber < 1000000
Begin

Insert into Tbtestdata (Uniqueid,companyname,companycode,address,owner,memo)
VALUES (@intrownumber, ' CompanyName ' + cast (@intrownumber as varchar (2000)),
' Companycode ' + cast (@intrownumber as varchar), ' address ' + cast (@intrownumber as varchar (2000)),
' Owner ' + cast (@intrownumber as varchar), ' memo ' + CAST (@intrownumber as varchar (2000)));
Select @intrownumber = @intrownumber + 1
End
(ii), pagination control code.

Namespace Dotnet.controls


{


&lt;summary&gt;


Paging controls (implemented using code without stored procedures)


Ucpagecontrolbycode


Change the record


///


2010-01-06 Hu Yong changes to a page from the original KeyPress method to KeyDown, let the user press ENTER to confirm the turn page to prevent two consecutive times.


2011-01-06 Hu Yong Add initialization code for a paging control: public DataTable Initializepagecontrol ().


2011-01-05 Hu Yong Create a paging control


2011-04-02 Hu Yong optimizes code, reduces unnecessary private variables, and removes unwanted code


///


&lt;author&gt;


&lt;name&gt; Hu Yong &lt;/name&gt;


&lt;qq&gt;80368704&lt;/qq&gt;


&lt;email&gt;80368704@qq.com&lt;/email&gt;


&lt;/author&gt;


&lt;/summary&gt;


[ToolboxItem (True)]


[DefaultEvent ("oneventpageclicked")]


[ToolboxBitmap (typeof (Ucpagecontrolbycode), "Images.ucpagecontrolbycodeicon.png")]


[Description (using code implementation without stored procedures)]


public partial class Ucpagecontrolbycode:usercontrol


{


#region Private variables


int RecordCount = 0; Number of records


int pagecount = 0; Total pages


int pageindex = 0; Current page


#endregion

#region Custom Events
<summary>
Occurs when you click the page-splitting button (first, previous, next, last, page hop)
</summary>
[Category ("Ucpagecontrolbycode"), description ("Occurs when a paging button is clicked")]
public event EventHandler Oneventpageclicked;
#endregion

#region Custom Properties


private int _pagesize = 50; Paging size


private string _sqlwherestatement = String.Empty; MSSQL WHERE statement


private string _sqlconnstring = String.Empty; MSSQL Database Connection string


private string _tablename = String.Empty; Table name


private string _orderfield = String.Empty; Sort fields for data tables


private string _primarykey = String.Empty; Primary keys for data tables


private string _queryfieldlist = "*"; Field List (defaults to: *)


Private DataTable _pagetable = new DataTable ();





&lt;summary&gt;


Returns the current page number


&lt;/summary&gt;


public int pageindex


{


Get


{


return pageindex + 1;


}


}

&lt;summary&gt;


Gets or sets the paging size (default: 50)


&lt;/summary&gt;


[Browsable (True), category ("Ucpagecontrolbycode"), description ("Get or set a paging size (default: 50)")]


public int pagesize


{


Get


{


return _pagesize;


}


Set


{


_pagesize = value;


}


}





&lt;summary&gt;


Where expression of the SQL statement


&lt;/summary&gt;


[Browsable (False), category ("Ucpagecontrolbycode"), description ("Get or set the where expression of the SQL statement")]


public string Sqlwherestatement


{


Get


{


return _sqlwherestatement;


}


Set


{


_sqlwherestatement = value;


}


}





&lt;summary&gt;


Gets or sets the connection string for SQL Server


&lt;/summary&gt;


[Browsable (False), category ("Ucpagecontrolbycode"), description ("Get or set SQL Server connection string")]


public string sqlconnstring


{


Get


{


return _sqlconnstring;


}


Set


{


_sqlconnstring = value;


}


}

&lt;summary&gt;


The DataTable returned after the user clicks the paging button


&lt;/summary&gt;


[Browsable (False), category ("Ucpagecontrolbycode"), Description ("DataTable returned after user clicks the paging button")]


Public DataTable Pagetable


{


Get


{


return _pagetable;


}


}





&lt;summary&gt;


Sets or gets the name of a table or view that is bound to a paging control


&lt;/summary&gt;


[Browsable (True), category ("Ucpagecontrolbycode"), Description ("Set or get the name of the table or view that is bound to the paging control")]


public string TableName


{


Get


{


return _tablename;


}


Set


{


_tablename = value;


}


}





&lt;summary&gt;


Set or get a paging control sort field


&lt;/summary&gt;


[Browsable (True), category ("Ucpagecontrolbycode"), Description ("Set or get sort fields for paging controls")]


public string OrderField


{


Get


{


return _orderfield;


}


Set


{


_orderfield = value;


}


}





&lt;summary&gt;


Sets or gets the primary key of the paging control binding data table


&lt;/summary&gt;


[Browsable (True), category ("Ucpagecontrolbycode"), Description ("Set or get the primary key of a paging control binding data table")]


public string PrimaryKey


{


Get


{


return _primarykey;


}


Set


{


_primarykey = value;


}


}





&lt;summary&gt;


Sets or gets the list of fields that the paging control binds to (default: *)


&lt;/summary&gt;


[Browsable (True), category ("Ucpagecontrolbycode"), Description ("Set or get a list of fields bound by a paging control (default: *)")]


public string Queryfieldlist


{


Get


{


return _queryfieldlist;


}


Set


{


_queryfieldlist = value;


}


}


#endregion

#region Constructors
<summary>
Paging controls (implemented using code without stored procedures)
</summary>
Public Ucpagecontrolbycode ()
{
InitializeComponent ();
}
#endregion

Implementing related Code #region paging

#region void Setucpagecontrolpars (String connstr, String wherestatement, String tbname, String OrderField, String primary KeyName, String fieldlist): Passing required arguments to Ucpagecontrolbycode control


&lt;summary&gt;


Passing required parameters to the Ucpagecontrolbycode control


&lt;/summary&gt;


&lt;param name= "ConnStr" &gt; Connection string &lt;/param&gt;


&lt;param name= "wherestatement" &gt;mssql where statement &lt;/param&gt;


&lt;param name= "Tbname" &gt; Datasheet or Visual name &lt;/param&gt;


&lt;param name= "OrderField" &gt; Sort fields &lt;/param&gt;


&lt;param name= "Primarykeyname" &gt; Primary key value &lt;/param&gt;


&lt;param name= "fieldlist" &gt; Field list (default: *) &lt;/param&gt;


public void Setucpagecontrolpars (string connstr, String wherestatement, String tbname


, string OrderField, String primarykeyname, String fieldlist)


{


if (String.IsNullOrEmpty (Connstr.trim ()))


{


Dialoghelper.showerrormsg ("Warm tip: N No available database connection!") ");


Return


}


Else


{


this.sqlconnstring = ConnStr;


}


This.sqlwherestatement = wherestatement;


This.tablename = Tbname;


This.orderfield = OrderField;


This.primarykey = Primarykeyname;


if (!string.isnullorempty (Fieldlist.trim ()))


{


This.queryfieldlist = FieldList;


}


}


#endregion

#region DataTable Initializepagecontrol (): Initializing Ucpagecontrolbycode


&lt;summary&gt;


Bind Ucpagecontrolbycode (and return a DataTable containing the current page)


&lt;/summary&gt;


&lt;returns&gt;datatable&lt;/returns&gt;


Public DataTable Bindpagecontrol ()


{


RecordCount = Gettotalrecordcount (); Get the total number of records


PageCount = Recordcount/pagesize-modpage (); Save the total number of pages (minus the Modpage () function to prevent the overflow of query scope when SQL statement execution, you can use the stored procedure paging algorithm to understand this sentence)


pageindex = 0; Save a page index value of 0 to pageindex


Lblpagecount.text = (recordcount/pagesize + overpage ()). ToString ()//shows the status of Lblpagecount, Lblreccount


Lblreccount.text = Recordcount.tostring ();





if (RecordCount &lt;= pagesize)


{


txtgotopage.enabled = false;


}


Else


{


Txtgotopage.enabled = true;


}

return Tdatabind ();
}
#endregion

#region remaining page calculation and total record number
<summary>
Calculate remaining pages
</summary>
<returns></returns>
private int Overpage ()
{
int returnvalue = 0;

if (recordcount% pagesize!= 0)
{
returnvalue = 1;
}

Return returnvalue;
}

       ///<summary>
       / Calculate the remaining pages to prevent overflow of query scope   
       ///</summary> When SQL statement execution
        ///<returns></returns>
         private int modpage ()
        {
             int returnvalue = 0;

            if (recordcount% pagesize = 0 && RecordCount!= 0)
            {
                 returnvalue = 1;
           }
           
             return returnvalue;
       }

       ///<summary>
       / Calculates the total number of records
       ///</summary>
        ///<returns> Records total </returns>
        private int Gettotalrecordcount ()
        {
             int returnvalue     = 0;
            String sqlstatement = "SELECT count (1) as Rowscount from "+ tablename;

if (Sqlwherestatement.trim (). length > 0)
{
SQLStatement = "SELECT count (1) as Rowscount from" + tablename + "where" + sqlwherestatement;
}

SqlDataReader dr = null;


Try


{


Dr = Dbhelpersql.executereader (SQLStatement, sqlconnstring);


if (Dr.read ())


{


returnvalue = Int32.Parse (dr["Rowscount"].tostring ());


}





}


catch (Exception ex)


{


Dialoghelper.showerrormsg (Ex.message);


}


Finally


{


Dr.close ();


Dr.dispose ();


}

Return returnvalue;
}
#endregion

#region DataTable Tdatabind (): Data binding


Private DataTable Tdatabind ()


{


StringBuilder sbsqlstatement = new StringBuilder ();


BOOL Isforward = pageindex + 1 &gt; 1;


BOOL Isbackward = (pageindex!= pagecount);


btnfirstpage.enabled = Isforward;


btnprevpage.enabled = Isforward;


btnnextpage.enabled = Isbackward;


btnlastpage.enabled = Isbackward;

if (String.IsNullOrEmpty (Sqlwherestatement.trim ()))


{


Sbsqlstatement.append ("select top" + pagesize + "" + Queryfieldlist + "from" + tablename + "where" + PrimaryKey + " Not in (select top);


Sbsqlstatement.append (pagesize * pageindex + "" + PrimaryKey + "from" + tablename);


Sbsqlstatement.append ("ORDER BY" + OrderField + "desc" ORDER By "+ OrderField +" desc ");


}


Else


{


Sbsqlstatement.append ("select top" + pagesize + "" + Queryfieldlist + "from" + tablename + "where" + Sqlwherestatem ent + "and" + PrimaryKey + "Not in" (select top);


Sbsqlstatement.append (pagesize * pageindex + "" + PrimaryKey + "from" + tablename + "where" + Sqlwherestatement + " ORDER BY "+ OrderField +" desc) Order BY "+ OrderField +" desc ");


}

            _pagetable           = Dbhelpersql.query (sbsqlstatement.tostring (), sqlconnstring). Tables[0];
            Lblcurrentpage.text = (pageindex + 1). ToString ();
            txtgotopage.text    = ( pageindex + 1). ToString ();
            return _pagetable;
       }
        #endregion

#region Button Event code
private void Btnfirstpage_click (object sender, EventArgs e)
{
pageindex = 0;
_pagetable = Tdatabind ();

if (oneventpageclicked!= null)
{
Oneventpageclicked (this, null);
}
}

private void Btnprevpage_click (object sender, EventArgs e)
{
pageindex--;
_pagetable = Tdatabind ();

if (oneventpageclicked!= null)
{
Oneventpageclicked (this, null);
}
}

private void Btnnextpage_click (object sender, EventArgs e)
{
pageindex++;
_pagetable = Tdatabind ();

if (oneventpageclicked!= null)
{
Oneventpageclicked (this, null);
}
}

private void Btnlastpage_click (object sender, EventArgs e)
{
pageindex = PageCount;
_pagetable = Tdatabind ();

if (oneventpageclicked!= null)
{
Oneventpageclicked (this, null);
}
}

private void Txtgotopage_keydown (object sender, KeyEventArgs e)


{


if (E.keycode = = Keys.enter)


{


Try


{


if (Int32.Parse (Txtgotopage.text) &gt; (recordcount/pagesize + overpage ()) | | int32.parse (TXTGOTOPAGE.TEXT) &lt;= 0)


{


Dialoghelper.showwarningmsg ("page range out of bounds!");


Txtgotopage.clear ();


Txtgotopage.focus ();


}


Else


{


pageindex = Int32.Parse (txtgotopage.text.tostring ())-1;


_pagetable = Tdatabind ();

if (oneventpageclicked!= null)


{


Oneventpageclicked (this, null);


}


}


}


catch (Exception ex)//catch exceptions caused by user input of incorrect data type


{


Dialoghelper.showwarningmsg (Ex.message);


Txtgotopage.clear ();


Txtgotopage.focus ();


}


}


}


#endregion

#endregion
}
}

Use instances for paging controls

The client uses the following code:

View Code


1 using System;


2 using System.Collections.Generic;


3 using System.ComponentModel;


4 using System.Data;


5 using System.Drawing;


6 using System.Configuration;


7 using Dotnet.controls;


8 using System.Text;


9 using System.Windows.Forms;


Using Dotnet.common;


Using Dotnet.winform.utilities;


12


Namespace Dotnet.winform.example


14 {


public partial class Frmucpagecontrolbycodetest:form


16 {


Frmucpagecontrolbycodetest Public ()


18 {


InitializeComponent ();


20}


21st


private void Frmucpagecontrolbycodetest_shown (object sender, EventArgs e)


23 {


24//initialization method one


//ucpagecontrolbycode.sqlconnstring = Configurationsettings.apps Tutorial ettings["DbConnection"];


//ucpagecontrolbycode.sqlwherestatement = "1=1";


//ucpagecontrolbycode.tablename = "Tbtestdata";


//ucpagecontrolbycode.orderfield = "UniqueID";


//ucpagecontrolbycode.primarykey = "UniqueID";


//ucpagecontrolbycode.queryfieldlist = "*";


31


32//initialization method two


Ucpagecontrolbycode.setucpagecontrolpars (configurationsettings.appsettings["DbConnection"), "1=1", "tbtestd ATA ",


"UniqueID", "UniqueID", "*");


DataTable dttest = new DataTable ();


Dttest = Ucpagecontrolbycode.bindpagecontrol ();


Panax Notoginseng ucdatagridview.datasource = dttest;


38


39//Bound query item


dictionary&lt;string, string&gt; diclistqueryitems = new dictionary&lt;string, string&gt; ();


foreach (DataColumn dc in Dttest.columns)


42 {


Diclistqueryitems.add (Dc.columnname, dc.datatype.tostring ());


44}


Uccombinquery1.setqueryitems (Diclistqueryitems);


46}


47


The private void Ucdatagridview_databindingcomplete (object sender, Datagridviewbindingcompleteeventargs e)


49 {


Gbmain.text = "Current total:" + ucdatagridview.rows.count.tostring () + "piece of data." ";


51}


52


The private void ucpagecontrolbycode_oneventpageclicked (object sender, EventArgs e)


54 {


Ucdatagridview.datasource = null;


Ucdatagridview.datasource = ucpagecontrolbycode.pagetable;


57}


58


The private void uccombinquery1_onqueryclicked (object sender, EventArgs e)


60 {


The Try


62 {


Splasher.show (typeof (Frmsplash));


Splasher.status = "Looking for data, please wait a moment ...";


System.threading.thread.sleep (450);


Ucdatagridview.datasource = null;


Ucpagecontrolbycode.sqlwherestatement = uccombinquery1.queryexpression; Specify a query expression


Ucdatagridview.datasource = Ucpagecontrolbycode.bindpagecontrol (); Binding DataGridView


69}


The catch (Exception ex)


71 {


Ucpagecontrolbycode.sqlwherestatement = "1&lt;&gt;1";


Ucdatagridview.datasource = Ucpagecontrolbycode.bindpagecontrol (); Binding DataGridView


Splasher.status = Ex.message;


System.threading.thread.sleep (1000);


76}


and finally


78 {


System.threading.thread.sleep (100);


Splasher.status = "Find finished ...";


Bayi Splasher.close ();


82}


83}


84}


85}