/// <summary>
/// 执行自定义分页返回<see cref="System.Data.DataSet">System.Data.DataSet</see>
/// </summary>
/// <param name="sTable_Name">要查询的表名</param>
/// <param name="sSign_Record">唯一标识的字段</param>
/// <param name="sFilter_Condition">查询条件</param>
/// <param name="sFind_RecordList">要查询的字段列表,全部字段查询使用空字符</param>
/// <param name="iPage_Size">分页的每页的大小</param>
/// <param name="iPage_Index">当前页数</param>
/// <param name="iTaxis_Sign">排序标志 0正序1倒序</param>
/// <param name="iPageCount">总页数.输出参数</param>
/// <param name="iiRecord_Count">总记录数.输出参数</param>
/// <returns>成功返回<see cref="System.Data.DataSet">System.Data.DataSet</see></returns>
DataSet ExecuteCustomPage(string sTable_Name,string sSign_Record,string sFilter_Condition,string sFind_RecordList,int iPage_Size,int iPage_Index,string sTaxisField,int iTaxis_Sign,out int iPageCount,out int iiRecord_Count);
public DataSet ExecuteCustomPage(string sTable_Name,string sSign_Record,string sFilter_Condition,string sFind_RecordList,int iPage_Size,int iPage_Index,int iTaxis_Sign,out int iPageCount,out int iiRecord_Count)
{
try
{
int iRecord_Count=0;
iiRecord_Count=0;
string sSql="";
if(sFilter_Condition=="")
{
sSql="SELECT COUNT(*) AS RC FROM "+sTable_Name;
}
else
{
sSql="SELECT COUNT(*) AS RC FROM "+sTable_Name+" WHERE "+sFilter_Condition;
}
DataTable dt=this.ExecuteDataSet(sSql).Tables[0];
if(dt==null)
{
iPageCount=0;
return null;
}
if(dt.Rows.Count<=0)
{
iPageCount=0;
return null;
}
iRecord_Count=(int)dt.Rows[0]["RC"];
iiRecord_Count=iRecord_Count;
if(iRecord_Count % iPage_Size==0)
{
iPageCount=iRecord_Count/iPage_Size;
}
else
{
iPageCount=iRecord_Count/iPage_Size+1;
}
ArrayList Params=new ArrayList();
Params.Add(new SqlParameter("@Table_Name",sTable_Name));
Params.Add(new SqlParameter("@Sign_Record",sSign_Record));
Params.Add(new SqlParameter("@Filter_Condition",sFilter_Condition));
Params.Add(new SqlParameter("@Page_Size",iPage_Size));
Params.Add(new SqlParameter("@Page_Index",iPage_Index));
Params.Add(new SqlParameter("@Taxis_Sign",iTaxis_Sign));
Params.Add(new SqlParameter("@Record_Count",iRecord_Count));
Params.Add(new SqlParameter("@Find_RecordList",sFind_RecordList));
SqlParameter paramsReturnVl=new SqlParameter("RETURN_VALUE",SqlDbType.Int);
paramsReturnVl.Direction=ParameterDirection.ReturnValue;
Params.Add(paramsReturnVl);
return this.ExecuteDataSet("ProcCustomPage",Params);
}
catch(System.Exception ex)
{
iPageCount=0;
iiRecord_Count=0;
throw new Exception(ex.Message);
}
}
/// <summary>
/// 执行自定义分页返回<see cref="System.Data.DataSet">System.Data.DataSet</see>
/// </summary>
/// <param name="sTable_Name">要查询的表名</param>
/// <param name="sSign_Record">唯一标识的字段</param>
/// <param name="sFilter_Condition">查询条件</param>
/// <param name="sFind_RecordList">要查询的字段列表,全部字段查询使用空字符</param>
/// <param name="iPage_Size">分页的每页的大小</param>
/// <param name="iPage_Index">当前页数</param>
/// <param name="iTaxis_Sign">排序标志 0正序1倒序</param>
/// <param name="iPageCount">总页数.输出参数</param>
/// <param name="iiRecord_Count">总记录数.输出参数</param>
/// <returns>成功返回<see cref="System.Data.DataSet">System.Data.DataSet</see></returns>
public DataTable GetLines( string Condition,string tableName,string Records,int PageSize,int PageIndex,out int PageCount,out int RecordSum)
{
DataTable dt=new DataTable();
dt=dataHelper.ExecuteCustomPage(tableName,"id",Condition,Records,PageSize,PageIndex,1,out PageCount,out RecordSum).Tables[0];
return dt;
}
CREATE PROCEDURE ProcCustomPage
(
@Table_Name varchar(1000), /* 查询的表名 */
@Sign_Record varchar(50), /* 标志字段 */
@Filter_Condition varchar(1000), /* 过滤条件 */
@Page_Size int, /* 每页记录数 */
@Page_Index int, /* 页号 */
@Taxis_Sign int, /* 排序标志 0:正序 1:倒序 */
@Find_RecordList varchar(1000), /* 查询的字段,字段间用,分割 空为查询全部*/
@Record_Count int /* 总记录数 */
)
AS
BEGIN
DECLARE @Start_Number int
DECLARE @End_Number int
DECLARE @TopN_Number int
DECLARE @sSQL varchar(5000)
if(@Find_RecordList='')
BEGIN
SELECT @Find_RecordList='*'
END
SELECT @Start_Number =(@Page_Index-1) * @Page_Size
IF @Start_Number<=0
SElECT @Start_Number=0
SELECT @End_Number=@Start_Number+@Page_Size
IF @End_Number>@Record_Count
SELECT @End_Number=@Record_Count
SELECT @TopN_Number=@End_Number-@Start_Number
IF @TopN_Number<=0
SELECT @TopN_Number=0
print @TopN_Number
print @Start_Number
print @End_Number
print @Record_Count
IF @Taxis_Sign=0
BEGIN
IF @Filter_Condition=''
BEGIN
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
ORDER BY '+@Sign_Record+') order by '+@Sign_Record+' DESC)order by '+@Sign_Record+' DESC'
END
ELSE
BEGIN
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
WHERE '+@Filter_Condition+' ORDER BY '+@Sign_Record+') and '+@Filter_Condition+' order by '+@Sign_Record+' DESC) and '+@Filter_Condition+' order by '+@Sign_Record+' DESC'
END
END
ELSE
BEGIN
IF @Filter_Condition=''
BEGIN
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
ORDER BY '+@Sign_Record+' DESC) order by '+@Sign_Record+')order by '+@Sign_Record+' DESC'
END
ELSE
BEGIN
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
WHERE '+@Filter_Condition+' ORDER BY '+@Sign_Record+' DESC) and '+@Filter_Condition+' order by '+@Sign_Record+') and '+@Filter_Condition+' order by '+@Sign_Record+' DESC'
END
END
EXEC (@sSQL)
IF @@ERROR<>0
RETURN -3 /* 查询记录出错 */
RETURN 0
END
GO
/// <summary>
/// 执行自定义分页返回<see cref="System.Data.DataSet">System.Data.DataSet</see>
/// </summary>
/// <param name="sTable_Name">要查询的表名</param>
/// <param name="sSign_Record">唯一标识的字段</param>
/// <param name="sFilter_Condition">查询条件</param>
/// <param name="sFind_RecordList">要查询的字段列表,全部字段查询使用空字符</param>
/// <param name="iPage_Size">分页的每页的大小</param>
/// <param name="iPage_Index">当前页数</param>
/// <param name="sTaxisField">排序的字段</param>
/// <param name="iTaxis_Sign">排序标志 0正序1倒序</param>
/// <param name="iPageCount">总页数.输出参数</param>
/// <param name="iiRecord_Count">总记录数.输出参数</param>
/// <returns>成功返回<see cref="System.Data.DataSet">System.Data.DataSet</see></returns>
public DataSet ExecuteCustomPage(string sTable_Name,string sSign_Record,string sFilter_Condition,string sFind_RecordList,int iPage_Size,int iPage_Index,string sTaxisField,int iTaxis_Sign,out int iPageCount,out int iiRecord_Count)
{
try
{
int iRecord_Count=0;
iiRecord_Count=0;
string sSql="";
if(sFilter_Condition=="")
{
sSql="SELECT COUNT(*) AS RC FROM "+sTable_Name;
}
else
{
sSql="SELECT COUNT(*) AS RC FROM "+sTable_Name+" WHERE "+sFilter_Condition;
}
DataTable dt=this.ExecuteDataSet(sSql).Tables[0];
if(dt==null)
{
iPageCount=0;
return null;
}
if(dt.Rows.Count<=0)
{
iPageCount=0;
return null;
}
iRecord_Count=(int)dt.Rows[0]["RC"];
iiRecord_Count=iRecord_Count;
if(iRecord_Count % iPage_Size==0)
{
iPageCount=iRecord_Count/iPage_Size;
}
else
{
iPageCount=iRecord_Count/iPage_Size+1;
}
ArrayList Params=new ArrayList();
Params.Add(new SqlParameter("@Table_Name",sTable_Name));
Params.Add(new SqlParameter("@Sign_Record",sSign_Record));
Params.Add(new SqlParameter("@Filter_Condition",sFilter_Condition));
Params.Add(new SqlParameter("@Page_Size",iPage_Size));
Params.Add(new SqlParameter("@Page_Index",iPage_Index));
Params.Add(new SqlParameter("@TaxisField",sTaxisField));
Params.Add(new SqlParameter("@Taxis_Sign",iTaxis_Sign));
Params.Add(new SqlParameter("@Record_Count",iRecord_Count));
Params.Add(new SqlParameter("@Find_RecordList",sFind_RecordList));
SqlParameter paramsReturnVl=new SqlParameter("RETURN_VALUE",SqlDbType.Int);
paramsReturnVl.Direction=ParameterDirection.ReturnValue;
Params.Add(paramsReturnVl);
return this.ExecuteDataSet("ProcCustomTaxisPage",Params);
}
catch(System.Exception ex)
{
iPageCount=0;
iiRecord_Count=0;
throw new Exception(ex.Message);
}
}
DataSet ExecuteCustomPage(string sTable_Name,string sSign_Record,string sFilter_Condition,string sFind_RecordList,int iPage_Size,int iPage_Index,string sTaxisField,int iTaxis_Sign,out int iPageCount,out int iiRecord_Count);
CREATE PROCEDURE ProcCustomTaxisPage
(
@Table_Name varchar(5000), /* 查询的表名 */
@Sign_Record varchar(50), /* 标志字段 */
@Filter_Condition varchar(1000), /* 过滤条件 */
@Page_Size int, /* 每页记录数 */
@Page_Index int, /* 页号 */
@TaxisField varchar(1000),
@Taxis_Sign int, /* 排序标志 0:正序 1:倒序 */
@Find_RecordList varchar(1000), /* 查询的字段,字段间用,分割 空为查询全部*/
@Record_Count int /* 总记录数 */
)
AS
BEGIN
DECLARE @Start_Number int
DECLARE @End_Number int
DECLARE @TopN_Number int
DECLARE @sSQL varchar(8000)
if(@Find_RecordList='')
BEGIN
SELECT @Find_RecordList='*'
END
SELECT @Start_Number =(@Page_Index-1) * @Page_Size
IF @Start_Number<=0
SElECT @Start_Number=0
SELECT @End_Number=@Start_Number+@Page_Size
IF @End_Number>@Record_Count
SELECT @End_Number=@Record_Count
SELECT @TopN_Number=@End_Number-@Start_Number
IF @TopN_Number<=0
SELECT @TopN_Number=0
print @TopN_Number
print @Start_Number
print @End_Number
print @Record_Count
IF @TaxisField=''
begin
select @TaxisField=@Sign_Record
end
IF @Taxis_Sign=0
BEGIN
IF @Filter_Condition=''
BEGIN
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+'
'+@Sign_Record+' FROM '+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+'
'+@Sign_Record+' FROM '+@Table_Name+'
ORDER BY '+@TaxisField+') order by '+@TaxisField+' DESC)order by '+@TaxisField
END
ELSE
BEGIN
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM
'+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM
'+@Table_Name+'
WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+'
DESC) and '+@Filter_Condition+' order by '+@TaxisField
END
END
ELSE
BEGIN
IF @Filter_Condition=''
BEGIN
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM
'+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM
'+@Table_Name+'
ORDER BY '+@TaxisField+' DESC) order by '+@TaxisField+')order by '+@TaxisField+' DESC'
END
ELSE
BEGIN
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM
'+@Table_Name+'
WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM
'+@Table_Name+'
WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+' DESC) and '+@Filter_Condition+' order by
'+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC'
END
END
EXEC (@sSQL)
IF @@ERROR<>0
RETURN -3 /* 查询记录出错 */
RETURN 0
END
GO