分页查询+组合查询

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data.SqlClient;

/// <summary>
/// CeshiData 的摘要说明
/// </summary>
public class CeshiData
{
    SqlConnection conn = null;
    SqlCommand cmd = null;
public CeshiData()
    {
        conn = new SqlConnection("server=.;database=Data0425;user=sa;pwd=123;");
        cmd = conn.CreateCommand();
    }
public List<Ceshi> Select(string Tsql, Hashtable hh)
    {
        List<Ceshi> list = new List<Ceshi>();
        cmd.CommandText = Tsql;
        cmd.Parameters.Clear();
foreach (string k in hh.Keys)
        {
            cmd.Parameters.Add(k, hh[k]);
        }
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
        {
while (dr.Read())
            {
                Ceshi c = new Ceshi();
                c.Ids = Convert.ToInt32(dr[0]);
                c.Name = dr[1].ToString();
                c.Sex = dr[2].ToString();
                list.Add(c);
            }
        }
        conn.Close();
return list;
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
public partial class Defaulet1 : System.Web.UI.Page
{
int PageCount = 5;//一页最多显示5条
    Hashtable hs = new Hashtable();
protected void Page_Load(object sender, EventArgs e)
    {
        Button1.Click += Button1_Click;//条件查询
        button_Prev.Click += button_Prev_Click;
        button_Next.Click += button_Next_Click;
if(IsPostBack==false)
        {
            Repeater1.DataSource = new CeshiData().Select(TSQL(1), hs);//注意 哈希表写法
            Repeater1.DataBind();
        }
    }
//下一页写法
void button_Next_Click(object sender, EventArgs e)
    {
if (Label2.Text == MaxNumber().ToString())
        {
return;//跳出
        }
//1.看看当前是第几页，然后将页数+1
int pagenumber = Convert.ToInt32(Label2.Text) + 1;//获取下一页的页数，默认其实页数是1！！！！！！！！
        Label2.Text = pagenumber.ToString();//页面显示页数+1
//2.再然后将+1后的页面显示的数据查出来绑定
        Repeater1.DataSource = new CeshiData().Select(TSQL(pagenumber), hs);//一页对应多个哈希表数据
        Repeater1.DataBind();
        Label1.Text = TSQL(pagenumber);
    }
//上一页
void button_Prev_Click(object sender, EventArgs e)
    {
if(Label2.Text=="1")//第一页直接返回
        {
return ;
        }
//1.看看当前是第几页，然后将页数加-1
int PageNumber=Convert.ToInt32(Label2.Text)-1;//获取下一页的页数
        Label2.Text=PageNumber.ToString();
//2.然后再将-1后的页数数据查询出来绑定
        Repeater1.DataSource=new CeshiData().Select(TSQL(PageNumber),hs);
        Repeater1.DataBind();
        Label1.Text=TSQL(PageNumber);
    }
//条件查询
void Button1_Click(object sender ,EventArgs e)
    {
//1.将语句拼完-调用Tsql()方法
//2.用拼完的语句查询数据并绑定
        Repeater1.DataSource=new CeshiData().Select(TSQL(1),hs);
        Repeater1.DataBind();
        Label1.Text=TSQL(1);
        Label2.Text="1";
    }
//核心，如何返回TAQL语句是难点
private string TSQL(int PageNumber)
    {
    hs.Clear();
int cc=0;//记录下一条查询数据
string sql="select top"+PageCount+"*from Ceshi";
string t1="";
string t2="";
//1.将条件查询的语句拼完
if(TextBox1.Text!="")
{
sql+="where Name like @name";
cc++;
t1="where Name like #name";
hs.Add("@name","%"+TextBox1.Text+"%");
}
if (TextBox2.Text!="")//判断性别
        {
if(cc>0)
            {
            sql+="and sex like @sex";
            t2="and sex like @sex";
            }
else
            {
            sql+="where sex like @sex";
            t2="where sex like @sex";
            }
            hs.Add("@sex","%"+TextBox2.Text+"%");
            cc++;
        }
//2.将分页的语句拼完
if(cc>0)
        {
        sql+="and Ids not in(select top "+(PageCount*(PageNumber-1))+"Ids from CeShi"+t1+t2+")";
        }
else 
        {
        sql+="whrere Ids not in(select top"+(PageCount*(PageNumber-1))+"Ids from Ceshi"+t1+t2+")";
        }
return sql;
    }
//查询全部的复合条件的数据
private string TSQL1()
    {
int CC=0;//记录一下查询的条数
string sql="select * from Ceshi";
//1.将条件查询的语句拼完
if(TextBox1.Text!="")
        {
            sql+="where name like '%"+TextBox1.Text+"%'";//单引号层级大于双引号
            CC++;
    }
if (TextBox2.Text!="")
        {
if (CC>0)
        {
            sql+="where sex like '%"+TextBox2.Text+"%'";
        }
else
        {
        sql+="where sex like '%"+TextBox2.Text+"%'";
        }
            CC++;
        }
return sql;
    }
private  int MaxNumber()
    {
    List<Ceshi>CCC=new CeshiData().Select(TSQL1(),hs);
double bbb=CCC.Count/(PageCount*1.0);//总页数，浮点型，防止最后一页不是整数
return Convert .ToInt32(Math.Ceiling(bbb));//取上限
    }
//1.做一个功能，先考虑，如何把他们合并起来
//2.如何合并？看看他们有什么共同点-都是拼TSQL语句
//3.再一步一步按照主要功能来做
   
}