本人愚笨,弄了一个分页存储过程。但不知道怎么在asp.net中实现调用实现分页。希望高手能给一个详细的调用这个存储的C#代码
use DIS
go
if exists (select 1 from sysobjects where id=OBJECT_ID('dbo.pr_pagination') and type='P')
drop procedure dbo.pr_pagination
go
create procedure dbo.pr_pagination
(
@page int, --页数
@count int output, --总条数
@size int --每页条数
)
as
select @count=COUNT(b.usercommentid)
from users a,usercomment b
where a.userid=b.userid
declare @tmpselect nvarchar(600)
set @tmpselect= 'select top ' +STR(@size)+ ' a.userlogin,b.commenttitle,b.commentcontent,b.usercommentid
from users a,usercomment b
where a.userid=b.userid and b.usercommentid not in (select top ' +STR(@size*@page)+ ' b.usercommentid
from users a,usercomment b
where a.userid=b.userid
order by b.commenttime desc)
order by b.commenttime desc '
print (@tmpselect);
exec (@tmpselect);
go
use DIS
go
if exists (select 1 from sysobjects where id=OBJECT_ID('dbo.pr_pagination') and type='P')
drop procedure dbo.pr_pagination
go
create procedure dbo.pr_pagination
(
@page int, --页数
@count int output, --总条数
@size int --每页条数
)
as
select @count=COUNT(b.usercommentid)
from users a,usercomment b
where a.userid=b.userid
declare @tmpselect nvarchar(600)
set @tmpselect= 'select top ' +STR(@size)+ ' a.userlogin,b.commenttitle,b.commentcontent,b.usercommentid
from users a,usercomment b
where a.userid=b.userid and b.usercommentid not in (select top ' +STR(@size*@page)+ ' b.usercommentid
from users a,usercomment b
where a.userid=b.userid
order by b.commenttime desc)
order by b.commenttime desc '
print (@tmpselect);
exec (@tmpselect);
go
解决方案 »
- 帮忙看下,sql问题???谢谢了!
- updatepanel中使用ScriptManager.RegisterStartupScript弹出提示
- 数据库操作类如何调用?
- 大家做网站怎么收费?
- 想实现就象动网一样的分类,想了半天也没想好如何递归得到数据,以及绑定到DropDownList
- 绑定数据的dropdownlist里要增加一行的问题
- VS2010中的web项目如何以管理员身份运行
- ASP.NET MVC中windows authentication验证
- 高手帮忙:为什么找不到ASP.NET编辑环境啊?
- 求助!怎么回事呢?
- ★★★小白又来提问了:一段代码看不懂,对于你课能很简单哦!★★★
- datagrid怎么样弹出删除对话框
{
string spName = "sp_getOrderdetailinfoTopN";
if(orderString != null && orderString.Length > 0)
{
if(isDesc == true)
{
orderString += " desc ";
}
else
{
orderString += " asc ";
}
}
return SqlHelp.ExecuteDataset(Config.StrConn,spName,orderId,serviceId,pageSize,pageIndex,orderString).Tables[0];
}
/// 获得数据集
/// </summary>
/// <param name="tblName">表名</param>
/// <param name="RetColumns">需要返回的列,默认为全部 </param>
/// <param name="Orderfld">排序字段名</param>
/// <param name="PageSize">页尺寸</param>
/// <param name="PageIndex">页码 </param>
/// <param name="IsCount">返回记录总数, 非 0 值则返回 </param>
/// <param name="OrderType">设置排序类型, 非 asc 值则降序 </param>
/// <param name="strWhere">查询条件 (注意: 不要加 where) </param>
/// <returns>数据集</returns>
public static SqlDataReader Get_DataReader(string tblName,string RetColumns,string Orderfld,int PageSize,int PageIndex,int IsCount,string OrderType,string strWhere)
{
IDataParameter[] parameters = new SqlParameter[8];
parameters[0]=new SqlParameter("@tblName", SqlDbType.NVarChar,255);//表名
parameters[1]=new SqlParameter("@RetColumns", SqlDbType.NVarChar,1000);//需要返回的列,默认为全部
parameters[2]=new SqlParameter("@Orderfld", SqlDbType.NVarChar,255);//排序字段名
parameters[3]=new SqlParameter("@PageSize", SqlDbType.Int);//页尺寸
parameters[4]=new SqlParameter("@PageIndex", SqlDbType.Int);//页码
parameters[5]=new SqlParameter("@IsCount", SqlDbType.Bit);//返回记录总数, 非 0 值则返回
parameters[6]=new SqlParameter("@OrderType", SqlDbType.NVarChar,50);//设置排序类型, 非 asc 值则降序
parameters[7]=new SqlParameter("@strWhere", SqlDbType.NVarChar,1000);//查询条件 (注意: 不要加 where) parameters[0].Value=tblName;
parameters[1].Value=RetColumns;
parameters[2].Value=Orderfld;
parameters[3].Value=PageSize;
parameters[4].Value=PageIndex;
parameters[5].Value=IsCount;
parameters[6].Value=OrderType;
parameters[7].Value=strWhere;
try
{
return DbHelperSQL.RunProcedure("proc_y_GetRecordFromPage",parameters);
}
catch
{
return null;
}
}/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
{
SqlConnection connection = new SqlConnection(connectionString);
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand( connection,storedProcName, parameters );
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader();
return returnReader;
}
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand( storedProcName, connection );
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter );
}
return command;
}
只要将SQL传给他就行了他会输出二个表,一个是分页信息,一个是数据。你直接存储到dataset中CREATE procedure usp_proPage
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
-- set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount
output select ceiling(1.0*@rowcount/@pagesize) as 总页数,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
-- set nocount off
GO
SqlCommand command = new SqlCommand("pr_pagination", connection );
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@page", SqlDbType.Int32, 4).Value = 你的值,第几页;
command.Parameters.Add("@size", SqlDbType.Int32, 4).Value = 你的值,每页条数;
SqlParameter parameter = new SqlParameter("@count", SqlDbType.Int32, 4);
parameter.Direction = ParameterDirection.Output;
command.Parameters.Add(parameter);
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
da.Fill(dt);
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.UI;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Xml;public partial class Default2 : System.Web.UI.Page
{
string strConn = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
fun();
}
} void fun()
{
using(SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("sp_page", conn);
cmd.CommandType = CommandType.StoredProcedure();
cmd.Parameters.Add("@pagesize", SqlDbType.Int).Value = TextBox1.Text;
cmd.Parameters.Add("@pageindex", SqlDbType.Int).Value = TextBox2.Text;
using(SqlDataAdapter adp=new SqlDataAdapter(cmd))
{
adp.Fill(dt);
this.GridView1.DataSource = dt;
this.GridView1.DataBind();
}
}
}
}
(@startIndex int,
@endIndex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(*) AS Counts from Product
else
begin
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select ID from Product order by ID desc
select * from Product O,@indextable t where O.ID=t.nid
and t.id between @startIndex and @endIndex order by t.id
end
set nocount off GO
SqlCommand command = new SqlCommand("pr_pagination", connection );
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@page", SqlDbType.Int32, 4).Value = 你的值,第几页;
command.Parameters.Add("@size", SqlDbType.Int32, 4).Value = 你的值,每页条数;
SqlParameter parameter = new SqlParameter("@count", SqlDbType.Int32, 4);
parameter.Direction = ParameterDirection.Output;
command.Parameters.Add(parameter);
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
da.Fill(dt);
CREATE PROCEDURE pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere
else
set @strSQL = "select count(*) as Total from [" + @tblName + "]"
end
--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @fldName +"] desc"
--如果@OrderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @fldName +"] asc"
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder
else
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName + "] "+ @strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "] where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
end
end
exec (@strSQL)
GO