public List <T> GetResult(List <T> t, GetReportDataParams param)
{
var count = t.Count();
if (param.PageSize > count)
{
return(t);
}
else
{
var tResult = t.Skip((param.PageIndex - 1) * param.PageSize).Take(param.PageSize).ToList();
return(tResult);
}
}
/// <summary>
/// 前台需要点击排序的时候调用
/// </summary>
/// <param name="reportName"> XML对?应?|的??名?称? </param>
/// <param name="where"> 查??询??条??件t </param>
/// <param name="pageSize"> 页?3面?打???小? </param>
/// <param name="pageIndex"> 页?3面?索??引?y </param>
/// <param name="order"></param>
/// <param name="systemID"> 系??统?3ID</param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static DataSet GetReportData(string reportName, GetReportDataParams param,
int systemID, out int totalCount, bool optimize = false)
{
if (param.Where != null)
{
foreach (var item in param.Where)
{
item.Value = item.Value.Trim();
}
}
DataSet ds;
ds = MSSqlHelper.GetReportData(reportName, param, systemID, out totalCount);
return(ds);
}
public static DataSet GetReportData(string reportName, GetReportDataParams param, int systemID, out int totalCount, bool optimize = false)
{
int pageSize = param.PageSize;
int pageIndex = param.PageIndex;
string order = param.Order;
List <KeyValue> where = param.Where;
totalCount = 0;
if (pageIndex < 1) //不能出现索引页小于1的情况，否则查询语句报错
{
return(new DataSet());
}
if (where == null)
{
where = new List <KeyValue>();
}
string reportPath = CReport.GetReportPath(systemID); //报表路径
if (string.IsNullOrEmpty(reportPath))
{
return(new DataSet());
}
string sqlCount = "";
string sql = CReport.GetSql(reportPath, reportName, where, 1); //获取要查询的SQL语句 及其 参数
if (string.IsNullOrEmpty(sql))
{
return(new DataSet());
}
else
{
sql = sql.Trim();
}
string conString = CReport.GetConnection(); //获取SQL连接串
if (string.IsNullOrEmpty(conString))
{
return(new DataSet());
}
string rowOrder = "";
if (!string.IsNullOrEmpty(order))
{
rowOrder = "order by " + order + "";
}
else
{
rowOrder = "order by (select 0)";
}
int start = pageSize * (pageIndex - 1) + 1;
int end = pageSize * pageIndex;
var matchs = Regex.Matches(sql, @"\s+order\s+", RegexOptions.IgnoreCase); //检查语句中是否含有order by
string strCount = sql;
if (matchs.Count > 1)
{
strCount = sql.Substring(0, matchs[matchs.Count - 1].Index);
if (string.IsNullOrEmpty(order))
{
rowOrder = sql.Substring(matchs[matchs.Count - 1].Index);
}
}
else if (matchs.Count == 1)
{
strCount = sql.Substring(0, matchs[0].Index);
if (string.IsNullOrEmpty(order))
{
rowOrder = sql.Substring(matchs[0].Index);
}
}
int firstSelectIndex = Regex.Matches(strCount, @"select", RegexOptions.IgnoreCase)[0].Index + 7;
sql = strCount.Insert(firstSelectIndex, " Row_Number() OVER ({0}) row ,");
sql = string.Format(sql, rowOrder);
sql = string.Format("select * from ( {0} ) item WHERE item.row BETWEEN {1} AND {2}", sql, start, end);
if (string.IsNullOrEmpty(sqlCount))
{
sqlCount = "select count(0) from (" + strCount + ") item ";
}
sql = sql + ";" + sqlCount;
var Rundate = DateTime.Now;
int RunTime = 0;
using (SqlConnection conn = new SqlConnection(conString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
//where 替换
foreach (var data in where)
{
cmd.Parameters.Add(new SqlParameter("@" + data.Key, data.Value));
}
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
if (ds.Tables.Count > 1)
{
totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
}
else
{
totalCount = 0;
}
conn.Close();
RunTime = (int)DateTime.Now.Subtract(Rundate).TotalMilliseconds;
return(ds);
}
}
