分页存储过程是这样的:
create PROCEDURE page_cut@currPage int = 1, --当前页页码 (即Top currPage)
@showColumn varchar(2000) = '*', --需要得到的字段 (即 column1,column2,......)
@tabName varchar(2000), --需要查看的表名 (即 from table_name)
@strCondition varchar(2000) = '', --查询条件 (即 where condition......) 不用加where关键字
@ascColumn varchar(100) = '', --排序的字段名 (即 order by column asc/desc)
@bitOrderType bit = 0, --排序的类型 (0为升序,1为降序)
@pkColumn varchar(50) = '', --主键名称
@pageSize int = 20 --分页大小
AS
BEGIN -- 存储过程开始
-- 该存储过程需要用到的几个变量
DECLARE @strTemp varchar(1000)
DECLARE @strSql varchar(4000) --该存储过程最后执行的语句
DECLARE @strOrderType varchar(1000) --排序类型语句 (order by column asc或者order by column desc)
BEGIN
IF @bitOrderType = 1 -- bitOrderType=1即执行降序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
SET @strTemp = '<(SELECT min'
END
ELSE
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
SET @strTemp = '>(SELECT max'
END
IF @currPage = 1 -- 如果是第一页
BEGIN
IF @strCondition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+
' WHERE '+@strCondition+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+@strOrderType
END
ELSE -- 其他页
BEGIN
IF @strCondition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+
' WHERE '+@strCondition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+
' '+@pkColumn+' FROM '+@tabName+@strOrderType+') AS TabTemp)'+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+
' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+' '+@pkColumn+
' FROM '+@tabName+@strOrderType+') AS TabTemp)'+@strOrderType
END
END
EXEC (@strSql)
END -- 存储过程结束--------------------------------------------------------------------
C#中的代码如下:public partial class pagecut : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
GV_Bind(GridView1);
} public DataSet GetPageDataset(int currPage, string showColumn, string tabName, string strCondition, string ascColumn,int bitOrderType, string pkColumn, int pageSize)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["connectionstring"]);
SqlCommand comm = new SqlCommand("page_cut", conn);
comm.CommandType = CommandType.StoredProcedure; comm.Parameters.Add(new SqlParameter("@currPage", SqlDbType.Int));//当前页
comm.Parameters[0].Value = currPage;
comm.Parameters.Add(new SqlParameter("@showColumn", SqlDbType.VarChar));//返回的字段
comm.Parameters[1].Value = showColumn;
comm.Parameters.Add(new SqlParameter("@tabName", SqlDbType.VarChar));//表名
comm.Parameters[2].Value =tabName;
comm.Parameters.Add(new SqlParameter("@strCondition", SqlDbType.VarChar));//查询条件
comm.Parameters[3].Value = strCondition;
comm.Parameters.Add(new SqlParameter("@ascColumn", SqlDbType.VarChar));//排序的字段名
comm.Parameters[4].Value = ascColumn;
comm.Parameters.Add(new SqlParameter("@bitOrderType", SqlDbType.Int));//排序种类(0为升序,1为降序)
comm.Parameters[5].Value = bitOrderType;
comm.Parameters.Add(new SqlParameter("@pkColumn", SqlDbType.VarChar));//主键
comm.Parameters[6].Value = pkColumn;
comm.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int));//页大小
comm.Parameters[7].Value = pageSize; SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
return ds;
} public void GV_Bind(GridView gv)
{
int currPage = gv.PageIndex;
string strCondition = " 1=1 ";
gv.DataSource = GetPageDataset(currPage, "SalTerID,SalSales", "DC_124Sales", strCondition, "SalTerID", 0, "SalTerID", 10);
} protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
this.GridView1.PageIndex = e.NewPageIndex;
GV_Bind(GridView1);
}
}————————————————————————
问题是:在点按钮提示 dataAdapter.Fill(ds); 有问题,说在“-”附近有语法错误。我感觉是调用gridview的当前页存在问题,但不知该怎么用才对。有哪位能帮忙解决吗?
create PROCEDURE page_cut@currPage int = 1, --当前页页码 (即Top currPage)
@showColumn varchar(2000) = '*', --需要得到的字段 (即 column1,column2,......)
@tabName varchar(2000), --需要查看的表名 (即 from table_name)
@strCondition varchar(2000) = '', --查询条件 (即 where condition......) 不用加where关键字
@ascColumn varchar(100) = '', --排序的字段名 (即 order by column asc/desc)
@bitOrderType bit = 0, --排序的类型 (0为升序,1为降序)
@pkColumn varchar(50) = '', --主键名称
@pageSize int = 20 --分页大小
AS
BEGIN -- 存储过程开始
-- 该存储过程需要用到的几个变量
DECLARE @strTemp varchar(1000)
DECLARE @strSql varchar(4000) --该存储过程最后执行的语句
DECLARE @strOrderType varchar(1000) --排序类型语句 (order by column asc或者order by column desc)
BEGIN
IF @bitOrderType = 1 -- bitOrderType=1即执行降序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
SET @strTemp = '<(SELECT min'
END
ELSE
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
SET @strTemp = '>(SELECT max'
END
IF @currPage = 1 -- 如果是第一页
BEGIN
IF @strCondition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+
' WHERE '+@strCondition+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+@strOrderType
END
ELSE -- 其他页
BEGIN
IF @strCondition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+
' WHERE '+@strCondition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+
' '+@pkColumn+' FROM '+@tabName+@strOrderType+') AS TabTemp)'+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+
' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+' '+@pkColumn+
' FROM '+@tabName+@strOrderType+') AS TabTemp)'+@strOrderType
END
END
EXEC (@strSql)
END -- 存储过程结束--------------------------------------------------------------------
C#中的代码如下:public partial class pagecut : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
GV_Bind(GridView1);
} public DataSet GetPageDataset(int currPage, string showColumn, string tabName, string strCondition, string ascColumn,int bitOrderType, string pkColumn, int pageSize)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["connectionstring"]);
SqlCommand comm = new SqlCommand("page_cut", conn);
comm.CommandType = CommandType.StoredProcedure; comm.Parameters.Add(new SqlParameter("@currPage", SqlDbType.Int));//当前页
comm.Parameters[0].Value = currPage;
comm.Parameters.Add(new SqlParameter("@showColumn", SqlDbType.VarChar));//返回的字段
comm.Parameters[1].Value = showColumn;
comm.Parameters.Add(new SqlParameter("@tabName", SqlDbType.VarChar));//表名
comm.Parameters[2].Value =tabName;
comm.Parameters.Add(new SqlParameter("@strCondition", SqlDbType.VarChar));//查询条件
comm.Parameters[3].Value = strCondition;
comm.Parameters.Add(new SqlParameter("@ascColumn", SqlDbType.VarChar));//排序的字段名
comm.Parameters[4].Value = ascColumn;
comm.Parameters.Add(new SqlParameter("@bitOrderType", SqlDbType.Int));//排序种类(0为升序,1为降序)
comm.Parameters[5].Value = bitOrderType;
comm.Parameters.Add(new SqlParameter("@pkColumn", SqlDbType.VarChar));//主键
comm.Parameters[6].Value = pkColumn;
comm.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int));//页大小
comm.Parameters[7].Value = pageSize; SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
return ds;
} public void GV_Bind(GridView gv)
{
int currPage = gv.PageIndex;
string strCondition = " 1=1 ";
gv.DataSource = GetPageDataset(currPage, "SalTerID,SalSales", "DC_124Sales", strCondition, "SalTerID", 0, "SalTerID", 10);
} protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
this.GridView1.PageIndex = e.NewPageIndex;
GV_Bind(GridView1);
}
}————————————————————————
问题是:在点按钮提示 dataAdapter.Fill(ds); 有问题,说在“-”附近有语法错误。我感觉是调用gridview的当前页存在问题,但不知该怎么用才对。有哪位能帮忙解决吗?
2,端点调试,单步执行。看在哪儿出的问题
先屏蔽掉,改成 print @strSql
然后把跟踪的条件都放到查询分析器里,执行以下这个存储过程,看看打印出的sql,执行一下就知道问题了
{
sqlCon.Open();
sqlComm = new SqlCommand("", sqlCon);
sqlComm.CommandType = CommandType.StoredProcedure; sqlComm.Parameters.Add("@tblName ", SqlDbType.VarChar,200);
sqlComm.Parameters.Add("@fldName ", SqlDbType.VarChar, 200);
sqlComm.Parameters.Add("@pageCountint ", SqlDbType.int);
...
sqlComm.Parameters["@pageCountint "].Direction = ParameterDirection.Output;
sqlComm.Parameters["@tblName "].Value = "";
sqlComm.Parameters["@fldName "].Value = "";
...
sqlComm.ExecuteNonQuery();
strng c= sqlComm.Parameters["@pageCountint "].Value.ToString();
}
参考
exec page_cut 3, "SalTerID,SalSales", "DC_124Sales", '', "SalTerID", 0, "SalTerID", 10
是可以执行的,有返回结果。在C#中执行时总提示dataAdapter.Fill(ds)这个地方,在“-”附近有错误。6楼的大哥,sqlComm.Parameters["@pageCountint "].Direction = ParameterDirection.Output;这是让作什么?急急啊,这个问题困挠了我2天了,谁能帮我一下啊!!!
public void GV_Bind(GridView gv)
{
int currPage = gv.PageIndex;
string strCondition = " 1=1 ";
gv.DataSource = GetPageDataset(currPage, "SalTerID,SalSales", "DC_124Sales", strCondition, "SalTerID", 0, "SalTerID", 10);
}
这段代码中,我将它改成:
public void GV_Bind(GridView gv)
{
string strCondition = " 1=1 ";
gv.DataSource = GetPageDataset(3, "SalTerID,SalSales", "DC_124Sales", strCondition, "SalTerID", 0, "SalTerID", 10);
}
即将原来里面的currpage改成一个数字进行执行,就可以完成查询,得到一个10行的表。问题就是出在这个变量的引用上,如何将gridview的当前页正确表示出来呢?请大家帮我想一下。
GO
/****** 对象: StoredProcedure [dbo].[GetQueyResult] 脚本日期: 02/06/2010 15:05:55 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
--注意:sp_cursoropen/sp_cursorprepare:语句参数只能是单个 SELECT 语句或单个存储过程。
ALTER procedure [dbo].[GetQueyResult]
@sql varchar(8000), --要执行的sql语句
@currentpage int=0, --要显示的页码
@pagesize int=20, --每页的大小
@recordcount int=0 out, --总页数
@pagecount int=0 out --总页数
as
set nocount on
declare @temp int
--concuropt
--#define CUR_READONLY 1 // Read only cursor, no data modifications
--#define CUR_LOCKCC 2 // Intent to update, all fetched data locked when
-- // dbcursorfetch is called inside a transaction block
--#define CUR_OPTCC 3 // Optimistic concurrency control, data modifications
-- // succeed only if the row hasn't been updated since
-- // the last fetch.
--#define CUR_OPTCCVAL 4 // Optimistic concurrency control based on selected column values
--scrollopt
--#define CUR_FORWARD 0 // Forward only scrolling
--#define CUR_KEYSET -1 // Keyset driven scrolling
--#define CUR_DYNAMIC 1 // Fully dynamic
--#define CUR_INSENSITIVE -2 // Server-side cursors only--PDBCURSOR dbcursoropen(PDBPROCESS dbproc, LPCSTR stmt, INT scrollopt, INT concuropt, UINT nrows, LPDBINT pstatus );
--第一个记录集(不使用)
exec sp_cursoropen @temp output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@recordcount output
if(@pagesize=-1) or (@pagesize=0)
select @pagecount=1
else
select @pagecount=ceiling(1.0*@recordcount/@pagesize)
,@currentpage=(@currentpage-1)*@pagesize+1
--第二个记录集(查询出的结果记录集)
if(@pagesize=-1) or (@pagesize=0) --表示取全部数据
exec sp_cursorfetch @temp,16,1,@recordcount
else
exec sp_cursorfetch @temp,16,@currentpage,@pagesize
exec sp_cursorclose @temp