该类实现了分页条件组合，但只有一个完整传参的方法，实际中可自己重载
该类与数据访问相关，可与分页组件合用（这两个类见其它文章）
相关存储过程附在下边
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 }}
