62,051
社区成员
发帖
与我相关
我的任务
分享
namespace GPSS.IDAL
{
/// <summary>
/// 根據 HeadInfo 查詢 Head 的詳細資料,賦給Gridview
/// </summary>
public interface IHeadListView
{
DataTable GetHead(HeadInfo myHeadInfo, int pagesize, int pageindex);
int GetHeadCount(HeadInfo myHeadInfo);
}
}
namespace GPSS.DAL
{
/// <summary>
/// Rule1:QSTATUS = '1' OR QSTATUS = '2'
/// It's used for Creation
/// </summary>
public class GetHeadByRule1 : IHeadListView
{
/// <summary>
/// Read the paging question head from the database
/// </summary>
/// <param name="myHeadInfo">Question Head Infomation</param>
/// <param name="pagesize">pages size</param>
/// <param name="pageindex">the current page index</param>
public DataTable GetHead(HeadInfo myHeadInfo, int pagesize, int pageindex)
{
StringBuilder sb = new StringBuilder();
sb.Append(GetBaseSql(myHeadInfo));
sb.Append(" select * from A\n");
sb.Append(" where Number>=(" + (pageindex - 1) + "*" + pagesize + "+1)\n");
sb.Append(" and Number<=" + pageindex + "*" + pagesize + "\n");
sb.Append(" order by Number\n");
string strSQL = sb.ToString();
return SQLHelper.GetDataTable(strSQL);
}
/// <summary>
/// Get the total count of Questions,and then assign it to the AspNetPager control
/// </summary>
public int GetHeadCount(HeadInfo myHeadInfo)
{
StringBuilder sb = new StringBuilder();
sb.Append(GetBaseSql(myHeadInfo));
sb.Append(" select count(1) from A");
string strSQL = sb.ToString();
return int.Parse(SQLHelper.ExecuteScalar(strSQL).ToString());
}
/// <summary>
/// Base SQL. Creation and Publish, the different points in here
/// </summary>
private string GetBaseSql(HeadInfo myHeadInfo)
{
StringBuilder sb = new StringBuilder();
sb.Append(" with A as\n");
sb.Append(" (\n");
sb.Append(" select row_number() over(order by qid desc) as Number,qid,title,createDate\n");
sb.Append(" from QUE_HEAD\n");
sb.Append(" where (QSTATUS = '1' OR QSTATUS = '2')\n");// the different points
if (!string.IsNullOrEmpty(myHeadInfo.QID))
{
sb.Append(" and qid like '" + myHeadInfo.QID.Replace('*', '%') + "'\n");
}
if (!string.IsNullOrEmpty(myHeadInfo.QTitle))
{
sb.Append(" and title like '" + myHeadInfo.QTitle.Replace('*', '%') + "'\n");
}
sb.Append(" )\n");
string strSQL = sb.ToString();
return strSQL;
}
}
}