mvc.net分页查询案例——DLL数据访问层（HouseDLL.cs）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Data;
using HouseSys.Models;
using System.Data.SqlClient;

namespace HouseSys.DLL
{
    /// <summary>
    /// 房屋的数据访问层
    /// </summary>
    public class HouseDLL
    {
        /// <summary>
        /// 分页查询所有房屋信息
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List<HouseModel> GetHousesAll(int  pageIndex,int pageSize,ConditionModel cond)
        {
            List<HouseModel> houseList = new List<HouseModel>();
            string sql = "select top "+pageSize+" * from house where  HouseId not in (select top "+(pageIndex-1)*pageSize+"  HouseId from House where 1=1) and 1=1 ";
            //动态查询
            if(cond!=null)
            {
                //根据标题
                if (cond.Title != null)
                {
                    sql += " and Title like"+cond.Title;
                }
                
                //最低价格到最高价格
                if (cond.StartPrice != null && cond.EndPrice != null)
                {
                    sql += " and Price >=" + cond.StartPrice + " and Price <= " + cond.EndPrice;
                }
                //根据最低的面积
                if(cond.StartProportion!=null && cond.EndProportion!=null)
                {
                    sql += " and floorage >=" + cond.StartProportion + " and floorage<="+cond.EndProportion;
                }

            }
            using (SqlDataReader reader = SqlHelper.ExcuteReader(sql, CommandType.Text, null))
            {
                while(reader.Read())
                {
                    HouseModel house = new HouseModel();
                    house.Contract = reader["Contract"].ToString();
                    house.Description = reader["Description"].ToString();
                    house.Floorage = Convert.ToDouble(reader["Floorage"]);
                    house.HouseId = Convert.ToInt32(reader["houseid"]);
                    house.Price = Convert.ToDouble(reader["Price"]);
                    house.PublishTime = Convert.ToDateTime(reader["PublishTime"]);
                    house.PublishUser = new UserDLL().GetUserById(Convert.ToInt32(reader["PublishUser"]));
                    house.Street = new StreetDLL().GetStreetById(Convert.ToInt32(reader["streetid"]));
                    house.Title = reader["title"].ToString();
                    house.Type = new HouseTypeDLL().GetHouseTypeById(Convert.ToInt32(reader["typeid"]));
                    houseList.Add(house);
                }
            }
            return houseList;
        }

        /// <summary>
        /// 查询总记录数
        /// </summary>
        /// <returns></returns>
        public int GetHouseCount()
        {
            string sql = "select count(1) from House";
            int rel = Convert.ToInt32(SqlHelper.ExecuteScalar(sql,CommandType.Text,null));
            return rel;
        }
    }
}