/// <summary>
/// 获取分页保底政策 分页方式二
/// </summary>
/// <returns>保底政策</returns>
internal PolicyRemarkSearchResponse GetPolicyRemarkPageList(PolicyRemarkSearchRequest request)
{
PolicyRemarkSearchResponse response = new PolicyRemarkSearchResponse();
const string sql =
@"WITH cte AS(
SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) AS RowID,*
FROM TblRateRemark WITH(NOLOCK) WHERE AgentID=@AgentID AND RateType=@RateType
)
SELECT * FROM cte WHERE RowID BETWEEN @RowBegin AND @RowEnd ORDER BY ID;
SELECT COUNT(1) FROM TblRateRemark WITH(NOLOCK) WHERE AgentID=@AgentID AND RateType=@RateType;";
DynamicParameters dp = new DynamicParameters();
dp.Add("@RowBegin", request.Paging.PageIndex * request.Paging.PageSize + 1);
dp.Add("@RowEnd", (request.Paging.PageIndex + 1) * request.Paging.PageSize);
dp.Add("@AgentID", request.ProviderId);
dp.Add("@RateType", request.PolicyType);
using (var conn = new SqlConnection(ConnectionString.JinRiRateDB_SELECT))
{
conn.Open();
using (var result = conn.QueryMultiple(sql, dp, null, null, CommandType.Text))
{
response.Success = true;
response.Data = result.Read <PolicyRemarkDTO>().ToList <PolicyRemarkDTO>();
response.Paging.TotalCount = result.Read <int>().SingleOrDefault <int>();
}
}
return(response);
}
/// <summary>
/// 分页查询政策备注列表
/// </summary>
/// <param name="request">请求参数</param>
/// <returns></returns>
public PolicyRemarkSearchResponse PolicyRemarkSearch(PolicyRemarkSearchRequest request)
{
PolicyRemarkSearchResponse response = new PolicyRemarkSearchResponse();
try
{
response.Success = true;
response.Paging.TotalCount = dbQuery.GetPolicyRemarkTotalCount(request);
if (response.Paging.TotalCount > 0)
{
List <PolicyRemark> list = dbQuery.GetPolicyRemarkPageData(request);
foreach (PolicyRemark rateRemark in list)
{
response.Data.Add(new PolicyRemarkDTO()
{
PolicyRemarkId = rateRemark.ID, ProviderId = rateRemark.AgentID, PolicyType = rateRemark.RateType, Remark = rateRemark.Info
});
}
}
}
catch (Exception ex)
{
//记录日志
response.Success = false;
response.ErrMsg = "获取数据失败。";
}
return(response);
}
protected void Page_Load(object sender, EventArgs e)
{
PolicyRemarkSearchRequest requestEntity = new PolicyRemarkSearchRequest();
requestEntity.Paging.PageSize = 10;
requestEntity.ProviderId = 18132;
PolicyRemarkSearchResponse responseEntity = policyRemarkLogic.PolicyRemarkSearch(requestEntity);
}
/// <summary>
/// 获取政策备注分页总记录数
/// </summary>
/// <returns></returns>
internal int GetPolicyRemarkTotalCount(PolicyRemarkSearchRequest request)
{
//Where条件必须与GetRateRemarkPageData()方法一致
//可以减少不必要的表关联，不需要排序
//相同查询条件的记录数可以适当缓存
const string sql = "SELECT COUNT(1) FROM TblRateRemark WITH(NOLOCK) WHERE AgentID=@AgentID AND RateType=@RateType";
DynamicParameters dp = new DynamicParameters();
dp.Add("@AgentID", request.ProviderId);
dp.Add("@RateType", request.PolicyType);
using (var conn = new SqlConnection(ConnectionString.JinRiRateDB_SELECT))
{
conn.Open();
return(conn.Query <int>(sql, dp).SingleOrDefault <int>());
}
}
/// <summary>
/// 从服务端获取数据
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public PolicyRemarkSearchResponse PolicyRemarkSearch(PolicyRemarkSearchRequest request)
{
PolicyRemarkSearchResponse response = new PolicyRemarkSearchResponse();
response.Success = false;
if (request.Paging == null)
{
response.ErrMsg = "无效的查询参数，Paging不能为空。";
return(response);
}
if (request.Paging.PageSize <= 0)
{
response.ErrMsg = "无效的查询参数Paging.PageSize";
return(response);
}
return(policyService.PolicyRemarkSearch(request));
}
public PolicyRemarkSearchResponse PolicyRemarkSearch(PolicyRemarkSearchRequest request)
{
PolicyRemarkSearchResponse response = new PolicyRemarkSearchResponse();
response.Success = false;
if (request.Paging == null)
{
response.ErrMsg = "无效的查询参数，Paging不能为空。";
return(response);
}
if (request.Paging.PageSize <= 0)
{
response.ErrMsg = "无效的查询参数Paging.PageSize。";
return(response);
}
response = JinRiRateFacade.PolicyRemarkSearch(request);
response.Paging.PageSize = request.Paging.PageSize;
response.Paging.PageIndex = request.Paging.PageIndex;
return(response);
}
/// <summary>
/// 获取政策备注列表
/// </summary>
/// <returns>政策备注列表</returns>
internal List <PolicyRemark> GetPolicyRemarkPageData(PolicyRemarkSearchRequest request)
{
const string sql =
@"WITH cte AS(
SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) AS RowID,*
FROM TblRateRemark WITH(NOLOCK) WHERE AgentID=@AgentID AND RateType=@RateType
)
SELECT * FROM cte WHERE RowID BETWEEN @RowBegin AND @RowEnd ORDER BY ID;";
DynamicParameters dp = new DynamicParameters();
dp.Add("@RowBegin", request.Paging.PageIndex * request.Paging.PageSize + 1);
dp.Add("@RowEnd", (request.Paging.PageIndex + 1) * request.Paging.PageSize);
dp.Add("@AgentID", request.ProviderId);
dp.Add("@RateType", request.PolicyType);
using (var conn = new SqlConnection(ConnectionString.JinRiRateDB_SELECT))
{
conn.Open();
return(conn.Query <PolicyRemark>(sql, dp).ToList <PolicyRemark>());
}
}
public PolicyRemarkSearchResponse PolicyRemarkSearch(PolicyRemarkSearchRequest request)
{
return(rateRemarkLogic.PolicyRemarkSearch(request));
}
