CREATE PROCEDURE Sp_page @strTable VARCHAR(50),--表名
@strColumn VARCHAR(50),--按该列来进行分页
@intColType INT,
--@strColumn列的类型,0-数字类型,1-字符类型,2-日期时间类型
@intOrder BIT,--排序,0-顺序,1-倒序
@strColumnlist VARCHAR(800),
--要查询出的字段列表,*表示全部字段
@intPageSize INT,--每页记录数
@intPageNum INT,--指定页
@strWhere VARCHAR(800),--查询条件
@intPageCount INT output --总页数
AS
DECLARE @sql NVARCHAR(4000) --用于构造SQL语句
DECLARE @where1 VARCHAR(800) --构造条件语句
DECLARE @where2 VARCHAR(800) --构造条件语句
IF @strWhere IS NULL
OR Rtrim(@strWhere) = ''
-- 为了避免SQL关键字与字段、表名等连在一起,首先为传入的变量添加空格
BEGIN --没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN --有查询条件
SET @where1=' WHERE (' + @strWhere + ') AND '
SET @where2=' WHERE (' + @strWhere + ') '
END
SET @strColumn = ' ' + @strColumn + ' '
SET @strColumnlist = ' ' + @strColumnlist + ' '
--构造SQL语句,计算总页数。计算公式为 总页数 = Ceiling ( 记录个数 / 页大小 )
SET @sql='SELECT @intPageCount=CEILING((COUNT(*)+0.0)/'
+ Cast(@intPageSize AS VARCHAR) + ') FROM '
+ @strTable + @where2
--执行SQL语句,计算总页数,并将其放入@intPageCount变量中
EXEC Sp_executesql
@sql,
N'@intPageCount int OUTPUT',
@intPageCount output
--将总页数放到查询返回记录集的第一个字段前,此语句可省略
SET @strColumnlist= Cast(@intPageCount AS VARCHAR(30))
+ ' as PageCount,' + @strColumnlist
IF @intOrder = 0 --构造升序的SQL
SET @sql='SELECT TOP '
+ Cast(@intPageSize AS VARCHAR) + ' '
+ @strColumnlist + ' FROM ' + @strTable + @where1
+ @strColumn + '>(SELECT MAX(' + @strColumn + ') '
+ ' FROM (SELECT TOP '
+ Cast(@intPageSize*(@intPageNum - 1) AS VARCHAR)
+ @strColumn + ' FROM ' + @strTable + @where2
+ 'ORDER BY ' + @strColumn + ') t) ORDER BY '
+ @strColumn
ELSE --构造降序的SQL
SET @sql='SELECT TOP '
+ Cast(@intPageSize AS VARCHAR) + ' '
+ @strColumnlist + ' FROM ' + @strTable + @where1
+ @strColumn + '<(SELECT MIN(' + @strColumn + ') '
+ ' FROM (SELECT TOP '
+ Cast(@intPageSize*(@intPageNum - 1) AS VARCHAR)
+ @strColumn + ' FROM ' + @strTable + @where2
+ 'ORDER BY ' + @strColumn
+ ' DESC) t) ORDER BY ' + @strColumn + ' DESC'
IF @intPageNum = 1--第一页
SET @sql='SELECT TOP '
+ Cast(@intPageSize AS VARCHAR(10)) + ' '
+ @strColumnlist + ' FROM ' + @strTable + @where2
+ ' ORDER BY ' + @strColumn + CASE @intOrder WHEN 0 THEN '' ELSE
' DESC' END
--PRINT @sql
EXEC(@sql)
go
存储过程使用示例
DECLARE @intPageCount INT
EXEC Sp_page
'stuInfo',--表名
'stuNO',--按该列来进行分页
0,-- 0-数字类型,1-字符类型,2-日期时间类型
0,-- 0-顺序,1-倒序
'*',--要查询出的字段列表,*表示全部字段
2,--每页记录数
1,--指定页
'',--查询条件
@intPageCount output
PRINT @intPageCount
数据库表:
数据库表
项目的数据模块图:
项目结构图
BLL层:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WebApplication3.DAL;
using WebApplication3.Model;
namespace WebApplication3.BLL
{
public class UserInfoBLL
{
UserInfoDAL userDal = new UserInfoDAL();
public object loadData(UserInfo user)
{
return userDal.loadData(user);
}
public int Count()
{
int count = UserInfoDAL.count;
return count;
}
}
}
DAL层:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using WebApplication3.Model;
namespace WebApplication3.DAL
{
public class UserInfoDAL
{
SQLHelper sqlHelp = new SQLHelper();
public static int count;
public object loadData(UserInfo user)
{
SqlParameter[] paraValues = {
new SqlParameter("@strTable",SqlDbType.VarChar),
new SqlParameter("@strColumn",SqlDbType.VarChar),
new SqlParameter("@intColType",SqlDbType.Int),
new SqlParameter("@intOrder",SqlDbType.Int),
new SqlParameter("@strColumnlist",SqlDbType.VarChar),
new SqlParameter("@intPageSize",SqlDbType.Int),
new SqlParameter("@intPageNum",SqlDbType.Int),
new SqlParameter("@strWhere",SqlDbType.VarChar),
new SqlParameter("@intPageCount",SqlDbType.Int)
};
paraValues[0].Value = user.tableName;
paraValues[1].Value = user.Column;
paraValues[2].Value = user.ColType;
paraValues[3].Value = user.Order;
paraValues[4].Value =user.Columnlist;
paraValues[5].Value = user.PageSize;
paraValues[6].Value = user.PageNum;
paraValues[7].Value = user.Where;
paraValues[8].Direction = ParameterDirection.Output;
object obj = sqlHelp.ExecuteProcTable("sp_page", paraValues);
count = Convert.ToInt32(paraValues[8].Value);
return obj;
}
}
}
Model层(Entity类):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace WebApplication3.Model
{
public class UserInfo
{
public string tableName { get; set; }
public string Column { get; set; }
public int ColType { get; set; }
public int Order { get; set; }
public string Columnlist { get; set; }
public int PageSize { get; set; }
public int PageNum { get; set; }
public string Where { get; set; }
//public string PageCount { get; set; }
}
}
UI层:前台代码(WebForm1.aspx)
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication3.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
<script type="text/javascript">
function integer() {
var num = document.getElementById("TextBox1").value;
if (num=="") {
alert('请输入内容');
return false;
}
if (!(/(^[1-9]\d*$)/.test(num))) {
alert('输入的不是正整数');
return false;
} else {
return true;
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="stuNO" HeaderText="学号" />
<asp:BoundField DataField="stuName" HeaderText="姓名" />
</Columns>
</asp:GridView>
<div>
<asp:Button ID="btnFirst" runat="server" OnClick="btnFirst_Click" Text="首页" style="height: 21px" />
<asp:Button ID="btnPre" runat="server" OnClick="btnPre_Click" Text="上一页" />
<asp:Button ID="btnNext" runat="server" OnClick="btnNext_Click" Text="下一页" style="height: 21px" />
<asp:Button ID="btnLast" runat="server" OnClick="btnLast_Click" Text="最后一页" />
<asp:TextBox ID="TextBox1" runat="server" Width="83px"></asp:TextBox>
<asp:Button ID="btnGO" runat="server" OnClientClick="integer()" OnClick="btnGO_Click" Text="跳转" />
</div>
<p>
<asp:Label ID="Label1" runat="server"></asp:Label>
<asp:Label ID="Label2" runat="server"></asp:Label>
<asp:Label ID="Label3" runat="server" Text="每页显示条数"></asp:Label>
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem>1</asp:ListItem>
<asp:ListItem Selected="True">2</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
</asp:DropDownList>
<asp:Button ID="btnOK" runat="server" OnClick="btnOK_Click" Text="确认" />
</p>
</form>
</body>
</html>
UI层:后台代码(WebForm1.aspx.cs)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using WebApplication3.BLL;
using WebApplication3.DAL;
using WebApplication3.Model;
namespace WebApplication3
{
public partial class WebForm1 : System.Web.UI.Page
{
UserInfoBLL userBll = new UserInfoBLL();
UserInfo user = new UserInfo();
int count = 1;
private void loadData()
{
count = Convert.ToInt32(ViewState["count"]);
user.tableName = "stuInfo";
user.Column = "stuNO";
user.ColType = 0;
user.Order = 0;
user.Columnlist = "*";
user.PageSize = Convert.ToInt32(DropDownList1.SelectedItem.Text);
user.PageNum = count;
user.Where = "";
GridView1.DataSource = userBll.loadData(user);
GridView1.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ViewState["count"] = 1;
loadData();
Label1.Text = "当前页:" + count;
Label2.Text = "总页数:" + userBll.Count();
}
}
protected void btnFirst_Click(object sender, EventArgs e)
{
ViewState["count"] = 1;
loadData();
Label1.Text = "当前页:" + count;
Label2.Text = "总页数:" + userBll.Count();
}
protected void btnPre_Click(object sender, EventArgs e)
{
if (Convert.ToInt32(ViewState["count"]) > 1)
{
ViewState["count"] = Convert.ToInt32(ViewState["count"]) - 1;
loadData();
Label1.Text = "当前页:" + count;
}
}
protected void btnNext_Click(object sender, EventArgs e)
{
if (Convert.ToInt32(ViewState["count"]) < userBll.Count())
{
ViewState["count"] = Convert.ToInt32(ViewState["count"]) + 1;
loadData();
Label1.Text = "当前页:" + count;
}
}
protected void btnLast_Click(object sender, EventArgs e)
{
ViewState["count"] = userBll.Count();
loadData();
Label1.Text = "当前页:" + count;
}
protected void btnGO_Click(object sender, EventArgs e)
{
try
{
ViewState["count"] = Convert.ToInt32(TextBox1.Text);
}
catch
{
return;
}
if (Convert.ToInt32(ViewState["count"]) <= userBll.Count())
{
loadData();
Label1.Text = "当前页:" + count;
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "", "alert('超出范围');", true);
}
}
protected void btnOK_Click(object sender, EventArgs e)
{
ViewState["count"] = 1;
loadData();
Label1.Text = "当前页:" + count;
Label2.Text = "总页数:" + userBll.Count();
}
}
}
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace Webapp.DBHelper
{
class SQLHelper
{
private string connStr = null;
private SqlConnection conn = null;
private SqlTransaction trans = null;
public SQLHelper()
{
connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
conn = new SqlConnection(connStr);
}
/// <summary>
/// 打开数据库连接
/// </summary>
private void OpenDB()
{
try
{
if (conn != null && conn.State != ConnectionState.Open)
{
conn.Open();
}
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
private void CloseDB()
{
try
{
if (conn != null && conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 执行带参非查询SQL命令
/// </summary>
/// <param name="cmdtxt">带参非查询SQL命令</param>
/// <param name="parmas">参数对象数组</param>
/// <returns>影响的行数</returns>
private SqlCommand CreateSqlCommand(string sql, SqlParameter[] parmas = null, CommandType cmdType = CommandType.Text)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = cmdType;
cmd.CommandText = sql;
if (trans != null)
{
cmd.Transaction = trans;
}
if (parmas != null)
{
cmd.Parameters.AddRange(parmas);
}
return cmd;
}
/// <summary>
/// 执行一般非查询SQL命令
/// </summary>
/// <param name="cmdtxt">非查询SQL命令</param>
/// <returns>影响的行数</returns>
public int ExecuteSqlNonQuery(string sql)
{
OpenDB();
SqlCommand cmd = CreateSqlCommand(sql);
try
{
return cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (trans == null)
{
CloseDB();
}
}
}
/// <summary>
/// 根据SQL指令返回第一行第一列结果
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">cmd.Parameters.AddWithValue</param>
/// <returns>返回第一行第一列结果</returns>
public int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
{
OpenDB();
SqlCommand cmd = CreateSqlCommand(sql, parameters);
try
{
return cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (trans == null)
{
CloseDB();
}
}
}
/// <summary>
/// 执行不带参非查询存储过程
/// </summary>
/// <param name="proctxt">带参非查询存储过程名</param>
/// <returns>影响的行数</returns>
public int ExecuteProcNonQuery(string proc)
{
OpenDB();
SqlCommand cmd = CreateSqlCommand(proc, null, CommandType.StoredProcedure);
try
{
return cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (trans == null)
{
CloseDB();
}
}
}
/// <summary>
/// 执行带参非查询存储过程
/// </summary>
/// <param name="proctxt">带参非查询存储过程名</param>
/// <param name="parmas">参数对象数组</param>
/// <returns>影响的行数</returns>
public int ExecuteProcNonQuery(string proc, params SqlParameter[] parameters)
{
OpenDB();
SqlCommand cmd = CreateSqlCommand(proc, parameters, CommandType.StoredProcedure);
try
{
return cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (trans == null)
{
CloseDB();
}
}
}
/// <summary>
/// 执行一般查询SQL命令,返回DataReader对象
/// </summary>
/// <param name="cmdtxt">一般查询SQL命令</param>
/// <returns>返回DataReader对象</returns>
public SqlDataReader ExecuteSqlReader(string sql)
{
OpenDB();
SqlCommand cmd = CreateSqlCommand(sql);
try
{
return cmd.ExecuteReader();
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 执行带参查询SQL命令,返回DataReader对象
/// </summary>
/// <param name="cmdtxt">带参非查询SQL命令</param>
/// <param name="parmas">参数对象数组</param>
/// <returns>返回DataReader对象</returns>
public SqlDataReader ExecuteSqlReader(string sql, params SqlParameter[] parameters)
{
OpenDB();
SqlCommand cmd = CreateSqlCommand(sql, parameters);
try
{
return cmd.ExecuteReader();
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 执行不带参查询存储过程
/// </summary>
/// <param name="proctxt">不参查询存储过程名</param>
/// <returns>返回DataReader对象</returns>
public SqlDataReader ExecuteProcReader(string proc)
{
OpenDB();
SqlCommand cmd = CreateSqlCommand(proc, null, CommandType.StoredProcedure);
try
{
return cmd.ExecuteReader();
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 执行带参查询存储过程,返回DataReader对象
/// </summary>
/// <param name="cmdtxt">带参查询存储过程</param>
/// <param name="parmas">参数对象数组</param>
/// <returns>返回DataReader对象</returns>
public SqlDataReader ExecuteProcReader(string proc, params SqlParameter[] parameters)
{
OpenDB();
SqlCommand cmd = CreateSqlCommand(proc, parameters, CommandType.StoredProcedure);
try
{
return cmd.ExecuteReader();
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 执行普通汇总查询SQL命令,返回object对象
/// </summary>
/// <param name="cmdtxt">普通汇总查询SQL命令</param>
/// <returns>汇总结果,object对象</returns>
public object ExecuteScalar(string sql)
{
OpenDB();
SqlCommand cmd = CreateSqlCommand(sql);
try
{
return cmd.ExecuteScalar();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
CloseDB();
}
}
/// <summary>
/// 执行带参汇总查询SQL命令,返回object对象
/// </summary>
/// <param name="cmdtxt">带参汇总查询SQL命令</param>
/// <param name="parmas">参数对象数组</param>
/// <returns>汇总结果,object对象</returns>
public object ExecuteScalar(string sql, params SqlParameter[] parameters)
{
OpenDB();
SqlCommand cmd = CreateSqlCommand(sql, parameters);
try
{
return cmd.ExecuteScalar();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
CloseDB();
}
}
/// <summary>
/// 执行不带参汇总查询存储过程,返回object对象
/// </summary>
/// <param name="cmdtxt">存储过程名</param>
/// <returns>汇总结果,object对象</returns>
public object ExecuteProcScalar(string proc)
{
OpenDB();
SqlCommand cmd = CreateSqlCommand(proc, null, CommandType.StoredProcedure);
try
{
return cmd.ExecuteScalar();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
CloseDB();
}
}
/// <summary>
/// 执行带参汇总查询存储过程,返回object对象
/// </summary>
/// <param name="cmdtxt">存储过程名</param>
/// <param name="parmas">参数对象数组</param>
/// <returns>汇总结果,object对象</returns>
public object ExecuteProcScalar(string proc, params SqlParameter[] parameters)
{
OpenDB();
SqlCommand cmd = CreateSqlCommand(proc, parameters, CommandType.StoredProcedure);
try
{
return cmd.ExecuteScalar();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
CloseDB();
}
}
/// <summary>
/// 执行普通查询SQL命令,返回结果集DataTable
/// </summary>
/// <param name="cmdtxt">普通查询SQL命令</param>
/// <returns>结果集DataTable</returns>
public DataTable ExecuteTable(string sql)
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = CreateSqlCommand(sql);
DataTable dt = new DataTable();
try
{
da.Fill(dt);
return dt;
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 执行带参查询SQL命令,返回结果集DataTable
/// </summary>
/// <param name="cmdtxt">带参查询SQL命令</param>
/// <param name="parmas">参数对象数组</param>
/// <returns>结果集DataTable</returns>
public DataTable ExecuteSqlTable(string sql, params SqlParameter[] parameters)
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = CreateSqlCommand(sql, parameters);
DataTable dt = new DataTable();
try
{
da.Fill(dt);
return dt;
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 执行不带参查询存储过程,返回结果集DataTable
/// </summary>
/// <param name="cmdtxt">存储过程名</param>
/// <returns>结果集DataTable</returns>
public DataTable ExecuteProcTable(string proc)
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = CreateSqlCommand(proc, null, CommandType.StoredProcedure);
DataTable dt = new DataTable();
try
{
da.Fill(dt);
return dt;
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 执行带参查询存储过程,返回结果集DataTable
/// </summary>
/// <param name="cmdtxt">存储过程名</param>
/// <param name="parmas">参数对象数组</param>
/// <returns>结果集DataTable</returns>
public DataTable ExecuteProcTable(string proc, params SqlParameter[] parameters)
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = CreateSqlCommand(proc, parameters, CommandType.StoredProcedure);
DataTable dt = new DataTable();
try
{
da.Fill(dt);
return dt;
}
catch (SqlException ex)
{
throw ex;
}
}
public DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
{
//conn = new SqlConnection(connStr);
OpenDB();
SqlCommand cmd = CreateSqlCommand(sql, parameters);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
try
{
adapter.Fill(dataset);
return dataset.Tables[0];
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 开始一个事务
/// </summary>
public void BeginSqlTranscation()
{
OpenDB();
trans = conn.BeginTransaction();
}
/// <summary>
/// 提交一个事务
/// </summary>
public void CommitSqlTranscation()
{
trans.Commit();
CloseDB();
trans = null;
}
/// <summary>
/// 事务回滚
/// </summary>
public void RollBackSqlTranscation()
{
trans.Rollback();
CloseDB();
trans = null;
}
}
}