sql语句
if exists (select * from sys.procedures where name='proc_page')
drop proc proc_page
go
create proc proc_page @beginIndex int,@endIndex int,@where varchar(100)
as
declare @sql varchar(1000)
set @sql='select * from (select ROW_NUMBER() over(order by a.DeptID) rowIndex,a.*,b.UserName from Department a left join UserInfo b on a.ManagerID=b.UserID where 1=1 '+@where+') c where rowIndex between '+str(@beginIndex)+' and '+str(@endIndex)
exec(@sql)
go
给gridview绑定数据
public static DataTable SelectDepartment(int PageIndex, int PageSize, string DeptName,string UserName, out int totalCount)
{
string sql = "exec proc_page @begIndex,@endIndex,@where";
string where = string.Empty;
if (!string.IsNullOrEmpty(DeptName))
{
where += string.Format(" and DeptName='{0}'", DeptName);
}
if (UserName != "请选择")
{
where += string.Format(" and UserName='{0}'", UserName);
}
int begIndex = (PageIndex - 1) * PageSize + 1;
int endIndex = PageSize * PageIndex;
SqlParameter[] ps =
{
new SqlParameter("@begIndex",begIndex),
new SqlParameter("@endIndex",endIndex),
new SqlParameter("@where",where)
};
//string sql = string.Format("select * from (select ROW_NUMBER() over(order by a.DeptID) rowIndex,a.*,b.UserName from Department a left join UserInfo b on a.ManagerID=b.UserID where 1=1 {0}) c where rowIndex between {1} and {2}",where,begIndex,endIndex);
string sqlCount = string.Format("select * from Department where 1=1 {0}",where);
totalCount = int.Parse((DBHelper.GetDataTable1(sqlCount).Rows.Count.ToString()));
return DBHelper.GetDataTable(sql,ps);
}
后台绑定
public void BindGv()
{
string DeptName = this.txtDeptName.Text.Trim();
string UserName = this.DDLUserName.SelectedItem.ToString();
int totalCount;
this.GridView1.DataSource = BLL.Department_BLL.selectDepartment(PageIndex, PageSize, DeptName, UserName, out totalCount);
this.GridView1.DataBind();
this.Label1.Text = "页数:" + PageIndex.ToString();
this.Label2.Text = "总记录行数:" + totalCount.ToString();
ViewState["TotalCount"] = totalCount;
}