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); }