DataGrid custom Paging stored Procedures

datagrid| Stored Procedures | paging

Stored Procedure Code:

CREATE proc Page
@RecordCount int OUTPUT,
@QueryStr nvarchar = ' table1 ',--table name, view name, query statement
@PageSize int=20--The size (number of rows) per page
@PageCurrent int=1--the page to display
@FdShow nvarchar (1000) = ' * ',--list of fields to display
@IdentityStr nvarchar = ' id ',--primary key
@WhereStr nvarchar = ' 1=1 and id% 11111 = 0 ',
@FdOrder nvarchar = ' id ',--sort
@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 + ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' ORDER BY ' + @IdentityStr + ') as T ) Order BY ' + @IdentityStr
End
If @FdOrder <> ' and @FdOrder <> @IdentityStr begin
Set @sql = ' select * FROM (' + @sql + ') as t4867435348493 order by ' + @FdOrder
End
--print @sql
Execute (@sql)
Go

Background code:

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

private void Page_Load (object sender, System.EventArgs e)
{
Place user code here to initialize page
if (! Page.IsPostBack)
{
Datagrid1databind (1);
}
}

private void Datagrid1databind (int page)
{
SqlConnection conn = new SqlConnection ("server=qq;uid=sa;pwd=***;d atabase=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);
  }