SQL Server General Stored Procedure paging code

In section 2005, we have more options. We can use the new syntax CTE (Public table expression). The paging stored procedures include the following:

1. Use Not in and select top

2. Use the value of id greater than or select top

3. Use the cursor in SQL

4. Temporary table

See the following link on the Internet.

Summary of commonly used paging stored procedures in C #
Http://read.newbooks.com.cn/info/174545.html

In 2005, we have more options. We can use the new syntax CTE (Public table expression). For more information about CTE, see a series of tutorials by a senior man in the blog.

Http://www.cnblogs.com/nokiaguy/archive/2009/01/31/1381562.html

Or simply go to Microsoft's official website.

Http://msdn.microsoft.com/zh-cn/library/ms190766 (SQL .90). x

View Details.

You can also use the newly added functions in 2005, including row_number (), rank, dense_rank, ntile, these new functions allow you to effectively analyze data and provide sorting values to the query result row. You may find that these new functions are useful in the following typical scenarios: allocating continuous Integers to result rows for presentation, paging, scoring, and histogram.

For more information, see the following links.

Http://blog.csdn.net/htl258/archive/2009/03/20/4006717.aspx

Here I mainly use row_number () combined with the new syntax CTE, first paste my stored procedure. It takes me two days to design, develop, and test stored procedures and related C # Code. However, the similar interface is very fast. In the morning, we can create two pages displayed by page, even complex queries can be done in the morning.

The following stored procedure does not return the total number of pages and the total number of items. If you are interested, add them by yourself. For more information, see the following section in the page-based stored procedure summary in C #.

Declare @ SQL nvarchar (4000 );
Declare @ totalRecord int;
-- Calculate the total number of records
If (@ SqlWhere = ''' or @ sqlWhere is NULL)
Set @ SQL = 'select @ totalRecord = count (*) from' + @ TableName
Else
Set @ SQL = 'select @ totalRecord = count (*) from '+ @ TableName + 'where' + @ sqlWhere
EXEC sp_executesql @ SQL, n' @ totalRecord int output', @ totalRecord OUTPUT -- calculate the total number of records

-- Calculate the total number of pages

Select @ TotalPage = @ totalRecord -- CEILING (@ totalRecord + 0.0)/@ PageSize)

The Stored Procedure SQL is as follows. It supports columns, conditions, multi-Table union, and arbitrary sorting.

The Code is as follows:
Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
Go
-- Declare @ SQL nvarchar (4000 );
-- Declare @ totalRecord int;
---- Calculate the total number of records
-- If (@ SqlWhere = ''' or @ sqlWhere is NULL)
-- Set @ SQL = 'select @ totalRecord = count (*) from' + @ TableName
-- Else
-- Set @ SQL = 'select @ totalRecord = count (*) from '+ @ TableName + 'where' + @ sqlWhere
-- EXEC sp_executesql @ SQL, n' @ totalRecord int output', @ totalRecord OUTPUT -- calculate the total number of records
--
---- Calculate the total number of pages
--
-- Select @ TotalPage = @ totalRecord -- CEILING (@ totalRecord + 0.0)/@ PageSize)
-- ===================================================== ======
-- Author: shiwenbin
-- MSN: jorden008@hotmail.com
-- Email: jorden008@163.com
-- Create date: 2009-10-20
-- Description: The paging stored procedure. The paging results are returned Based on the passed parameters.
-- Parameters:
-- ===================================================== ======
Alter procedure [dbo]. [Proc_GetDataPaged]
-- Add the parameters for the stored procedure here
@ StrSelect varchar (max) = null, -- columns to be displayed (multiple columns are separated by commas), for example: id, name
@ StrFrom varchar (max) = null, -- table name, or table connection string. Multi-Table connection example: student as s inner join dwinfo as dw on s. dwbh = dw. bh
@ StrWhere varchar (max) = null, -- Query condition. ''indicates no conditions, single conditions, or multiple conditions. For example, name = 'Ah' and id = 10.
@ StrOrder varchar (max) = null, -- Sort columns (separated by commas), for example, id desc, name
-- @ PageCount int output, -- total number of pages
@ ItemCount bigint output, -- total number of records
@ PageSize int = 50, -- number of entries per page
@ BeginIndex int = 1, -- start number of records
@ DoCount bit = 0 -- indicates whether to count the total number. If the value is 0, no statistics are collected. If the value is 1, no statistics are collected.
-- @ PageIndex int = 1 -- current page
-- @ ClassCode char (10) = null, -- unit number (class number)
AS
BEGIN
Set nocount on;
Declare @ SQL nvarchar (4000 );
Declare @ totalRecord int;
-- Calculate the total number of records
If (@ StrWhere = ''' or @ StrWhere = ''or @ StrWhere is NULL)
Set @ SQL = 'select @ totalRecord = count (*) from' + @ StrFrom
Else
Set @ SQL = 'select @ totalRecord = count (*) from' + @ StrFrom + 'where' + @ StrWhere
EXEC sp_executesql @ SQL, n' @ totalRecord int output', @ ItemCount OUTPUT -- calculate the total number of records
Declare @ SqlQuery varchar (max)
-- If (@ PageIndex = 1)
If (@ BeginIndex = 1 or @ BeginIndex = 0 or @ BeginIndex <0)
Begin
If (@ StrWhere is null) -- if (@ StrWhere = '')
Set @ SqlQuery = 'select top' + convert (varchar, @ PageSize)
+ 'Row_number () over (order by '+ @ StrOrder +') as RowNumber, '+ @ StrSelect +
'From' + @ StrFrom;
Else
-- Set @ SQL = 'select top @ PageSize * from @ TableName order by id desc ';
-- Select top @ PageSize * from @ TableName order by id desc;
Set @ SqlQuery = 'select top' + convert (varchar, @ PageSize)
+ 'Row_number () over (order by '+ @ StrOrder +') as RowNumber, '+ @ StrSelect + 'from' + @ StrFrom + 'where' + @ StrWhere;
-- Exec (@ SqlQuery)
-- @ SqlQuery
End
Else
Begin
If (@ StrWhere is null) -- if (@ StrWhere = '')
Begin
Set @ SqlQuery = 'with cte (
Select row_number () over (order by '+ @ StrOrder +') as RowNumber, '+ @ StrSelect + 'from' + @ StrFrom +'
)
Select * from cte where RowNumber between '+
-- Convert (varchar, (@ PageIndex-1) * @ PageSize) + 1) + 'and' +
--
-- Convert (varchar, @ PageIndex * @ PageSize)
Convert (varchar, @ BeginIndex) + 'and' +
Convert (varchar, @ BeginIndex + @ PageSize)
-- Print @ SqlQuery
End
Else
Begin
Set @ SqlQuery = 'with cte (
Select row_number () over (order by '+ @ StrOrder +') as RowNumber, '+ @ StrSelect + 'from' + @ StrFrom + 'where' + @ StrWhere +'
)
Select * from cte where RowNumber between '+
-- Convert (varchar, (@ PageIndex-1) * @ PageSize) + 1) + 'and' +
--
-- Convert (varchar, @ PageIndex * @ PageSize)
Convert (varchar, @ BeginIndex) + 'and' +
Convert (varchar, @ BeginIndex + @ PageSize)
-- Print @ SqlQuery
End
End
-- Set @ SqlQuery = @ SqlQuery + '; select @ ItemCount = count (*) from' + @ TableName
-- Set @ PageCount = @ ItemCount/@ PageSize
-- Print 'colout' + @ PageConut + 'page' + @ ItemCount + 'two'
-- Print @ ItemCount
Print @ SqlQuery
Exec (@ SqlQuery)
END

C # access to the relevant code uses the Microsoft enterprise database V4.1

Enterprise Library 4.1:

Http://www.microsoft.com/downloads/details.aspx? FamilyId = 1643758b-2986-47f7-b529-3e000084b6ce5 & displaylang = en

Sample Code, front-end page, front-end is a user control

The Code is as follows:
<% @ Control Language = "C #" AutoEventWireup = "true" CodeBehind = "StudentDetailsTable. ascx. cs "Inherits =" Kimbanx. UCS. foreignStudentAdmin. userControl. userControl. studentDetailsTable "%>

Unit:	Level:Level Node
Total unitsStudents Person	Onselectedindexchanged = "ddlPageSize_SelectedIndexChanged"> Total PersonsPage NowPage Oncommand = "LinkButton_Command"> Homepage Oncommand = "LinkButton_Command"> next page Oncommand = "LinkButton_Command"> Previous Page Oncommand = "LinkButton_Command"> last page

EmptyDataText = "no matching data">



Runat = "server"/>




'>


'>









'>


'>





'>


'>

Sample Code, background code
The Code is as follows:
Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Web;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Text;
Using System. Data;
Using System. Data. Common;
Using Microsoft. Practices. EnterpriseLibrary. Common;
Using Microsoft. Practices. EnterpriseLibrary. Data;
Using Kimbanx. ucs.foreignstudentadmin. Model;
Using Kimbanx. ucs.foreignstudentadmin. Common;
Namespace Kimbanx. ucs.foreignstudentadmin. UserControl. UserControl
{
Public partial class StudentDetailsTable: System. Web. UI. UserControl
{
Private Database _ db = DatabaseFactory. CreateDatabase ();
Private DbCommand _ command;
Private DbConnection _ connection;
Private DataSet _ ds;
Private string _ classCode;
Private string _ classFullName;
Private string _ studentType;
Private string _ studentCount;
Private string _ queryStringWhere;
Private DataTable _ studentTable;
Protected string SetBirthDate (object obj)
{
String result = string. Empty;
String temp = obj. ToString ();
Result = DateTime. Parse (temp). tow.datestring ();
Return result;
}
Protected string SetEnrollDate (object obj)
{
String result = string. Empty;
String temp = obj. ToString ();
Result = DateTime. Parse (temp). tow.datestring ();
Return result;
}
Protected void Filldata_dllPageSize ()
{
For (int I = 1; I <100; I ++)
{
DdlPageSize. Items. Add (I. ToString ());
}
DdlPageSize. SelectedIndex = 14;
}
Protected void InitSession ()
{
// Session ["PageSize"] = 0;
Session ["PageIndex"] = 1;
Session ["PageCount"] = int. Parse (_ studentCount)/15 + 1;
}
///

/// Obtain the QueryString passing Parameter
///
Protected void GetQueryStringPara ()
{
_ ClassCode = Request. QueryString ["dwbh"];
_ ClassFullName = HttpUtility. UrlDecode (Request. QueryString ["dwmc"]);
_ StudentCount = Request. QueryString ["studentCount"];
_ StudentType = HttpUtility. UrlDecode (Request. QueryString ["studentType"]);
_ QueryStringWhere = Request. QueryString ["where"];
}
Protected void SetLabelText ()
{
This. lblClassName. Text = _ classFullName;
This. lblClassLevel. Text = GetClassInfo (_ classCode). Level. ToString ();
This. lblStudentCount. Text = _ studentCount;
This. lblStudentType. Text = _ studentType;
}
# Region
/////
//// Obtain student data
/////
///// Field displayed
///// Used
///// Query Conditions
///// Number of entries per page
///// Current page
/////
// Protected DataTable GetStudentData (string strSelect, string strFrom, string strWhere, int pageSize, int pageIndex)
//{
// _ Command = _ db. GetStoredProcCommand ("StudentPaging ");
// _ Db. addInParameter (_ command, "StrSelect", DbType. string, "zpadress, xmjz, xmjy, jx, zw, gj, sjyqk, zj, csrq, rwrq, xzz, dhd, dhx, fcjp, hzh, xh ");
// _ Db. AddInParameter (_ command, "StrFrom", DbType. String, "tx_xyzl ");
// _ Db. AddInParameter (_ command, "StrWhere", DbType. String, strWhere );
// _ Db. AddInParameter (_ command, "StrOrder", DbType. String, "id ");
// _ Db. AddInParameter (_ command, "PageSize", DbType. Int32, pageSize );
// _ Db. AddInParameter (_ command, "PageIndex", DbType. Int32, pageIndex );
// _ StudentTable = _ db. ExecuteDataSet (_ command). Tables [0];
// Return _ studentTable;
/