使用ADO.NET 数据访问技术制作web端组合查询加分页的功能关键在于查询SQL语句的拼接
以Car 表为例 每页显示3条数据
数据访问类使用查询方法，tsql 查询的连接字符串，查询的参数放到Hashtable中
1 public List<Car> Select(string tsql, Hashtable hh) 2 { 3 List<Car> list = new List<Car>(); 4 cmd.CommandText = tsql; 5 cmd.Parameters.Clear(); 6 foreach (string k in hh.Keys) 7 { 8 cmd.Parameters.Add(k, hh[k]); 9 } 10 conn.Open(); 11 SqlDataReader dr = cmd.ExecuteReader(); 12 if (dr.HasRows) 13 { 14 while (dr.Read()) 15 { 16 Car c = new Car(); 17 c.Code = dr["Code"].ToString(); 18 c.Name = dr["Name"].ToString(); 19 c.Brand = dr["Brand"].ToString(); 20 c.Oil = Convert.ToDecimal(dr["Oil"]); 21 c.Powers = Convert.ToInt32(dr["Powers"]); 22 c.Exhaust = Convert.ToInt16(dr["Exhaust"]); 23 c.Price = Convert.ToDecimal(dr["Price"]); 24 list.Add(c); 25 } 26 } 27 conn.Close(); 28 return list; 29 }
后台代码封装查询连接字符的方法
/// <summary> /// 拼接查询分页的SQL语句 /// </summary> /// <param name="pagenumber">显示第几页</param> /// <param name="isall">查最大页数的SQL语句</param> /// <returns>查询sql语句的字符创</returns> public string ReturnTsql(int pagenumber,bool isall) { hs.Clear();//清空哈希表数据 int count = 0;//文本是否输内容 string Tsql = "select top " + pagecount + " *from Car"; if (isall)//true查所有拼接的语句 Tsql = "select *from Car"; string tj = ""; if (!string.IsNullOrEmpty(TextBox_name.Text.Trim()))//文本框有内容 { Tsql += " where Name like @a"; tj += "where Name like @a"; hs.Add("@a", "%" + TextBox_name.Text.Trim() + "%"); count++; } if (!string.IsNullOrEmpty(TextBox_oil.Text.Trim())) { if (count == 0) { Tsql += " where Oil" + DropDownList_oil.SelectedValue + " @b"; tj += " where Oil" + DropDownList_oil.SelectedValue + " @b"; } else { Tsql += " and Oil" + DropDownList_oil.SelectedValue + " @b"; tj += " and Oil" + DropDownList_oil.SelectedValue + " @b"; } hs.Add("@b", TextBox_oil.Text.Trim()); count++; } if (!string.IsNullOrEmpty(TextBox_price.Text.Trim())) { if (count == 0) { Tsql += " where Price" + DropDownList_pri.SelectedValue + " @c"; tj += " where Price" + DropDownList_pri.SelectedValue + " @c"; } else { Tsql += " and Price" + DropDownList_pri.SelectedValue + " @c"; tj += " and Price" + DropDownList_pri.SelectedValue + " @c"; } hs.Add("@c", TextBox_price.Text.Trim()); count++; } //最终拼接的TSQL语句 if (count > 0)//文本框有输入 { Tsql += " and Code not in(select top " + (pagecount * (pagenumber - 1)) + " Code from Car " + tj + ")"; } else//没有输入 { Tsql += " where Code not in(select top " + (pagecount * (pagenumber - 1)) + " Code from Car " + tj + ")"; } return Tsql; } //查询最大页数 public int Maxpage(string tsql,Hashtable has) { List<Car> clist = new CarData().Select(tsql, has); return Convert.ToInt32(Math.Ceiling(clist.Count / (pagecount * 1.0))); }
功能代码 （查询 首页 下一页）
1 int pagecount = 3;//每页显示的数据 2 Hashtable hs = new Hashtable();//实例化一个全局的哈希表集合 3 protected void Page_Load(object sender, EventArgs e) 4 { 5 if (!IsPostBack)//加载页面数据绑定 6 { 7 Repeater1.DataSource = new CarData().Select(ReturnTsql(1,false), hs); 8 Repeater1.DataBind(); 9 Label_now.Text = "1";//当前页数 10 Label_max.Text = Maxpage(ReturnTsql(1,true), hs).ToString();//最大页数 11 DropDownList_jump.Items.Clear();//跳转页下拉列表数据绑定 12 for (int i = 1; i <= Maxpage(ReturnTsql(1, true), hs); i++) 13 { 14 15 DropDownList_jump.Items.Add(new ListItem(i.ToString(),i.ToString())); 16 } 17 } 18 Button_sel.Click += Button_sel_Click;//查询 19 LinkButton_frist.Click += LinkButton_frist_Click;//首页 20 LinkButton_prve.Click += LinkButton_prve_Click;//上一页 21 LinkButton_next.Click += LinkButton_next_Click;// 下一页 22 LinkButton_last.Click += LinkButton_last_Click;//尾页 23 DropDownList_jump.SelectedIndexChanged += DropDownList_jump_SelectedIndexChanged;//跳转 24 } 25 void Button_sel_Click(object sender, EventArgs e)// 查询 26 { 27 Label_now.Text = "1"; 28 Label1.Text = ReturnTsql(1, false); 29 Repeater1.DataSource = new CarData().Select(ReturnTsql(1, false), hs); 30 Repeater1.DataBind(); 31 Label_max.Text = Maxpage(ReturnTsql(1, true), hs).ToString(); 32 DropDownList_jump.Items.Clear(); 33 for (int i = 1; i <= Maxpage(ReturnTsql(1, true), hs); i++) 34 { 35 36 DropDownList_jump.Items.Add(new ListItem(i.ToString(), i.ToString())); 37 } 38 } 40 void LinkButton_frist_Click(object sender, EventArgs e)//首页 41 { 42 Label_now.Text = "1"; 43 Repeater1.DataSource = new CarData().Select(ReturnTsql(1, false), hs); 44 Repeater1.DataBind(); 45 //上一页按钮不可用，下一页按钮可用 46 LinkButton_next.Enabled = true; 47 LinkButton_prve.Enabled = false; 50 }
void LinkButton_next_Click(object sender, EventArgs e)//下一页 51 { 52 int pagenumber = Convert.ToInt32(Label_now.Text) + 1; 53 Repeater1.DataSource = new CarData().Select(ReturnTsql(pagenumber, false), hs); 54 Repeater1.DataBind(); 55 Label_now.Text = pagenumber.ToString();//改变当前页数的显示数据 56 if (pagenumber >= Maxpage(ReturnTsql(1,true),hs))//大于最大页数下一页按钮不可用 57 { 58 LinkButton_next.Enabled = false; 59 } 60 LinkButton_prve.Enabled = true; 61 } 62