using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using fjt.DBUtility;
/// <summary>
/// 分页用sql查询类 http://www.my400800.cn
/// </summary>
public class SelectTableDataForPage
{
/// <summary>
/// 构造函数
/// </summary>
public SelectTableDataForPage()
{
//
// TODO: Add constructor logic here
//
}
#region public static int getSelectNowPageCount(string strTableName,string strSqlWhere, int iPageSize) 指定表指定条件总页数取得
/// <summary>
/// 指定表指定条件总页数取得
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strSqlWhere">查询条件</param>
/// <param name="iPageSize">每页显示记录数</param>
/// <returns></returns>
public static int getSelectNowPageCount(string strTableName, string strSqlWhere, int iPageSize)
{
//如果指定每页显示记录数小于0,每页显示记录数10设定
if (iPageSize <= 0) iPageSize = 10;
StringBuilder sbSql = new StringBuilder("select count(*) from ");
sbSql.Append(strTableName);
if (!string.IsNullOrEmpty(strSqlWhere))
{
sbSql.Append(" where 1=1 ").Append(strSqlWhere);
}
int RowCount = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, sbSql.ToString()));
int pageCount = RowCount % iPageSize > 0 ? RowCount / iPageSize + 1 : RowCount / iPageSize;
return pageCount;
}
#endregion
#region 取得指定条件指定页码指定排序的表数据 public static DataTable SelectNowPageForDS(string strTableName, string strSelFeilds, string strPk, string strOrderBy, string strWhere, int iNowPage, int iPageSize)
/// <summary>
/// 取得指定条件指定页码指定排序的表数据
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strSelFeilds">取得字段 多个字段用逗号分开</param>
/// <param name="strPk">唯一主键</param>
/// <param name="strOrderBy">排序字段,多个用逗号分开</param>
/// <param name="strWhere">查询条件</param>
/// <param name="iNowPage">要取得的页码数据</param>
/// <param name="iPageSize">当前页码显示记录数量</param>
/// <returns></returns>
public static DataTable SelectNowPageForDS(string strTableName, string strSelFeilds, string strPk, string strOrderBy, string strWhere, int iNowPage, int iPageSize)
{
//如果指定每页显示记录数小于0,每页显示记录数10设定
if (iPageSize <= 0) iPageSize = 10;
//分页用sql取得
string strExecSql = GetSelectForPageSql(strTableName, strSelFeilds, strPk, strOrderBy, strWhere, iNowPage, iPageSize);
DataTable dtSel = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, strExecSql).Tables[0];
return dtSel;
}
#endregion
#region 取得指定条件指定页码指定排序的sql查询语句
/// <summary>
/// 取得指定条件指定页码指定排序的sql查询语句
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strSelFeilds">取得字段 多个字段用逗号分开</param>
/// <param name="strPk">唯一主键</param>
/// <param name="strOrderBy">排序字段,多个用逗号分开</param>
/// <param name="strWhere">查询条件</param>
/// <param name="iNowPage">要取得的页码数据</param>
/// <param name="iPageSize">当前页码显示记录数量</param>
/// <returns>sql查询语句返回</returns>
public static string GetSelectForPageSql(string strTableName, string strSelFeilds, string strPk, string strOrderBy, string strWhere, int iNowPage, int iPageSize)
{
//如果指定每页显示记录数小于0,每页显示记录数10设定
if (iPageSize <= 0) iPageSize = 10;
//拼接后结果
//http://www.my400800.cn
//语句形式:
// SELECT TOP 页大小 *
//FROM TestTable
//WHERE (ID NOT IN
// (SELECT TOP 页大小*页数 id
// FROM 表
// ORDER BY id))
//ORDER BY ID
//检索字段内容如果是空 取得所有字段信息
strSelFeilds = string.IsNullOrEmpty(strSelFeilds) ? "*" : strSelFeilds;
StringBuilder sbSql = new StringBuilder("");
sbSql.Append("( SELECT ")
.AppendLine(strSelFeilds)
.AppendLine(" FROM ")
.AppendLine(strTableName);
//查询条件拼接处理
if (!string.IsNullOrEmpty(strWhere))
{
sbSql.AppendLine(" WHERE 1=1 ").Append(strWhere);
}
sbSql.AppendLine(") selForPageTempTable");
//排序字段sql用字符取得
string strSqlOrderBy = "";
if (!string.IsNullOrEmpty(strOrderBy))
{
strSqlOrderBy = " ORDER BY " + strOrderBy;
}
StringBuilder sbExecSql = new StringBuilder("SELECT ");
sbExecSql.Append(" TOP ").Append(iPageSize).Append(" ").Append(strSelFeilds).Append(" FROM ").Append(sbSql).Append(" WHERE (").Append(strPk)
.Append(" NOT IN ( SELECT TOP ").Append((iNowPage - 1) * iPageSize).Append(" ").Append(strPk).Append(" FROM ").Append(strTableName)
.Append(strSqlOrderBy).Append(")) ").AppendLine(strSqlOrderBy);
return sbExecSql.ToString();
}
/// <summary>
/// 取得指定条件指定页码指定排序的sql查询语句
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strPk">唯一主键</param>
/// <param name="strOrderBy">排序字段,多个用逗号分开</param>
/// <param name="strWhere">查询条件</param>
/// <param name="iNowPage">要取得的页码数据</param>
/// <param name="iPageSize">当前页码显示记录数量</param>
/// <returns>sql查询语句返回</returns>
/// <returns>sql查询语句返回</returns>
public static string GetSelectForPageSql(string strTableName, string strPk, string strOrderBy, string strWhere, int iNowPage, int iPageSize)
{
return GetSelectForPageSql(strTableName, null, strPk, strOrderBy, strWhere, iNowPage, iPageSize);
}
/// <summary>
/// 取得指定条件指定页码指定排序的sql查询语句
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strPk">唯一主键</param>
/// <param name="strOrderBy">排序字段,多个用逗号分开</param>
/// <param name="iNowPage">要取得的页码数据</param>
/// <param name="iPageSize">当前页码显示记录数量</param>
public static string GetSelectForPageSql(string strTableName, string strPk, string strOrderBy, int iNowPage, int iPageSize)
{
return GetSelectForPageSql(strTableName, null, strPk, strOrderBy, null, iNowPage, iPageSize);
}
#endregion
}
sql2000数据库 sql语句C#分页类代码
最新推荐文章于 2020-06-11 09:24:36 发布