小弟第一次使用存储过程。。主要是学习下。。
用他带的分页存储过程怎么使用?
怎么绑定数据 求源码效果。。
用他带的分页存储过程怎么使用?
怎么绑定数据 求源码效果。。
解决方案 »
- Asp.net利用javascript弹出层加载数据或者实现登录
- 一个关于IIS设置网页可播放flv和swf的问题
- 谁有FrontPage服务器扩展的 急!急!急!急!
- 为什么在MVC3中使用jquery没有任何效果 还有jquery的智能感知也没有
- 为什么明明存在txtlogo 生成时却说不存在.
- 高分问个datagrid删除的功能
- 请介绍一个ASP.net的空间,支持C#的,好吗?要质量好的.
- Page.RegisterClientScriptBlock 与Page.RegisterStartupScript
- Application缓存如何更新?
- 多线程,如何判断所有子线程都已经运行完毕(结束)
- 急用:如何取得模板列按钮所在行的行号
- Cookie的问题
{
int FType = Convert.ToInt32(Request.QueryString["Ftype"]);
DataSet DS;
DS = RaychnBLL.GetPageRecodeBLL.getList("News", " Newstype=" + FType + " ", " Newsid,Createtime,Crowd,NewsTitle ", AspNetPager1.CurrentPageIndex, AspNetPager1.PageSize, "Createtime desc", 0);
this.AspNetPager1.RecordCount = RaychnBLL.GetPageRecodeBLL.Getcount("select Count(Newsid) from News where Newstype=" + FType + "");
this.NewsList.DataSource = DS.Tables[1];
this.NewsList.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
MyBind();
}
}
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
MyBind();
}
<title>
<%@ register assembly="AspNetPager" namespace="Wuqi.Webdiyer" tagprefix="webdiyer" %></title>
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" CssClass="paginator" CurrentPageButtonClass="cpb"
PageSize="20" Width="648px" Font-Size="12px" OnPageChanged="AspNetPager1_PageChanged"
CustomInfoHTML="共%PageCount%页,当前为第%CurrentPageIndex%页,每页%PageSize%条" FirstPageText="首页"
LastPageText="尾页" NextPageText="下一页" PrevPageText="上一页" PageIndexBoxType="DropDownList"
ShowPageIndexBox="Always" SubmitButtonText="Go" TextAfterPageIndexBox="页" TextBeforePageIndexBox="转到">
</webdiyer:AspNetPager>
哦,这里错了·
是<head>....</head>
/// <summary>
/// 返回要查询的记录(支持distinct的存储过程)
/// </summary>
/// <param name="tblName">表名</param>
/// <param name="strWhere">条件(不加where)</param>
/// <param name="colName">列集合</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页大小</param>
/// <param name="strOrd">排序(自己添加asc,desc)</param>
/// <param name="isDis">是否distinct</param>
/// <returns></returns>
public static DataSet getList(string tblName, string strWhere, string colName, int pageIndex, int pageSize, string strOrd, int isDis)
{
return DAL.SqlHepler.ExecuteProcRetTable("proc_viewlist", new SqlParameter("@Table", tblName), new SqlParameter("@Where", strWhere), new SqlParameter("@Cou", colName), new SqlParameter("@NewPageIndex", pageIndex), new SqlParameter("@pageSize", pageSize), new SqlParameter("@order", strOrd), new SqlParameter("@isDistinct", isDis));
}
[email protected]
<webdiyer:AspNetPager ID="AspNetPager2" runat="server" PageSize="20" onpagechanged="AspNetPager2_PageChanged" >20为每分页就显示20条数据后台;
AspNetPager1.RecordCount =100;//设置总行数
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
//分页事件
int stint = AspNetPager1.CurrentPageIndex - 1;//获取分页数
}
CREATE PROCEDURE Pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@doType bit=0,--执行的类型1取记录的行数,否取记录集
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = ''-- 查询条件 (注意: 不要加 where)
AS
SET NOCOUNT ON
declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doType=1--取记录的行数
begin
if @strWhere !=''
set @strSQL = 'select count(*) from ' + @tblName + ' where '+@strWhere
else
set @strSQL ='select count(*) from ' + @tblName + ''
--EXEC SP_EXECUTESQL @strSQL,N'@TotalRecordCount int output',@TotalRecordCount output
end
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"
--如果@OrderType是0,就执行升序,这句很重要!
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)GODAL层代码
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Marlboro.Model;namespace Marlboro.DAL
{
public class SQLProPage
{
public SQLProPage()
{
} /// <summary>
/// 取记录数
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int iRecordCount(page_model model)
{
string CommanText = "Pagination";
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@tblName", model.TblName),
new SqlParameter("@strGetFields", model.StrGetFields),
new SqlParameter("@fldName", model.FldName),
new SqlParameter("@doType", 1),
new SqlParameter("@PageSize", 1),
new SqlParameter("@PageIndex", 1),
new SqlParameter("@OrderType", 0),
new SqlParameter("@strWhere", model.StrWhere)
};
return Marlboro.Common.SqlHelper.SqlHelper.ExecuteScalar(CommanText, para, CommandType.StoredProcedure);
}
/// <summary>
/// 返回数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataSet getDataSet(page_model model)
{
string CommanText = "Pagination";
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@tblName", model.TblName),
new SqlParameter("@strGetFields", model.StrGetFields),
new SqlParameter("@fldName", model.FldName),
new SqlParameter("@doType", model.DoType),
new SqlParameter("@PageSize", model.PageSize),
new SqlParameter("@PageIndex", model.PageIndex),
new SqlParameter("@OrderType", model.OrderType),
new SqlParameter("@strWhere", model.StrWhere)
};
return Marlboro.Common.SqlHelper.SqlHelper.ReturnDataSet(CommanText, CommandType.StoredProcedure, para);
}
}
}
BLL
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Marlboro.Model;
using System.Data.SqlClient;
namespace Marlboro.BLL
{
public class T_SQLProPage
{
/// <summary>
/// 取记录数
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int iRecordCount(page_model model)
{
return Marlboro.DAL.SQLProPage.iRecordCount(model);
} /// <summary>
/// 返回数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataSet getDataSet(page_model model)
{
return Marlboro.DAL.SQLProPage.getDataSet(model);
} }
}WEB
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Marlboro.Model;public partial class Wage_search : System.Web.UI.Page
{
//声明实体
Marlboro.Model.page_model model = new page_model();
protected void Page_Load(object sender, EventArgs e)
{
//加载实体
model.TblName = "V_WAGE";
model.StrGetFields = "*";
model.FldID = "STAFFID";
model.StrWhere = "1=1";
model.PageSize = Pager.PageSize;
model.FldName = "STAFFID";//[MONTH]
model.OrderType = 1;
if (!IsPostBack)
{
BindData(model);
//Gvbind();
}
}
protected void btnsearch_Click(object sender, EventArgs e)
{
string SearchTime = Convert.ToDateTime(Calendar1.Text.Trim()).ToShortDateString().ToString();
string Searchddl = Dr_Search.SelectedValue.ToString();
string Searchtxt = txtsearch.Text.Trim();
DataTable dt = new DataTable();
dt = new Marlboro.BLL.T_wage().ReturnWageViewbyWhere(Searchddl, SearchTime, Searchtxt);
if (dt.Rows.Count != 0)
{
Marlboro.Common.ControlHelper.ControlHelper.GridViewBind(this.GridView1, dt);
}
else
{
Marlboro.Common.WebHelper.MessageBox.Show("没数据");
BindData(model);
} }
void Gvbind()
{
Marlboro.Common.ControlHelper.ControlHelper.GridViewBind(GridView1, new Marlboro.BLL.T_wage().WageView());
} void BindData(page_model model)
{
//返回数
Pager.RecordCount = Marlboro.BLL.T_SQLProPage.iRecordCount(model);
if (Pager.CurrentPageIndex <= 0)
{
model.PageIndex = 1;
}
else
{
model.PageIndex = Pager.CurrentPageIndex;
}
//返回数据
DataTable ds = Marlboro.BLL.T_SQLProPage.getDataSet(model).Tables[0];
//绑定
Marlboro.Common.ControlHelper.ControlHelper.GridViewBind(GridView1, ds);
} protected void Pager_PageChanged(object sender, EventArgs e)
{
BindData(model);
}
}
1、AspNetPager和数据源没有任何关系,和存储过程更没有任何直接联系,所以用AspNetPager必须用存储过程的说法是完全错误的;
2、AspNetPager是生成分页用户界面的分页逻辑的控件,而存储过程是从数据库获取数据的方法,所以存储过程和AspNetPager是功能完全不同的,但两者可以结合使用,常见论坛里有人说用存储过程分页而不用AspNetPager,这是完全错误的,存储过程永远不可能为在页面上生成分页界面并实现相应的分页逻辑,存储过程仅仅是从数据库获取分页数据的一种方式而已。谢谢大家对AspNetPager的信任和支持!
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere nvarchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0 --如果@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
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