創建表格并添加300萬數據
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
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 ---返回記錄總數 Endend--exec GetDataList 'Userinfo','*','id','desc','',10,1,3,3000000 |
前臺頁面Default2.aspx
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
<%@ 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"><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> |
后臺CS代碼Default2.aspx.cs
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
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;//當前頁碼 private int PageSize = 50;//每頁幾條記錄 private int TotalRecord = 0;//總記錄 private string OrderType = " desc";//排序方式 默認正序 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); } } //數據庫連接字符 public static string StrConn() { //return string.Format("{0}","server=.;database=Stored;user=sa;password=123456"); return ConfigurationSettings.AppSettings["ConnString"].ToString(); } //Get方式獲得下一頁 private void GetParams() { if (!String.IsNullOrEmpty(Request["page"])) { PageIndex = Convert.ToInt32(Request["Page"]); } else { PageIndex = 1; } } #region 獲得分頁字符 public string GetDivPager(string queryString, DataSet ds) { StringBuilder sp = new StringBuilder(); int TotalCount = TotalRecord; int rowCount = TotalPage; if (ds != null) { sp.AppendFormat(" <p>總記錄：<span id=\"sum\">{0}</span>", TotalCount); sp.AppendFormat(" 頁碼：<em><b id=\"current\">{0}</b>/<span id=\"count\">{1}</span></em> ", PageIndex, rowCount); sp.AppendFormat(" 每頁：<span id=\"eachPage\">{0}</span></p> ", PageSize); sp.AppendFormat(" <a href='{0}'>首頁</a> ", "?page=1" + queryString); if (PageIndex > 1) { sp.AppendFormat(" <a href='{0}'>< 上一頁 </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}'>下一頁 ></a>", "?page=" + (PageIndex + 1) + queryString); } sp.AppendFormat(" <a href='{0}'>尾頁</a>", "?page=" + rowCount + queryString); } else { ds = null; } return sp.ToString(); } #endregion #region 獲取分頁的數據 /// <summary> /// 獲取分頁的數據 /// </summary> /// <param name="TblName">數據表名</param> /// <param name="Fields">要讀取的字段</param> /// <param name="OrderField">排序字段</param> /// <param name="OrderType">排序方式</param> /// <param name="SqlWhere">查詢條件</param> /// <param name="PageSize">每頁顯示多少條數據</param> /// <param name="pageIndex">當前頁碼</param> /// <param name="TotalPage">返回值，共有多少頁</param> /// <param name="TotalRecord">返回值，總有多少條記錄</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;//獲得out出來的參數值 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} |
以上這篇通用SQL存儲過程分頁以及asp.net后臺調用的方法就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持服務器之家。
