C # Generic paging class, supported based on Typed Dataset

This class implements a combination of paging conditions, but only one complete method for passing parameters can be reloaded by yourself.

This class is related to data access and can be used with paging components (see other articles for these two classes)

The stored procedure is attached to the following

Using system;
Using system. Data;
Using system. collections;
Using hkh. database;
Using hkh. database. type;

Namespace hkh. Common
{
/// <Summary>
/// Retrieve data by PAGE
/// </Summary>
Public class clspagination
{
Idbdataadapter adapter = NULL;
Idbcommand cmd = NULL;
Public clspagination ()
{
Adapter = dbfactory. getdaobuilder (). getquerydataadapter ();
Cmd = Adapter. selectcommand;
}

# Region sorting method by lwt 2006-03-30

# Region organization query Conditions

/// <Summary>
/// Organization query Conditions
/// </Summary>
/// <Param name = "myds"> dataset that can contain multiple tables </param>
/// <Param name = "key"> Search Keyword </param>
/// <Returns> </returns>
Public String getcondition (string key, dataset myds)
{
Return getcondition (myds, key, 0 );
}

/// <Summary>
/// Organization query Conditions
/// </Summary>
/// <Param name = "myds"> dataset that can contain multiple tables </param>
/// <Param name = "key"> Search Keyword </param>
/// <Param name = "userid"> search by reader domain </param>
/// <Returns> </returns>
Public String getcondition (Dataset myds, string key, int userid)
{
String condition = "";
String tempcondition = "";
For (INT I = 0; I <myds. Tables. Count; I ++)
{
String currtablename = ""; // current table name or Alias
// Alias available when the SQL string is too long
If (myds. tables [I]. extendedproperties. count> 0 & myds. tables [I]. extendedproperties. containskey ("alias") & myds. tables [I]. extendedproperties ["alias"]. tostring (). trim ()! = "")
{
Currtablename = myds. Tables [I]. extendedproperties ["alias"]. tostring (). Trim ();
}
Else
{
Currtablename = myds. Tables [I]. tablename;
}

For (Int J = 0; j <myds. Tables [I]. Columns. Count; j ++)
{
If (myds. tables [I]. columns [J]. extendedproperties. count> 0 & myds. tables [I]. columns [J]. extendedproperties. containskey ("query") & myds. tables [I]. columns [J]. extendedproperties ["query"]. tostring () = "1 ")
{
If (myds. Tables [I]. Columns [J]. datatype = system. type. GetType ("system. datetime "))
{
If (clscheckvalid. checkdate (key ))
{
Condition = condition + currtablename + "." + myds. Tables [I]. Columns [J]. columnname + "= '" + key + "' or ";
}
}
Else
{
Condition = condition + currtablename + "." + myds. Tables [I]. Columns [J]. columnname + "like '%" + key + "%' or ";
}
}
}
}

If ("" = condition)
{
Condition = "(1 = 1)"; // All are displayed by default.
}
Else
{
Condition + = "(1 = 0 )";
}

If (""! = Tempcondition)
{
Condition = "(" + tempcondition + ") and (" + condition + ")";
}
Return condition;
}

# Endregion

# Region General paging Method

/// <Summary>
/// General paging Method
/// </Summary>
/// <Param name = "myds"> dataset used by organization conditions </param>
/// <Param name = "displaycolumns"> field display list </param>
/// <Param name = "searchkey"> Search Keyword </param>
/// <Param name = "sortcolumn"> sort columns </param>
/// <Param name = "joincolumn"> external join column </param>
/// <Param name = "jointable"> external join table </param>
/// <Param name = "userid"> User ID </param>
/// <Param name = "condition"> Custom conditions </param>
/// <Param name = "pagesize"> Number of lines per page </param>
/// <Param name = "linknumber"> current page index </param>
/// <Param name = "totalamount"> total number of rows </param>
/// <Returns> </returns>
Public dataset getpagedata (Dataset myds, arraylist displaycolumns, string searchkey, string sortcolumn, string joincolumn, string jointable, int userid, string condition, int pagesize, int linknumber, ref int totalamount)
{
// Clear the user's temporary table
Cleartemptab (userid );

String currtablename = ""; // master table name or Alias

If (myds. tables [0]. extendedproperties. count> 0 & myds. tables [0]. extendedproperties. containskey ("alias") & myds. tables [0]. extendedproperties ["alias"]. tostring (). trim ()! = "")
{
Currtablename = myds. Tables [0]. extendedproperties ["alias"]. tostring (). Trim ();
}
Else
{
Currtablename = myds. Tables [0]. tablename;
}

String mycondition = getcondition (myds, searchkey, userid );

If (condition! = NULL & condition. Trim ()! = "")
{
Mycondition = "(" + mycondition + ") and (" + condition + ")";
}

Int startrow = pagesize * (linkNumber-1 );
Int endrow = pagesize * linkNumber-1;

Datacolumn [] primarykeys = myds. Tables [0]. primarykey;

String strcolumns = "";
If (displaycolumns! = NULL & displaycolumns. Count> 0)
{
For (INT I = 0; I <displaycolumns. Count; I ++)
{
If (displaycolumns [I]. tostring (). Trim ()! = "")
{
Strcolumns + = "," + currtablename + "." + displaycolumns [I]. tostring (). Trim ();
}
}
}
Else
{
Strcolumns + = "," + currtablename + ".*";
}

String strcreate = "select distinct (" + currtablename + ". "+ primarykeys [0]. columnname + ") as onlyone" + strcolumns + joincolumn + "into # temptab" + userid. tostring () + "from" + myds. tables [0]. tablename;

// Whether an alias exists
If (myds. Tables [0]. tablename. compareto (currtablename )! = 0)
{
Strcreate + = "as" + currtablename;
}

Strcreate + = jointable + "where" + mycondition;

If (sortcolumn! = NULL & sortcolumn. Trim ()! = "")
{
Strcreate + = "order by" + sortcolumn + ";";
}
Else
{
Strcreate + = ";";
}

String strdrop = "Drop table # temptab" + userid. tostring () + ";";
String strsql = "";

// ------ Create the table alias
Arraylist tables = new arraylist ();
For (INT I = 0; I <primarykeys. Length + 2; I ++)
{
Tables. Add ("T" + I. tostring ());
}
Strsql = "select * from (select Top 100 percent ";
// ---- Construct the serial number
Arraylist sqls = new arraylist ();
For (INT I = 0; I <primarykeys. length; I ++)
{
# Region: Condition for splicing a sequence

Int temp = I;
String strcondition = "";

While (temp! = 0)
{
Temp = temp-1;
Strcondition = strcondition + tables [I] + ". "+ primarykeys [temp]. columnname + "=" + tables [tables. count-2] + ". "+ primarykeys [temp]. columnname + "and ";
}

Strcondition = strcondition + tables [I] + ". "+ primarykeys [I]. columnname + "<" + tables [tables. count-2] + ". "+ primarykeys [I]. columnname + "and ";

Strcondition = strcondition + "(1 = 1 )";

# Endregion

If (I = primarykeys. Length-1)
{
Sqls. add ("(select count (*) from # temptab" + userid. tostring () + "as" + tables [I] + "where" + strcondition + ")");
}
Else
{
Sqls. add ("(select count (*) from # temptab" + userid. tostring () + "as" + tables [I] + "where" + strcondition + ") + ");
}

}
For (INT I = 0; I <sqls. Count; I ++)
{
Strsql = strsql + sqls [I];
}

Strsql = strsql + "as rowindex, * from # temptab" + userid. tostring () + "as" + tables [tables. count-2] + "order by rowindex)" + "as" +
Tables [tables. count-1] + "where" + tables [tables. count-1] + ". "+" rowindex between "+ startrow. tostring () + "and" + endrow. tostring ();

Strsql + = "select count (*) from # temptab" + userid. tostring () + ";";

Dataset DS = new dataset ();

Procparameter [] parameters = new procparameter [4];
Parameters [0] = new procparameter ("@ sqlstring", strsql, procparametertype. nvarchar, 4000 );
Parameters [1] = new procparameter ("@ subsqlstring", strcreate + "~ "+ Strdrop, procparametertype. nvarchar, 4000 );
Parameters [2] = new procparameter ("@ pagenum", linknumber, procparametertype. Int, 4 );
Parameters [3] = new procparameter ("@ showcount", pagesize, procparametertype. Int, 4 );

Idbcommand proccmd = dbfactory. getdaobuilder (). getsproccommand ("sp_execsql_2", parameters );

Adapter. selectcommand = proccmd;

Adapter. Fill (DS );

Totalamount = Ds. Tables [1]. Rows. Count> 0? (Convert. isdbnull (Ds. Tables [1]. Rows [0] [0])? 0: Convert. toint32 (Ds. Tables [1]. Rows [0] [0]): 0;

DS. Tables [0]. Columns. Remove ("rowindex ");
DS. Tables [0]. Columns. Remove ("onlyone ");

Return Ds;
}
 
# Endregion

# Region REPORT query method (mainly used for paging)

/// <Summary>
/// Report page
/// </Summary>
/// <Param name = "reportsql"> SQL statement for querying report data </param>
/// <Param name = "displaycolumns"> columns to be displayed </param>
/// <Param name = "userid"> current user name </param>
/// <Param name = "pagesize"> Number of lines per page </param>
/// <Param name = "linknumber"> current page index </param>
/// <Param name = "totalamount"> total number of rows </param>
/// <Returns> </returns>
Public dataset getdataforreport (string reportsql, arraylist displaycolumns, int userid, int pagesize, int linknumber, ref int totalamount)
{
// Clear the user's temporary table
Cleartemptab (userid );

Int startrow = pagesize * (linkNumber-1 );
Int endrow = pagesize * linkNumber-1;

// -------- Define the search range
String strcreate = "create table # temptab" + userid. tostring () + "(PID int identity primary key ";
For (INT I = 0; I <displaycolumns. Count; I ++)
{
Strcreate + = "," + displaycolumns [I];
}
Strcreate + = "); insert into # temptab" + userid. tostring () + "(";
For (INT I = 0; I <displaycolumns. Count; I ++)
{
String keyi = displaycolumns [I]. tostring ();
Strcreate + = keyi. substring (0, keyi. indexof ("") + ",";
}
Strcreate = strcreate. substring (0, strcreate. Length-1 );
Strcreate + = ")" + reportsql;
String strdrop = "Drop table # temptab" + userid. tostring ();
String strrange = "# temptab" + userid. tostring ();
// ------ Create the table alias
Arraylist tables = new arraylist ();
For (INT I = 0; I <3; I ++)
{
Tables. Add ("T" + I. tostring ());
}
String strsql = "select * from (select Top 100 percent ";
// ---- Construct the serial number
Arraylist sqls = new arraylist ();
# Region: Condition for splicing a sequence

String strcondition = "";
Strcondition = strcondition + tables [0] + ". PID <" + tables [1] + ". PID and ";
Strcondition = strcondition + "(1 = 1 )";
Sqls. Add ("(select count (*) from" + strrange + "as" + tables [0] + "where" + strcondition + ")");

# Endregion
For (INT I = 0; I <sqls. Count; I ++)
{
Strsql = strsql + sqls [I];
}

Strsql = strsql + "as rowindex, * from" + strrange + "as" + tables [tables. Count-2] + "order by rowindex)" + "as" +
Tables [tables. count-1] + "where" + tables [tables. count-1] + ". "+" rowindex between "+ startrow. tostring () + "and" + endrow. tostring () + ";";

Strsql + = "select count (*) from # temptab" + userid. tostring () + ";";

Dataset DS = new dataset ();

Cmd. commandtext = strcreate + "" + strsql + "" + strdrop;

Adapter. Fill (DS );

Totalamount = Ds. Tables [1]. Rows. Count> 0? (Convert. isdbnull (Ds. Tables [1]. Rows [0] [0])? 0: Convert. toint32 (Ds. Tables [1]. Rows [0] [0]): 0;

DS. Tables [0]. Columns. Remove ("rowindex ");

Return Ds;
}

# Endregion

# Region clears the temporary table. Once an exception occurs during execution, the temporary table is resident in the tempdb database.

Private void cleartemptab (INT userid)
{
Try
{
Procparameter [] parameters = new procparameter [1];
Parameters [0] = new procparameter ("@ temptabname", "# temptab" + userid. tostring () + ";", procparametertype. varchar, 200 );

Idbcommand proccmd = dbfactory. getdaobuilder (). getsproccommand ("cleartemptab", parameters );

Proccmd. Connection. open ();
Proccmd. executenonquery ();
Proccmd. Connection. Close ();
Proccmd. Dispose ();
}
Catch (exception ex)
{
Throw ex;
}
}

# Endregion

# Endregion
}
}