General SQL stored procedure paging and asp.net background call methods, stored procedure asp.net
Create a table and add 3 million data
Use StoredCREATE TABLE UserInfo (-- Create TABLE id int IDENTITY () primary key not null, -- add primary key and id column UserName varchar (50 )) declare @ I int -- add 3 million data, about 4 minutes set @ I = 1 while @ I <3000000 begininsert into UserInfo (UserName) values (@ I) set @ I = @ I + 1end
Stored Procedure T-SQL
Create PROCEDURE [dbo]. [GetDataList] (@ TableName varchar (5000), -- table name @ Fields varchar (5000) = '*', -- field name (all Fields are *) @ OrderField varchar (5000 ), -- Sort field (required! Supports multiple fields) @ OrderType varchar (5000), -- sorting type @ sqlWhere varchar (5000) = Null, -- Condition Statement (where is not required) @ pageSize int, -- How many records per page @ pageIndex int = 1, -- specifies the page at which the current record is @ TotalPage int output, -- returns the total number of pages @ totalRecord int output -- calculates the total number of records -- returns the total number of records) asbegin Tran in Tran -- start transaction Declare @ SQL nvarchar (500 ); if (@ SqlWhere = ''or @ sqlWhere = 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 = CEILING (@ totalRecord + 0.0)/@ PageSize) if (@ SqlWhere = ''or @ sqlWhere = NULL) set @ SQL = 'select * FROM (Select ROW_NUMBER () over (order by '+ @ OrderField + ''+ @ Ordertype +') as rowId, '+ @ Fields + 'from' + @ TableName else set @ SQL = 'select * from (Select ROW_NUMBER () over (order by '+ @ OrderField + ''+ @ Ordertype +') as rowId, '+ @ Fields + 'from' + @ TableName + 'where' + @ SqlWhere -- processing page out of range if @ PageIndex <= 0 Set @ pageIndex = 1 if @ pageIndex> @ totalPage Set @ pageIndex = @ TotalPage -- processing start point and end point Declare @ StartRecord int Declare @ EndRecord int set @ StartRecord = (@ pageIndex-1) * @ PageSize + 1 set @ EndRecord = @ StartRecord + @ pageSize-1 -- continue to merge SQL statements set @ SQL = @ SQL + ') as '+ @ TableName + 'where rowid between' + Convert (varchar (50), @ StartRecord) + 'and' + Convert (varchar (50), @ EndRecord) -- print @ SQL Exec (@ SQL) else If @ Error <> 0 Begin RollBack Tran Return-1 End Else Begin Commit Tran Return @ totalRecord --- Return the total number of records End -- exec GetDataList 'userinfo ','*', 'id', 'desc ', '',
Foreground page Default2.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">Backend CS code Default2.aspx. cs
Using System; using System. collections. generic; using System. linq; using System. web; using System. web. UI; using System. web. UI. webControls; using System. data; using System. data. sqlClient; using System. configuration; using System. text; public partial class Default2: System. web. UI. page {private int PageIndex = 0; // current Page number private int PageSize = 50; // Several private int TotalPage = 1 records per Page; // total Page number private int TotalRecord = 0; // The total record private string OrderType = "desc"; // The default sorting method is the forward Order protected void Page_Load (object sender, EventArgs e) {if (! IsPostBack) {GetParams (); DataSet ds = PageData ("UserInfo", "*", "id", OrderType, "", PageSize, PageIndex, out TotalPage, out TotalRecord ); gridView1.DataSource = ds; GridView1.DataBind (); lbl_page.Text = GetDivPager ("", ds) ;}// database connection character public static string StrConn () {// return string. format ("{0}", "server = .; database = Stored; user = sa; password = 123456 "); return ConfigurationSettings. appSettings ["Con NString "]. ToString ();} // Get the private void GetParams () {if (! String. isNullOrEmpty (Request ["page"]) {PageIndex = Convert. toInt32 (Request ["Page"]);} else {PageIndex = 1 ;}# region obtains the public string GetDivPager (string queryString, DataSet ds) {StringBuilder sp = new StringBuilder (); int TotalCount = TotalRecord; int rowCount = TotalPage; if (ds! = Null) {sp. appendFormat ("<p> total record: <span id = \" sum \ ">{0} </span>", TotalCount); sp. appendFormat ("page number: <em> <B id = \ "current \"> {0} </B>/<span id = \ "count \"> {1} </span> </ em> ", pageIndex, rowCount); sp. appendFormat ("per page: <span id = \" eachPage \ ">{0} </span> </p>", PageSize); sp. appendFormat ("<a href = '{0}'> homepage </a> ","? Page = 1 "+ queryString); if (PageIndex> 1) {sp. AppendFormat (" <a href = '{0}'> <previous page </a> ","? Page = "+ (PageIndex-1) + queryString);} int temp = 0; int loopc = rowCount> 10? 10: rowCount; for (int I = 0; I <loopc; I ++) {temp = I + 1; if (PageIndex> 10) {temp = (PageIndex-10) + I + 1;} sp. appendFormat ("<a class = \" {0} \ "href = '{1}'> {2} </a>", PageIndex = temp? "Active ":"","? Page = "+ temp + queryString, temp);} if (PageIndex! = RowCount) {sp. AppendFormat ("<a href = '{0}'> next page> </a> ","? Page = "+ (PageIndex + 1) + queryString);} sp. AppendFormat (" <a href = '{0}'> last page </a> ","? Page = "+ rowCount + queryString);} else {ds = null;} return sp. toString ();} # endregion # region obtain paging data /// <summary> // obtain paging data /// </summary> /// <param name = "TblName"> data table name </param> /// <param name = "Fields"> field to be read </param> /// <param name = "OrderField"> sorting field </param> /// <param name = "OrderType"> sorting method </param> /// <param name = "SqlWhere"> query condition </param> /// <param name = "PageSize"> How many data entries are displayed on each page </param> // <param name = "pageIndex"> current page number </param> // <param name = "TotalPage"> return value, total number of pages </param> /// <param name = "TotalRecord"> returned values, total number of records </param> /// <returns> </returns> public static DataSet PageData (string TblName, string Fields, string OrderField, string OrderType, string SqlWhere, int PageSize, int pageIndex, out int TotalPage, out int TotalRecord) {SqlConnection conn = new SqlConnection (StrConn (); SqlCommand comm = new SqlCommand ("GetDataList", conn ); comm. parameters. add (new SqlParameter ("@ TableName", SqlDbType. NVarChar, 100 )). value = TblName; comm. parameters. add (new SqlParameter ("@ Fields", SqlDbType. NVarChar, 1000 )). value = Fields; comm. parameters. add (new SqlParameter ("@ OrderField", SqlDbType. NVarChar, 1000 )). value = OrderField; comm. parameters. add (new SqlParameter ("@ OrderType", SqlDbType. NVarChar, 1000 )). value = OrderType; comm. parameters. add (new SqlParameter ("@ sqlWhere", SqlDbType. NVarChar, 1000 )). value = SqlWhere; comm. parameters. add (new SqlParameter ("@ pageSize", SqlDbType. int )). value = PageSize; comm. parameters. add (new SqlParameter ("@ pageIndex", SqlDbType. int )). value = pageIndex; comm. parameters. add (new SqlParameter ("@ TotalPage", SqlDbType. int); comm. parameters ["@ TotalPage"]. direction = ParameterDirection. output; // obtain the Output parameter value comm. parameters. add (new SqlParameter ("@ totalRecord", SqlDbType. int); comm. parameters ["@ totalRecord"]. direction = ParameterDirection. output; comm. commandType = CommandType. storedProcedure; SqlDataAdapter dataAdapter = new SqlDataAdapter (comm); DataSet ds = new DataSet (); dataAdapter. fill (ds); TotalPage = (int) comm. parameters ["@ TotalPage"]. value; TotalRecord = (int) comm. parameters ["@ totalRecord"]. value; conn. close (); conn. dispose (); comm. dispose (); return ds ;}# endregion}The above general SQL stored procedure paging and asp.net background call method is all the content shared by xiaobian. I hope to give you a reference, and I hope you can provide more support for the customer's house.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
