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 of the specified page CREATE Procedure [getCustomersDataPage] @PageIndex INT, - Page Index, get @pagesize int, - page display quantity from DataGrid, get @recordcount int out from DataGrid, - Return to record total @PageCount Int out, - Return to page number @StrGetfields nvarchar (1000), - Require query column @tablename nvarchar (500), - Table Name @id nvarchar (100), - Primon, (for the primary key of the table) @strwhere nvarchar (1000) = ', Query conditions (Note: Do not add where) @SortName nvarchar (50) = 'asc', - Sort by @Ordername nvarchar (100) - Parent Query Sort by 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 ONDECLARE @SQLSTR NVARCHAR (3000) - The actual page number is less than the current page number or the largest 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 the DataGrid index --OR @ PageCount = 1 Begin - set to the last One page set @ PageIndex = @ PageCount-1 End else if @PageCount <= @PageIndex and @ PageIndex = 0; End Endif @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 One page begin if len (@strwhere) = 0 begin set @Sqlstr = n 'select' @ strGetfields 'from' @ Tablename 'Where' @ i D '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 (SELECT TOP' STR (/ * @ 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' @ otname @ SortName ' ) and ' @ Strwhere ' Order by ' @ OrderName @ SortName Endexec (@
SQLSTR) set nocount offGO call method in asp.net #region // call the function to bind data private void DataGridDataBind () {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 ("@ pagesize", sqldbtype.int); comm.selectcommand.parameters [1] .Value = pagesize; comm.selectcommand.parameters.add (New Sqlparameter ("@ recordcount", SQLDB Type.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 (new SqlParameter ( "@ strGetFields", SqlDbType.NVarChar)); comm.SelectCommand.Parameters [4] .Value = the "tOrder.orderTime as' time line ', Torder.FacRernum as' factory order number, torder.quantity as 'fixed order', Torder.RealQuantity As 'actual delivery number', torder.reqtime as 'requires shipping time' , torder.reptime as 'shipping time'
, tmaterial.matname as 'material', tMATERIAL.COLNAME AS 'color', Torder.Leavequantity As 'not ship number', Torder.OrDerstatic AS 'All shipments', Torder.OrderDetail AS 'Note' "; / * Torder .comname as' company ', Torder.comordernum as' company order number, * / comm.selectcommand.parameters.add (New SQLParameter ("@ TableName", 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 ("@ OrderName", SqldbType.nvarchar); comm.selectcommand.parameters [7] .value = "TMATERIAL.MATNAME"; comm.selectcommand.parameters.add (New Sqlparameter ("@ strwhere"); comm.selectcommand.parameters [8] .value = "FACNAME = '" en1.deRPT (this .Request.QueryString ["FACNAME"]. TOSTRING ()) "'and facordernum ='" en1.decyrpt (this.Request.QueryString " "FACNUM"]. TOSTRING ()) "'"; // Comm.Parameters.Add (New SQLParameter ("@ SortName", SqldbType.nvarchar); // 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;} // return datafill.mydateset;} ///
