[Original] Storage Procedure DataGrid Pedestrian Paging

SQLSERVER complete code / ***************************************************** ******************************* Add procedure name: getCustomersDataPage * Procedure: Universal Big Data Collection * Introduction Parameters: * Outlet Parameters: * Modify Record * Name Date Modification Type * NickleE 2005-1-17 New ***************************************************** *********************************** / - Get the data for the specified page

CREATE Procedure [getCustomersDataPage] @PageIndex Int, - Page Index, get @PageSize Int from DataGrid, - Get @RecordCount Int Out from DataGrid, - Return to Record Total @PageCount Int Out, - Return Sub-page After page number @StrGetfields nvarchar (1000), - Requires Query @Tablename NVARCHAR (500), - Table Name @id nvarchar (100), - Primary key, (for the primary key) @Strwhere NVARCHAR (1000 ) = ', - query conditions (Note: Do not add where) @SortName nvarchar (50) =' ASC ', - Sort by @Ordername nvarchar (100) - Parent query sort

Asdeclare @countselect nvarchar (2000) - Set statistics query statement if len (@strwhere) = 0 - If there is no query condition begin set @ countselect = n'select @countselect = count (*) from ' @ Tablename endelse - otherwise begin set @ countSelect = N'SELECT @CountRecord = COUNT (*) FROM ' @ tableName ' where ' @ strWhere end-- execute and return the total number of exec sp_executesql @ countSelect, N' @ CountRecord int output ', @ RecordCount outputSET @PageCount = CEILING (@Recordcount * 1.0 / @PageSize)

Set nocount on

Declare @sqlstr nvarchar (3000) - The actual page number is smaller than the current page number or maximum page number if @PageCount> = 0 - If the page number is greater than 0 begin if @PageCount <= @ PageIndex and @PageCount> 0 - if The actual number of pages smaller than the number of pages of DataGrid index --OR @ pagecount = 1 begin - set to the last page set @ pageindex = @PageCount <= @PageIndex and @ PageCount = 0 Begin SET @PageIndex = 0; End end

IF @PageIndex = 0 or @PageCount <= 1 - if the first page begin if len (@strwhere) = 0 begin set @sqlstr = N'select Top ' Str (@PageSize) @ strGetfields ' from ' @ tableName 'ORDER BY' @ orderName @ sortName end else begin SET @SQLSTR = N'SELECT TOP ' STR (@PageSize) @ strGetFields ' FROM ' @ tableName ' where ' @ strWhere ' ORDER BY ' @ orderName @ sortName end endELSE IF @PageIndex = @PageCount - 1 - If the last page begin if len (@strWhere) = 0 begin SET @SQLSTR = N 'SELECT' @ strGetFields 'FROM' @ tableName 'where' @ ID 'NOT IN (SELECT TOP' STR (/ * @ recordcount - * / @ Pagesize * @PageIndex) @ ID 'from' @ Tablename 'Order By' @ OrderName @ Sortname ') Order By' @ OrderName @ SortName End else begin set @sqlstr = n 'SELECT' @ strgetfields 'from' @ Tablename 'Where' @ ID 'NOT IN (/ * @ recordcount - * / @PageSize * @PageIndex) @ ID 'from' @ Tablename 'Where' @ Strwhere 'Order By' @ OrderName @ SortName ') And' @ Strwhere 'Order By'

@ OrderName @ sortName end endELSE - otherwise performing begin if len (@strWhere) = 0 begin SET @SQLSTR = N 'SELECT TOP' STR (@PageSize) @ strGetFields 'FROM' @ tableName 'where' @ ID 'not in (SELECT TOP' STR (/ * @ RecordCount - * / @ PageSize * @PageIndex) @ ID 'FROM' @ tableName 'ORDER BY' @ orderName @ sortName ') ORDER BY' @ orderName @ sortName end else begin SET @SQLSTR = N 'SELECT TOP' STR (@PageSize) @ strGetFields 'FROM' @ tableName 'where' @ ID 'not in (SELECT TOP' STR (/ * @ RecordCount - * / @PageSize * @PageIndex) @ ID 'FROM' @ tableName 'where' @ strWhere 'ORDER BY' @ orderName @ sortName ') and' @ strWhere 'ORDER BY' @ orderName @ sortName end endEXEC ( @Sqlstr) Set NoCount Offgo Call Method in ASP.NET #Region Call Function // Binding Data #Region Declaration / / ----------------------- ----------------------------------------------- // /// author: Li Miao (Nick.L EE) //// stored procedure DataGrid paging and attention point ///// Boyorgril@msn.com/////-------------------- ------------------------------------------------ # endregion Private void DataGridDatabase () {DataSet DS = GetCustomersData (PageIndex, PageSize, Ref Recordcount, Ref PageCount);

DataGrid1.VirtualItemCount = RecordCount; DataGrid1.DataSource = ds; DataGrid1.DataBind (); // GridExpand (this.DataGrid1,2); SetPagingState ();} private DataSet GetCustomersData (int pageIndex, int pageSize, ref int recordCount, ref int pageCount) {dataFill.ConString = System.Configuration.ConfigurationSettings.AppSettings [ "sqlConnectionString"]; dataFill.sqlClientDataSet ( "GetCustomersDataPage"); System.Data.SqlClient.SqlDataAdapter comm = dataFill.mySqlAdapter;

comm.SelectCommand.Parameters.Add (new SqlParameter ( "@ PageIndex", SqlDbType.Int)); comm.SelectCommand.Parameters [0] .Value = pageIndex; comm.SelectCommand.Parameters.Add (new SqlParameter ( "@ PageSize" , SqlDbType.Int)); comm.SelectCommand.Parameters [1] .Value = pageSize; comm.SelectCommand.Parameters.Add (new SqlParameter ( "@ RecordCount", SqlDbType.Int)); comm.SelectCommand.Parameters [2] .Direction = ParameterDirection.Output; comm.SelectCommand.Parameters.Add (new SqlParameter ( "@ PageCount", SqlDbType.Int)); comm.SelectCommand.Parameters [3] .Direction = ParameterDirection.Output;

Comm.selectcommand.Parameters.add ("@ strgetfields", sqldbtype.nvarchar); comm.selectcommand.parameters [4] .value = "Torder.ordertime as' Under Order Time ', Torder.FACNAME AS' Factory ', Torder.Facordernum as' plant order number, Torder.quantity as' set number', Torder.Realquantity As' actual delivery number ', torder.reqtime as' requires shipping time', torder.reptime as' shipping Time ', TMATERIAL.MATNAME AS' Material ', TiRDERIAL.COLNAME AS' Color ', Torder.LeaveQuantity As' Number (Torder.OrDerstatic AS 'All Shipped', Torder.Orderdetail AS 'Note'; / * Torder.comName As' company ', Torder.comordernum as' company order number, * / comm.selectcommand.parameters.add (New Sqlparameter ("@ TableName", SqldbType.nvarchar); Comm.SelectCommand.Parameters [5 ] .Value = "tOrder left join tStock on tOrder.stoID = tStock.stoID left join tMaterial on tStock.matID = tMaterial.matID"; comm.SelectCommand.Parameters.Add (new SqlParameter ( "@ ID", SqlDbType.NVarChar) ); Comm.selectcommand.parameters [6] .value = "torder.orderid"; comm.selectcommand.parameters.add (New Sqlparameter ("@ OrderName", SqldbType.nvarchar); CommArchar .SelectCommand.Parameters [7] .Value = "tmaterial.matname"; comm.selectcommand.parameters.add (New Sqlparameter ("@ strwhere", sqldbtype.nvarchar); comm.selectcommand.parameters [8] .value = " FACNAME = '" en1.decyrpt (this.Request.queryString [" FACNAME "]. TOSTRING ()) "' and facordernum = '" en1.decyrpt (this.Request.QueryString [" FACNUM "]. Tostring )) "'"; // comm.Parameters.Add (New Sqlparameter ("@ SortName");

// Comm.Parameters [8] .value = "desc"; Comm.Fill (DataFill.MydateSet);

Recordcount = (int) comm.selectcommand.parameters [2] .value; pagecount = (int) comm.selectcommand.parameters [3] .value;

IF (PageIndex> = PageCount && pageCount> 0) {pageindex = pagecount-1;} else f (pageindex> = PageCount && pageCount == 0) {pageIndex = 0;

}

///