SQL获取分页数据
public DataSet GetPagingData(string table, string fields, string filter, string sort, int pageSize, int pageIndex)
{
IGSPDatabase db = CommonCore.ComDatabase.GetDatabase;
string vsSql = @"select * from(select row_number() over(order by @Sort@ ) rownumber,@Fields@ from @Tables@ where @Filter@) tempTable
where tempTable.rownumber> {0} and tempTable.rownumber<{1} ";
vsSql=vsSql.Replace("@Fields@", fields);
vsSql=vsSql.Replace("@Sort@", sort);
vsSql=vsSql.Replace("@Tables@", table);
vsSql=vsSql.Replace("@Filter@", filter);
int fromRecord = (pageIndex - 1) * pageSize;
int toRecord = fromRecord + pageSize + 1;
DataSet pageData = CommonCore.ComDatabase.GetData(vsSql, fromRecord, toRecord);
vsSql = "Select Count(0) from @Tables@ where @Filter@ ";
vsSql = vsSql.Replace("@Tables@", table);
vsSql = vsSql.Replace("@Filter@", filter);
DataSet countData = CommonCore.ComDatabase.GetData(vsSql);
pageData.Tables[0].TableName = "PageData";
pageData.Tables.Add(countData.Tables[0].Copy());
return pageData;
}