通用SQL分页程序
近来在网上找了一些分页程序，大部分都是基于SQL存储过程的,在实现上有一定的局限性，其中有一片文章我比较喜欢是基于SQL命令的，但是在实现代码有一点点不合理，我做了一些改进放上来给大家看看，希望能帮得上大家。
1
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DefaultDataGrid_02.aspx.cs" Inherits="数据分页DEMO_DefaultDataGrid" %>
2
3<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4
5<html xmlns="http://www.w3.org/1999/xhtml" >
6<head runat="server">
7 <title>无标题页</title>
8</head>
9<body>
10 <form id="form1" runat="server">
11 <div>
12<asp:LinkButton id="lbtnFirst" Font-Size="Smaller" Runat="server" OnClick="lbtnFirst_Click">首頁</asp:LinkButton>
13<asp:LinkButton id="lbtnBack" Font-Size="Smaller" Runat="server" OnClick="lbtnBack_Click">上頁</asp:LinkButton>
14<asp:LinkButton id="lbtnNext" Font-Size="Smaller" Runat="server" OnClick="lbtnNext_Click">下頁</asp:LinkButton>
15<asp:LinkButton id="lbtnLast" Font-Size="Smaller" Runat="server" OnClick="lbtnLast_Click">尾頁</asp:LinkButton>
16<asp:Label id="Label1" Font-Size="Smaller" runat="server">当前页:</asp:Label>
17<asp:Label id="lblCurrentPage" Font-Size="Smaller" runat="server">1</asp:Label>
18<asp:Label id="Label2" Font-Size="Smaller" runat="server">总页:</asp:Label>
19<asp:Label id="lblPageCount" Font-Size="Smaller" runat="server">200</asp:Label>
20<asp:Label id="Label3" Font-Size="Smaller" runat="server">跳转:</asp:Label>
21<asp:TextBox id="txtToPage" Font-Size="Smaller" runat="server" Width="88px"></asp:TextBox>
22<asp:Button id="btnToPage" Font-Size="Smaller" runat="server" Text="go" OnClick="btnToPage_Click"></asp:Button>
23
24<asp:DataGrid id="DataGrid1" runat="server" CellPadding="4" ForeColor="#333333"
25 GridLines="None" Font-Bold="False" Font-Italic="False"
26 Font-Names="幼圆" Font-Overline="False" Font-Size="Smaller"
27 Font-Strikeout="False" Font-Underline="False" HorizontalAlign="Left">
28 <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
29 <EditItemStyle BackColor="#7C6F57" />
30 <SelectedItemStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
31 <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
32 <AlternatingItemStyle BackColor="White" />
33 <ItemStyle BackColor="#E3EAEB" />
34 <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
35</asp:DataGrid>
36
37
38 </div>
39 </form>
40</body>
41</html>
42
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DefaultDataGrid_02.aspx.cs" Inherits="数据分页DEMO_DefaultDataGrid" %>2
3
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">4
5
<html xmlns="http://www.w3.org/1999/xhtml" >6
<head runat="server">7
<title>无标题页</title>8
</head>9
<body>10
<form id="form1" runat="server">11
<div>12
<asp:LinkButton id="lbtnFirst" Font-Size="Smaller" Runat="server" OnClick="lbtnFirst_Click">首頁</asp:LinkButton>13
<asp:LinkButton id="lbtnBack" Font-Size="Smaller" Runat="server" OnClick="lbtnBack_Click">上頁</asp:LinkButton>14
<asp:LinkButton id="lbtnNext" Font-Size="Smaller" Runat="server" OnClick="lbtnNext_Click">下頁</asp:LinkButton>15
<asp:LinkButton id="lbtnLast" Font-Size="Smaller" Runat="server" OnClick="lbtnLast_Click">尾頁</asp:LinkButton>16
<asp:Label id="Label1" Font-Size="Smaller" runat="server">当前页:</asp:Label>17
<asp:Label id="lblCurrentPage" Font-Size="Smaller" runat="server">1</asp:Label>18
<asp:Label id="Label2" Font-Size="Smaller" runat="server">总页:</asp:Label>19
<asp:Label id="lblPageCount" Font-Size="Smaller" runat="server">200</asp:Label>20
<asp:Label id="Label3" Font-Size="Smaller" runat="server">跳转:</asp:Label>21
<asp:TextBox id="txtToPage" Font-Size="Smaller" runat="server" Width="88px"></asp:TextBox>22
<asp:Button id="btnToPage" Font-Size="Smaller" runat="server" Text="go" OnClick="btnToPage_Click"></asp:Button>23
24
<asp:DataGrid id="DataGrid1" runat="server" CellPadding="4" ForeColor="#333333" 25
GridLines="None" Font-Bold="False" Font-Italic="False" 26
Font-Names="幼圆" Font-Overline="False" Font-Size="Smaller" 27
Font-Strikeout="False" Font-Underline="False" HorizontalAlign="Left">28
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />29
<EditItemStyle BackColor="#7C6F57" />30
<SelectedItemStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />31
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />32
<AlternatingItemStyle BackColor="White" />33
<ItemStyle BackColor="#E3EAEB" />34
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />35
</asp:DataGrid>36
37
38
</div>39
</form>40
</body>41
</html>42
1using System;
2using System.Data;
3using System.Configuration;
4using System.Collections;
5using System.Web;
6using System.Web.Security;
7using System.Web.UI;
8using System.Web.UI.WebControls;
9using System.Web.UI.WebControls.WebParts;
10using System.Web.UI.HtmlControls;
11using System.Data.SqlClient;
12using System.Text;
13public partial class 数据分页DEMO_DefaultDataGrid : System.Web.UI.Page
14
{
15
protected void Page_Load(object sender, EventArgs e)
16
{
17 if (!Page.IsPostBack)
18 {
19 ShowData(Convert.ToInt64(this.lblCurrentPage.Text),"报关进度表","运单号","ASC",10);
20
}
21 }
22 private void ShowData(long page,string tableName,string fieldName,
23 string orderString,int GridSize)
24 {
25
26
27 /**/
28 /*-------------------设置参数------------------*/
29 //指定要排序的表
30 //比如报关进度表
31 string tblName = tableName;
32 //指定要排序的字段
33 //比如"运单号,收货公司"
34 string fldName = fieldName;
35 //指定要使用的排序方式
36 //比如ASC DES
37 string orderStr = orderString;
38 //指定分页大小
39 int PageSize = GridSize;
40
41 /**/
42 /*-------------------设置结束------------------*/
43
44 string conStr = ConfigurationManager.AppSettings["SQLDB"].ToString();
45
46
47
48 SqlConnection conn = new SqlConnection(conStr);
49 conn.Open();
50 SqlCommand cmd = new SqlCommand("select count(*) from " + tblName, conn);
51 //得到总页数
52 lblPageCount.Text = Convert.ToString(((int)cmd.ExecuteScalar() / PageSize + 1));
53
54 //排除的记录部分
55 StringBuilder TopSql = new StringBuilder();
56 TopSql.AppendFormat("select top {0} {1} from {2} order by {3} {4}",
57 Convert.ToString((page - 1) * PageSize),
58 fldName, tblName, fldName,orderStr
59 );
60 //得到排除记录里的最大ID号
61 StringBuilder MaxSql = new StringBuilder();
62 MaxSql.AppendFormat("select max({0}) from ({1}) as t",fldName,TopSql.ToString());
63 //得到最终结果
64 StringBuilder sql = new StringBuilder();
65 sql.AppendFormat("select top {0} * from {1} where {2}>({3}) order by {4} {5}",
66 PageSize.ToString(), tblName, fldName, MaxSql.ToString(), fldName,
67 orderStr
68 );
69
70
71 if (page == 1)
72 {
73 sql = new StringBuilder();
74 sql.AppendFormat("select top {0} * from {1}",
75 PageSize,tblName,lblCurrentPage.Text);
76 }
77
78 try
79 {
80 SqlDataAdapter da = new SqlDataAdapter(sql.ToString(), conn);
81 System.Data.DataSet ds = new DataSet();
82 da.Fill(ds);
83 DataGrid1.DataSource = ds.Tables[0].DefaultView;
84 DataGrid1.DataBind();
85
86 conn.Close();
87
88 }
89 catch (Exception ex)
90 {
91 Response.Write(ex.Message.ToString());
92 }
93
94 }
95
96
97 protected void lbtnFirst_Click(object sender, EventArgs e)
98 {
99 lblCurrentPage.Text = "1";
100 ShowData(1, "报关进度表", "运单号", "ASC", 10);
101
102 }
103 protected void lbtnBack_Click(object sender, EventArgs e)
104 {
105 if (lblCurrentPage.Text != "1")
106 {
107 lblCurrentPage.Text = Convert.ToString(Convert.ToInt64(lblCurrentPage.Text) - 1);
108 ShowData(Convert.ToInt64(lblCurrentPage.Text), "报关进度表", "运单号", "ASC", 10);
109 }
110 }
111 protected void lbtnNext_Click(object sender, EventArgs e)
112 {
113 if (lblCurrentPage.Text != lblPageCount.Text)
114 {
115 lblCurrentPage.Text = Convert.ToString(Convert.ToInt64(lblCurrentPage.Text) + 1);
116 ShowData(Convert.ToInt64(lblCurrentPage.Text), "报关进度表", "运单号", "ASC", 10);
117 }
118
119 }
120 protected void lbtnLast_Click(object sender, EventArgs e)
121 {
122 lblCurrentPage.Text = lblPageCount.Text;
123 ShowData(Convert.ToInt64(lblPageCount.Text), "报关进度表", "运单号", "ASC", 10);
124 }
125 protected void btnToPage_Click(object sender, EventArgs e)
126 {
127 if (Convert.ToInt64(txtToPage.Text.Trim()) > 0 && Convert.ToInt64(txtToPage.Text.Trim()) < Convert.ToInt64(lblPageCount.Text))
128 {
129 lblCurrentPage.Text = txtToPage.Text;
130 ShowData(Convert.ToInt64(txtToPage.Text),"报关进度表", "运单号", "ASC", 10);
131 }
132
133 }
134
}
135
using System;2
using System.Data;3
using System.Configuration;4
using System.Collections;5
using System.Web;6
using System.Web.Security;7
using System.Web.UI;8
using System.Web.UI.WebControls;9
using System.Web.UI.WebControls.WebParts;10
using System.Web.UI.HtmlControls;11
using System.Data.SqlClient;12
using System.Text;13
public partial class 数据分页DEMO_DefaultDataGrid : System.Web.UI.Page14
{
15
protected void Page_Load(object sender, EventArgs e)16
{
17
if (!Page.IsPostBack)18
{19
ShowData(Convert.ToInt64(this.lblCurrentPage.Text),"报关进度表","运单号","ASC",10);20
}21
}22
private void ShowData(long page,string tableName,string fieldName,23
string orderString,int GridSize)24
{25
26
27
/**/28
/*-------------------设置参数------------------*/29
//指定要排序的表30
//比如报关进度表31
string tblName = tableName;32
//指定要排序的字段33
//比如"运单号,收货公司"34
string fldName = fieldName;35
//指定要使用的排序方式36
//比如ASC DES37
string orderStr = orderString;38
//指定分页大小39
int PageSize = GridSize;40
41
/**/42
/*-------------------设置结束------------------*/43
44
string conStr = ConfigurationManager.AppSettings["SQLDB"].ToString();45
46
47
48
SqlConnection conn = new SqlConnection(conStr);49
conn.Open();50
SqlCommand cmd = new SqlCommand("select count(*) from " + tblName, conn);51
//得到总页数52
lblPageCount.Text = Convert.ToString(((int)cmd.ExecuteScalar() / PageSize + 1));53
54
//排除的记录部分55
StringBuilder TopSql = new StringBuilder();56
TopSql.AppendFormat("select top {0} {1} from {2} order by {3} {4}",57
Convert.ToString((page - 1) * PageSize),58
fldName, tblName, fldName,orderStr59
);60
//得到排除记录里的最大ID号61
StringBuilder MaxSql = new StringBuilder();62
MaxSql.AppendFormat("select max({0}) from ({1}) as t",fldName,TopSql.ToString());63
//得到最终结果64
StringBuilder sql = new StringBuilder();65
sql.AppendFormat("select top {0} * from {1} where {2}>({3}) order by {4} {5}",66
PageSize.ToString(), tblName, fldName, MaxSql.ToString(), fldName,67
orderStr68
);69
70
71
if (page == 1) 72
{73
sql = new StringBuilder();74
sql.AppendFormat("select top {0} * from {1}",75
PageSize,tblName,lblCurrentPage.Text); 76
}77
78
try79
{80
SqlDataAdapter da = new SqlDataAdapter(sql.ToString(), conn);81
System.Data.DataSet ds = new DataSet();82
da.Fill(ds);83
DataGrid1.DataSource = ds.Tables[0].DefaultView;84
DataGrid1.DataBind();85
86
conn.Close();87
88
}89
catch (Exception ex)90
{91
Response.Write(ex.Message.ToString());92
}93
94
}95
96
97
protected void lbtnFirst_Click(object sender, EventArgs e)98
{99
lblCurrentPage.Text = "1";100
ShowData(1, "报关进度表", "运单号", "ASC", 10);101
102
}103
protected void lbtnBack_Click(object sender, EventArgs e)104
{105
if (lblCurrentPage.Text != "1")106
{107
lblCurrentPage.Text = Convert.ToString(Convert.ToInt64(lblCurrentPage.Text) - 1);108
ShowData(Convert.ToInt64(lblCurrentPage.Text), "报关进度表", "运单号", "ASC", 10);109
}110
}111
protected void lbtnNext_Click(object sender, EventArgs e)112
{113
if (lblCurrentPage.Text != lblPageCount.Text)114
{115
lblCurrentPage.Text = Convert.ToString(Convert.ToInt64(lblCurrentPage.Text) + 1);116
ShowData(Convert.ToInt64(lblCurrentPage.Text), "报关进度表", "运单号", "ASC", 10);117
}118
119
}120
protected void lbtnLast_Click(object sender, EventArgs e)121
{122
lblCurrentPage.Text = lblPageCount.Text;123
ShowData(Convert.ToInt64(lblPageCount.Text), "报关进度表", "运单号", "ASC", 10);124
}125
protected void btnToPage_Click(object sender, EventArgs e)126
{127
if (Convert.ToInt64(txtToPage.Text.Trim()) > 0 && Convert.ToInt64(txtToPage.Text.Trim()) < Convert.ToInt64(lblPageCount.Text))128
{129
lblCurrentPage.Text = txtToPage.Text;130
ShowData(Convert.ToInt64(txtToPage.Text),"报关进度表", "运单号", "ASC", 10);131
}132
133
}134
}135
