62,074
社区成员
发帖
与我相关
我的任务
分享
public DataTable Pagination(string sqlStr, int currentpage, int pagesize)
{
conn = new SqlConnection(lt.Common.Config.connStr);
comm = new SqlCommand();
comm.Connection = conn;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "lt_Paginationcursor";
SqlParameter paramOUT = comm.Parameters.AddWithValue("@RETURN_VALUE", "");
paramOUT.Direction = ParameterDirection.ReturnValue;
SqlParameter paramOUTPUT = comm.Parameters.Add("@pagecount", SqlDbType.BigInt);
paramOUTPUT.Direction = ParameterDirection.Output;
SqlParameter paramOUTPUT1 = comm.Parameters.Add("@recordCount", SqlDbType.BigInt);
paramOUTPUT1.Direction = ParameterDirection.Output;
comm.Parameters.AddWithValue("sql", sqlStr);
comm.Parameters.AddWithValue("currentpage", currentpage);
comm.Parameters.AddWithValue("pagesize", pagesize);
SqlDataAdapter da = new SqlDataAdapter(comm);
DataTable dtable = new DataTable();
da.Fill(dtable);
_pageCount = System.Convert.ToInt32(comm.Parameters["@pagecount"].Value.ToString());
_recordCount = System.Convert.ToInt32(comm.Parameters["@recordcount"].Value.ToString());
return dtable;
}
ALTER PROCEDURE dbo.lt_Paginationcursor
@sql nvarchar(4000), --要执行的sql语句
@currentpage int=1, --要显示的页码
@pagesize int=10, --每页的大小
@pagecount int=0 output, --总页数
@recordCount int=0 output--总记录数
as
set nocount on
declare @cursor int --cursor 是游标的id
exec sp_cursoropen @cursor output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
select @recordCount=@pagecount
select @pagecount=ceiling(1.0*@pagecount/@pagesize) ,@currentpage=(@currentpage-1)*@pagesize+1
select @pagecount,@currentpage
exec sp_cursorfetch @cursor ,16,@currentpage,@pagesize
exec sp_cursorclose @cursor
return
SQLServerDAL.Perform p = new SQLServerDAL.Perform();
DataTable dt = p.Pagination("select * from lt_mshospital", 1, 10);
GridView1.DataSource = dt;
GridView1.DataBind();
Response.Write(p.pageCount + "/" + p.recordCount + "/");
Response.Write(dt.Rows.Count);
public DataTable Pagination(string sqlStr, int currentpage, int pagesize)
{
conn = new SqlConnection(lt.Common.Config.connStr);
comm = new SqlCommand();
comm.Connection = conn;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "lt_Paginationcursor";
SqlParameter paramOUT = comm.Parameters.AddWithValue("@RETURN_VALUE", "");
paramOUT.Direction = ParameterDirection.ReturnValue;
SqlParameter paramOUTPUT = comm.Parameters.Add("@pagecount", SqlDbType.BigInt);
paramOUTPUT.Direction = ParameterDirection.Output;
SqlParameter paramOUTPUT1 = comm.Parameters.Add("@recordCount", SqlDbType.BigInt);
paramOUTPUT1.Direction = ParameterDirection.Output;
comm.Parameters.AddWithValue("sql", sqlStr);
comm.Parameters.AddWithValue("currentpage", currentpage);
comm.Parameters.AddWithValue("pagesize", pagesize);
SqlDataAdapter da = new SqlDataAdapter(comm);
DataTable dtable = new DataTable();
da.Fill(dtable);
_pageCount = System.Convert.ToInt32(comm.Parameters["@pagecount"].Value.ToString());
_recordCount = System.Convert.ToInt32(comm.Parameters["@recordcount"].Value.ToString());
return dtable;
}
========》
public DataTable Pagination(string sqlStr, int currentpage, int pagesize)
{
conn = new SqlConnection(lt.Common.Config.connStr);
comm = new SqlCommand();
comm.Connection = conn;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "lt_Paginationcursor";
SqlParameter paramOUT = comm.Parameters.AddWithValue("@RETURN_VALUE", "");
paramOUT.Direction = ParameterDirection.ReturnValue;
SqlParameter paramOUTPUT = comm.Parameters.Add("@pagecount", SqlDbType.BigInt);
paramOUTPUT.Direction = ParameterDirection.Output;
SqlParameter paramOUTPUT1 = comm.Parameters.Add("@recordCount", SqlDbType.BigInt);
paramOUTPUT1.Direction = ParameterDirection.Output;
comm.Parameters.AddWithValue("sql", sqlStr);
comm.Parameters.AddWithValue("currentpage", currentpage);
comm.Parameters.AddWithValue("pagesize", pagesize);
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds= new DataSet ();
da.Fill(ds);
_pageCount = System.Convert.ToInt32(comm.Parameters["@pagecount"].Value.ToString());
_recordCount = System.Convert.ToInt32(comm.Parameters["@recordcount"].Value.ToString());
或者_pageCount = Convert.ToInt32(ds.Tables[1].Rows[0][0])
_recordCount = Convert.ToInt32(ds.Tables[1].Rows[0][1])
return ds.Tables[2];//返回第3張表
}
comm.Parameters.AddWithValue("sql", sqlStr);
comm.Parameters.AddWithValue("currentpage", currentpage);
comm.Parameters.AddWithValue("pagesize", pagesize);
// 加这句话看看
con.Open();