SQL Server Stored Procedures and C # paging-this article is taken from, if any infringement

This article from (Baidu space), if there is infringement, Please mail to xiaoqcn@126.com

Network Address:

Http://hi.baidu.com/followashadow/blog/item/7dd5ce12673ba7876438db77%2Ehtml

The following is the Stored Procedure (passed under sqlserver2000)

-- The most common paging Stored Procedure
-- Get data on a specified page

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 = 10, -- page size

@ Pageindex Int = 1, -- page number

@ Docount bit = 0, -- returns the total number of records. If the value is not 0, the system returns

@ Ordertype bit = 0, -- 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 following codes are
-- The value of @ docount is 0.

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, which will speed up the execution.

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)
Go

Below is the C # code

Using system. Data;
Using system. Data. sqlclient;
Using Microsoft. applicationblocks. Data;
Using system. Web;
Using system. Web. UI;
Namespace rsslayer. pagehelper
{
/** // <Summary>
/// Summary of pagerhelper.
/// </Summary>
Public class pagerhelper
{
Private string connectionstring;

Public pagerhelper (string tblname, string sortname, bool docount, string connectionstring)
{
This. tblname = tblname;
This. fldname = sortname;
This. connectionstring = connectionstring;
This.doc ount = docount;
}

Public pagerhelper (string tblname, bool docount,
String strgetfields, string fldname, int pagesize,
Int pageindex, bool ordertype, string strwhere, string connectionstring
)
{
This. tblname = tblname;
This.doc ount = docount;
This. strgetfields = strgetfields;
This. fldname = fldname;
This. pagesize = pagesize;
This. pageindex = pageindex;
This. ordertype = ordertype;
This. strwhere = strwhere;
This. connectionstring = connectionstring;

}

/** // <Summary>
/// Obtain the record set Constructor
/// </Summary>
/// <Param name = "tblname"> </param>
/// <Param name = "strwhere"> </param>
/// <Param name = "connectionstring"> </param>
Public pagerhelper (string tblname, string strwhere, string connectionstring)
{
This. tblname = tblname;
This. strwhere = strwhere;
This.doc ount = true;
This. connectionstring = connectionstring;
}

Private string tblname;
Public String tblname
{
Get {return tblname ;}
Set {tblname = value ;}
}

Private string strgetfields = "*";
Public String strgetfields
{
Get {return strgetfields ;}
Set {strgetfields = value ;}
}

Private string fldname = string. empty;
Public String fldname
{
Get {return fldname ;}
Set {fldname = value ;}
}

Private int pagesize = 10;
Public int pagesize
{
Get {return pagesize ;}
Set {pagesize = value ;}
}

Private int pageindex = 1;
Public int pageindex
{
Get {return pageindex ;}
Set {pageindex = value ;}
}

Private bool docount = false;
Public bool docount
{
Get {return docount ;}
Set {docount = value ;}
}

Private bool ordertype = false;
Public bool ordertype
{
Get {return ordertype ;}
Set {ordertype = value ;}
}

Private string strwhere = string. empty;
Public String strwhere
{
Get {return strwhere ;}
Set {strwhere = value ;}
}

Public idatareader getdatareader ()
{

If(this.doc ount)
{
Throw new argumentexception ("The docount attribute must be false to return the record set ");
}

// System. Web. httpcontext. Current. response. Write (pageindex );

Return sqlhelper. executereader (connectionstring, commandtype. storedprocedure, "pagination ",
New sqlparameter ("@ tblname", this. tblname ),
New sqlparameter ("@ strgetfields", this. strgetfields ),
New sqlparameter ("@ fldname", this. fldname ),
New sqlparameter ("@ pagesize", this. pagesize ),
New sqlparameter ("@ pageindex", this. pageindex ),
New sqlparameter ("@ docount", this.doc ount ),
New sqlparameter ("@ ordertype", this. ordertype ),
New sqlparameter ("@ strwhere", this. strwhere)
);
}

Public dataset getdataset ()
{
If(this.doc ount)
{
Throw new argumentexception ("The docount attribute must be false to return the record set ");
}

Return sqlhelper. executedataset (connectionstring, commandtype. storedprocedure, "pagination ",
New sqlparameter ("@ tblname", this. tblname ),
New sqlparameter ("@ strgetfields", this. strgetfields ),
New sqlparameter ("@ fldname", this. fldname ),
New sqlparameter ("@ pagesize", this. pagesize ),
New sqlparameter ("@ pageindex", this. pageindex ),
New sqlparameter ("@ docount", this.doc ount ),
New sqlparameter ("@ ordertype", this. ordertype ),
New sqlparameter ("@ strwhere", this. strwhere)
);
}

Public int getcount ()
{
If (! This.doc ount)
{
Throw new argumentexception ("to return the total count statistics, the docount attribute must be true ");
}

Return (INT) sqlhelper. executescalar (connectionstring, commandtype. storedprocedure, "pagination ",
New sqlparameter ("@ tblname", this. tblname ),
New sqlparameter ("@ strgetfields", this. strgetfields ),
New sqlparameter ("@ fldname", this. fldname ),
New sqlparameter ("@ pagesize", this. pagesize ),
New sqlparameter ("@ pageindex", this. pageindex ),
New sqlparameter ("@ docount", this.doc ount ),
New sqlparameter ("@ ordertype", this. ordertype ),
New sqlparameter ("@ strwhere", this. strwhere)
);
}

}

}

How to call ???

Assume that I have created two classes. One is the favlist database entity class and the other is the favlistcollection collection class. Favlistcollection stores a set of favlist object classes.

I can write a method like this.

/** // <Summary>
& Nb...