protected override string GetPageTSql(Shotgun.Model.List.IDBSQLHelper list)
{
string sql = string.Empty;
string sqlWhere;
int start = 0, SelCount = 0;
//正序排法
string sqlOrder = string.Empty;
//反序排法
string sqlOrderRev = string.Empty;
sqlWhere = list.GetWhere();
if (list.CurrentPage <= 1)
{
if (list.PageSize == int.MaxValue)
{
sql = string.Format("select {0} from [{1}] {2} {3}",
list.GetFieldsString(), list.table, sqlWhere, list.GetOrderBy(false));
}
else
{
sql = string.Format("select top {4} {0} from [{1}] {2} {3}",
list.GetFieldsString(), list.table, sqlWhere, list.GetOrderBy(false), list.PageSize);
}
return(sql);
}
if (list.SortKey.Count == 0)
{
throw new ArgumentException("分页调取数据时，需要添加排序字段");
}
start = (list.CurrentPage - 1) * list.PageSize;
SelCount = list.TotalCount - start;
if (SelCount < 0)
{
sql = string.Format("select top 0 {0} from {1} ", list.GetFieldsString(), list.table);
}
if (SelCount > list.PageSize)
{
SelCount = list.PageSize;
}
sqlWhere = list.GetWhere();
sqlOrder = list.GetOrderBy(false);
sqlOrderRev = list.GetOrderBy(true);
if (string.IsNullOrEmpty(sql))
{
sql = "select " + list.GetFieldsString() + " from [" + list.table + "] where [" + list.IdentityField + "] in (\n" +
"select top " + SelCount.ToString() + " " + list.IdentityField + " from (\n" +
"select top " + (start + SelCount).ToString() + " " + list.GetOrderField() +
" from [" + list.table + "] \n" + sqlWhere + "\n " + sqlOrder + " ) t \n" +
sqlOrderRev + ") \n" + sqlOrder;
}
return(sql);
}
public void SetTableName(Shotgun.Model.List.IDBSQLHelper dbHelper)
{
_tabName = dbHelper.table;
}
protected abstract string GetPageTSql(Shotgun.Model.List.IDBSQLHelper list);
