分页查询任务列表 支持SQL

/// <summary>
/// 分页查询任务列表
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public PageModel<TaskModel> GetListByPageView(TaskSearchModel where)
{
    int rowCount = 0;
    try
    {
        var sql = new StringBuilder(@"SELECT DISTINCT t.*
                FROM Task t WITH(NOLOCK)
                LEFT JOIN TaskExecute te WITH(NOLOCK) ON t.Id = te.TaskId AND te.RowStatus=1 
                Where 1=1 AND t.RowStatus=1");
        var parameters = new List<SqlParameter>();
        if (!string.IsNullOrEmpty(where.No))
        {
            sql.AppendFormat(" AND t.No=@No");
            parameters.Add(new SqlParameter("@No", where.No));
        }
        if (!string.IsNullOrEmpty(where.NameLike))
        {
            sql.AppendFormat(" AND t.Name LIKE '%{0}%'", where.NameLike);
            //parameters.Add(new SqlParameter("@Name", where.NameLike));
        }
        if (where.ProjectId > 0)
        {
            sql.AppendFormat(" AND t.ProjectId=@ProjectId");
            parameters.Add(new SqlParameter("@ProjectId", where.ProjectId));
        }
        if (where.ModuleId > 0)
        {
            sql.AppendFormat(" AND t.ModuleId=@ModuleId");
            parameters.Add(new SqlParameter("@ModuleId", where.ModuleId));
        }
        if (where.VersionsId > 0)
        {
            sql.AppendFormat(" AND t.VersionsId=@VersionsId");
            parameters.Add(new SqlParameter("@VersionsId", where.VersionsId));
        }
        if (where.DemandId > 0)
        {
            sql.AppendFormat(" AND t.DemandId=@DemandId");
            parameters.Add(new SqlParameter("@DemandId", where.DemandId));
        }
        if (where.State >= 0)
        {
            sql.AppendFormat(" AND t.State=@State");
            parameters.Add(new SqlParameter("@State", where.State));
        }
        if (where.QATestState >= 0)
        {
            sql.AppendFormat(" AND t.QATestState=@QATestState");
            parameters.Add(new SqlParameter("@QATestState", where.QATestState));
        }
        if (where.PrincipalId > 0)
        {
            sql.AppendFormat(" AND te.PrincipalId=@PrincipalId");
            parameters.Add(new SqlParameter("@PrincipalId", where.PrincipalId));
        }
        if (where.ScheduledStartTime.HasValue)
        {
            //sql.AppendFormat(" AND te.ScheduledStartTime >= @ScheduledStartTime");
            sql.AppendFormat(" AND t.ScheduledStartTime >= @ScheduledStartTime");
            parameters.Add(new SqlParameter("@ScheduledStartTime", where.ScheduledStartTime.Value.Date));
        }
        if (where.ScheduledEndTime.HasValue)
        {
            //sql.AppendFormat(" AND te.ScheduledEndTime <= @ScheduledEndTime");
            sql.AppendFormat(" AND t.ScheduledEndTime <= @ScheduledEndTime");
            parameters.Add(new SqlParameter("@ScheduledEndTime", where.ScheduledEndTime.Value.Date.GetDayLastTime()));
        }
        if (where.ActualStartTime.HasValue)
        {
            sql.AppendFormat(" AND te.ActualStartTime >= @ActualStartTime");
            parameters.Add(new SqlParameter("@ActualStartTime", where.ActualStartTime.Value.Date));
        }
        if (where.ActualEndTime.HasValue)
        {
            sql.AppendFormat(" AND te.ActualEndTime <= @ActualEndTime");
            parameters.Add(new SqlParameter("@ActualEndTime", where.ActualEndTime.Value.Date.GetDayLastTime()));
        }
        var exeSql = CommonHelpLD.GetRowIdSql("CreatedOn") + "  * FROM (" + sql.ToString() + ")TableTEMP";
        exeSql = SqlUtility.GetLimitPageSqlCommandSqlServer(exeSql, where.PageIndex, where.PageSize);

        var typeModel = typeof(TaskModel);
        var modelProperties = MfModelInfo.GetProperties(typeModel);
        var dataList = SqlUtility.GetExeResultToList<TaskModel, SqlParameter, SqlDataReader>(dbHelper, DataBaseName, exeSql, parameters, modelProperties, out rowCount);
        return new PageModel<TaskModel>(dataList, where.PageIndex, where.PageSize, rowCount);
    }
    catch (Exception ex)
    {
        return new PageModel<TaskModel>(null, where.PageIndex, where.PageSize, rowCount);
    }
}