CREATE PROCEDURE UP_GetRecordByPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' , -- 查询条件 (注意: 不要加 where)
@IsReCount bit = 0 -- 返回记录总数, 非 0 值则返回
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
if @strWhere != ''---条件不为空
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
else---条件为空
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
if @PageIndex = 1
begin
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
if @IsReCount != 0
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+ @strTmp
exec (@strSQL)
GO
public void BindID()
{
int PageSize=12;
if (Request.QueryString["Page"] != null)
{
CurrentPage = Convert.ToInt32(Request.QueryString["Page"]);
}
else
{
CurrentPage = 1;
}
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from Dict_Bishun");
DataSet ds = morepage.GetList(PageSize, CurrentPage, "ID>1", "Dict_Bishun");
DList_ID.DataSource = ds;
DList_ID.DataBind();
//获取总数
strSql.Remove(0, strSql.Length);
strSql.Append("select count(*) from Dict_Bishun");
int total = normal.GetTotal(strSql.ToString());
//页数
int TotalPage = total/PageSize+1;
// Lbl_Total.Text = "共有"+TotalPage+"页";
if (CurrentPage != 1)
{
HL_Last.NavigateUrl = Request.CurrentExecutionFilePath + "?page=" + (CurrentPage-1);
}
if (CurrentPage != TotalPage)
{
HL_Next.NavigateUrl = Request.CurrentExecutionFilePath + "?page=" + (CurrentPage + 1);
}
}
public DataSet GetList(int PageSize, int PageIndex, string strWhere,string tblName)
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar, 255), //表名
new SqlParameter("@fldName", SqlDbType.VarChar, 255), //主键字段名
new SqlParameter("@PageSize", SqlDbType.Int), //页尺寸
new SqlParameter("@PageIndex", SqlDbType.Int), //页码
new SqlParameter("@IsReCount", SqlDbType.Bit), //返回记录总数
new SqlParameter("@OrderType", SqlDbType.Bit), //设置排序类型, 非 0 值则降序
new SqlParameter("@strWhere", SqlDbType.VarChar,1000), //查询条件 (注意: 不要加 where)
};
parameters[0].Value = tblName;
parameters[1].Value = "ID";
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Value = 0;
parameters[5].Value = 0;
parameters[6].Value = strWhere;
return pro.RunProcedure("GetRecordByPage", parameters, "ds");
}
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection con = new SqlConnection(DB.ConnectionString))
{
DataSet dataSet = new DataSet();
con.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(con, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
con.Close();
return dataSet;
}
}
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
/// </summary>
/// <param name="con">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private SqlCommand BuildQueryCommand(SqlConnection con, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, con);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input ) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
return command;
}