［C#实战］通用的数据表分页类

高性能,支持任意排序和链表.公开资源供大家参考。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


/**//// Common Class For Getting Data By page
/// Froog 20071130


namespace LiveBookings.Concierge.DAL.Common
...{
    public class CommonPage
    ...{
        private string mtableId = string.Empty;
        private string mtableName = string.Empty;
        private string mselectFields="*";
        private string morderFields;
        private int mpageIndex = 1;
        private int mpageCount;
        private int mrecordCount ;
        private int mpageSize = 15;
        private string mstrWhere = string.Empty;
        private readonly static string mconnString =ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        /**//// <summary>
        /// 
        /// </summary>
        /// <param name="tableName">Table Name</param>
        /// <param name="tableKey">Table PrimaryKey</param>
        public CommonPage(string tableName,string tableKey)
        ...{
            mtableName = tableName;
            mtableId = tableKey;
        }
        /**//// <summary>
        /// Table name or  a joined Table. ex  Order A JOIN OrderDetail.  e.g.B ON A.ID=B.OrderID
        /// </summary>
        public string TableName
        ...{
            get ...{ return mtableName; }
        }
        /**//// <summary>
        /// Get  PrimaryKey of the table.type must be int 
        /// </summary>
        public string TableKey
        ...{
            get ...{ return mtableId; }
        }   
        /**//// <summary>
        /// Get or Set Fields you want select.muliti field seperated by comma. e.g. "UserID,UserEmail"
        /// </summary>
        public string SelectFields
        ...{
            set
            ...{
                if (!string.IsNullOrEmpty(value))
                ...{
                    mselectFields = value;
                }
            }
            get ...{ return mselectFields; }
        }
        /**//// <summary>
        /// select condition. e.g."UserFirstName = 'Wang'"
        /// </summary>
        public string StrWhere
        ...{
            set ...{ mstrWhere = value; }
            get ...{ return mstrWhere; }
        }
        /**//// <summary>
        /// the string for ordering by
        /// </summary>
        public string OrderBy
        ...{
            set ...{ morderFields = value; }
            get ...{ return morderFields; }
        }
        /**//// <summary>
        ///  Get or Set page Size
        /// </summary>
        public int PageSize
        ...{
            set ...{ mpageSize = value; }
            get ...{ return mpageSize; }
        }
        /**//// <summary>
        /// Get or Set  Current Page Index
        /// </summary>
        public int PageIndex
        ...{
            set ...{ mpageIndex = value; }
            get ...{ return mpageIndex; }
        }
        /**//// <summary>
        /// Get Page Count
        /// </summary>
        public int PageCount
        ...{
            get
            ...{
                if (mrecordCount > 0)
                ...{
                    if (mrecordCount % mpageSize == 0)
                    ...{
                        mpageCount= mrecordCount / mpageSize;
                    }
                    else
                    ...{
                        mpageCount = mrecordCount / mpageSize+1; 
                    }
                }
                return mpageCount;
                
            }
        }
        public int RecordCount
        ...{
            get ...{ return mrecordCount; }
        }
        
        public void GetPageList(DataSet ds)
        ...{
            SqlParameter param = new SqlParameter();
            param = new SqlParameter("@Rowcount", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            param.Value = 0;
            using (SqlConnection conn = new SqlConnection(mconnString))
            ...{
                SqlCommand comm = new SqlCommand(generalSQL(), conn);
                comm.Parameters.Add(param);
                SqlDataAdapter adapter = new SqlDataAdapter(comm);
                adapter.Fill(ds.Tables[0]);
                mrecordCount = (int)param.Value;
                conn.Close();
            }
        }
        private string generalSQL()
        ...{
            int lastCount = 0;
            if (mpageIndex > 1)
            ...{
                lastCount = (mpageIndex - 1) * mpageSize;
            }
            if (string.IsNullOrEmpty(mstrWhere))
            ...{
                mstrWhere = "1=1";
            }
            if (!string.IsNullOrEmpty(morderFields))
            ...{
                morderFields = " ORDER BY " + morderFields;
            }
            if(!mselectFields.Trim().Equals("*"))
            ...{
                if (mselectFields.LastIndexOf(mtableId.Trim())<0)
                ...{
                    mselectFields += "," + mtableId;
                }
            }
            string sql = " SELECT TOP {3} {4} FROM {0} ";
            sql += " WHERE ({2})";
            if (lastCount > 0)
            ...{
                sql += " AND {1} NOT IN ( SELECT TOP {5} {1} FROM {0} WHERE ({2}) {6} ) ";
            }
            sql += " {6} ";
            sql += " SELECT @Rowcount=COUNT(*) FROM {0} WHERE {2} ";
            return string.Format(sql, mtableName, mtableId, mstrWhere, mpageSize,mselectFields,lastCount,morderFields);
        }
    }


}