该类实现了分页条件组合，但只有一个完整传参的方法，实际中可自己重载
该类与数据访问相关，可与分页组件合用（这两个类见其它文章）
相关存储过程附在下边
using System;
using System.Data;
using System.Collections;
using HKH.DataBase;
using HKH.DataBase.Type;
namespace HKH.Common
{
/// <summary>
/// 分页取得数据
/// </summary>
public class clsPagination
{
IDbDataAdapter adapter = null;
IDbCommand cmd = null;
public clsPagination()
{
adapter = DBFactory.GetDAOBuilder().GetQueryDataAdapter();
cmd = adapter.SelectCommand;
}
#region 整理后的方法 By Lwt 2006-03-30
#region 组织查询条件
/// <summary>
/// 组织查询条件
/// </summary>
/// <param name="myds">可包含多表的DataSet</param>
/// <param name="Key">搜索关键字</param>
/// <returns></returns>
public string GetCondition(string Key,DataSet myds)
{
return GetCondition(myds,Key,0);
}
/// <summary>
/// 组织查询条件
/// </summary>
/// <param name="myds">可包含多表的DataSet</param>
/// <param name="Key">搜索关键字</param>
/// <param name="UserID">读者域单独搜索</param>
/// <returns></returns>
public string GetCondition(DataSet myds,string Key,int UserID)
{
string condition="";
string tempcondition="";
for(int i=0;i<myds.Tables.Count;i++)
{
string currTableName="";//当前表名，或别名
//SQL串超长时，可用别名
if(myds.Tables[i].ExtendedProperties.Count>0 && myds.Tables[i].ExtendedProperties.ContainsKey("Alias") && myds.Tables[i].ExtendedProperties["Alias"].ToString().Trim() != "")
{
currTableName = myds.Tables[i].ExtendedProperties["Alias"].ToString().Trim();
}
else
{
currTableName = myds.Tables[i].TableName;
}
for(int j=0;j<myds.Tables[i].Columns.Count;j++)
{
if(myds.Tables[i].Columns[j].ExtendedProperties.Count>0 && myds.Tables[i].Columns[j].ExtendedProperties.ContainsKey("Query") && myds.Tables[i].Columns[j].ExtendedProperties["Query"].ToString()=="1")
{
if(myds.Tables[i].Columns[j].DataType==System.Type.GetType("System.DateTime"))
{
if(clsCheckValid.CheckDate(Key))
{
condition=condition+currTableName+"."+myds.Tables[i].Columns[j].ColumnName+" = '"+Key+"' or ";
}
}
else
{
condition=condition+currTableName+"."+myds.Tables[i].Columns[j].ColumnName+" like '%"+Key+"%' or ";
}
}
}
}
if( "" == condition )
{
condition = "(1=1)";//默认显示所有
}
else
{
condition += " (1=0)";
}
if( "" != tempcondition )
{
condition="("+tempcondition+") and ("+condition+")";
}
return condition;
}
#endregion
#region 通用分页方法
/// <summary>
/// 通用分页方法
/// </summary>
/// <param name="myds">组织条件使用的DataSet</param>
/// <param name="DisplayColumns">字段显示列表</param>
/// <param name="SearchKey">搜索关键字</param>
/// <param name="SortColumn">排序列</param>
/// <param name="joinColumn">外联接的列</param>
/// <param name="joinTable">外联接的表</param>
/// <param name="UserID">用户ID</param>
/// <param name="Condition">用户自定义条件</param>
/// <param name="pageSize">每页行数</param>
/// <param name="linkNumber">当前页索引</param>
/// <param name="TotalAmount">总行数</param>
/// <returns></returns>
public DataSet GetPageData(DataSet myds,ArrayList DisplayColumns,string SearchKey,string SortColumn,string joinColumn,string joinTable,int UserID,string Condition,int pageSize,int linkNumber,ref int TotalAmount)
{
//清除本用户的临时表
ClearTempTab(UserID);
string currTableName="";//主表名或别名
if(myds.Tables[0].ExtendedProperties.Count>0 && myds.Tables[0].ExtendedProperties.ContainsKey("Alias") && myds.Tables[0].ExtendedProperties["Alias"].ToString().Trim() != "")
{
currTableName = myds.Tables[0].ExtendedProperties["Alias"].ToString().Trim();
}
else
{
currTableName = myds.Tables[0].TableName;
}
string mycondition=GetCondition(myds,SearchKey,UserID);
if(Condition !=null && Condition.Trim() !="")
{
mycondition ="(" + mycondition + ") and ("+Condition+")";
}
int startRow=pageSize*(linkNumber-1);
int endRow=pageSize*linkNumber-1;
DataColumn[] PrimaryKeys=myds.Tables[0].PrimaryKey;
string strColumns="";
if(DisplayColumns !=null && DisplayColumns.Count>0)
{
for(int i=0;i<DisplayColumns.Count;i++)
{
if(DisplayColumns[i].ToString().Trim()!="")
{
strColumns+=","+currTableName+"."+DisplayColumns[i].ToString().Trim();
}
}
}
else
{
strColumns+=","+currTableName+".*";
}
string strCreate="select Distinct("+currTableName+"."+PrimaryKeys[0].ColumnName+") as OnlyOne"+strColumns+joinColumn+" into ##TempTab" + UserID.ToString() + " from "+myds.Tables[0].TableName;
//是否有别名
if (myds.Tables[0].TableName.CompareTo(currTableName) != 0)
{
strCreate+=" as " + currTableName;
}
strCreate+=joinTable+" where "+mycondition;
if(SortColumn!=null && SortColumn.Trim()!="")
{
strCreate+=" order by "+SortColumn+";";
}
else
{
strCreate+=";";
}
string strDrop="drop table ##TempTab" + UserID.ToString() + ";";
string strSQL="";
//------构造表的别名
ArrayList tables=new ArrayList();
for(int i=0;i<PrimaryKeys.Length+2;i++)
{
tables.Add("T"+i.ToString());
}
strSQL="select * from (select top 100 percent";
//----构造序列号
ArrayList sqls=new ArrayList();
for(int i=0;i<PrimaryKeys.Length;i++)
{
#region 拼凑生成序列的条件
int temp=i;
string strcondition="";
while(temp!=0)
{
temp=temp-1;
strcondition=strcondition+tables[i]+"."+PrimaryKeys[temp].ColumnName+"="+tables[tables.Count-2]+"."+PrimaryKeys[temp].ColumnName+" and ";
}
strcondition=strcondition+tables[i]+"."+PrimaryKeys[i].ColumnName+"<"+tables[tables.Count-2]+"."+PrimaryKeys[i].ColumnName+" and ";
strcondition=strcondition+"(1=1)";
#endregion
if(i==PrimaryKeys.Length-1)
{
sqls.Add("(select count(*) from ##TempTab" + UserID.ToString() +" as "+tables[i]+" where "+strcondition+")");
}
else
{
sqls.Add("(select count(*) from ##TempTab" + UserID.ToString() + " as "+tables[i]+" where "+strcondition+")+");
}
}
for(int i=0;i<sqls.Count;i++)
{
strSQL=strSQL+sqls[i];
}
strSQL=strSQL+" as RowIndex,* from ##TempTab" + UserID.ToString() + " as "+tables[tables.Count-2]+" order by RowIndex)"+" as "+
tables[tables.Count-1]+" where "+tables[tables.Count-1]+"."+"RowIndex between "+startRow.ToString()+" and "+endRow.ToString();
strSQL+="select count(*) from ##TempTab" + UserID.ToString() + ";";
DataSet ds=new DataSet();
ProcParameter[] parameters = new ProcParameter[4];
parameters[0] = new ProcParameter("@SQLString",strSQL,ProcParameterType.NVarChar,4000);
parameters[1] = new ProcParameter("@SubSQLString",strCreate+"~"+strDrop,ProcParameterType.NVarChar,4000);
parameters[2] = new ProcParameter("@PageNum",linkNumber,ProcParameterType.Int,4);
parameters[3] = new ProcParameter("@ShowCount",pageSize,ProcParameterType.Int,4);
IDbCommand procCmd = DBFactory.GetDAOBuilder().GetSprocCommand("SP_ExecSQL_2",parameters);
adapter.SelectCommand = procCmd;
adapter.Fill(ds);
TotalAmount=ds.Tables[1].Rows.Count>0 ? (Convert.IsDBNull(ds.Tables[1].Rows[0][0]) ? 0 : Convert.ToInt32(ds.Tables[1].Rows[0][0])) : 0;
ds.Tables[0].Columns.Remove("RowIndex");
ds.Tables[0].Columns.Remove("OnlyOne");
return ds;
}
#endregion
#region 报表查询方法（主要完成分页功能）
/// <summary>
/// 报表分页
/// </summary>
/// <param name="reportSql">报表数据查询的SQL语句</param>
/// <param name="DisplayColumns">要显示的列</param>
/// <param name="UserID">当前用户名</param>
/// <param name="pageSize">每页行数</param>
/// <param name="linkNumber">当前页索引</param>
/// <param name="TotalAmount">总行数</param>
/// <returns></returns>
public DataSet GetDataForReport(string reportSql,ArrayList DisplayColumns,int UserID,int pageSize,int linkNumber,ref int TotalAmount)
{
//清除本用户的临时表
ClearTempTab(UserID);
int startRow=pageSize*(linkNumber-1);
int endRow=pageSize*linkNumber-1;
//--------限定搜索范围
string strCreate="create table ##TempTab" + UserID.ToString() + "(pid int IDENTITY PRIMARY KEY";
for(int i=0;i<DisplayColumns.Count;i++)
{
strCreate+=","+DisplayColumns[i];
}
strCreate+=");insert into ##TempTab" + UserID.ToString() + "(";
for(int i=0;i<DisplayColumns.Count;i++)
{
string keyi=DisplayColumns[i].ToString();
strCreate+=keyi.Substring(0,keyi.IndexOf(" "))+",";
}
strCreate=strCreate.Substring(0,strCreate.Length-1);
strCreate+=")"+reportSql;
string strDrop="drop table ##TempTab" + UserID.ToString();
string strRange="##TempTab" + UserID.ToString();
//------构造表的别名
ArrayList tables=new ArrayList();
for(int i=0;i<3;i++)
{
tables.Add("T"+i.ToString());
}
string strSQL="select * from (select top 100 percent";
//----构造序列号
ArrayList sqls=new ArrayList();
#region 拼凑生成序列的条件
string strcondition="";
strcondition=strcondition+tables[0]+".pid <"+tables[1]+".pid and ";
strcondition=strcondition+"(1=1)";
sqls.Add("(select count(*) from "+strRange+" as "+tables[0]+" where "+strcondition+")");
#endregion
for(int i=0;i<sqls.Count;i++)
{
strSQL=strSQL+sqls[i];
}
strSQL=strSQL+" as RowIndex,* from "+strRange + " as "+tables[tables.Count-2]+" order by RowIndex)"+" as "+
tables[tables.Count-1]+" where "+tables[tables.Count-1]+"."+"RowIndex between "+startRow.ToString()+" and "+endRow.ToString()+";";
strSQL+="select count(*) from ##TempTab" + UserID.ToString() + ";";
DataSet ds=new DataSet();
cmd.CommandText=strCreate+" "+strSQL+" "+strDrop;
adapter.Fill(ds);
TotalAmount=ds.Tables[1].Rows.Count>0 ? (Convert.IsDBNull(ds.Tables[1].Rows[0][0]) ? 0 : Convert.ToInt32(ds.Tables[1].Rows[0][0])) : 0;
ds.Tables[0].Columns.Remove("RowIndex");
return ds;
}
#endregion
#region 清除临时表，因一旦执行过程中出现异常，临时表驻于TEMPDB数据库
private void ClearTempTab(int UserID)
{
try
{
ProcParameter[] parameters = new ProcParameter[1];
parameters[0] = new ProcParameter("@TempTabName","##TempTab" + UserID.ToString() + ";",ProcParameterType.VarChar,200);
IDbCommand procCmd = DBFactory.GetDAOBuilder().GetSprocCommand("clearTempTab",parameters);
procCmd.Connection.Open();
procCmd.ExecuteNonQuery();
procCmd.Connection.Close();
procCmd.Dispose();
}
catch(Exception ex)
{
throw ex;
}
}
#endregion
#endregion
}
}