SQL Server paging stored procedure: Counter (@ PageIndexint, @ PageSizeint, @ BlogIDint0, @ PostTypeint-1, @ CategoryIDint-1, @ Hidingbit0, @ Countintoutput) asDECLARE @ counter @ PageUpperBoundin
SQL server paging stored procedure: Create PROC blog_GetPagedPosts (@ PageIndex int, @ PageSize int, @ BlogID int = 0, @ PostType int =-1, @ CategoryID int =-1, @ Hiding bit = 0, @ Count int output) as DECLARE @ PageLowerBound int DECLARE @ PageUpperBound in
Turning Pages in SQL serverStorageProcess:
Create PROC blog_GetPagedPosts
(
@ PageIndex int,
@ PageSize int,
@ BlogID int = 0,
@ PostType int =-1,
@ CategoryID int =-1,
@ Hiding bit = 0,
@ Count int output
)
As
DECLARE @ PageLowerBound int
DECLARE @ PageUpperBound int
SET @ PageLowerBound = @ PageSize * @ PageIndex-@ PageSize
SET @ PageUpperBound = @ PageLowerBound + @ PageSize + 1
Create Table # IDs
(
TempID int IDENTITY (1, 1) not null,
EntryID int not null
)
Insert into # IDs (EntryID) select DISTINCT [ID] from view_Content where CategoryID = @ CategoryID and blogID = @ BlogID order by [ID] desc
SELECT vc .*
FROM View_Content vc
Inner join # IDS tmp ON (vc. [ID] = tmp. EntryID)
WHERE tmp. TempID> @ PageLowerBound
AND tmp. TempID <@ PageUpperBound and vc. Hiding = 0
Order by tmp. TempID
SELECT @ Count = COUNT (*) FROM # IDS
SELECT @ Count = COUNT (*) FROM # IDS
Drop table # IDS
Return @ Count
GO
Because access does not supportStorageProcess. Temporary tables cannot be created and can only be implemented in programs.
Access is implemented as follows, which is also used in myblog Access:
Public List GetPagedPost (PagedPost p, out int TotalRecords)
{
List List = new List ();
Using (OleDbConnection conn = GetOleDbConnection ())
{
StringBuilder SQL = new StringBuilder ();
SQL. AppendFormat ("select [ID] from blog_Content as p"); // construct a query Condition
If (p. CategoryID> 0)
{
SQL. appendFormat (", blog_Categories AS c, blog_Links AS l WHERE c. categoryID = l. categoryID and (p. ID = l. postID) and c. categoryID = {1} and p. blogID = {0} ", p. blogID, p. categoryID );
}
Else
{
SQL. AppendFormat ("where p. blogID = {0}", p. BlogID );
}
If (p. PostType! = PostType. Undeclared)
{
SQL. AppendFormat ("and p. PostType = {0}", (int) p. PostType );
}
SQL. Append ("order by p. [DateUpdated] desc ");
// NetDiskContext. Current. Context. Response. Write (SQL. ToString ());
// NetDiskContext. Current. Context. Response. End ();
OleDbCommand MyComm = new OleDbCommand (SQL. ToString (), conn );
List IDs = new List (); // Obtain the topic ID list
Conn. Open ();
Using (OleDbDataReader dr = MyComm. ExecuteReader ())
{
While (dr. Read ())
{
IDs. Add (int) dr [0]);
}
}
TotalRecords = IDs. Count; // total number of returned records
If (TotalRecords <1)
Return list;
Int pageLowerBound = p. PageSize * p. PageIndex-p. PageSize; // record index
Int pageUpperBound = pageLowerBound + p. PageSize;
StringBuilder sb = new StringBuilder ();
If (TotalRecords> = pageLowerBound)
For (int I = pageLowerBound; I <TotalRecords & I <pageUpperBound; I ++)
{
Sb. AppendFormat ("{0},", IDs [I]); // construct the ID in () condition, take one of the pages
}
Else return list; // if no record is returned, an empty table is returned.
If (sb. Length> 1)
Sb. Remove (sb. Length-1, 1); // Delete the last comma
MyComm. commandText = string. format ("SELECT B. *, c. account as Account FROM blog_Content B, Blog_Config c where B. blogID = c. blogID and B. [ID] in ({0}) order by B. dateadded desc ", sb. toString ());
Using (OleDbDataReader dr = MyComm. ExecuteReader ())
{
While (dr. Read ())
{
List. Add (DataHelp. LoadDayBook (dr ));
}
}
Return list;
}
}
Please indicate the source of the post... deep Q