SQL Server paging stored procedures and calling encapsulation code used in recent projects

Stored Procedure:

 

Use [RS] Go/****** object: storedprocedure [DBO]. [up_paging] script Date: 05/30/2013 17:43:23 *****/set ansi_nulls ongoset quoted_identifier ongoalter procedure [DBO]. [up_paging] @ tables nvarchar (512), -- table name. For Multiple tables, use ta a inner join tb B on. aid = B. aid @ PK nvarchar (128) = '', -- primary key, with table header. aid @ sort nvarchar (512) = '', -- Sort field @ pageindex Int = 1, -- start page number @ pagesize Int = 10, -- page size @ fields nvarchar (1024) = '*', -- Read field @ where nvarchar (1024) = NULL, -- Where condition @ recordcount int output -- returns the total number of asdeclare @ strfilter nvarchar (4000) Declare @ SQL nvarchar (4000) if @ Where is not null and @ where! = ''In in set @ strfilter = 'where' + @ where + ''' endelse begin set @ strfilter = ''endif @ sort = ''set @ sort = @ PK + 'desc 'If @ pageindex <1 Set @ pageindex = 1if @ pageindex = 1 -- the first page improves performance. Begin set @ SQL = 'select top' + STR (@ pagesize) + ''+ @ fields + 'from' + @ tables +'' + @ strfilter + 'ORDER BY' + @ sort print @ sqlend else begin declare @ start_id varchar (50) -- page START index declare @ end_id varchar (50) -- page end index set @ start_id = convert (varchar (50), (@ pageindex-1) * @ pagesize + 1) set @ end_id = convert (varchar (50), @ pageindex * @ pagesize) set @ SQL = 'select' + @ fields + 'from' + '(' + 'select row_number () over (order by '+ @ sort +') as rownum, '+ @ fields + 'from' + @ tables + ''+ @ strfilter + ') as d' + 'where rownum between' + @ start_id + 'and' + @ end_id + 'ORDER BY' + @ sort endexec (@ SQL) -- total number of items set @ recordcount = 0; Set @ SQL = n' select @ recordcount = count (1) from '+ @ tables + @ strfilterexec sp_executesql @ SQL, n' @ recordcount int out', @ recordcount outgo

C # Call code:

 

 

/// <Summary> /// common paging /// </Summary> /// <Param name = "Pi"> </param> Public Virtual void Pager (rpageinfo pi) {/* stored procedure create procedure up_paging @ tables nvarchar (512), -- table name. For Multiple tables, use ta a inner to join tb B on. aid = B. aid @ PK nvarchar (128) = '', -- primary key, with table header. aid @ sort nvarchar (512) = '', -- Sort field @ pageindex Int = 1, -- start page number @ pagesize Int = 10, -- page size @ fields nvarchar (1024) = '*', -- read field @ where nvarchar (1024) = NULL, -- Where condition @ recordcount int output -- total number of returned results as */sqlparameter [] parameters = {New sqlparameter ("@ tables", sqldbtype. nvarchar, 512), new sqlparameter ("@ Pk", sqldbtype. nvarchar, 128), new sqlparameter ("@ sort", sqldbtype. nvarchar, 512), new sqlparameter ("@ pageindex", sqldbtype. INT), new sqlparameter ("@ pagesize", sqldbtype. INT), new sqlparameter ("@ fields", sqldbtype. nvarchar, 1024), new sqlparameter ("@ where", sqldbtype. nvarchar, 1024), new sqlparameter ("@ recordcount", sqldbtype. INT)}; Parameters [0]. value = pi. tablename; Parameters [1]. value = pi. PK; Parameters [2]. value = pi. sort; Parameters [3]. value = pi. pageindex; Parameters [4]. value = pi. pagesize; Parameters [5]. value = pi. fields; Parameters [6]. value = pi. fwhere; Parameters [7]. direction = parameterdirection. output; datatable DATA = dbhelpersql. runprocedure ("up_paging", parameters, "ds "). tables [0]; Pi. data = data; Pi. recordcount = convert. toint32 (parameters [7]. value); Pi. pagecount = (long) math. ceiling (Pi. recordcount/(Pi. pagesize + 0.0 ));}

The page information encapsulated by rpageinfo. The Code is as follows:

 

 

/// <Summary> /// paging information class /// </Summary> public class rpageinfo {private string _ tablename; // <summary> // table name, it can be a subquery, but it must be: // (select * from Dept) as model // </Summary> Public String tablename {get {return _ tablename ;} set {_ tablename = value ;}} private string _ PK = string. empty; // <summary> // primary key or other fields // </Summary> Public String PK {get {return _ PK ;} set {_ PK = value ;}} private string _ sort = string. empty; // <summary> // sort, for example, field 1 ASC, field 2 DESC // The primary key and sorting fields are mutually exclusive. // </Summary> Public String sort {get {return _ sort;} set {_ sort = value ;}} private string _ fields = "*"; // <summary> // the field to be displayed, the default value is * // </Summary> Public String fields {get {return _ fields;} set {_ fields = value;} private string _ fwhere = string. empty; // <summary> // filtering condition /// </Summary> Public String fwhere {get {return _ fwhere;} set {_ fwhere = value ;}} private int _ pageindex = 1; // <summary> // page number // </Summary> Public int pageindex {get {return _ pageindex ;} set {_ pageindex = value ;}} private int _ pagesize = 10; /// <summary> // page size /// </Summary> Public int pagesize {get {return _ pagesize;} set {_ pagesize = value ;}} private long _ recordcount; // <summary> // Total number of items /// </Summary> Public long recordcount {get {return _ recordcount ;} set {_ recordcount = value ;}} private long _ pagecount; /// <summary> // total number of pages /// </Summary> Public long pagecount {get {return _ pagecount;} set {_ pagecount = value ;}} private datatable _ data; // <summary> // result set // </Summary> Public datatable data {get {return _ data ;} set {_ DATA = value ;}}}

Code:

 

 

/// <Summary> /// obtain Hospital Information /// </Summary> /// <Param name = "Pi"> </param> /// <Param name = "iscityhosploud"> whether a county-level hospital </param> // <Param name = "hospitalname"> hospital name </param> Public void gethospitals (rpageinfo Pi, bool? Iscityhospame, string hospitalname) {pi. tablename = "(select * from [hospital] where 1 = 1"; if (iscityhospital! = NULL & iscityhospital. Value) {pi. tablename + = "and [level] = 1";} else if (iscityhospital! = NULL &&! Iscityhospital. Value) {pi. tablename + = "and [level] = 0";} If (hospitalname! = NULL & hospitalname. Trim ()! = String. empty) {pi. tablename + = "and [hostpitalname] Like '%" + hospitalname + "%'";} pi. tablename + = ") as model"; Pi. PK = "hospitalid"; base. pager (PI );}