How to call SQL stored procedure implementation paging in ASP.net _mssql

First look at the following code to create a stored procedure

1. Create a stored procedure with the following statement:

 CREATE PROC p_viewpage @TableName VARCHAR (200),--table name @FieldList VARCHAR (2000),--Displays column name, if all fields are * @PrimaryKey VARCHAR ( 100)--single primary key or unique value key @Where VARCHAR (2000)--the query condition does not contain a ' Where ' character, such as Id>10 and Len (userid) >9 @Order VARCHAR (1000),--the sort does not contain ' Order BY ' character, such as ID Asc,userid desc, you must specify ASC or DESC--note that when the @sorttype=3 takes effect, remember to add the primary key at the end, otherwise it will make you more depressed @SortType INT   ,--Collation 1: Positive order ASC 2: Reverse DESC 3: Multi-column Sort method @RecorderCount INT,--Total records 0: Returns the total record @PageSize int,--the number of records per page output @PageIndex int, --The current number of pages @TotalCount int output,--Returns the total record @TotalPageCount int output--Returns the total number of pages as SET NOCOUNT on IF ISNULL (@TotalCount , ') = ' Set @TotalCount = 0 Set @Order = RTRIM (LTRIM (@Order)) Set @PrimaryKey = RTRIM (LTRIM (@PrimaryKey)) Set @FieldL ist = REPLACE (RTRIM (LTRIM (@FieldList)), ', ' while CHARINDEX (', ', @Order) > 0 OR CHARINDEX (', ', @Order) > 0 BEG In Set @Order = replace (@Order, ', ', ', ') SET @Order = replace (@Order, ', ', ', ") End IF ISNULL (@TableName, ') = ' O R ISNULL (@FieldList, "")= ' or ISNULL (@PrimaryKey, ') = ' or @SortType < 1 or @SortType >3 or @RecorderCount < 0 or @PageSize &L T 0 OR @PageIndex < 0 begin PRINT (' err_00 ') return end IF @SortType = 3 begin if (UPPER (@Order, 4))!
 = ' ASC ' and UPPER (right (@Order, 5))!= ' DESC '] BEGIN PRINT (' err_02 ') return end DECLARE @new_where1 VARCHAR (1000) DECLARE @new_where2 VARCHAR (1000) DECLARE @new_order1 VARCHAR (1000) DECLARE @new_order2 VARCHAR (1000) DECLARE @new_or Der3 VARCHAR (1000) DECLARE @Sql VARCHAR (8000) DECLARE @SqlCount NVARCHAR (4000) IF ISNULL (@where, ') = ' BEGIN SE T @new_where1 = ' Set @new_where2 = ' where ' end ELSE BEGIN set @new_where1 = ' where ' + @where set @ne  W_where2 = ' WHERE ' + @where + ' and ' End IF ISNULL (@order, ') = ' or @SortType = 1 or @SortType = 2 BEGIN If @SortType = 1 BEGIN SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC ' SET @new_order2 = ' ORDER BY ' + @
PrimaryKey + ' DESC '   End IF @SortType = 2 BEGIN SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC ' SET @new_order2 = ' O Rder by ' + @PrimaryKey + ' ASC ' End ELSE is BEGIN SET @new_order1 = ' ORDER BY ' + @Order end IF @SortTyp E = 3 and CHARINDEX (', ' + @PrimaryKey + ', ', ' + @Order) >0 BEGIN Set @new_order1 = ' ORDER BY ' + @Order set @new_or Der2 = @Order + ', ' Set @new_order2 = replace (replace (@new_order2, ' ASC, ', ' {ASC}, '), ' DESC, ', ' {DESC}, ') set @new_o Rder2 = replace (replace (@new_order2, ' {ASC}, ', ' DESC, '), ' {DESC}, ', ' ASC, ') SET @new_order2 = ' ORDER BY ' + SUBSTRING (@new _order2,1,len (@new_order2)-1) IF @FieldList <> ' * ' BEGIN SET @new_order3 = replace (replace (@Order +
     ', ', ' ASC, ', ', ', ' DESC, ', ', ') SET @FieldList = ', ' + @FieldList while CHARINDEX (', ', @new_order3) >0 
      Begin IF CHARINDEX (SUBSTRING (', ' + @new_order3, 1,charindex (', ', @new_order3)), ', ' + @FieldList + ', ') >0 begin 
 SET @FieldList =      @FieldList + ', ' + SUBSTRING (@new_order3, 1,charindex (', ', @new_order3)) End SET @new_order3 = S Ubstring (@new_order3, CHARINDEX (', ', @new_order3) +1,len (@new_order3)) End SET @FieldList = SUBSTRING (@FieldList, 2, LEN (@FieldList)) End SET @SqlCount = ' SELECT @TotalCount =count (*), @TotalPageCount =ceiling ((COUNT (*) +0.0)/ ' + CAST (@PageSize as VARCHAR) + ') from ' + @TableName + @new_where1 IF @RecorderCount = 0 BEGIN EXEC sp_execut
  ESQL @SqlCount, N ' @TotalCount int output, @TotalPageCount int output ', @TotalCount output, @TotalPageCount output End ELSE is BEGIN SELECT @TotalCount = @RecorderCount end IF @PageIndex > CEILING ((@TotalCount +0.0)/@PageSize The BEGIN SET @PageIndex = CEILING (@TotalCount +0.0)/@PageSize) End IF @PageIndex = 1 OR @PageIndex >= CEILING ( @TotalCount +0.0)/@PageSize) begin IF @PageIndex = 1--Returns the first page of data begin SET @Sql = ' SELECT top ' + STR (@PageSize ) + ' + @FieldList + ' From ' + @TableName + @new_where1 + @new_order1 end IF @PageIndex >= CEILING ((@TotalCount +0.0)/@PageS ize)--Returns the last page of data BEGIN SET @Sql = ' SELECT top ' + STR (@PageSize) + ' + @FieldList + ' from (' + ' Selec T Top ' + STR (ABS (@PageSize * @PageIndex-@TotalCount-@PageSize)) + "+ @FieldList + ' from ' + @TableNa Me + @new_where1 + @new_order2 + ') as TMP ' + @new_order1 end ELSE BEGIN IF @SortType = 1 --only primary key positive sequence sort begin IF @PageIndex <= CEILING ((@TotalCount +0.0)/@PageSize)/2--forward retrieve begin SET @Sql = ' SELECT Top ' + STR (@PageSize) + "+ @FieldList + ' from ' + @TableName + @new_where2 + @PrimaryKey + ' > ' + ' (select MAX (' + @PrimaryKey + ') from (select Top + STR (@PageSize * (@PageIndex-1)) + ' + @Pr
     Imarykey + ' from ' + @TableName + @new_where1 + @new_order1 + ') as TMP] ' + @new_order1 end ELSE--Reverse Retrieve BEgin SET @Sql = ' select top ' + str (@PageSize) + ' + @FieldList + ' from (' + ' select top ' + str (@Pa
           Gesize) + ' + @FieldList + ' from ' + @TableName + @new_where2 + @PrimaryKey + ' < ' + ' (select MIN (' + @PrimaryKey + ') from (select Top + STR (@TotalCount-@PageSize * @PageIndex) + "+ @Prima  Rykey + ' from ' + @TableName + @new_where1 + @new_order2 + ') as TMP ' + @new_order2 + ') as TMP ' + @new_order1 End If @SortType = 2--only primary key reverse order sort BEGIN IF @PageIndex <= CEILING (@T otalcount+0.0)/@PageSize)/2--forward retrieval BEGIN SET @Sql = ' SELECT top ' + STR (@PageSize) + ' + @FieldList + ' FRO M ' + @TableName + @new_where2 + @PrimaryKey + ' < ' + ' (SELECT MIN (' + @PrimaryKey + ') from (S Elect top ' + STR (@PageSize * (@PageIndex-1)) + "+ @PrimaryKey + ' from ' + @TableName + @new_where1 + @new_order1 + ') as TMP ' + @new_order1 end ELSE--Reverse Retrieve BEGIN SET @Sql = ' SELECT top ' + STR (@PageSize
           + ' + @FieldList + ' from (' + ' SELECT top ' + STR (@PageSize) + ' + @FieldList + ' from ' + @TableName + @new_where2 + @PrimaryKey + ' > ' + ' (select MAX (' + @PrimaryKey + ') from (select T
           OP ' + STR (@TotalCount-@PageSize * @PageIndex) + ' + @PrimaryKey + ' from ' + @TableName    
   + @new_where1 + @new_order2 + ') as TMP) ' + @new_order2 + ') as TMP ' + @new_order1 end If @SortType = 3-Multiple-column sort, must contain a primary key and be placed last, otherwise do not process the BEGIN IF CHARINDEX (', ' + @PrimaryKey + ', ', ' + @Order) = 0 B
       Egin PRINT (' err_02 ') return end IF @PageIndex <= CEILING ((@TotalCount +0.0)/@PageSize)/2--forward retrieval BEGIN SET @Sql = ' select top ' + str (@PageSize) + ' + @FieldList + ' from (' + ' select top ' + str (@PageSize) + ' ' + @FieldList + ' from(' + ' SELECT top ' + STR (@PageSize * @PageIndex) + "+ @FieldList + ' from ' + @TableName + @new_w
      Here1 + @new_order1 + ') as TMP ' + @new_order2 + ') as TMP ' + @new_order1 end ELSE--Reverse retrieval BEGIN SET @Sql = ' select top ' + str (@PageSize) + ' + @FieldList + ' from (' + ' select top ' + str (  @PageSize) + ' + @FieldList + ' from (' + ' SELECT top ' + STR (@TotalCount-@PageSize * @PageIndex + @PageSize) +  ' + @FieldList + ' from ' + @TableName + @new_where1 + @new_order2 + ') as TMP ' + @new_order1 + ') as TMP ' + @new_order1 end end PRINT (@Sql) EXEC (@Sql) go

2. Calling test code in SQL Server

--Execute stored procedure

declare @TotalCount int,
    @TotalPageCount int
exec p_viewpage ' t_module ', ' * ', ' moduleid ', ', ', ', ', 1,0,10,1, @TotalCount output, @TotalPageCount output
Select @TotalCount, @TotalPageCount;

asp.net code implementation:

#region =========== Common Paging stored procedure =========== public static DataSet runprocedureds (String connectionString, String StoredProc Name, idataparameter[] parameters, string tablename) {using (SqlConnection connection = new SqlConnection (Connectionst
   Ring)) {The DataSet DataSet = new DataSet (); Connection.
   Open ();
   SqlDataAdapter SqlDA = new SqlDataAdapter ();
   Sqlda.selectcommand = Buildquerycommand (connection, storedprocname, parameters);
   Sqlda.fill (DataSet, TableName); Connection.
   Close ();
 return dataSet; ///<summary>///Common Paging stored procedure///</summary>///<param name= "connectionString" ></param&gt
  ; <param name= "Tblname" ></param>///<param name= "Strgetfields" ></param>///<param name= "PrimaryKey" ></param>///<param name= "strwhere" ></param>///<param name= "Strorder" >< /param>///<param name= "SortType" ></param>///<param name= "RecordCount" &GT;&LT;/PARAM&GT <param name= "PageSize" ></param>///<param name= "PageIndex" ></param>///<param name= "t Otalcount "></param>///<param name=" Totalpagecount "></param>///<returns></returns&
  Gt public static DataSet PageList (String connectionString, String tblname, String strgetfields, String PrimaryKey, String str Where, string strorder, int sorttype, int recordCount, int PageSize, int pageindex,ref int TOTALCOUNT,REF int Totalpageco UNT) {sqlparameter[] Parameters ={new SqlParameter ("@TableName", sqldbtype.varchar,200), New SqlParameter ("@FieldLi St ", sqldbtype.varchar,2000), New SqlParameter (" @PrimaryKey ", sqldbtype.varchar,100), New SqlParameter (" @Where ", sqldbtype.varchar,2000), New SqlParameter ("@Order", sqldbtype.varchar,1000), New SqlParameter ("@SortType", SqlDbType.Int), New SqlParameter ("@RecorderCount", SqlDbType.Int), New SqlParameter ("@PageSize", SqlDbType.Int), new S Qlparameter ("@PageIndex", sqldbtype.inT), New SqlParameter ("@TotalCount", SqlDbType.Int), New SqlParameter ("@TotalPageCount", SqlDbType.Int)}; Parameters[0].
 Value = tblname; PARAMETERS[1].
 Value = Strgetfields; PARAMETERS[2].
 Value = PrimaryKey; PARAMETERS[3].
 Value = strwhere; PARAMETERS[4].
 Value = Strorder; PARAMETERS[5].
 Value = SortType; PARAMETERS[6].
 Value = RecordCount; PARAMETERS[7].
 Value = PageSize; PARAMETERS[8].
 Value = PageIndex; PARAMETERS[9].
 Value = TotalCount; PARAMETERS[9].
 Direction = ParameterDirection.Output; PARAMETERS[10].
 Value = Totalpagecount; PARAMETERS[10].

 Direction = ParameterDirection.Output;
 DataSet ds = Runprocedureds (connectionString, "P_viewpage", Parameters, "pagelisttable"); totalcount = Int. Parse (Parameters[9].
 Value.tostring ()); totalpagecount = Int. Parse (parameters[10].
 Value.tostring ());
  return DS; #endregion DataSet ds = Sqlhelper.pagelist (Sqlhelper.localsqlserver, "T_user", "*", "UserID", "" "," ", 1, 0, PageSize,
1, ref totalcount, ref totalpagecount); This. Rptdata.daTasource = ds; This. Rptdata.databind ();

The above is the article describes how to invoke SQL stored procedures in the ASP.net to implement the full page of paging, I hope to help you learn in the future, of course, the method is not only described in this article, welcome to share a good plan.