C# 实现分页查询方法实例

namespace ConsoleApplication
{
    /// <summary>
    /// 定义查询类
    /// </summary>
    public class Query
    {
   
        public DateTime BeginTime = new DateTime(1900, 1, 1, 0, 0, 0);

        public string Mackey = string.Empty;

        public string DeviceNumber = string.Empty;

        public string MarketName = string.Empty;

        public int ProviderId = 0;

        public int ModerId = 0;

        public int PageNumber = 1;

        public int PageSize = 0;

        public bool ReturnCount = false;

        /// <summary>
        /// 默认构造函数
        /// </summary>
        public Query()
        {
        }

    }

        #region GetDeviceDetail
        public static ProductInfoList GetDetail(Query query)
        {
            // 定义返回值数组list
            ProductInfoList products = new ProductInfoList();
            // 定义要查询的列名，方便查询count(*)时做替换
            string fields = @"row_number() over (order by a.mackey) row, a.*, b.model_name, b.business_type, c.*, s.store_name";
            
            // 定义分页查询的sql，条件默认为1=1，列名用占位符代替
            string sql = @"select {0} from device a join device_model b on a.model_id=b.model_id 
                        where 1=1 ";
            // 定义一个条件sql stringbuilder
            StringBuilder sb = new StringBuilder();

            if (!string.IsNullOrEmpty(query.Mackey))
            {
                sb.Append(string.Format(" and a.mackey like '{0}%' ", query.Mackey.Trim()));
            }

            if (query.BeginTime != null && query.BeginTime != new DateTime(1900, 1, 1, 0, 0, 0))
            {
                DateTime beginDate = new DateTime(query.BeginTime.Year, query.BeginTime.Month, query.BeginTime.Day);
                sb.AppendFormat(
                    " and a.register_date >='{0}'",
                    beginDate.ToString("yyyy-MM-dd"));
            }

            try
            {
                if (query.ReturnCount)
                {
                    // 查询总总条数，满足条件的所有记录
                    object o = SqlHelper.ExecuteScalar(Conn.IOF, CommandType.Text, string.Format(sql + sb.ToString(), "count(*)"));
                    if (o != null)
                    {
                        products.MatchCount = Convert.ToInt32(o);
                    }
                }
                // 定义分页sql
                string sqlOrder = string.Format(@"select * from ( {0} )  as products where row between {1}*({2}-1)+1 and {1}*{2}", string.Format(sql + sb.ToString(), fields), query.PageSize, query.PageNumber);
               
                DataSet ds = SqlHelper.ExecuteDataset(Conn.IOF, CommandType.Text, sqlOrder);

                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    // 循环DT
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        ProductInfo product = new ProductInfo();
                        product.Cost = Convert.ToDecimal(dr["cost"].ToString());
                        // 。。。。。。
                        products.Products.Add(product);
                    }
                }
                products.BaseResult.Code = 0;
            }
            catch (Exception ex)
            {
                products.BaseResult.Code = 401;
                products.BaseResult.Message = ex.Message + ex.StackTrace + ex.InnerException;
                log.ErrorException("GeDetail", ex);
            }
            return products;
        }
        #endregion
}