asp.net sql 分页，，优化排序及分页，

调用代码：

<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>

View Code

 1         private void bind()
 2         {
 3             string sqlwhere = "CreatorId=\'" + user.Id.ToString() + "\'";
 4             Pager page = new Pager();
 5             page.Procedure = "SPager";
 6             page.PageIndex = pageindex;
 7             page.SelectStr = "*";
 8             page.PageSize = pagesize;
 9             page.TableName = "Papers";
10             page.Pagekey = "Id";
11             page.Subkey = "Id";
12             page.PagekeyOrderType = 0;
13             page.Order = "";
14             page.WhereCondition = sqlwhere;
15             DataTable dt = page.GetDatas(pageindex);
16             listpage.RecordCount = page.RecordCount;
17             this.r_scoreCount.DataSource = dt.DefaultView;
18             this.r_scoreCount.DataBind();
19 
20         }
21         //分页控件绑定 
22         protected void listpage_PageChanged(object sender, EventArgs e)
23         {
24             pageindex = listpage.CurrentPageIndex;
25             bind();
26         }

View Code

C#代码

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Data;
  4 using System.Data.SqlClient;
  5 using System.Configuration;
  6 //using DocumentTransfer.DAL;
  7 
  8 namespace ExamOnline.Utils
  9 {
 10     /// <summary>
 11     /// 分页类,通过存储过程进行分页
 12     /// </summary>
 13     public class Pager
 14     {
 15         #region 参数
 16         private int tableNo;
 17         private int pageIndex = 1;
 18         private int pageSize = 20;
 19         private int rowCount;
 20         private string procedure = "pager";
 21         private string tableName = "";
 22         private string whereCondition = "1=1";
 23         private string selectStr = "*";
 24         private string pagekey = "";
 25         private string subkey = "";
 26         private int pagekeyorderType = 0;
 27         private string order = "";
 28         private string _FirstStr = "";
 29         private string _PrevStr = "";
 30         private string _NextStr = "";
 31         private string _LastStr = "";
 32         private string _TurnUrlStr = "";
 33         private string _Options = "";
 34         private string strCountww = "";  //共N条信息
 35         private string strPageww = "";    //第N页/共N页    
 36         private string strTurnww;  //跳转控件
 37         private string pageindexName = "page";
 38         private string _ConnString = SqlHelper.ConnectionString;
 39         /// <summary>
 40         /// 信息池连接字符串
 41         /// </summary>
 42         //public static string PoolConnString = "Data Source=10.10.101.79;Initial Catalog=CER_Master;Persist Security Info=True;User ID=sa;Password=123";
 43         /////////////////////2011-05-27张立华修改.连接字符串都从web.config中获取.////////////////////
 44         public static string PoolConnString =ConfigurationManager.AppSettings["SqlConnMaster"];
 45         /// <summary>
 46         /// 所要操作的存储过程名称,已有默认的分页存储过程
 47         /// </summary>
 48         public string Procedure
 49         {
 50             get
 51             {
 52                 return procedure;
 53             }
 54             set
 55             {
 56                 if (value == null || value.Length <= 0)
 57                 {
 58                     procedure = "pager";
 59                 }
 60                 else
 61                 {
 62                     procedure = value;
 63                 }
 64             }
 65         }
 66 
 67 
 68         /// <summary>
 69         /// 当前所要显示的页面数
 70         /// </summary>
 71         public int PageIndex
 72         {
 73             get
 74             {
 75                 return pageIndex;
 76             }
 77             set
 78             {
 79                 pageIndex = value;
 80             }
 81         }
 82 
 83 
 84         /// <summary>
 85         /// 总的页面数
 86         /// </summary>
 87         public int PageCount { get; set; }
 88 
 89 
 90         /// <summary>
 91         /// 总行数
 92         /// </summary>
 93         public int RecordCount { get; set; }
 94 
 95 
 96         /// <summary>
 97         /// 每页条数
 98         /// </summary>
 99         public int PageSize
100         {
101             get
102             {
103                 return pageSize;
104             }
105             set
106             {
107                 pageSize = value;
108             }
109         }
110 
111 
112         /// <summary>
113         /// 表名称
114         /// </summary>
115         public string TableName
116         {
117             get
118             {
119                 return tableName;
120             }
121             set
122             {
123                 tableName = value;
124             }
125         }
126 
127 
128         /// <summary>
129         /// 条件查询
130         /// </summary>
131         public string WhereCondition
132         {
133             get
134             {
135                 return whereCondition;
136             }
137             set
138             {
139                 whereCondition = value;
140             }
141         }
142 
143 
144         /// <summary>
145         /// 查询目标(搜索目标),比如:AddTime AS 时间,ID AS 编号
146         /// </summary>
147         public string SelectStr
148         {
149             get
150             {
151                 return selectStr;
152             }
153             set
154             {
155                 selectStr = value;
156             }
157         }
158         /// <summary>
159         /// /
160         /// </summary>
161         public string Subkey
162         {
163             get
164             {
165                 return subkey;
166             }
167             set
168             {
169                 subkey = value;
170             }
171         }
172 
173         /// <summary>
174         /// 排序表达式
175         /// </summary>
176         public string Pagekey
177         {
178             get
179             {
180                 return pagekey;
181             }
182             set
183             {
184                 pagekey = value;
185             }
186         }
187         /// <summary>
188         /// 排序类型 true:asc false:desc
189         /// </summary>
190         public int PagekeyOrderType
191         {
192             get
193             {
194                 return pagekeyorderType;
195             }
196             set
197             {
198                 pagekeyorderType = value;
199             }
200         }
201         /// <summary>
202         /// 
203         /// </summary>
204         public string Order
205         {
206             get
207             {
208                 return order;
209             }
210             set
211             {
212                 order = value;
213             }
214         }
215         /// <summary>
216         /// 得到当前返回的数量
217         /// </summary>
218         public int RowCount
219         {
220             get
221             {
222                 return rowCount;
223             }
224         }
225         /// <summary>
226         /// 首页 显示样式
227         /// </summary>
228         public string FirstStr
229         {
230             get { return _FirstStr; }
231             set { _FirstStr = value; }
232         }
233 
234         /// <summary>
235         /// 上一页 显示样式
236         /// </summary>
237         public string PrevStr
238         {
239             get { return _PrevStr; }
240             set { _PrevStr = value; }
241         }
242 
243         /// <summary>
244         /// 下一页 显示样式
245         /// </summary>
246         public string NextStr
247         {
248             get { return _NextStr; }
249             set { _NextStr = value; }
250         }
251         /// <summary>
252         /// 尾页 显示样式
253         /// </summary>
254         public string LastStr
255         {
256             get { return _LastStr; }
257             set { _LastStr = value; }
258         }
259         /// <summary>
260         /// 跳转 的url链接
261         /// </summary>
262         public string TurnUrlStr
263         {
264             get { return _TurnUrlStr; }
265             set { _TurnUrlStr = value; }
266         }
267         /// <summary>
268         /// 跳转的url链接的参数前面不要加问号和与号
269         /// </summary>
270         public string Options
271         {
272             get { return _Options; }
273             set { _Options = value; }
274         }
275         /// <summary>
276         /// 分页参数名称
277         /// </summary>
278         public string PageIndexName
279         {
280             get { return pageindexName; }
281             set { pageindexName = value; }
282         }
283         /// <summary>
284         /// 连接字符串
285         /// </summary>
286         public string ConnString
287         {
288             get { return _ConnString; }
289             set { _ConnString = value; }
290         }
291         #endregion 参数
292         /// <summary>
293         /// 分页查寻结果
294         /// </summary>
295         public DataTable GetDatas(int pageIndex)
296         {
297             this.pageIndex = pageIndex;
298             Pager pager = this;
299             DataTable returnTb = Pagination(ref pager).Tables[0];
300             rowCount = returnTb.Rows.Count;
301             return returnTb;
302         }
303 
304         /// <summary>
305         /// 分页操作存储过程函数
306         /// </summary>
307         /// <param name="pager">Pager</param>
308         /// <returns>返回DataSet</returns>
309         private static DataSet Pagination(ref Pager pager)
310         {
311             SqlParameter[] par = new SqlParameter[11];
312             par[0] = new SqlParameter("@TableName",SqlDbType.NVarChar,200);
313             par[0].Value = pager.TableName;
314             par[1] = new SqlParameter("@SelectStr", SqlDbType.NVarChar, 200);
315             par[1].Value = pager.SelectStr;
316             par[2] = new SqlParameter("@Pagekey", SqlDbType.NVarChar, 200);
317             par[2].Value = pager.Pagekey;
318             par[3] = new SqlParameter("@Subkey", SqlDbType.NVarChar, 200);
319             par[3].Value = pager.Subkey;
320             par[4] = new SqlParameter("@KeyOrderType", SqlDbType.Int);
321             par[4].Value = pager.PagekeyOrderType;
322             
323             par[5] = new SqlParameter("@pageSize",SqlDbType.Int);
324             par[5].Value = pager.PageSize;
325             par[6] = new SqlParameter("@PageIndex", SqlDbType.Int);
326             par[6].Value = pager.pageIndex;
327 
328             par[7] = new SqlParameter("@RecordCount",SqlDbType.Int);
329             par[7].Direction = ParameterDirection.InputOutput;
330             par[8] = new SqlParameter("@PageCount",SqlDbType.Int);
331             par[8].Direction = ParameterDirection.InputOutput;
332 
333             par[9] = new SqlParameter("@Order", SqlDbType.NVarChar, 200);
334             par[9].Value = pager.Order;
335 
336             par[10] = new SqlParameter("@WhereCondition", SqlDbType.NVarChar, 1000);
337             par[10].Value = pager.WhereCondition;
338             DataSet ds = SqlHelper.ExecuteDataset(pager.ConnString,CommandType.StoredProcedure,pager.Procedure,par);
339             pager.RecordCount = (int)par[7].Value;
340             pager.PageCount = (int)par[8].Value;
341             return ds;
342         }
343         #region 返回分页后的页码显示
344         /// <summary>
345         /// 返回分页后的页码显示
346         /// </summary>
347         /// <param name="bolCount">是否显示 共N条信息</param>
348         /// <param name="bolPage">是否显示 第N页/共N页</param>
349         /// <param name="bolFirst">是否显示 首页</param>
350         /// <param name="bolLast">是否显示 尾页</param>
351         /// <param name="bolTurn">是否显示 跳转控件</param>
352         /// <param name="IsChinese">是否 用中文显示</param>
353         /// <param name="intStyle">样式选择 1:字符 2:符号</param>
354         /// <param name="intShowNum">每页显示多少个数字</param>
355         /// <param name="isHtml">是否HTML分页</param>
356         /// <param name="exName">如果为HTML分页,要输入HTML后缀名</param>
357         /// <returns>返回分页后的页码显示</returns>
358         public string GetShowPageStr(bool bolCount, bool bolPage, bool bolFirst, bool bolLast, bool bolTurn, bool IsChinese, int intStyle, int intShowNum,bool isHtml,string exName)
359         {
360             string strPageShowww = "";
361             string _FirstStr2 = "";
362             string _PrevStr2 = "";
363             string _NextStr2 = "";
364             string _LastStr2 = "";
365 
366              #region 公共处理
367             //总页数
368             PageCount = (RecordCount + pageSize - 1) / pageSize;
369 
370             //超出最小页码
371             if (pageIndex < 1)
372             {
373                 pageIndex = 1;
374             }
375 
376             //超出最大页码
377             if (pageIndex > PageCount)
378             {
379                 pageIndex = PageCount;
380             }
381 
382 
383             if (IsChinese)//中文分页
384             {
385                 //跳转
386                 strTurnww = String.Format("<input value='{0}' id='txtPageGo' name='txtPageGo' type='text' style='width:35px;'><input name='btnGo' type='button' id='btnGo' value='跳转' onclick=\"javascript:window.location.href='{1}?Page=' + document.getElementById('txtPageGo').value + '&{2}'\">", pageIndex, _TurnUrlStr, Options);
387                 //共N条信息
388                 strCountww = String.Format("共 {0} 条信息", RecordCount);
389                 //第N页/共N页
390                 strPageww = String.Format("第{0}页/共{1}页", pageIndex, PageCount);
391 
392                 //处理页码显示样式
393                 if (intStyle == 1)
394                 {
395                     if (_FirstStr == "")
396                     {
397                         _FirstStr = "首页";
398                     }
399                     if (_PrevStr == "")
400                     {
401                         _PrevStr = "上一页";
402                     }
403                     if (_NextStr == "")
404                     {
405                         _NextStr = "下一页";
406                     }
407                     if (_LastStr == "")
408                     {
409                         _LastStr = "尾页";
410                     }
411                 }
412                 else
413                 {
414                     if (_FirstStr == "")
415                     {
416                         _FirstStr = " << ";
417                     }
418                     if (_PrevStr == "")
419                     {
420                         _PrevStr = " < ";
421                     }
422                     if (_NextStr == "")
423                     {
424                         _NextStr = " > ";
425                     }
426                     if (_LastStr == "")
427                     {
428                         _LastStr = " >> ";
429                     }
430                 }
431             }
432             else//英文文分页
433             {
434                 //跳转
435                 strTurnww = String.Format("<input value='{0}' id='txtPageGo' name='txtPageGo' type='text' style='width:35px;'><input name='btnGo' type='button' id='btnGo' value='Goto' onclick=\"javascript:window.location.href='{1}?Page=' + document.getElementById('txtPageGo').value + '&{2}'\">", pageIndex, _TurnUrlStr, Options);
436                 //共N条信息
437                 strCountww = String.Format("Total {0} Infos", RecordCount);
438                 //第N页/共N页
439                 strPageww = String.Format(" {0}/{1} ", pageIndex, PageCount);
440 
441                 //处理页码显示样式
442                 if (intStyle == 1)
443                 {
444                     if (_FirstStr == "")
445                     {
446                         _FirstStr = " First ";
447                     }
448                     if (_PrevStr == "")
449                     {
450                         _PrevStr = " Previous ";
451                     }
452                     if (_NextStr == "")
453                     {
454                         _NextStr = " Next ";
455                     }
456                     if (_LastStr == "")
457                     {
458                         _LastStr = " Last ";
459                     }
460                 }
461                 else
462                 {
463                     if (_FirstStr == "")
464                     {
465                         _FirstStr = " << ";
466                     }
467                     if (_PrevStr == "")
468                     {
469                         _PrevStr = " < ";
470                     }
471                     if (_NextStr == "")
472                     {
473                         _NextStr = " > ";
474                     }
475                     if (_LastStr == "")
476                     {
477                         _LastStr = " >> ";
478                     }
479                 }
480             }
481             #endregion
482             
483             //没有记录
484             if (RecordCount <= 0)
485             {
486                 strPageShowww = strCountww;
487             }
488             //有记录
489             else
490             {
491                 //只有一页
492                 if (PageCount <= 1)
493                 {
494                     strPageShowww = String.Format("{0}  {1}", strCountww, strPageww);
495                 }
496                 //不止一页
497                 else
498                 {
499                     //页码链接处理
500                     #region 页码链接处理
501                     //第一页
502                     if (pageIndex == 1)
503                     {
504                         _FirstStr2 = _FirstStr;
505                         _PrevStr2 = _PrevStr;
506                     }
507                     else
508                     {
509                         if (isHtml)
510                         {
511                             _FirstStr2 = String.Format("<a href=\"{0}.{1}\">{2}</a>", _TurnUrlStr, exName, _FirstStr);
512                             if (pageIndex - 1 == 1)
513                             {
514                                 _PrevStr2 = String.Format("<a href=\"{0}.{1}\">{2}</a>", _TurnUrlStr, exName, _PrevStr);
515                             }
516                             else
517                             {
518                                 _PrevStr2 = String.Format("<a href=\"{0}_{1}.{2}\">{3}</a>", _TurnUrlStr, Convert.ToString(pageIndex - 1), exName, _PrevStr);
519                             }
520                         }
521                         else
522                         {
523                             _FirstStr2 = String.Format("<a href=\"{0}?{1}=1&{2}\">{3}</a>", _TurnUrlStr, pageindexName, _Options, _FirstStr);
524                             _PrevStr2 = String.Format("<a href=\"{0}?{1}={2}&{3}\">{4}</a>", _TurnUrlStr, pageindexName, Convert.ToString(pageIndex - 1), _Options, _PrevStr);
525                         }
526                     }
527 
528                     //最后一页
529                     if (pageIndex == PageCount)
530                     {
531                         _NextStr2 = _NextStr;
532                         _LastStr2 = _LastStr;
533                     }
534                     else
535                     {
536                         if (isHtml)
537                         {
538                             _NextStr2 = String.Format("<a href=\"{0}_{1}.{2}\">{3}</a>", _TurnUrlStr, Convert.ToString(pageIndex + 1), exName, _NextStr);
539                             _LastStr2 = String.Format("<a href=\"{0}_{1}.{2}\">{3}</a>", _TurnUrlStr, PageCount, exName, _LastStr);
540                         }
541                         else
542                         {
543                             _NextStr2 = String.Format("<a href=\"{0}?{1}={2}&{3}\">{4}</a>", _TurnUrlStr, pageindexName, Convert.ToString(pageIndex + 1), _Options, _NextStr);
544                             _LastStr2 = String.Format("<a href=\"{0}?{1}={2}&{3}\">{4}</a>", _TurnUrlStr, pageindexName, PageCount, _Options, _LastStr);
545                         }
546                     }
547 
548                     //----处理显示页码-----------
549                     if (bolCount == true)//共N条信息
550                     {
551                         strPageShowww = String.Format("{0}  {1}", strPageShowww, strCountww);
552                     }
553                     if (bolPage == true)//第N页/共N页
554                     {
555                         strPageShowww = String.Format("{0}  {1}", strPageShowww, strPageww);
556                     }
557                     if (bolFirst == true) //首页
558                     {
559                         strPageShowww = String.Format("{0}  {1}", strPageShowww, _FirstStr2);
560                     }
561                     strPageShowww = strPageShowww + "{0}";//上一页
562                     //下一页
563 
564                     if (bolLast == true)
565                         //尾页
566                         strPageShowww = String.Format("{0}  {1}", strPageShowww + "{1}{2}", _LastStr2);
567                     else
568                         strPageShowww = strPageShowww + "{1}{2}";
569                     if (bolTurn == true)//跳转控件
570                     {
571                         strPageShowww = String.Format("{0}  {1}", strPageShowww, strTurnww);
572                     }
573 
574                     #endregion
575                     #region 样式一: 共X条信息 第N页/共M页 首页 上一页 下一页 尾页  跳转
576                     if (intStyle == 1)
577                     {
578                         strPageShowww = strPageShowww.Replace("{0}", "  " + _PrevStr2);//上一页
579                         strPageShowww = strPageShowww.Replace("{1}", "  " + _NextStr2);//下一页
580                         strPageShowww = strPageShowww.Replace("{2}", "");//
581                     }
582                     #endregion
583                     #region 样式二: 共X条信息 第N页/共M页 首页 1 2 3 尾页 跳转
584 
585                     if (intStyle == 2)
586                     {
587                         int PageTemp = 0;
588                         string strPageNum = "";
589                         string strTempNow = "";
590 
591                         //当页码超过最后一批该显示
592                         if (pageIndex > PageCount - intShowNum + 1)
593                         {
594                             PageTemp = PageCount < intShowNum ? 0 : PageCount - intShowNum;
595                             for (int i = 1; i <= intShowNum; i++)
596                             {
597                                 if (i > PageCount) break;
598 
599                                 strTempNow = Convert.ToString(PageTemp + i);
600 
601                                 //当前页不显示超链接
602                                 if( PageIndex == PageTemp + i)
603                                 {
604                                     strPageNum = String.Format("{0}<b>{1}</b> ", strPageNum, strTempNow);
605                                 }
606                                 else
607                                 {
608                                     if (isHtml)
609                                     {
610                                         if (strTempNow == "1")
611                                         {
612                                             strPageNum = String.Format("{0}<a href=\"{1}.{2}\">{3}</a> ", strPageNum, _TurnUrlStr, exName, strTempNow);
613                                         }
614                                         else
615                                         {
616                                             strPageNum = String.Format("{0}<a href=\"{1}_{2}.{3}\">{2}</a> ", strPageNum, _TurnUrlStr, strTempNow, exName);
617                                         }
618                                     }
619                                     else
620                                     {
621                                         strPageNum = String.Format("{0}<a href=\"{1}?{2}={3}&{4}\">{3}</a> ", strPageNum, _TurnUrlStr, pageindexName, strTempNow, _Options);
622                                     }
623                                 }
624                             }
625                         }
626                         else
627                         {
628                             for (int i = 0; i < intShowNum; i++)
629                             {
630                                 strTempNow = Convert.ToString(PageIndex + i);
631 
632                                 //当前页不显示超链接
633                                 if (i == 0)
634                                 {
635                                     strPageNum = String.Format("{0}<b>{1}</b> ", strPageNum, strTempNow);
636                                 }
637                                 else
638                                 {
639                                     if (isHtml)
640                                     {
641                                         if (strTempNow == "1")
642                                         {
643                                             strPageNum = String.Format("{0}<a href=\"{1}.{2}\">{3}</a> ", strPageNum, _TurnUrlStr, exName, strTempNow);
644                                         }
645                                         else
646                                         {
647                                             strPageNum = String.Format("{0}<a href=\"{1}_{2}.{3}\">{2}</a> ", strPageNum, _TurnUrlStr, strTempNow, exName);
648                                         }
649                                     }
650                                     else
651                                     {
652                                         strPageNum = String.Format("{0}<a href=\"{1}?P{2}={3}&{4}\">{3}</a> ", strPageNum, _TurnUrlStr, pageindexName, strTempNow, _Options);
653                                     }
654                                 }
655                             }
656                         }
657 
658                         //
659                         strPageShowww = strPageShowww.Replace("{0}", "  " + _PrevStr2);//上一页
660                         strPageShowww = strPageShowww.Replace("{1}", "  " + strPageNum);//显示数字
661                         strPageShowww = strPageShowww.Replace("{2}", "  " + _NextStr2);//下一页
662                     }
663                     #endregion
664                 }
665             }
666             return strPageShowww;
667         }
668         #endregion
669 
670     }
671 }

View Code

sql 存储过程。

 1 USE [ExamOnline]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[SPager]    Script Date: 06/13/2015 14:00:45 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 ALTER PROCEDURE [dbo].[SPager]
 9 (
10 @TableName VARCHAR(255), -- 表名(注意：可以多表链接)
11 @SelectStr VARCHAR(1000) = '', -- 需要返回的列
12 @Pagekey VARCHAR(255)='',--必选，分页标识键，尽量保证无重复
13 @Subkey VARCHAR(255)='', --标识键别名，用于子流程
14 @KeyOrderType BIT = 0, -- 设置排序类型, 非 0 值则降序
15 @PageSize INT = 10, -- 页尺寸
16 @PageIndex INT = 1, -- 页码
17 @RecordCount INT = 1 OUT, --查询到的记录数
18 @PageCount INT = 0 OUTPUT,--总页数4
19 @Order VARCHAR(255)='', -- 分页后排序 “order by yufa”
20 @WhereCondition VARCHAR(500) = '' -- 查询条件 (注意: 不要加 where)
21 )
22 AS
23 DECLARE @strSQL NVARCHAR(4000) -- 主语句
24 DECLARE @strTmp VARCHAR(110) -- 临时变量
25 DECLARE @strPagekeyOrder VARCHAR(300) -- 排序类型
26  
27 IF @WhereCondition != ''
28 SET @strSQL = 'select @RecordCount=count(*) from ' + @TableName + ' where '+@WhereCondition
29 ELSE
30 SET @strSQL = 'select @RecordCount=count(*) from ' + @TableName
31 EXEC sp_executesql @strSQL,N'@RecordCount int out',@RecordCount OUT
32 --以上代码的意思是如果@RecordCount传递过来的不是0，就执行总数统计。以下的所有代码都是@RecordCount为0的情况
33 SET @strSQL='';
34 SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
35 
36 IF @KeyOrderType != 0
37 BEGIN
38 SET @strTmp = '<(select min'
39 SET @strPagekeyOrder = ' order by ' + @Subkey +' desc'--如果@OrderType不是0，就执行降序，这句很重要！
40 END
41 ELSE
42 BEGIN
43 SET @strTmp = '>(select max'
44 SET @strPagekeyOrder = ' order by ' + @Subkey +' asc'
45 END
46 IF @PageIndex = 1
47 BEGIN
48 IF @WhereCondition != ''
49 SET @strSQL = 'select top ' + STR(@PageSize) +' '+@SelectStr+ ' from ' + @TableName + ' where ' + @WhereCondition + ' ' + @strPagekeyOrder
50 ELSE
51 SET @strSQL = 'select top ' + STR(@PageSize) +' '+@SelectStr+ ' from '+ @TableName + ' '+ @strPagekeyOrder--如果是第一页就执行以上代码，这样会加快执行速度
52 END
53 ELSE
54 begin--以下代码赋予了@strSQL以真正执行的SQL代码
55 SET @strSQL = 'select top ' + STR(@PageSize) +' '+@SelectStr+ ' from '
56 + @TableName + ' where ' + @Pagekey + ' ' + @strTmp + '('+ @Subkey + ') from (select top ' + STR((@PageIndex-1)*@PageSize) + ' '+ @SelectStr + ' from ' + @TableName + ' ' + @strPagekeyOrder + ') as tblTmp)'+ @Order
57 IF @WhereCondition != ''
58 SET @strSQL = 'select top ' + STR(@PageSize) +' '+@SelectStr+ ' from '
59 + @TableName + ' where ' + @Pagekey + ' ' + @strTmp + '('+ @Subkey + ') from (select top ' + STR((@PageIndex-1)*@PageSize) + ' '+ @SelectStr + ' from ' + @TableName + ' where ' + @WhereCondition + ' '+ @strPagekeyOrder + ') as tblTmp) and ' + @WhereCondition + ' ' + @Order
60 END
61 EXEC (@strSQL)

View Code

posted @ 2015-06-13 14:12 Rocken.li 阅读(642) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

asp.net sql 分页，，优化 排序 及分页，

公告

asp.net sql 分页，，优化排序及分页，