Custom Patement for DataGrid Controls in ASP.NET

Here is the stored procedure to access the single page, there are many implementations, but this is the most common, create procedure] @PageSize Int, @PageSize Int, @recordcount int out, @PageCount int out as select @recordcount = count (* ) FROM Customers SET @PageCount = CEILING (@RecordCount * 1.0 / @PageSize) DECLARE @SQLSTR NVARCHAR (1000) IF @PageIndex = 0 OR @PageCount <= 1 SET @SQLSTR = N'SELECT TOP ' STR (@PageSize) 'CustomerID, CompanyName, Address, Phone FROM Customers ORDER BY CustomerID DESC' ELSE IF @PageIndex = @PageCount - 1 SET @SQLSTR = N 'SELECT * FROM (SELECT TOP' STR (@RecordCount - @PageSize * @PageIndex) 'CustomerID, CompanyName, Address, Phone FROM Customers ORDER BY CustomerID ASC) TempTable ORDER BY CustomerID DESC' ELSE SET @SQLSTR = N 'SELECT TOP' STR (@PageSize) '* FROM (SELECT TOP' STR (@ RecordCount - @PageSize * @PageIndex) 'CustomerID, CompanyName, Address, Phone FROM Customers ORDER BY CustomerID ASC) TempTable ORDER BY CustomerID DESC' EXEC (@SQLSTR) GO acquisition records are used the number of pages stored and output of the process parameters. Get the data source, here returns a DataSet.

First define a data member, private int pagecount; // record number // Get a single page Data Private Static Dataset getCustomersData (int pageindex, int pageSize, ref int recordcount, ref int pagecount) { string connString = ConfigurationSettings.AppSettings [ "connString"]; SqlConnection conn = new SqlConnection (connString); SqlCommand comm = new SqlCommand ( "GetCustomersDataPage", conn); comm.Parameters.Add (new SqlParameter ( "@ PageIndex", SqlDbType. Int)); comm.Parameters [0] .value = pageindex; comm.parameters.add (new sqlparameter ("@ pagesize", sqldbtype.int)); comm.parameters [1] .value = pagesize; comm.Parameters. Add (new SqlParameter ( "@ RecordCount", SqlDbType.Int)); comm.Parameters [2] .Direction = ParameterDirection.Output; comm.Parameters.Add (new SqlParameter ( "@ PageCount", SqlDbType.Int)); comm .Parameters [3] .Direction = ParameterDirection.Output; comm.CommandType = CommandType.StoredProcedure; SqlDataAdapter dataAdapter = new SqlDataAdapter (comm); DataSet ds = new DataSet (); dataAdapter.Fill (ds); recordCount = (in T) Comm .Parameters [2] .value; PageCount = (int) comm.parameters [3] .value; return ds;} // Bind data to DataGrid, simultaneous refresh data total record number private void DataGridDataBind () {dataset ds = GetCustomersData (PageIndex, PageSize, ref recordCount, ref pageCount); this.DataGrid1.VirtualItemCount = recordCount; this.DataGrid1.DataSource = ds; this.DataGrid1.DataBind ();} the following are several variables paging public int attributes PageCount {get {return this.DataGrid1.PageCount;}} public int PageSize {get {return this.DataGrid1.PageSize;}} public int PageIndex {get {return this.DataGrid1.CurrentPageIndex;} set {this.DataGrid1.CurrentPageIndex = Value;