分页查询+组合查询

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.再一步一步按照主要功能来做
   
}