1.1 Paging

/// <Summary>
/// Execute the custom page to return <see CREF = "system. Data. dataset"> system. Data. dataset </See>
/// </Summary>
/// <Param name = "stable_name"> name of the table to be queried </param>
/// <Param name = "ssign_record"> uniquely identified field </param>
/// <Param name = "sfilter_condition"> query condition </param>
/// <Param name = "sfind_recordlist"> List of fields to be queried. Empty characters are used to query all fields. </param>
/// <Param name = "ipage_size"> size of each page </param>
/// <Param name = "ipage_index"> current page number </param>
/// <Param name = "itaxis_sign"> sorting mark 0 forward Order 1 reverse order </param>
/// <Param name = "ipagecount"> total number of pages. Output Parameters </param>
/// <Param name = "iirecord_count"> total number of records. Output Parameters </param>
/// <Returns> <see CREF = "system. Data. dataset"> system. Data. dataset </See> </returns> is returned successfully.
Dataset executecustompage (string stable_name, string ssign_record, string delimiter, string delimiter, int ipage_size, int ipage_index, string staxisfield, int itaxis_sign, out int ipagecount, out int iirecord_count );
Public dataset executecustompage (string stable_name, string ssign_record, string sfilter_condition, string condition, int ipage_size, int ipage_index, int itaxis_sign, out int ipagecount, out int iirecord_count)
{
Try
{
Int irecord_count = 0;
Iirecord_count = 0;
String ssql = "";
If (sfilter_condition = "")
{
Ssql = "select count (*) as RC from" + stable_name;
}
Else
{
Ssql = "select count (*) as RC from" + stable_name + "where" + sfilter_condition;
}
Datatable dt = This. executedataset (ssql). Tables [0];
If (Dt = NULL)
{
Ipagecount = 0;
Return NULL;
}
If (Dt. Rows. Count <= 0)
{
Ipagecount = 0;
Return NULL;
}
Irecord_count = (INT) dt. Rows [0] ["RC"];
Iirecord_count = irecord_count;
If (irecord_count % ipage_size = 0)
{
Ipagecount = irecord_count/ipage_size;
}
Else
{
Ipagecount = irecord_count/ipage_size + 1;
}
Arraylist Params = new arraylist ();
Params. Add (New sqlparameter ("@ table_name", stable_name ));
Params. Add (New sqlparameter ("@ sign_record", ssign_record ));
Params. Add (New sqlparameter ("@ filter_condition", sfilter_condition ));
Params. Add (New sqlparameter ("@ page_size", ipage_size ));
Params. Add (New sqlparameter ("@ page_index", ipage_index ));
Params. Add (New sqlparameter ("@ taxis_sign", itaxis_sign ));
Params. Add (New sqlparameter ("@ record_count", irecord_count ));
Params. Add (New sqlparameter ("@ find_recordlist", sfind_recordlist ));
Sqlparameter paramsreturnvl = new sqlparameter ("return_value", sqldbtype. INT );
Paramsreturnvl. Direction = parameterdirection. returnvalue;
Params. Add (paramsreturnvl );
Return this. executedataset ("proccustompage", Params );
}
Catch (system. Exception ex)
{
Ipagecount = 0;
Iirecord_count = 0;
Throw new exception (ex. Message );
}

}

/// <Summary>
/// Execute the custom page to return <see CREF = "system. Data. dataset"> system. Data. dataset </See>
/// </Summary>
/// <Param name = "stable_name"> name of the table to be queried </param>
/// <Param name = "ssign_record"> uniquely identified field </param>
/// <Param name = "sfilter_condition"> query condition </param>
/// <Param name = "sfind_recordlist"> List of fields to be queried. Empty characters are used to query all fields. </param>
/// <Param name = "ipage_size"> size of each page </param>
/// <Param name = "ipage_index"> current page number </param>
/// <Param name = "itaxis_sign"> sorting mark 0 forward Order 1 reverse order </param>
/// <Param name = "ipagecount"> total number of pages. Output Parameters </param>
/// <Param name = "iirecord_count"> total number of records. Output Parameters </param>
/// <Returns> <see CREF = "system. Data. dataset"> system. Data. dataset </See> </returns> is returned successfully.

Public datatable getlines (string condition, string tablename, string records, int pagesize, int pageindex, out int pagecount, out int recordsum)
{
Datatable dt = new datatable ();
Dt = datahelper. executecustompage (tablename, "ID", condition, records, pagesize, pageindex, 1, out pagecount, out recordsum). Tables [0];
Return DT;
}

Create procedure proccustompage
(
@ Table_name varchar (1000),/* Name of the queried table */
@ Sign_record varchar (50),/* flag field */
@ Filter_condition varchar (1000),/* filter condition */
@ Page_size int,/* Number of records per page */
@ Page_index int,/* page number */
@ Taxis_sign int,/* sorting mark 0: Forward 1: reverse */
@ Find_recordlist varchar (1000),/* query fields, used between fields, split null to query all */
@ Record_count int/* Total number of records */
)
As
Begin
Declare @ start_number int
Declare @ end_number int
Declare @ topn_number int
Declare @ ssql varchar (5000)
If (@ find_recordlist = '')
Begin
Select @ find_recordlist = '*'
End
Select @ start_number = (@ Page_Index-1) * @ page_size
If @ start_number <= 0
Select @ start_number = 0
Select @ end_number = @ start_number + @ page_size
If @ end_number> @ record_count
Select @ end_number = @ record_count
Select @ topn_number = @ end_number-@ start_number
If @ topn_number <= 0
Select @ topn_number = 0
Print @ topn_number
Print @ start_number
Print @ end_number
Print @ record_count
If @ taxis_sign = 0
Begin
If @ filter_condition =''
Begin
Select @ ssql = 'select' + @ find_recordlist + 'from' + @ table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ topn_number as varchar (10) + ''+ @ sign_record + 'from' + @ table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ end_number as varchar (10) + ''+ @ sign_record + 'from' + @ table_name +'
Order by '+ @ sign_record +') order by '+ @ sign_record + 'desc'
End
Else
Begin
Select @ ssql = 'select' + @ find_recordlist + 'from' + @ table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ topn_number as varchar (10) + ''+ @ sign_record + 'from' + @ table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ end_number as varchar (10) + ''+ @ sign_record + 'from' + @ table_name +'
Where '+ @ filter_condition + 'ORDER BY' + @ sign_record +') and '+ @ filter_condition + 'ORDER BY' + @ sign_record + 'desc) and '+ @ filter_condition + 'ORDER BY' + @ sign_record + 'desc'
End
End
Else
Begin
If @ filter_condition =''
Begin
Select @ ssql = 'select' + @ find_recordlist + 'from' + @ table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ topn_number as varchar (10) + ''+ @ sign_record + 'from' + @ table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ end_number as varchar (10) + ''+ @ sign_record + 'from' + @ table_name +'
Order by '+ @ sign_record + 'desc') order by' + @ sign_record + ') order by' + @ sign_record + 'desc'
End
Else
Begin
Select @ ssql = 'select' + @ find_recordlist + 'from' + @ table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ topn_number as varchar (10) + ''+ @ sign_record + 'from' + @ table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ end_number as varchar (10) + ''+ @ sign_record + 'from' + @ table_name +'
Where '+ @ filter_condition + 'ORDER BY' + @ sign_record + 'desc) and' + @ filter_condition + 'ORDER BY' + @ sign_record + ') and '+ @ filter_condition + 'ORDER BY' + @ sign_record + 'desc'
End
End
Exec (@ ssql)
If @ error <> 0
Return-3/* query record Error */
Return 0
End

Go

/// <Summary>
/// Execute the custom page to return <see CREF = "system. Data. dataset"> system. Data. dataset </See>
/// </Summary>
/// <Param name = "stable_name"> name of the table to be queried </param>
/// <Param name = "ssign_record"> uniquely identified field </param>
/// <Param name = "sfilter_condition"> query condition </param>
/// <Param name = "sfind_recordlist"> List of fields to be queried. Empty characters are used to query all fields. </param>
/// <Param name = "ipage_size"> size of each page </param>
/// <Param name = "ipage_index"> current page number </param>
/// <Param name = "staxisfield"> sort fields </param>
/// <Param name = "itaxis_sign"> sorting mark 0 forward Order 1 reverse order </param>
/// <Param name = "ipagecount"> total number of pages. Output Parameters </param>
/// <Param name = "iirecord_count"> total number of records. Output Parameters </param>
/// <Returns> <see CREF = "system. Data. dataset"> system. Data. dataset </See> </returns> is returned successfully.
Public dataset executecustompage (string stable_name, string ssign_record, string delimiter, string delimiter, int ipage_size, int ipage_index, string staxisfield, int itaxis_sign, out int ipagecount, out int iirecord_count)
{
Try
{
Int irecord_count = 0;
Iirecord_count = 0;
String ssql = "";
If (sfilter_condition = "")
{
Ssql = "select count (*) as RC from" + stable_name;
}
Else
{
Ssql = "select count (*) as RC from" + stable_name + "where" + sfilter_condition;
}
Datatable dt = This. executedataset (ssql). Tables [0];
If (Dt = NULL)
{
Ipagecount = 0;
Return NULL;
}
If (Dt. Rows. Count <= 0)
{
Ipagecount = 0;
Return NULL;
}
Irecord_count = (INT) dt. Rows [0] ["RC"];
Iirecord_count = irecord_count;
If (irecord_count % ipage_size = 0)
{
Ipagecount = irecord_count/ipage_size;
}
Else
{
Ipagecount = irecord_count/ipage_size + 1;
}
Arraylist Params = new arraylist ();
Params. Add (New sqlparameter ("@ table_name", stable_name ));
Params. Add (New sqlparameter ("@ sign_record", ssign_record ));
Params. Add (New sqlparameter ("@ filter_condition", sfilter_condition ));
Params. Add (New sqlparameter ("@ page_size", ipage_size ));
Params. Add (New sqlparameter ("@ page_index", ipage_index ));
Params. Add (New sqlparameter ("@ taxisfield", staxisfield ));
Params. Add (New sqlparameter ("@ taxis_sign", itaxis_sign ));
Params. Add (New sqlparameter ("@ record_count", irecord_count ));
Params. Add (New sqlparameter ("@ find_recordlist", sfind_recordlist ));
Sqlparameter paramsreturnvl = new sqlparameter ("return_value", sqldbtype. INT );
Paramsreturnvl. Direction = parameterdirection. returnvalue;
Params. Add (paramsreturnvl );
Return this. executedataset ("proccustomtaxispage", Params );
}
Catch (system. Exception ex)
{
Ipagecount = 0;
Iirecord_count = 0;
Throw new exception (ex. Message );
}
}

Dataset executecustompage (string stable_name, string ssign_record, string delimiter, string delimiter, int ipage_size, int ipage_index, string staxisfield, int itaxis_sign, out int ipagecount, out int iirecord_count );
Create procedure proccustomtaxispage
(
@ Table_name varchar (5000),/* Name of the queried table */
@ Sign_record varchar (50),/* flag field */
@ Filter_condition varchar (1000),/* filter condition */
@ Page_size int,/* Number of records per page */
@ Page_index int,/* page number */
@ Taxisfield varchar (1000 ),
@ Taxis_sign int,/* sorting mark 0: Forward 1: reverse */
@ Find_recordlist varchar (1000),/* query fields, used between fields, split null to query all */
@ Record_count int/* Total number of records */
)
As
Begin
Declare @ start_number int
Declare @ end_number int
Declare @ topn_number int
Declare @ ssql varchar (8000)
If (@ find_recordlist = '')
Begin
Select @ find_recordlist = '*'
End
Select @ start_number = (@ Page_Index-1) * @ page_size
If @ start_number <= 0
Select @ start_number = 0
Select @ end_number = @ start_number + @ page_size
If @ end_number> @ record_count
Select @ end_number = @ record_count
Select @ topn_number = @ end_number-@ start_number
If @ topn_number <= 0
Select @ topn_number = 0
Print @ topn_number
Print @ start_number
Print @ end_number
Print @ record_count
If @ taxisfield =''
Begin
Select @ taxisfield = @ sign_record
End
If @ taxis_sign = 0
Begin
If @ filter_condition =''
Begin
Select @ ssql = 'select' + @ find_recordlist + 'from' + @ table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ topn_number as varchar (10) +'

'+ @ Sign_record + 'from' + @ table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ end_number as varchar (10) +'

'+ @ Sign_record + 'from' + @ table_name +'
Order by '+ @ taxisfield +') order by '+ @ taxisfield + 'desc) order by' + @ taxisfield
End
Else
Begin
Select @ ssql = 'select' + @ find_recordlist + 'from' + @ table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ topn_number as varchar (10) + ''+ @ sign_record +' from

'+ @ Table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ end_number as varchar (10) + ''+ @ sign_record +' from

'+ @ Table_name +'
Where '+ @ filter_condition + 'ORDER BY' + @ taxisfield +') and '+ @ filter_condition + 'ORDER BY' + @ taxisfield +'

DESC) and '+ @ filter_condition + 'ORDER BY' + @ taxisfield
End
End
Else
Begin
If @ filter_condition =''
Begin
Select @ ssql = 'select' + @ find_recordlist + 'from' + @ table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ topn_number as varchar (10) + ''+ @ sign_record +' from

'+ @ Table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ end_number as varchar (10) + ''+ @ sign_record +' from

'+ @ Table_name +'
Order by '+ @ taxisfield + 'desc') order by' + @ taxisfield + ') order by' + @ taxisfield + 'desc'
End
Else
Begin
Select @ ssql = 'select' + @ find_recordlist + 'from' + @ table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ topn_number as varchar (10) + ''+ @ sign_record +' from

'+ @ Table_name +'
Where '+ @ sign_record +' in (select top '+ Cast (@ end_number as varchar (10) + ''+ @ sign_record +' from

'+ @ Table_name +'
Where '+ @ filter_condition + 'ORDER BY' + @ taxisfield + 'desc) and' + @ filter_condition + 'order

'+ @ Taxisfield +') and '+ @ filter_condition + 'ORDER BY' + @ taxisfield + 'desc'
End
End
Exec (@ ssql)
If @ error <> 0
Return-3/* query record Error */
Return 0
End
Go