public static SqlServerAnalysis Analyse(string sql)
{
SqlServerAnalysis sa = new SqlServerAnalysis();
//sql = sql.ToLower();
string pattern = @"order[\040\n\r]+by";
MatchCollection coll = Regex.Matches(sql, pattern, RegexOptions.RightToLeft | RegexOptions.IgnoreCase);
if (coll.Count > 0)
{
sa.select = sql.Substring(0, coll[0].Index - 1);
sa.orderBy = sql.Substring(coll[0].Index);
}
else
{
sa.select = sql;
}
return(sa);
}
public override DataSet ExecuteDataSet(string sql, IDataAccessSession session, int startIndex, int endIndex, out int totalCount, params DbParameter[] dbParams)
{
SqlServerAnalysis sa = SqlServerAnalysis.Analyse(sql);
string countSql = "select count(*) from ( {0} ) as page#CountSql";
countSql = string.Format(countSql, sa.Select);
string rowNumberSql = "select *,ROW_NUMBER() OVER ({0}) AS RowNumber from ({1}) as page#RowNumber";
rowNumberSql = string.Format(rowNumberSql, sa.OrderBy, sa.Select);
string filterRowNumberSql = "select * from ({0}) as filter@RowNumber WHERE RowNumber BETWEEN {1} AND {2} ";
filterRowNumberSql = string.Format(filterRowNumberSql, rowNumberSql, startIndex, endIndex);
string allSql = string.Format("{0};{1}", filterRowNumberSql, countSql);
DataSet ds = this.ExecuteDataSet(allSql, session, dbParams);
totalCount = Int32.Parse(ds.Tables[1].Rows[0][0].ToString());
return(ds);
}