asp.net分頁預存程序

 

QueryAdRecordForAdmin queryAdrecord = new QueryAdRecordForAdmin
{
AdName = txtTitle.Value,
PageIndex = AspNetPager1.CurrentPageIndex,
PageSize = AspNetPager1.PageSize,
PromotionType = ValueConverter.Parse<int>(dropPromotionType.SelectedValue),
ApprovedState = ValueConverter.Parse<int>(dropApprovedState.SelectedValue),
UserName = txtUserName.Value,
ImageWidth=ValueConverter.Parse<int>(splict[0]),
ImageHeight=ValueConverter.Parse<int>(splict[1])

};
DateTime startDate;
if (DateTime.TryParse(txtCreateDate.Value.Trim(), out startDate))
queryAdrecord.CreateDate = startDate;
DateTime endDate;
if (DateTime.TryParse(txtDateEnd.Value.Trim(), out endDate))
queryAdrecord.PublishEndDate = endDate.AddDays(1);

IList<AdRecordInfo> list = adRecorBll.GetAdRecordMananger(queryAdrecord);
AspNetPager1.RecordCount = queryAdrecord.RecordCount;
Repeater1.DataSource = list;
Repeater1.DataBind();

1 public static DataSet GetPagerList(int pageSize, int pageIndex, string tableName, string fieldName, string where, string order, bool isCount, out int totalRecord)
{
totalRecord = 0;
SqlParameter totalPageParm = new SqlParameter("@TotalPage", SqlDbType.Int);
totalPageParm.Direction = ParameterDirection.Output;
SqlParameter totalRecordParm = new SqlParameter("@totalRecord", SqlDbType.Int);
totalRecordParm.Direction = ParameterDirection.Output;

SqlParameter[] parameters = {
new SqlParameter("@TableName", SqlDbType.VarChar, 50),
new SqlParameter("@Fields", SqlDbType.VarChar, 5000),
new SqlParameter("@OrderField", SqlDbType.VarChar, 5000),
new SqlParameter("@sqlWhere", SqlDbType.VarChar, 8000),
new SqlParameter("@pageSize", SqlDbType.Int),
new SqlParameter("@pageIndex", SqlDbType.Int),
new SqlParameter("@isCount", SqlDbType.Bit),
totalPageParm,totalRecordParm
};
parameters[0].Value = tableName;
parameters[1].Value = fieldName;
parameters[2].Value = order;
parameters[3].Value = where;
parameters[4].Value = pageSize;
parameters[5].Value = pageIndex;
parameters[6].Value = isCount;
DataSet ds = DbHelperSQL.RunProcedure("Pager2005", parameters, "PagerTable");
if (isCount)
{
int.TryParse(totalRecordParm.Value.ToString(), out totalRecord);
}
return ds;
}

2

public static DataTable GetPagerList(int StartIndex, int EndIndex, string tableName, string where, string order)
{
string sql = "select row_number() over(order by " + order + ") row_id,* from " + tableName + " where " + where;
if (StartIndex > 0 && EndIndex > 0)
sql = "select * from (" + sql + ")tb where row_id between " + StartIndex.ToString() + " and " + EndIndex.ToString();
return DbHelperSQL.Query(sql).Tables[0];
}

<webdiyer:AspNetPager ID="AspNetPager1" runat="server" OnPageChanged="AspNetPager1_PageChanged"
CustomInfoHTML="第%CurrentPageIndex%/%PageCount%頁，每頁%PageSize%條，共%RecordCount%條"
FirstPageText="首頁" LastPageText="尾頁" NextPageText="下一頁" AlwaysShow="true" CssClass="paipai"
CurrentPageButtonClass="cpb" PageIndexBoxType="TextBox" PrevPageText="上一頁" ShowCustomInfoSection="Left"
ShowPageIndexBox="Always" SubmitButtonText=" Go " TextAfterPageIndexBox="頁" PageSize="4"
TextBeforePageIndexBox="轉到" Height="25px" NumericButtonCount="5" Width="98%"
CenterCurrentPageButton="True" CustomInfoTextAlign="Left" HorizontalAlign="Right">
</webdiyer:AspNetPager>

int totalRecord = 0;

strWere = " UserID=" + model.UserID + " and ispass=" + state.ToString();

if (!string.IsNullOrEmpty(txtkeyword.Text.Trim()))
strWere += " and subject like '%" + Text.CleanSqlString(txtkeyword.Text.Trim()) + "%'";

if (!string.IsNullOrEmpty(txtStartDate.Value.Trim()) && !string.IsNullOrEmpty(txtEndDate.Value.Trim()))
strWere += " and datecreated between '" + Text.CleanSqlString(txtStartDate.Value.Trim()) + "' and '" + DateTime.Parse(Text.CleanSqlString(txtEndDate.Value.Trim())).AddDays(1).ToString() + "'";

DataSet ds = CommonBLL.GetPagerList(AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, "View_Users",
"*",
strWere, ddlorderby.SelectedValue, true, out totalRecord);
AspNetPager1.RecordCount = totalRecord;
Repeater1.DataSource = ds;
Repeater1.DataBind();