. Net massive paging data storage process

Some time ago, I made a massive data storage process and made a test version Program . Hope to help you. There are a lot of such stored procedures on the Internet, but after trying them in sequence, such paging is better, and there are few test programs on the Internet. Even if the stored procedure is found, the calling process is extremely troublesome.
------------------------------------
-- Supports any sorting of paging stored procedures
------------------------------------

Code:

Create procedure pagination
@ Tblname varchar (255), -- table name
@ Strgetfields varchar (1000) = '*', -- the column to be returned
@ Fldname varchar (255) = '', -- Name of the sorted Field
@ Pagesize int, -- page size
@ Pageindex int, -- page number
@ Docount bit, -- total number of records returned. If the value is not 0
@ Ordertype bit, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ Strwhere varchar (1500) = ''-- Query condition (Note: Do not add where)
As
Declare @ strsql varchar (5000) -- subject sentence
Declare @ strtmp varchar (110) -- Temporary Variable
Declare @ strorder varchar (400) -- sort type

If @ docount! = 0
Begin
If @ strwhere! =''
Set @ strsql = "select count (*) as total from [" + @ tblname + "] Where" + @ strwhere
Else
Set @ strsql = "select count (*) as total from [" + @ tblname + "]"
End
-- AboveCodeIt means that if @ docount is not passed over 0, the total number of statistics will be executed. All the code below is 0 @ docount
Else
Begin

If @ ordertype! = 0
Begin
Set @ strtmp = "<(select Min"
Set @ strorder = "order by [" + @ fldname + "] DESC"
-- If @ ordertype is not 0, execute the descending order. This sentence is very important!
End
Else
Begin
Set @ strtmp = "> (select Max"
Set @ strorder = "order by [" + @ fldname + "] ASC"
End

If @ pageindex = 1
begin
If @ strwhere! = ''
set @ strsql =" select top "+ STR (@ pagesize) + "" + @ strgetfields + "from [" + @ tblname + "] Where" + @ strwhere + "" + @ strorder
else
set @ strsql =" select top "+ STR (@ pagesize) + "" + @ strgetfields + "from [" + @ tblname + "]" + @ strorder
-- execute the above Code on the first page, this will accelerate the execution speed
end
else
begin
-- the following code gives @ strsql the SQL code to be actually executed
set @ strsql = "select top" + STR (@ pagesize) + "" + @ strgetfields + "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) + "" + @ strgetfields + "from ["
+ @ Tblname + "] Where [" + @ fldname + "]" + @ strtmp + "(["
+ @ Fldname + "]) from (select top" + STR (@ PageIndex-1) * @ pagesize) + "["
+ @ Fldname + "] from [" + @ tblname + "] Where" + @ strwhere + ""
+ @ Strorder + ") as tbltmp) and" + @ strwhere + "" + @ strorder
End
End
Exec (@ strsql)
The program called by go (for the sake of universality, I have written a method. You can give comments or modify it, exchange comments and make progress together)
Private Static dataset getcustomersdata (string tblname, string strgetfields, string fldname, int pagesize, int pageindex, int docount, int ordertype, string strwhere)
{
String connstring = configurationsettings. receivettings ["connstr"];
Sqlconnection conn = new sqlconnection (connstring );
Sqlcommand comm = new sqlcommand ("pagination3", Conn );

Comm. Parameters. Add (New sqlparameter ("@ tblname", sqldbtype. varchar); // table name
Comm. Parameters [0]. value = tblname;
Comm. Parameters. Add (New sqlparameter ("@ strgetfields", sqldbtype. varchar); // The returned Column
Comm. Parameters [1]. value = strgetfields;
Comm. Parameters. Add (New sqlparameter ("@ fldname", sqldbtype. varchar); // name of the sorted Field
Comm. Parameters [2]. value = fldname;
Comm. Parameters. Add (New sqlparameter ("@ pagesize", sqldbtype. INT); // page size
Comm. Parameters [3]. value = pagesize;
Comm. Parameters. Add (New sqlparameter ("@ pageindex", sqldbtype. INT); // page number
Comm. Parameters [4]. value = pageindex;
Comm. Parameters. Add (New sqlparameter ("@ docount", sqldbtype. INT); // whether to return the total number of records. If the value is 0, no is returned. If the value is 1, no is returned.
Comm. Parameters [5]. value = docount;
Comm. Parameters. Add (New sqlparameter ("@ ordertype", sqldbtype. INT); // you can specify the sorting type. If the value is 0, the sorting type is ascending. If the value is not 0, the sorting type is descending.
Comm. Parameters [6]. value = ordertype;
Comm. Parameters. Add (New sqlparameter ("@ strwhere", sqldbtype. varchar); // Where statement
Comm. Parameters [7]. value = strwhere;
Comm. commandtype = commandtype. storedprocedure;
Sqldataadapter dataadapter = new sqldataadapter (Comm );
Dataset DS = new dataset ();
Dataadapter. Fill (DS );
Return Ds;
}