aspnet实现搜索查询_.net搜索查询并实现分页实例

前台：

第一页 上一页 下一页 最后一页

后台：

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

using System.Text;

namespace 分页练习

{

public partial class 分页 : System.Web.UI.Page

{

int pagesize = 3;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

//ViewState虽然是声明在函数内部，看似是局部变量，但是在类中的其他函数中也可以直接使用

ViewState["pageindex"] = 1;

LoadData();

Count();

}

}

//搜索查询

private void LoadData()

{

string strcon = "Data Source=PC-DLL;Initial Catalog=News;Persist Security Info=True;User Id=sa;Password=linlin";

SqlConnection conn = new SqlConnection(strcon);

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandText = "SELECT TOP(@pagesize) * FROM T_News WHERE(NewsTitle LIKE @newskey OR NewsContent LIKE @newskey) AND Id NOT IN(SELECT TOP ((@pageindex-1)*@pagesize) Id FROM T_News WHERE NewsTitle LIKE @newskey OR NewsContent LIKE @newskey ORDER BY Id )ORDER BY Id";

cmd.Parameters.AddWithValue("@newskey", "%" + txtKey.Text + "%");

cmd.Parameters.AddWithValue("@pagesize",pagesize);

cmd.Parameters.AddWithValue("@pageindex", Convert.ToInt32(ViewState["pageindex"]));

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

adapter.Fill(dt);

StringBuilder sb1 = new StringBuilder();

sb1.Append("

sb1.Append("

标题内容创建时间");

foreach (DataRow row in dt.Rows)

{

sb1.Append("

");

sb1.Append("

" + row["NewsTitle"].ToString() + "");

sb1.Append("

" + row["NewsContent"].ToString() + "");

sb1.Append("

" + row["CreateTime"].ToString() + "");

sb1.Append("

");

}

sb1.Append("

");

divResult.InnerHtml = sb1.ToString();

}

private void Count()

{

string strcon = "Data Source=PC-DLL;Initial Catalog=News;Persist Security Info=True;User Id=sa;Password=linlin";

SqlConnection conn = new SqlConnection(strcon);

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandText = "SELECT COUNT(*) FROM T_News WHERE NewsTitle LIKE @newskey OR NewsContent LIKE @newskey";

cmd.Parameters.AddWithValue("@newskey", "%" + txtKey.Text + "%");

conn.Open();

int totalcount = Convert.ToInt32(cmd.ExecuteScalar());

if (totalcount % pagesize == 0)

{

ViewState["pagelastindex"] = totalcount / pagesize;

}

else

{

ViewState["pagelastindex"] = totalcount / pagesize + 1;

}

cmd.Dispose();

conn.Dispose();

}

//第一页

protected void btnFirst_Click(object sender, EventArgs e)

{

ViewState["pageindex"] = 1;

LoadData();

}

//上一页

protected void btnBefore_Click(object sender, EventArgs e)

{

int pageindex = Convert.ToInt32(ViewState["pageindex"]);

if (pageindex > 1)

{

pageindex--;

ViewState["pageindex"] = pageindex;

LoadData();

}

}

//下一页

protected void btnNext_Click(object sender, EventArgs e)

{

int pageindex = Convert.ToInt32(ViewState["pageindex"]);

if (pageindex < Convert.ToInt32(ViewState["pagelastindex"]))

{

pageindex++;

ViewState["pageindex"] = pageindex;

LoadData();

}

}

//最后一页

protected void btnLast_Click(object sender, EventArgs e)

{

ViewState["pageindex"] = ViewState["pagelastindex"];

LoadData();

}

protected void btnQuery_Click(object sender, ImageClickEventArgs e)

{

Count();

LoadData();

}

}

}