創建表格并添加300萬數據
| use StoredCREATE TABLE UserInfo( --創建表id int IDENTITY(1,1) PRIMARY KEY not null,--添加主鍵和標識列UserName varchar(50) ) declare @i int --添加3百萬數據，大概4分鐘時間set @i=1while @i<3000000begininsert into UserInfo (UserName) values(@i)set @i=@i+1end |
存儲過程T-SQL
| create PROCEDURE [dbo].[GetDataList]( @TableName varchar(5000), --表名 @Fields varchar(5000) = '*', --字段名(全部字段為*) @OrderField varchar(5000), --排序字段(必須!支持多字段) @OrderType varchar(5000), --排序類型 @sqlWhere varchar(5000) = Null, --條件語句(不用加where) @pageSize int, --每頁多少條記錄 @pageIndex int = 1 , --指定當前為第幾頁 @TotalPage int output, --返回總頁數 @totalRecord int output --計算總記錄數 --返回總記錄數 )asbegin Begin Tran --開始事務 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--計算總記錄數 --計算總頁數 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 --處理頁數超出范圍情況 if @PageIndex<=0 Set @pageIndex = 1 if @pageIndex>@TotalPage Set @pageIndex = @TotalPage --處理開始點和結束點 Declare @StartRecord int Declare @EndRecord int set @StartRecord = (@pageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @pageSize - 1 --繼續合成sql語句 set @Sql = @Sql + ') as ' + @TableName + ' where rowid between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) --print @Sql Exec(@Sql) --------------------------------------------------- If @@Error <> 0 Begin RollBack Tran Return -1 End Else Begin Commit Tran Return @totalRecord ---返回記錄總數 End end--exec GetDataList 'Userinfo','*','id','desc','',10,1,3,3000000 |
前臺頁面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"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title></title></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server"> </asp:GridView> <asp:Label ID="lbl_page" runat="server" Text="Label"></asp:Label> </div> </form></body></html> |
