快捷搜索:

DataGrid自定义分页存储过程

存储历程代码:

CREATE proc page

@RecordCount int output,

@QueryStr nvarchar(100)='table1',--表名、视图名、查询语句

@PageSize int=20,--每页的大年夜小(行数)

@PageCurrent int=1,--要显示的页

@FdShow nvarchar (1000)='*', --要显示的字段列表

@IdentityStr nvarchar (100)='id', --主键

@WhereStr nvarchar (200)='1=1 and id % 11111 = 0',

@FdOrder nvarchar(100)='id', --排序

@isReturn bit=0

as

declare

@sql nvarchar(2000)

set @sql = ''

if @WhereStr = '' begin

set @WhereStr = '1=1'

end

declare @tsql nvarchar(200)

if(@isReturn=1)begin

set @tsql=N'select @RecordCount = count(*) from ' + @QueryStr + ' where ' + @WhereStr

exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount output

end

else begin

set @RecordCount = @PageSize * @PageCurrent + 1

end

if @PageCurrent = 1 begin

set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr

end

else begin

set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + cast(@PageSize*(@PageCurrent-1) as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ') as t) order by ' + @IdentityStr

end

if @FdOrder @IdentityStr begin

set @sql = 'select * from (' + @sql + ') as t4867435348493 order by ' + @FdOrder

end

--print @sql

execute(@sql)

GO

后台代码:

protected System.Web.UI.WebControls.DataGrid DataGrid1;

private void Page_Load(object sender, System.EventArgs e)

{

// 在此处放置用户代码以初始化页面

if(!Page.IsPostBack)

{

DataGrid1DataBind(1);

}

}

private void DataGrid1DataBind(int page)

{

SqlConnection conn = new SqlConnection("server=qq;uid=sa;pwd=奸淫;database=testdb");

SqlCommand cmd = new SqlCommand("page",conn);

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter p1 = cmd.Parameters.Add("@RecordCount",SqlDbType.Int);

p1.Direction = ParameterDirection.Output;

SqlParameter p = cmd.Parameters.Add("@PageCurrent",SqlDbType.Int);

p.Value = page;

SqlDataAdapter da= new SqlDataAdapter();

DataSet ds = new DataSet();

da.SelectCommand = cmd;

da.Fill(ds);

DataGrid1.DataSource = ds.Tables[0].DefaultView;

int count = int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());

if(null == ViewState["page"] || "" == ViewState["page"].ToString())

{

ViewState["page"] = count.ToString();

}

else

{

count = int.Parse(ViewState["page"].ToString());

}

DataGrid1.VirtualItemCount = count;

DataGrid1.DataBind();

}

private void DataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)

{

DataGrid1.CurrentPageIndex = e.NewPageIndex;

DataGrid1DataBind(e.NewPageIndex + 1);

}

您可能还会对下面的文章感兴趣: