构造获取分页操作SQL语句
来源：互联网 发布：编程语言执行效率 编辑：程序博客网 时间：2024/04/30 07:39
构造获取分页操作SQL语句
/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引，优化分页提取方式)
/// </summary>
/// <param name="tblName">操作表名称</param>
/// <param name="fldName">排序的索引字段</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="totalRecord">总记录数</param>
/// <param name="OrderType">排序方式(0升序，1为降序)</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL(string tblName,
string fldName,
int PageIndex,
int PageSize,
int totalRecord,
int OrderType,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";
string rtnFields = "*";
// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");
strOldWhere = " AND " + strWhere + " ";
strWhere = " WHERE " + strWhere + " ";
}
// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
//strSQL += "WHERE (" + fldName + " >= ( SELECT MAX(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
strSQL += strWhere + "ORDER BY " + fldName + " ASC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
//strSQL += "WHERE (" + fldName + " <= ( SELECT MIN(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
strSQL += strWhere + "ORDER BY " + fldName + " DESC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
}
}
else // 异常处理
{
throw new DataException("未指定任何排序类型。0升序，1为降序");
}
return strSQL;
}
/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引)
/// </summary>
/// <param name="tblName">操作表名</param>
/// <param name="fldName">操作索引字段名称</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
/// <param name="OrderType">排序方式(0升序，1为降序)</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL(string tblName,
string fldName,
int PageIndex,
int PageSize,
string rtnFields,
int OrderType,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";
// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");
strOldWhere = " AND " + strWhere + " ";
strWhere = " WHERE " + strWhere + " ";
}
// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
//strSQL += "WHERE (" + fldName + " >= ( SELECT MAX(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
strSQL += strWhere + "ORDER BY " + fldName + " ASC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
//strSQL += "WHERE (" + fldName + " <= ( SELECT MIN(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
strSQL += strWhere + "ORDER BY " + fldName + " DESC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
}
}
else // 异常处理
{
throw new DataException("未指定任何排序类型。0升序，1为降序");
}
return strSQL;
}
/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引)
/// </summary>
/// <param name="tblName">操作表名</param>
/// <param name="fldName">操作索引字段名称</param>
/// <param name="unionCondition">用于连接的条件，例如: LEFT JOIN UserInfo u ON (u.UserID = b.UserID)</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
/// <param name="OrderType">排序方式，0升序，1为降序</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL(string tblName,
string fldName,
string unionCondition,
int PageIndex,
int PageSize,
string rtnFields,
int OrderType,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";
// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");
strOldWhere = " AND " + strWhere + " ";
strWhere = " WHERE " + strWhere + " ";
}
// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + unionCondition + " ";
//strSQL += "WHERE (" + fldName + " >= ( SELECT MAX(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
strSQL += strWhere + "ORDER BY " + fldName + " ASC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + unionCondition + " ";
strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + unionCondition + " ";
//strSQL += "WHERE (" + fldName + " <= ( SELECT MIN(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
strSQL += strWhere + "ORDER BY " + fldName + " DESC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + unionCondition + " ";
strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
}
}
else // 异常处理
{
throw new DataException("未指定任何排序类型。0升序，1为降序");
}
return strSQL;
}
/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引)
/// </summary>
/// <param name="tblName">操作表名</param>
/// <param name="fldName">操作索引字段名称</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
/// <param name="OrderType">排序方式(0升序，1为降序)</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL_TOP(string tblName,
string fldName,
int PageIndex,
int PageSize,
string rtnFields,
int OrderType,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";
// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");
strOldWhere = " AND " + strWhere + " ";
strWhere = " WHERE " + strWhere + " ";
}
// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += strWhere + " ORDER BY " + fldName + " ASC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += strWhere + " ORDER BY " + fldName + " DESC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
}
}
else // 异常处理
{
throw new DataException("未指定任何排序类型。0升序，1为降序");
}
return strSQL;
}
/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引)
/// </summary>
/// <param name="tblName">操作表名</param>
/// <param name="fldName">操作索引字段名称</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
/// <param name="OrderType">排序方式(0升序，1为降序)</param>
/// <param name="sort">排序表达式</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL_sort(string tblName,
string fldName,
int PageIndex,
int PageSize,
string rtnFields,
int OrderType,
string sort,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";
// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");
strOldWhere = " AND " + strWhere + " ";
strWhere = " WHERE " + strWhere + " ";
}
if (sort != "") sort = " ORDER BY " + sort;
// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
//strSQL += "WHERE (" + fldName + " >= ( SELECT MAX(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
strSQL += strWhere + sort;
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + sort + " ) AS T )) ";
strSQL += strOldWhere + sort;
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
//strSQL += "WHERE (" + fldName + " <= ( SELECT MIN(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
strSQL += strWhere + sort;
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + sort + " ) AS T )) ";
strSQL += strOldWhere + sort;
}
}
else // 异常处理
{
throw new DataException("未指定主索引排序类型。0升序，1为降序");
}
return strSQL;
}
/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引，优化分页提取方式)
/// </summary>
/// <param name="tblName">操作表名称</param>
/// <param name="fldName">排序的索引字段</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="totalRecord">总记录数</param>
/// <param name="OrderType">排序方式(0升序，1为降序)</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL(string tblName,
string fldName,
int PageIndex,
int PageSize,
int totalRecord,
int OrderType,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";
string rtnFields = "*";
// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");
strOldWhere = " AND " + strWhere + " ";
strWhere = " WHERE " + strWhere + " ";
}
// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
//strSQL += "WHERE (" + fldName + " >= ( SELECT MAX(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
strSQL += strWhere + "ORDER BY " + fldName + " ASC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
//strSQL += "WHERE (" + fldName + " <= ( SELECT MIN(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
strSQL += strWhere + "ORDER BY " + fldName + " DESC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
}
}
else // 异常处理
{
throw new DataException("未指定任何排序类型。0升序，1为降序");
}
return strSQL;
}
/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引)
/// </summary>
/// <param name="tblName">操作表名</param>
/// <param name="fldName">操作索引字段名称</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
/// <param name="OrderType">排序方式(0升序，1为降序)</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL(string tblName,
string fldName,
int PageIndex,
int PageSize,
string rtnFields,
int OrderType,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";
// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");
strOldWhere = " AND " + strWhere + " ";
strWhere = " WHERE " + strWhere + " ";
}
// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
//strSQL += "WHERE (" + fldName + " >= ( SELECT MAX(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
strSQL += strWhere + "ORDER BY " + fldName + " ASC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
//strSQL += "WHERE (" + fldName + " <= ( SELECT MIN(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
strSQL += strWhere + "ORDER BY " + fldName + " DESC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
}
}
else // 异常处理
{
throw new DataException("未指定任何排序类型。0升序，1为降序");
}
return strSQL;
}
/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引)
/// </summary>
/// <param name="tblName">操作表名</param>
/// <param name="fldName">操作索引字段名称</param>
/// <param name="unionCondition">用于连接的条件，例如: LEFT JOIN UserInfo u ON (u.UserID = b.UserID)</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
/// <param name="OrderType">排序方式，0升序，1为降序</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL(string tblName,
string fldName,
string unionCondition,
int PageIndex,
int PageSize,
string rtnFields,
int OrderType,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";
// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");
strOldWhere = " AND " + strWhere + " ";
strWhere = " WHERE " + strWhere + " ";
}
// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + unionCondition + " ";
//strSQL += "WHERE (" + fldName + " >= ( SELECT MAX(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
strSQL += strWhere + "ORDER BY " + fldName + " ASC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + unionCondition + " ";
strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + unionCondition + " ";
//strSQL += "WHERE (" + fldName + " <= ( SELECT MIN(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
strSQL += strWhere + "ORDER BY " + fldName + " DESC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + unionCondition + " ";
strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
}
}
else // 异常处理
{
throw new DataException("未指定任何排序类型。0升序，1为降序");
}
return strSQL;
}
/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引)
/// </summary>
/// <param name="tblName">操作表名</param>
/// <param name="fldName">操作索引字段名称</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
/// <param name="OrderType">排序方式(0升序，1为降序)</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL_TOP(string tblName,
string fldName,
int PageIndex,
int PageSize,
string rtnFields,
int OrderType,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";
// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");
strOldWhere = " AND " + strWhere + " ";
strWhere = " WHERE " + strWhere + " ";
}
// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += strWhere + " ORDER BY " + fldName + " ASC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += strWhere + " ORDER BY " + fldName + " DESC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
}
}
else // 异常处理
{
throw new DataException("未指定任何排序类型。0升序，1为降序");
}
return strSQL;
}
/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引)
/// </summary>
/// <param name="tblName">操作表名</param>
/// <param name="fldName">操作索引字段名称</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
/// <param name="OrderType">排序方式(0升序，1为降序)</param>
/// <param name="sort">排序表达式</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL_sort(string tblName,
string fldName,
int PageIndex,
int PageSize,
string rtnFields,
int OrderType,
string sort,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";
// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");
strOldWhere = " AND " + strWhere + " ";
strWhere = " WHERE " + strWhere + " ";
}
if (sort != "") sort = " ORDER BY " + sort;
// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
//strSQL += "WHERE (" + fldName + " >= ( SELECT MAX(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
strSQL += strWhere + sort;
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + sort + " ) AS T )) ";
strSQL += strOldWhere + sort;
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
//strSQL += "WHERE (" + fldName + " <= ( SELECT MIN(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";
//strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
strSQL += strWhere + sort;
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";
strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + sort + " ) AS T )) ";
strSQL += strOldWhere + sort;
}
}
else // 异常处理
{
throw new DataException("未指定主索引排序类型。0升序，1为降序");
}
return strSQL;
}
- 构造获取分页操作SQL语句
- ASP构造大数据量的分页SQL语句。
- ASP构造大数据量的分页SQL语句。
- ASP构造大数据量的分页SQL语句
- ASP构造大数据量的分页SQL语句
- ASP构造大数据量的分页SQL语句
- IBatisNet之获取和操作SQL语句
- 简单的实现分页获取数据的sql语句
- SQL分页语句，.NET中获取字符串的MD5码
- 使用标准SQL语句实现分页操作（Oracle）
- 分页：用sql语句分页
- 构造查询语句 sql
- excel构造 sql语句
- python sql 语句构造
- oracle分页sql语句
- ORACLE分页SQL语句
- 分页sql语句
- sql分页语句
- Flex+Papervision3D三维技术研究（基础篇二）
- 越能放下自己你就越快乐——梁文道
- Java 使用DataInputStream将数据写入文件，使用FileReader读取演示
- Windows 下安装NPM
- redhat5上手动安装oracle
- 构造获取分页操作SQL语句
- Java学习日志--其他对象Math
- Android中xml的部分属性
- 图片管理类ImgCmdUtils
- cout 重定向到文件
- 云计算时代：大数据泡沫正无限膨胀
- i2c的读取cmd发送完毕后并不会立刻同步的读到数据
- 15个重要的Android代码
- QEMU编译问题
