基类
/// <summary>
/// 执行SQL查询，返回分页记录集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="startRowIndex">开始行，1开始</param>
/// <param name="maximumRows">最大返回行数</param>
/// <param name="keyColumn">主键列。用于not in分页</param>
/// <returns></returns>
public virtual DataSet Query(String sql, Int32 startRowIndex, Int32 maximumRows, String keyColumn)
{
// 从第一行开始，不需要分页
if (startRowIndex <= 1)
{
if (maximumRows < 1)
return Query(sql);
else
return Query(String.Format("Select Top {0} * From ({1}) a", maximumRows, sql));
}
if (maximumRows < 1)
sql = String.Format("Select * From ({1}) b Where {2} Not In(Select Top {0} {2} From ({1}) a)", startRowIndex - 1, sql, keyColumn);
else
sql = String.Format("Select Top {0} * From ({1}) b Where {2} Not In(Select Top {3} {2} From ({1}) a)", startRowIndex + maximumRows - 1, sql, keyColumn, startRowIndex - 1);
return Query(sql);
}
派生类
public override DataSet Query(string sql, int startRowIndex, int maximumRows, string keyColumn)
{
// 如果没有Order By，直接调用基类方法
// 先用字符串判断，如果命中率高，这样可以提高处理效率
if (!sql.Contains("order")) return base.Query(sql, startRowIndex, maximumRows, keyColumn);
if (!sql.ToLower().Contains("order")) return base.Query(sql, startRowIndex, maximumRows, keyColumn);
// 使用正则进行严格判断。必须包含Order By，并且它右边没有右括号)，表明有order by，且不是子查询的，才需要特殊处理
MatchCollection ms = Regex.Matches(sql, @"/border/s*by/b[^)]+$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
if (ms == null || ms.Count < 1 || ms[0].Index < 1)
{
return base.Query(sql, startRowIndex, maximumRows, keyColumn);
}
// 已确定该sql最外层含有order by，再检查最外层是否有top。因为没有top的order by是不允许作为子查询的
if (!Regex.IsMatch(sql, @"^[^(]+/btop/b", RegexOptions.Compiled | RegexOptions.IgnoreCase))
{
return base.Query(sql, startRowIndex, maximumRows, keyColumn);
}
String orderBy = sql.Substring(ms[0].Index);
sql = sql.Substring(0, ms[0].Index);
// 从第一行开始，不需要分页
if (startRowIndex <= 1)
{
if (maximumRows < 1)
return Query(sql);
else
return Query(String.Format("Select Top {0} * From ({1}) a {2}", maximumRows, sql, orderBy));
}
if (maximumRows < 1)
sql = String.Format("Select * From ({1}) b Where {2} Not In(Select Top {0} {2} From ({1}) a {3}) {3}", startRowIndex - 1, sql, keyColumn, orderBy);
else
sql = String.Format("Select Top {0} * From ({1}) b Where {2} Not In(Select Top {3} {2} From ({1}) a {4}) {4}", startRowIndex + maximumRows - 1, sql, keyColumn, startRowIndex - 1, orderBy);
return Query(sql);
}
Access的DAL继承基类的时候，是不需要重写这个方法的，现在SqlServer就要做一个特殊处理，所以需要重启。