使用SQL Server 2005的新函数构造分页存储过程
2009-10-25 00:00:00 来源：WEB开发网示例代码，后台代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Kimbanx.UCS.ForeignStudentAdmin.Model;
using Kimbanx.UCS.ForeignStudentAdmin.Common;
namespace Kimbanx.UCS.ForeignStudentAdmin.UserControl.UserControl
{
public partial class StudentDetailsTable : System.Web.UI.UserControl
{
private Database _db = DatabaseFactory.CreateDatabase();
private DbCommand _command;
private DbConnection _connection;
private DataSet _ds;
private string _classCode;
private string _classFullName;
private string _studentType;
private string _studentCount;
private string _queryStringWhere;
private DataTable _studentTable;
protected string SetBirthDate(object obj)
{
string result = string.Empty;
string temp = obj.ToString();
result = DateTime.Parse(temp).ToShortDateString();
return result;
}
protected string SetEnrollDate(object obj)
{
string result = string.Empty;
string temp = obj.ToString();
result = DateTime.Parse(temp).ToShortDateString();
return result;
}
protected void Filldata_dllPageSize()
{
for (int i = 1; i < 100; i++)
{
ddlPageSize.Items.Add(i.ToString());
}
ddlPageSize.SelectedIndex = 14;
}
protected void InitSession()
{
//Session["PageSize"] = 0;
Session["PageIndex"] = 1;
Session["PageCount"] = int.Parse(_studentCount) / 15 + 1;
}
/// <summary>
/// 获取QueryString传递参数
/// </summary>
protected void GetQueryStringPara()
{
_classCode = Request.QueryString["dwbh"];
_classFullName =HttpUtility.UrlDecode( Request.QueryString["dwmc"]);
_studentCount = Request.QueryString["studentCount"];
_studentType =HttpUtility.UrlDecode( Request.QueryString["studentType"]);
_queryStringWhere = Request.QueryString["where"];
}
protected void SetLabelText()
{
this.lblClassName.Text = _classFullName;
this.lblClassLevel.Text = GetClassInfo(_classCode).Level.ToString();
this.lblStudentCount.Text = _studentCount;
this.lblStudentType.Text = _studentType;
}
#region
///// <summary>
///// 获取学员数据
///// </summary>
///// <param name="strSelect">显示的字段</param>
///// <param name="strFrom">用到的</param>
/////<param name="strWhere">查询条件</param>
///// <param name="pageSize">每页显示条数</param>
///// <param name="pageIndex">当前页</param>
///// <returns></returns>
//protected DataTable GetStudentData(string strSelect,string strFrom,string strWhere,int pageSize,int pageIndex)
//{
// _command = _db.GetStoredProcCommand("StudentPaging");
// _db.AddInParameter(_command, "StrSelect", DbType.String, "zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh");
// _db.AddInParameter(_command, "StrFrom", DbType.String, "tx_xyzl");
// _db.AddInParameter(_command, "StrWhere", DbType.String, strWhere );
// _db.AddInParameter(_command, "StrOrder", DbType.String, "id");
// _db.AddInParameter(_command, "PageSize", DbType.Int32, pageSize );
// _db.AddInParameter(_command, "PageIndex", DbType.Int32,pageIndex );
// _studentTable = _db.ExecuteDataSet(_command).Tables[0];
// return _studentTable;
//}
#endregion
protected string GetStudentImageUrl(object imageUrl)
{
string serverUrl = http://192.168.0.1/admin;
string imageurl = string.Empty;
if (!(imageUrl == null))
{
string temp = imageUrl.ToString().Trim();
if (!string.IsNullOrEmpty(temp))
{ imageurl = string.Format("{0}{1}", serverUrl, temp.Substring(temp.IndexOf("/"))); }
}
return imageurl;
}
/// <summary>
/// 绑定分页之后的数据
/// </summary>
/// <param name="pageSize">每页显示的数据量</param>
/// <param name="pageIndex">当前页</param>
protected void BindStudentData(int pageSize, int pageIndex)
{
switch (_queryStringWhere)
{
case "jx":
this.gvStudent.DataSource = Helper.StudentPagingResult(
"zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh",
"student",
string.Format("dwbh='{0}' and jx='{1}'", _classCode, _studentType),
"id",
pageSize,
pageIndex);
this.gvStudent.DataBind();
break;
case "gj":
this.gvStudent.DataSource = Helper.StudentPagingResult(
"zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh",
"student",
string.Format("dwbh='{0}' and gj='{1}'", _classCode, _studentType),
"id",
pageSize,
pageIndex);
this.gvStudent.DataBind();
break;
case "allyear":
this.gvStudent.DataSource = Helper.StudentPagingResult(
"s.zpadress,s.xmjz,s.xmjy,s.jx,s.zw,s.gj,s.sjyqk,s.zj,s.csrq,s.rwrq,s.xzz,s.dhd,s.dhx,s.fcjp,s.hzh,s.xh",
"student as s inner join class as dw on s.dwbh=dw.bh",
string.Format(@"s.dwbh='{0}' and (dw.kxsj>=convert(datetime,'{1}'+'-01-01',120) and
dw.kxsj<=convert(datetime,'{1}'+'-12-31',120) or dw.bysj>=convert(datetime,'{1}'+'-01-01',120) and
dw.bysj<=convert(datetime,'{1}'+'-12-31',120)) ", _classCode, _studentType),
"s.id",
pageSize,
pageIndex);
this.gvStudent.DataBind();
break;
case "new":
this.gvStudent.DataSource = Helper.StudentPagingResult(
"s.zpadress,s.xmjz,s.xmjy,s.jx,s.zw,s.gj,s.sjyqk,s.zj,s.csrq,s.rwrq,s.xzz,s.dhd,s.dhx,s.fcjp,s.hzh,s.xh",
"student as s inner join class as dw on s.dwbh=dw.bh",
string.Format(@"s.dwbh='{0}' and (dw.kxsj>=convert(datetime,'{1}'+'-01-01',120) and
dw.kxsj<=convert(datetime,'{1}'+'-12-31',120)) ", _classCode, _studentType),
"s.id",
pageSize,
pageIndex);
this.gvStudent.DataBind();
break;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (UserAuthHelper.GetUserAuthType("1") == UserAuthEnum.Admin||
UserAuthHelper.GetUserAuthType("2") == UserAuthEnum.CurrentStudentDetails)
{
GetQueryStringPara();
SetLabelText();
if (GetStudentCount() == 0)
{
StudentCountZero();
return;
}
if (!IsPostBack)
{
Filldata_dllPageSize();
SetPageIndex(1);
SetPageCount();
BindStudentData(GetPageSize(), GetPageIndex());
}
else
{
}
}
else
{
this.Controls.Add(new LiteralControl("您没有相应的权限，请联系管理员"));
}
}
/// <summary>
/// 获取班级信息，班级全称，班级级别
/// </summary>
/// <param name="classCode">班级编号</param>
/// <returns></returns>
protected ClassEntity GetClassInfo(string classCode)
{
ClassEntity entity = new ClassEntity();
entity.Code = classCode;
_command = _db.GetStoredProcCommand("ClassInfo");
_db.AddInParameter(_command, "bh", DbType.String, classCode);
using (IDataReader reader = _db.ExecuteReader(_command))
{
while (reader.Read())
{
entity.FullName = reader.GetString(1);
entity.Level = reader.GetInt32(2);
}
}
return entity;
}
#region Get and Set PageSize
protected int GetPageSize()
{
return int.Parse(ddlPageSize.SelectedValue);
}
protected void SetPageSize(int pageSize)
{
this.ddlPageSize.Text = pageSize.ToString();
}
#endregion
#region Get and Set PageIndex
protected int GetPageIndex()
{
return int.Parse(this.lblPageIndex.Text.Trim());
}
protected void SetPageIndex(int pageIndex)
{
this.lblPageIndex.Text = pageIndex.ToString();
}
#endregion
#region Get and Set PageCount
protected int GetPageCount()
{
return int.Parse(this.lblPageCount.Text.Trim());
}
protected void SetPageCount()
{
int studentCount = GetStudentCount();
int pageSize = GetPageSize();
if (studentCount % pageSize == 0)
{
this.lblPageCount.Text = (studentCount / pageSize).ToString();
}
else
{
this.lblPageCount.Text = (studentCount / pageSize + 1).ToString();
}
}
#endregion
#region Get and Set StudentCount
protected int GetStudentCount()
{
return int.Parse(this.lblStudentCount.Text.Trim());
}
protected void SetStudentCount(int studentCount)
{
this.lblStudentCount.Text = studentCount.ToString();
}
#endregion
protected void StudentCountZero()
{
this.lblPageIndex.Text = "0";
this.lblPageCount.Text = "0";
}
protected void LinkButton_Command(object sender, CommandEventArgs e)
{
if (GetStudentCount() == 0)
{
StudentCountZero();
return;
}
int pageCount = GetPageCount();
int pageIndex = GetPageIndex();
int pageSize = GetPageSize();
switch (e.CommandArgument.ToString())
{
case "first":
if (pageIndex == 1) { }
else
{
pageIndex = 1;
SetPageIndex(pageIndex);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
break;
case "next":
if (pageCount == pageIndex & pageIndex == 1)
{ }
else if (pageIndex == 1 && pageCount > pageIndex)
{
SetPageIndex(++pageIndex);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
else if (pageIndex > 1 && pageCount == pageIndex)
{ }
else
{
SetPageIndex(++pageIndex);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
break;
case "prev":
if (pageIndex == 1)
{ }
else if (pageIndex == pageCount && pageIndex > 1)
{
SetPageIndex(--pageIndex);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
else if (pageIndex == 2)
{
SetPageIndex(1);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
else
{
SetPageIndex(--pageIndex);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
break;
case "last":
if (pageCount == pageIndex)
{ }
else
{
SetPageIndex(pageCount);
pageIndex = GetPageIndex();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
break;
default:
SetPageIndex(1);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
break;
}
}
protected void ddlPageSize_SelectedIndexChanged(object sender, EventArgs e)
{
int pageIndex = GetPageIndex();
int pageCount = GetPageCount();
int pageSize = GetPageSize();
pageIndex = 1;
SetPageIndex(pageIndex);
SetPageSize(int.Parse(((DropDownList)sender).SelectedValue));
pageSize=GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
}
}
- ››sql server自动生成批量执行SQL脚本的批处理
- ››使用linux中的quota教程
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
- ››使用jxl生成带动态折线图的excel
- ››SQL SERVER无法安装成功，sqlstp.log文件提示[未发...
- ››Sql Server中通过父记录查找出所有关联的子记录
- ››SqlServer触发器、存储过程和函数
- ››SQL Server 中的事务(含义,属性,管理)
- ››Sqlite数据库插入和读取图片数据
中查找“使用SQL Server 2005的新函数构造分页存储过程”更多相关内容
中查找“使用SQL Server 2005的新函数构造分页存储过程”更多相关内容更多精彩
赞助商链接
