前用到的两个分页存储过程，需要的朋友可以参考下。
第一个,取得数据总行数
代码如下:
set ansi_nulls on
set quoted_identifier on
go
alter procedure [sq8reyoung].[fenye_num]
(
@tablenames nvarchar(200),
@filter nvarchar(200))
as
if @filter = ''
set @filter = ' where 1=1'
else
set @filter = ' where ' + @filter
execute('select count(*) from '+@tablenames+' '+@filter)
第二个取得分页数据
代码如下:
set ansi_nulls on
set quoted_identifier on
go
alter procedure [sq8reyoung].[fenye]
@tablenames varchar(200), --表名，可以是多个表，但不能用别名
@primarykey varchar(100), --主键，可以为空，但@order为空时该值不能为空
@fields varchar(200), --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@pagesize int, --每页记录数
@currentpage int, --当前页，0表示第1页
@filter varchar(200) = '', --条件，可以为空，不用填 where
@group varchar(200) = '', --分组依据，可以为空，不用填 group by
@order varchar(200) = '' --排序，可以为空，为空默认按主键升序排列，不用填 order by
as
begin
declare @sortcolumn varchar(200)
declare @operator char(2)
declare @sorttable varchar(200)
declare @sortname varchar(200)
if @fields = ''
set @fields = '*'
if @filter = ''
set @filter = 'where 1=1'
else
set @filter = 'where ' + @filter
if @group ''
set @group = 'group by ' + @group
if @order ''
begin
declare @pos1 int, @pos2 int
set @order = replace(replace(@order, ' asc', ' asc'), ' desc', ' desc')
if charindex(' desc', @order) > 0
if charindex(' asc', @order) > 0
begin
if charindex(' desc', @order) set @operator = 'else
set @operator = '>='
end
else
set @operator = 'else
set @operator = '>='
set @sortcolumn = replace(replace(replace(@order, ' asc', ''), ' desc', ''), ' ', '')
set @pos1 = charindex(',', @sortcolumn)
if @pos1 > 0
set @sortcolumn = substring(@sortcolumn, 1, @pos1-1)
set @pos2 = charindex('.', @sortcolumn)
if @pos2 > 0
begin
set @sorttable = substring(@sortcolumn, 1, @pos2-1)
if @pos1 > 0
set @sortname = substring(@sortcolumn, @pos2+1, @pos1-@pos2-1)
else
set @sortname = substring(@sortcolumn, @pos2+1, len(@sortcolumn)-@pos2)
end
else
begin
set @sorttable = @tablenames
set @sortname = @sortcolumn
end
end
else
begin
set @sortcolumn = @primarykey
set @sorttable = @tablenames
set @sortname = @sortcolumn
set @order = @sortcolumn
set @operator = '>='
end
declare @type varchar(50)
declare @prec int
select @type=t.name, @prec=c.prec
from sysobjects o
join syscolumns c on o.id=c.id
join systypes t on c.xusertype=t.xusertype
where o.name = @sorttable and c.name = @sortname
if charindex('char', @type) > 0
set @type = @type + '(' + cast(@prec as varchar) + ')'
declare @toprows int
set @toprows = @pagesize * @currentpage + 1
print @type
declare @sql nvarchar(4000)
set @sql = 'declare @sortcolumnbegin ' + @type + '
set rowcount ' + cast(@toprows as varchar(10))+ ' select @sortcolumnbegin=' +
@sortcolumn + ' from ' + @tablenames + ' ' + @filter + ' ' + @group + ' order by ' + @order + '
set rowcount ' + cast(@pagesize as varchar(10)) + '
select ' + @fields + ' from ' + @tablenames + ' ' + @filter + ' and ' + @sortcolumn + '' + @operator + '@sortcolumnbegin ' + isnull(@group,'') + ' order by ' + @order + ''
-- print(@sql)
exec(@sql)
end
以及实现此方法的数据操作类
代码如下:
using system;
using system.collections.generic;
using system.text;
using system.data.sqlclient ;
using system.data;
using system.configuration;
using wuqi.webdiyer;
using models;
namespace dal
{
public class dbhelper
{
public static readonly string conn_string = configurationmanager.connectionstrings[rymedicalconnectionstring].connectionstring;
public static sqldatareader getreader(string safesql)
{
sqlconnection conn = new sqlconnection(conn_string);
sqlcommand cmd = new sqlcommand(safesql, conn);
sqldatareader reader = cmd.executereader(commandbehavior.closeconnection);
reader.close();
return reader;
}
public static sqldatareader getreader(string sql, params sqlparameter[] values)
{
sqlconnection conn = new sqlconnection(conn_string);
sqlcommand cmd = new sqlcommand(sql, conn);
conn.open();
cmd.parameters.addrange(values);
sqldatareader reader = cmd.executereader(commandbehavior.closeconnection);
reader.close();
conn.close();
return reader;
}
public static datatable getdataset(string safesql)
{
sqlconnection conn = new sqlconnection(conn_string);
dataset ds = new dataset();
sqlcommand cmd = new sqlcommand(safesql, conn);
conn.open();
sqldataadapter da = new sqldataadapter(cmd);
da.fill(ds);
conn.close();
return ds.tables[0];
}
public static datatable getdataset(commandtype cmdtype, string cmdtext, params sqlparameter[] cmdparms)
{
sqlconnection conn = new sqlconnection(conn_string);
dataset ds = new dataset();
sqlcommand cmd = new sqlcommand(cmdtext, conn);
conn.open();
preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
sqldataadapter da = new sqldataadapter(cmd);
da.fill(ds);
conn.close();
return ds.tables[0];
}
public static sqldatareader executereader(commandtype cmdtype, string cmdtext, params sqlparameter[] cmdparms)
{
sqlcommand cmd = new sqlcommand();
sqlconnection conn = new sqlconnection(conn_string);
conn.open();
preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
sqldatareader rdr = cmd.executereader(commandbehavior.closeconnection);
cmd.parameters.clear();
rdr.close();
conn.close();
return rdr;
}
public static object executescalar(commandtype cmdtype, string cmdtext, params sqlparameter[] cmdparms)
{
sqlcommand cmd = new sqlcommand();
using (sqlconnection conn = new sqlconnection(conn_string))
{
conn.open();
preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
object val = cmd.executescalar();
cmd.parameters.clear();
conn.close();
return val;
}
}
public static object executescalar(sqlconnection conn, commandtype cmdtype, string cmdtext, params sqlparameter[] cmdparms)
{
sqlcommand cmd = new sqlcommand();
preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
object val = cmd.executescalar();
cmd.parameters.clear();
return val;
}
private static void preparecommand(sqlcommand cmd, sqlconnection conn, sqltransaction trans, commandtype cmdtype, string cmdtext, sqlparameter[] cmdparms)
{
if (conn.state != connectionstate.open)
conn.open();
cmd.connection = conn;
cmd.commandtext = cmdtext;
if (trans != null)
cmd.transaction = trans;
cmd.commandtype = cmdtype;
if (cmdparms != null)
{
foreach (sqlparameter parm in cmdparms)
cmd.parameters.add(parm);
}
}
public static void executenonquery(string sql)
{
sqlconnection conn = new sqlconnection(conn_string);
sqlcommand cmd = new sqlcommand(sql,conn);
conn.open();
cmd.executenonquery();
conn.close();
}
///
/// 传入sql语句,返回int
///
///
///
public static int excutecommand(string sql)
{
sqlconnection conn = new sqlconnection(conn_string);
sqlcommand cmd = new sqlcommand(sql, conn);
conn.open();
int result = cmd.executenonquery();
conn.close();
return result;
}
///
///
///
/// 需要分页的表明
/// 主键名
/// 需要取出的字段,留空则为*
/// 每页的记录数
/// 当前页
/// 条件，可以为空，不用填 where
/// 分组依据，可以为空，不用填 group by
/// 排序，可以为空，为空默认按主键升序排列，不用填 order by
///
public static datatable pagedlist(string name, string pk, string fields, int pagesize, int currentpage, string filter, string group, string order)
{
currentpage = currentpage - 1;
datatable dt = getdataset(commandtype.storedprocedure, fenye,
new sqlparameter(@tablenames, name),
new sqlparameter(@primarykey, pk),
new sqlparameter(@fields, fields),
new sqlparameter(@pagesize, pagesize),
new sqlparameter(@currentpage, currentpage),
new sqlparameter(@filter, filter),
new sqlparameter(@group, group),
new sqlparameter(@order, order)
);
return dt;
}
public static int fenye_num(string name, string filter)
{
return (int)executescalar(commandtype.storedprocedure, fenye_num,
new sqlparameter(@tablenames, name),
new sqlparameter(@filter, filter));
}
///
///
///
/// 需要分页的表明
/// 主键名
/// 需要取出的字段,留空则为*
/// 每页的记录数
/// 当前页
/// 条件，可以为空，不用填 where
/// 分组依据，可以为空，不用填 group by
/// 排序，可以为空，为空默认按主键升序排列，不用填 order by
/// 传递aspnetpager控件
///
public static datatable paged(string name, string pk, string fields, int pagesize, int currentpage, string filter, string group, string order, aspnetpager objanp)
{
currentpage = currentpage - 1;
datatable dt = getdataset(commandtype.storedprocedure, fenye,
new sqlparameter(@tablenames, name),
new sqlparameter(@primarykey, pk),
new sqlparameter(@fields, fields),
new sqlparameter(@pagesize, pagesize),
new sqlparameter(@currentpage, currentpage),
new sqlparameter(@filter, filter),
new sqlparameter(@group, group),
new sqlparameter(@order, order)
);
objanp.recordcount = fenye_num(name, filter);
return dt;
}
}
}
以及页面调用方式
代码如下:
using system;
using system.collections;
using system.configuration;
using system.data;
using system.linq;
using system.web;
using system.web.security;
using system.web.ui;
using system.web.ui.htmlcontrols;
using system.web.ui.webcontrols;
using system.web.ui.webcontrols.webparts;
using system.xml.linq;
using insus.net;
using dal;
using system.data.sqlclient;
public partial class news_newlist : system.web.ui.page
{
int nid;
int totalorders;
protected void page_load(object sender, eventargs e)
{
if (!ispostback)
{
if (request.querystring[typeid] != null)
{
nid = convert.toint16(request.querystring[typeid].tostring());
binddata(1);
}
else
{
response.redirect(~/default.aspx);
}
}
}
private void binddata(int page)
{
datatable dt = dbhelper.paged(m_newinfoall, new_id, , aspnetpager1.pagesize, page, new_typeid= + nid.tostring() + , , new_pubdate desc, aspnetpager1);
this.repeater1.datasource = dt;
this.repeater1.databind();
datarow dr = dt.rows[0];
this.label1.text = dr[new_typename].tostring();
//this.literal1.text = dr[new_typename].tostring();
page.title = label1.text.trim() + - 新农合医药网;
}
protected void aspnetpager1_pagechanged(object sender, eventargs e)
{
if (request.querystring[page] != null)
{
binddata(convert.toint32(request.querystring[page].tostring()));
}
}
}
如此分页即可实现(下图),在任何项目中只需要copy2个存储过程一个数据操作类,或者喜欢将数据类做成dll也可以,在页面调用时传入参数只需一行代码即可.