Common SQL stored procedure paging and ASP.net background call methods _mysql

Create a table and add 3 million data

Use Stored CREATE TABLE
UserInfo (--Creating table
ID int IDENTITY (1,1) PRIMARY key NOT NULL,--add primary key and identity column
UserName varchar (m) 
)
 
DECLARE @i INT--Add 3 million data, about 4 minutes time
set @i=1 while
@i<3000000
begin
inserts into UserInfo (UserName) VALUES (@i)
set @i=@i+1
end

Stored Procedures T-SQL

Create PROCEDURE [dbo].
[Getdatalist] (@TableName varchar (5000),--table name @Fields varchar (5000) = ' * ',--field name (all fields are *) @OrderField varchar (5000),--sort field (must Have to! support multiple fields) @OrderType varchar (5000),--Sort type @sqlWhere varchar (5000) = Null,--conditional statement (without where) @pageSize int,-        -Number of records per page @pageIndex int = 1,--Specifies the current page @TotalPage int output,--Returns the total number of pages @totalRecord int output--Calculates total records 
  --Returns the total number of records) as begin begin 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 ' @totalRec Ord int OUTPUT ', @totalRecord output--total Record Count--Calculates the total number of pages select @TotalPage =ceiling ((@totalRecord +0.0)/@PageSiz E if (@SqlWhere = ' or @sqlWhere =null) Set @sql = ' SELECT * FROM (select Row_number () over () + @OrderField + ' + @Ordertype + ') as rowId, ' + @Fields + ' from ' + @TableName else set @sql = ' Select # from (select Row_number ()  OrderField + ' + @Ordertype + ') as rowId, ' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere--handling pages out of range if
  @PageIndex <=0 Set @pageIndex = 1 if @pageIndex > @TotalPage Set @pageIndex = @TotalPage--processing start and end points  Declare @StartRecord int Declare @EndRecord int set @StartRecord = (@pageIndex-1) * @PageSize + 1 Set @EndRecord = @StartRecord + @pageSize-1--continue to synthesize the SQL statement set @Sql = @Sql + ') as ' + @TableName + ' where rowid between ' + Convert (VA Rchar, @StartRecord) + ' and ' + Convert (varchar, @EndRecord)--print @Sql Exec (@Sql)------------------ 
    ---------------------------------If @ @Error <> 0 begin RollBack Tran Return-1 end Else begin Commit Tran return @totalRecord---Returns the total number of records end--exec getdatalist ' Userinfo ', ' * ', ' id ', ' desc ', ', ', 10,1,3,30 00000

Front 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 ">

 
 

Background 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;//per page Several records private int totalpage = 1;//Total number of pages private int totalrecord = 0;//Total record private string ordertype = "desc";//Sort By default Positive sequence 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["ConnString"].
  ToString (); //get way to get next page private void Getparams () {if (!
    String.IsNullOrEmpty (request["page")) {PageIndex = Convert.ToInt32 (request["page"));
    else {PageIndex = 1; #region Get the paging character public string Getdivpager (string querystring, DataSet ds) {StringBuilder sp = new Stringbu
    Ilder ();
    int totalcount = Totalrecord;
    int rowcount = Totalpage; if (ds!= null) {sp.
      AppendFormat ("<p> Total records: <span id=\" sum\ ">{0}</span>", totalcount); Sp. AppendFormat ("page: <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} ' > Home </a>", "? Page=1" + querystring); if (PageIndex > 1) {sp. AppendFormat ("<a href= ' {0} ' >< prev </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 >) {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 </a>", "? page=" + RowCount + querystring);
    else {ds = null; } return SP.
  ToString (); #endregion #region Get the paging data///<summary>///Get the paging data///</summary>///<param name= "Tblname" > Datasheet name </param>///<param name= "Fields" > Fields to read </param>///<param name= "OrderField" > Sort field & Lt;/param>/<param name= "OrderType" > Sort way </param>///<param name= "Sqlwhere" > Query conditions </param>///<param Name= "PageSize" > per-page display how many data </param>///<param name= "pageIndex" > Current page number </param>///<param name= " Totalpage "> return value, total number of pages </param>///<param name=" Totalrecord "> return value, 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 Sq
    Lconnection (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;//gets out of the 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}

Above this general SQL stored procedure paging and ASP.net background call method is small series to share all the content, hope to give you a reference, but also hope that we support cloud habitat community.