In asp.net, how does one call SQL stored procedures to implement paging? asp.net stored procedures

In asp.net, how does one call SQL stored procedures to implement paging? asp.net stored procedures

First, let's take a look at the following code to create a stored procedure:

1. Create a stored procedure. The statement is as follows:

Create proc P_viewPage @ TableName VARCHAR (200), -- table name @ FieldList VARCHAR (2000), -- display the column name. If it is all fields, it is * @ PrimaryKey VARCHAR (100 ), -- single primary key or unique value Key @ Where VARCHAR (2000), -- the query condition does not contain the 'where' character, such as id> 10 and len (userid)> 9 @ Order VARCHAR (1000), -- the sorting does not contain the 'ORDER BY' characters, such as id asc and userid desc. asc or desc must be specified. -- note that it takes effect when @ SortType = 3, remember to add a primary key at the end, otherwise it will make you more depressed @ SortType INT, -- sorting Rule 1: Positive asc 2: reverse desc 3: Multi-column sorting method @ RecorderCount INT, -- total records 0: Total records returned Record @ PageSize INT, -- number of records OUTPUT per page @ PageIndex INT, -- current page number @ TotalCount int output, -- Record total returned records @ TotalPageCount int output -- total number of returned pages as set nocount on if isnull (@ TotalCount ,'') = ''set @ TotalCount = 0 SET @ Order = RTRIM (LTRIM (@ Order) SET @ PrimaryKey = RTRIM (LTRIM (@ PrimaryKey )) SET @ FieldList = REPLACE (RTRIM (LTRIM (@ FieldList), '','') while charindex (',', @ Order)> 0 or charindex (',', @ Order)> 0 begin set @ Ord Er = REPLACE (@ Order, ') SET @ Order = REPLACE (@ Order,', ') end if isnull (@ TableName, '') ='' or isnull (@ FieldList, '') ='' or isnull (@ PrimaryKey ,'') = ''OR @ SortType <1 OR @ SortType> 3 OR @ RecorderCount <0 OR @ PageSize <0 OR @ PageIndex <0 begin print ('err _ 00 ') return end if @ SortType = 3 begin if (UPPER (RIGHT (@ 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_order3 VARCHAR (1000) DECLARE @ SQL VARCHAR (8000) DECLARE @ SqlCount NVARCHAR (4000) IF ISNULL (@ where ,'') = ''in in SET @ new_where1 = ''SET @ new_where2 = 'where' end else begin set @ new_where1 = 'where' + @ where set @ new_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 = 'ORDER' + @ PrimaryKey + 'asc 'end else begin set @ new_order1 = 'ORDER BY' + @ order end if @ SortType = 3 and charindex (', '+ @ PrimaryKey + '',', '+ @ Order)> 0 begin set @ new_order1 = 'ORDER BY' + @ order set @ new_order2 = @ Order + ', 'set @ new_order2 = REPLACE (@ new_order2, 'asc, ',' {ASC}, '), 'desc',' {DESC },') SET @ new_order2 = 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 (@ 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 = SUBSTRING (@ 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_EXECUTESQL @ SqlCount, n' @ TotalCount int output, @ TotalPageCount INT output', @ TotalCount OUTPUT, @ TotalPageCount output end else begin select @ TotalCount = @ RecorderCount end if @ PageIndex> CEILING (@ TotalCount + 0.0)/@ PageSize) 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)/@ PageSize) -- returns the last page of data begin set @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from ('+ 'select TOP' + STR (ABS (@ PageSize * @ PageIndex-@ TotalCount-@ PageSize )) + ''+ @ FieldList + 'from' + @ TableName + @ new_where1 + @ new_order2 + ') as tmp '+ @ new_order1 end else begin if @ SortType = 1 -- only the primary key is sorted in positive order begin if @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize) /2 -- forward query begin set @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from' + @ TableName + @ new_where2 + @ PrimaryKey + '>' + '(select max (' + @ PrimaryKey + ') FROM (select top '+ STR (@ PageSize * (@ PageIndex-1) + ''+ @ PrimaryKey + 'from' + @ TableName + @ new_where1 + @ new_order1 + ') as tmp) '+ @ new_order1 end else -- reverse retrieval begin set @ SQL = 'select top' + STR (@ PageSize) + ''+ @ FieldList + 'from ('+ 'select TOP' + STR (@ PageSize) + ''+ @ FieldList + 'from' + @ TableName + @ new_where2 + @ PrimaryKey + '<' + '(select min (' + @ PrimaryKey + ') FROM (select top '+ STR (@ TotalCount-@ PageSize * @ PageIndex) + ''+ @ PrimaryKey + 'from' + @ TableName + @ new_where1 + @ new_order2 + ') as tmp) '+ @ new_order2 + ') as tmp '+ @ new_order1 end if @ SortType = 2 -- only the primary key is sorted in reverse order begin if @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize) /2 -- forward query begin set @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from' + @ TableName + @ new_where2 + @ PrimaryKey + '<' + '(select min (' + @ PrimaryKey + ') FROM (select top '+ STR (@ PageSize * (@ PageIndex-1) + ''+ @ PrimaryKey + 'from' + @ TableName + @ new_where1 + @ new_order1 + ') as tmp) '+ @ new_order1 end else -- reverse retrieval begin set @ SQL = 'select top' + STR (@ PageSize) + ''+ @ FieldList + 'from ('+ 'select TOP' + STR (@ PageSize) + ''+ @ FieldList + 'from' + @ TableName + @ new_where2 + @ PrimaryKey + '>' + '(select max (' + @ PrimaryKey + ') FROM (select top '+ STR (@ TotalCount-@ PageSize * @ PageIndex) + ''+ @ PrimaryKey + 'from' + @ TableName + @ new_where1 + @ new_order2 + ') as tmp) '+ @ new_order2 +') as tmp '+ @ new_order1 end if @ SortType = 3 -- Multi-column sorting, which must contain the primary key and be placed at the END, otherwise, do not process begin if charindex (',' + @ PrimaryKey + '', ',' + @ Order) = 0 begin 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_where1 + @ 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 print (@ SQL) EXEC (@ SQL) GO

2. Call test code in SQL Server

-- Run the Stored Procedure declare @ TotalCount int, @ TotalPageCount intexec P_viewPage 't_ module', '*', 'leleid, @ TotalCount output, @ TotalPageCount outputSelect @ TotalCount, @ TotalPageCount;

Asp.net code implementation:

# Region ============= General paging stored procedures =========== public static DataSet RunProcedureDS (string connectionString, string storedProcName, IDataParameter [] parameters, string tableName) {using (SqlConnection connection = new SqlConnection (connectionString) {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> /// General paging Stored Procedure /// </summary> /// <param name = "connectionString"> </param> /// <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"> </param> /// <param name = "PageSize"> </param> /// <param name = "PageIndex"> </param> // <param name = "totalCount"> </param> // <param name = "totalPageCount"> </param> /// <returns> </returns> public static DataSet PageList (string connectionString, string tblName, string strGetFields, string primaryKey, string strWhere, string strOrder, int sortType, int recordCount, int PageSize, int PageIndex, ref int totalCount, ref int totalPageCount) {SqlParameter [] parameters = {new SqlParameter ("@ TableName", SqlDbType. varChar, 200), new SqlParameter ("@ FieldList", 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 SqlParameter ("@ 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 ;}# endregionDataSet 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 content is the whole article about how to call the SQL stored procedure in asp.net to implement paging. I hope it will help you in your future studies. Of course, the method is not limited to the content described in this article. You are welcome to share a good solution with you.