[SQL] 纯文本查看 复制代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Psp_SetPagination]
(
@vc_Table varchar(50),--表名
@vc_SqlWhere varchar(1000)='',--条件
@vc_SqlOrder varchar(1000)='',--排序规则
@vc_SqlFile varchar(1000)='*',--选取字段
@vc_Key varchar(50)='',--关键字
@vc_FieldType varchar(50)='',--字段类型
@i_PageSize int=1,--分页大小
@i_PageIndex int=1,--页面索引
@bt_Count bit --在统计吗？
)
As
set nocount on
Declare @vc_Sql varchar(5000)
if @bt_Count=1
BEGIN
set @vc_Sql='select count(*) from '+@vc_Table+' '+@vc_SqlWhere
END
ELSE
BEGIN
set @vc_Sql='Declare @indexTable Table(Id decimal identity(1,1),Temp_Key '+@vc_FieldType+ ')'+char(13)
set @vc_Sql=@vc_Sql+
'Declare @PageLowerBound int'+char(13)
set @vc_Sql=@vc_Sql+
'Declare @PageUpperBound int'+char(13)
set @vc_Sql=@vc_Sql+
'set @PageLowerBound='+cast((@i_PageIndex-1)*@i_PageSize as varchar(8))+char(13)
set @vc_Sql=@vc_Sql+
'set @PageUpperBound=@PageLowerBound+'+cast(@i_PageSize as varchar(8))+char(13)
set @vc_Sql=@vc_Sql+
'set rowcount @PageUpperBound'+char(13)
set @vc_Sql=@vc_Sql+
'Insert into @indexTable(Temp_Key)'+
'Select '+@vc_Key+' from '+@vc_Table+' '+@vc_SqlWhere+' '+@vc_SqlOrder+char(13)
if @vc_SqlWhere<>''
begin
set @vc_SqlWhere=@vc_SqlWhere+' and '+@vc_Key+'=t.Temp_Key and t.Id>@PageLowerBound and t.Id<=@PageUpperBound'
end
else
begin
set @vc_SqlWhere='Where '+@vc_Key+'=t.Temp_Key and t.Id>@PageLowerBound and t.Id<=@PageUpperBound'
end
set @vc_Sql=@vc_Sql+
'Select '+@vc_SqlFile+' from '+@vc_Table+',@indexTable t '+@vc_SqlWhere+' Order by t.Id'
END
EXEC(@vc_Sql)
[C#] 纯文本查看 复制代码
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;
/// <summary>
/// AspNetPagerInfo 的摘要说明
/// </summary>
public class AspNetPagerInfo
{
private string _TableName;
private string _SqlWhere;
private string _SqlOrder;
private string _SqlFile;
private string _KeyField;
private string _FieldType;
private int _PageSize;
private int _PageIndex;
/// <summary>
/// 信息表或视图
/// </summary>
public string TableName
{
get
{
return _TableName;
}
set
{
_TableName = value;
}
}
/// <summary>
/// 信息检索条件
/// </summary>
public string SqlWhere
{
get
{
return _SqlWhere;
}
set
{
_SqlWhere = value;
}
}
/// <summary>
/// 排序关键字
/// </summary>
public string SqlOrder
{
get
{
return _SqlOrder;
}
set
{
_SqlOrder = value;
}
}
/// <summary>
/// 检索字段，各个字段用‘,’分割
/// </summary>
public string SqlFile
{
get
{
return _SqlFile;
}
set
{
_SqlFile = value;
}
}
/// <summary>
/// 关键字名称
/// </summary>
public string KeyField
{
get
{
return _KeyField;
}
set
{
_KeyField = value;
}
}
/// <summary>
/// 关键字类型
/// </summary>
public string FieldType
{
get
{
return _FieldType;
}
set
{
_FieldType = value;
}
}
/// <summary>
/// 分页大小
/// </summary>
public int PageSize
{
get
{
return _PageSize;
}
set
{
_PageSize = value;
}
}
/// <summary>
/// 页面索引
/// </summary>
public int PageIndex
{
get
{
return _PageIndex;
}
set
{
_PageIndex = value;
}
}
}[/code]
AspNetPager
[code=csharp]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.Data.SqlClient;
using DBUtility;
/// <summary>
/// AspNetPager 的摘要说明
/// </summary>
public class AspNetPager
{
/// <summary>
/// 获取记录集总数
/// </summary>
/// <returns></returns>
public int GetRecordCount(AspNetPagerInfo info)
{
SqlParameter[] parameters = SqlHelper.GetCachedParameters("Psp_SetPagination");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@vc_Table",SqlDbType.VarChar,50),
new SqlParameter("@vc_SqlWhere",SqlDbType.VarChar,1000),
new SqlParameter("@vc_SqlOrder",SqlDbType.VarChar,1000),
new SqlParameter("@vc_SqlFile",SqlDbType.VarChar,1000),
new SqlParameter("@vc_Key",SqlDbType.VarChar,50),
new SqlParameter("@vc_FieldType",SqlDbType.VarChar,50),
new SqlParameter("@i_PageSize",SqlDbType.Int),
new SqlParameter("@i_PageIndex",SqlDbType.Int),
new SqlParameter("@bt_Count",SqlDbType.Bit)
};
SqlHelper.CacheParameters("Psp_SetPagination", parameters);
}
parameters[0].Value = info.TableName;
parameters[1].Value = info.SqlWhere;
parameters[2].Value = info.SqlOrder;
parameters[3].Value = info.SqlFile;
parameters[4].Value = info.KeyField;
parameters[5].Value = info.FieldType;
parameters[6].Value = info.PageSize;
parameters[7].Value = info.PageIndex;
parameters[8].Value = 1;
return (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocal, CommandType.StoredProcedure, "Psp_SetPagination", parameters);
}
/// <summary>
/// 获取分页后列表
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public SqlDataReader GetListForDataReader(AspNetPagerInfo info)
{
SqlParameter[] parameters = SqlHelper.GetCachedParameters("Psp_SetPagination");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@vc_Table",SqlDbType.VarChar,50),
new SqlParameter("@vc_SqlWhere",SqlDbType.VarChar,1000),
new SqlParameter("@vc_SqlOrder",SqlDbType.VarChar,1000),
new SqlParameter("@vc_SqlFile",SqlDbType.VarChar,1000),
new SqlParameter("@vc_Key",SqlDbType.VarChar,50),
new SqlParameter("@vc_FieldType",SqlDbType.VarChar,50),
new SqlParameter("@i_PageSize",SqlDbType.Int),
new SqlParameter("@i_PageIndex",SqlDbType.Int),
new SqlParameter("@bt_Count",SqlDbType.Bit)
};
SqlHelper.CacheParameters("Psp_SetPagination", parameters);
}
parameters[0].Value = info.TableName;
parameters[1].Value = info.SqlWhere;
parameters[2].Value = info.SqlOrder;
parameters[3].Value = info.SqlFile;
parameters[4].Value = info.KeyField;
parameters[5].Value = info.FieldType;
parameters[6].Value = info.PageSize;
parameters[7].Value = info.PageIndex;
parameters[8].Value = 0;
return SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.StoredProcedure, "Psp_SetPagination", parameters);
}
public DataSet GetListForDataSet(AspNetPagerInfo info)
{
SqlParameter[] parameters = SqlHelper.GetCachedParameters("Psp_SetPagination");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@vc_Table",SqlDbType.VarChar,50),
new SqlParameter("@vc_SqlWhere",SqlDbType.VarChar,1000),
new SqlParameter("@vc_SqlOrder",SqlDbType.VarChar,1000),
new SqlParameter("@vc_SqlFile",SqlDbType.VarChar,1000),
new SqlParameter("@vc_Key",SqlDbType.VarChar,50),
new SqlParameter("@vc_FieldType",SqlDbType.VarChar,50),
new SqlParameter("@i_PageSize",SqlDbType.Int),
new SqlParameter("@i_PageIndex",SqlDbType.Int),
new SqlParameter("@bt_Count",SqlDbType.Bit)
};
SqlHelper.CacheParameters("Psp_SetPagination", parameters);
}
parameters[0].Value = info.TableName;
parameters[1].Value = info.SqlWhere;
parameters[2].Value = info.SqlOrder;
parameters[3].Value = info.SqlFile;
parameters[4].Value = info.KeyField;
parameters[5].Value = info.FieldType;
parameters[6].Value = info.PageSize;
parameters[7].Value = info.PageIndex;
parameters[8].Value = 0;
return SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringLocal, CommandType.StoredProcedure, "Psp_SetPagination", parameters);
}
}