咨询电话:186 7916 6165 咨询电话:186 7916 6165 (微信同号)    在线QQ:181796286
NEWS BLOG ·
学无止境
关注开优网络 关注前沿
ASP.NET常用方法及属性
MVC使用下拉列表

ASP.NET使用SQL多表查询分页

发表日期:2019-01-08    文章编辑:    浏览次数:1058    标签:

/// <summary>GetList分页
///
/// </summary>
/// <param name="fileds">选择字段</param>
/// <param name="order">排序字段</param>
/// <param name="ordertype">排序方式</param>
/// <param name="PageSize">页大小</param>
/// <param name="PageIndex">页索引</param>
/// <param name="strWhere">条件</param>
/// <returns></returns>
public DataSet GetListByNaswer(string fileds, string order, string ordertype, int PageSize, int PageIndex, string strWhere)
{
    // select top 每页显示的记录数 * from topic where id not in (select top (当前的页数-1)×每页显示的记录数 id from topic order by id desc)  order by id desc  
    string sql = Get_FenYeSQL(fileds, order, ordertype, PageSize, PageIndex, strWhere);
    Database db = DatabaseFactory.CreateDatabase();
    DbCommand dbCommand = db.GetSqlStringCommand(sql);
    return db.ExecuteDataSet(dbCommand);
}

/// <summary>分页函数
///
/// </summary>
/// <param name="tblname">表名</param>
/// <param name="fileds">字段名</param>
/// <param name="order">排序字段</param>
/// <param name="ordertype">排序类型:asc或者desc</param>
/// <param name="PageSize">页大小</param>
/// <param name="PageIndex">页索引</param>
/// <param name="strWhere">条件</param>
/// <returns></returns>
private static string Get_FenYeSQL(string fileds, string order, string ordertype, int PageSize, int PageIndex, string strWhere)
{
    // select top 每页显示的记录数 * from topic where id not in (select top (当前的页数-1)×每页显示的记录数 id from topic order by id desc)  order by id desc
    string sql = "";
    if (PageIndex == 1)
    {
        // 第一页
        if (strWhere == "")
        {
            //条件为空
            sql = "select top " + PageSize + " " + fileds + " from cms_exam_User_Ks_Details left join cms_exam_Answer on cms_exam_User_Ks_Details.questionid=cms_exam_Answer.questionid order by " + order + " " + ordertype;
        }
        else
        {
            //条件不为空
            sql = "select top " + PageSize + " " + fileds + " from cms_exam_User_Ks_Details left join cms_exam_Answer on cms_exam_User_Ks_Details.questionid=cms_exam_Answer.questionid where " + strWhere + " order by " + order + " " + ordertype;
        }
    }
    else
    {
        // 不是第一页
        if (strWhere == "")
        {
            //条件为空
            sql = "select top " + PageSize + " " + fileds + " from cms_exam_User_Ks_Details left join cms_exam_Answer on cms_exam_User_Ks_Details.questionid=cms_exam_Answer.questionid where cms_exam_User_Ks_Details.id not in (select top " + (PageIndex - 1) * PageSize + " cms_exam_User_Ks_Details.id from cms_exam_User_Ks_Details left join cms_exam_Answer on cms_exam_User_Ks_Details.questionid=cms_exam_Answer.questionid order by " + order + " " + ordertype + ") order by " + order + " " + ordertype;
        }
        else
        {
            //条件不为空
            sql = "select top " + PageSize + " " + fileds + " from cms_exam_User_Ks_Details left join cms_exam_Answer on cms_exam_User_Ks_Details.questionid=cms_exam_Answer.questionid where " + strWhere + " and cms_exam_User_Ks_Details.id not in (select top " + (PageIndex - 1) * PageSize + " cms_exam_User_Ks_Details.id from cms_exam_User_Ks_Details left join cms_exam_Answer on cms_exam_User_Ks_Details.questionid=cms_exam_Answer.questionid where " + strWhere + " order by " + order + " " + ordertype + ") order by " + order + " " + ordertype;
        }
    }
    return sql;

}


//使用方法 参数里必须指定哪个表的参数

repSxlx.DataSource = new DAL.Exam_User_Ks_DetailsDAL().GetListByNaswer("*", "cms_exam_User_Ks_Details.id", "asc", anp.PageSize, anp.CurrentPageIndex, GetCond());
repSxlx.DataBind();