如何从数据库中搜索两个文本框并在gridview上显示 [英] How to search on two text boxes from a database and display on gridview

问题描述

我在asp页面上有两个文本框和一个搜索按钮。用户可以通过在任何搜索框中输入文本进行搜索。我正在使用gridview与项目模板并绑定数据集。请告诉我如何按文本框搜索数据。例如：用户可以通过在所有2个文本框或3个文本框中输入数据进行搜索。请为我提供asp.net的任何代码或建议c＃



我尝试过：

I have 2 text boxes on an asp page and one search button. Users can search by entering text in any of the search boxes. I am using gridview with item template and bind with dataset. please tell me how to search data by textbox. for ex: user can search by entering data in all 2 text boxes or 3 text boxes. please provide me any code or suggestion for asp.net c#

What I have tried:

protected void SearchButton_Click(object sender, EventArgs e)
{
   string str = "Select [ITEM No#], [Company Name], [Discipline Required], [Service Description], Institution, [Award Date] from PSP_Report where ([ITEM NO#] like '%' + @search + '%' [Company Name] like '%' + @search1 + '%')";
   SqlCommand xp = new SqlCommand(str, con);

   xp.Parameters.Add("@search", SqlDbType.NVarChar).Value = RefNo.Text;
   xp.Parameters.Add("@search1", SqlDbType.NVarChar).Value = RefNo.Text;

   con.Open();
   xp.ExecuteNonQuery();
   SqlDataAdapter da = new SqlDataAdapter();

   da.SelectCommand = xp;
   DataSet ds = new DataSet();
   da.Fill(ds, "ITEM No#");
   Search.DataSource = ds;
   Search.DataBind();
   con.Close();        
}

推荐答案

如果我这样做，那么我会有一些这样的代码：

If I was doing this then I'd have some code like this:

protected void Search_Click(object sender, EventArgs e)
  {

      var constr = ConfigurationManager.ConnectionStrings["ConnectToDB"].ConnectionString;

      var sql = new StringBuilder();
      sql.Append("Select [ITEM No#], [Company Name], [Discipline Required], [Service Description], Institution, [Award Date]");
      sql.Append(" from PSP_Report ");

      using (var con = new SqlConnection(constr))
      {
          using (var xp = new SqlCommand())
          {
              xp.Connection = con;

              var andRequired = false;
              var whereAdded = false;

              AddASearchItem(ref whereAdded, ref andRequired, sql, Search1.Text, "[ITEM NO#]", 1, xp);
              AddASearchItem(ref whereAdded, ref andRequired, sql, Search2.Text, "[Company Name]", 2, xp);
              AddASearchItem(ref whereAdded, ref andRequired, sql, Search3.Text, "[Discipline Required]", 3, xp);

              xp.CommandText = sql.ToString();

              con.Open();

              var da = new SqlDataAdapter {SelectCommand = xp};

              var ds = new DataSet();
              da.Fill(ds);
              GridView1.DataSource = ds.Tables[0];
              GridView1.DataBind();
              con.Close();
          }
      }
  }

  protected void AddASearchItem(ref bool whereAdded, ref bool andRequired, StringBuilder s,
      string searchText, string colName, int item, SqlCommand cmd)
  {
      //Add a search item to the string builder determining whether or not the words 'WHERE' or 'AND'
      //need to be added. Add an appropriate parameter to the sql command.
      if (string.IsNullOrEmpty(searchText)) return;
      if (!whereAdded)
      {
          s.Append(" where ");
          whereAdded = true;
      }
      else
          if (andRequired) s.Append(" AND");
      s.Append(string.Format("  {0} LIKE @search{1}", colName,item));
      cmd.Parameters.AddWithValue("@search" + item.ToString(), "%" + searchText + "%");
      andRequired = true;
  }

注意事项：

1 - 我用StringBuilder动态构建sql命令文本。这比连接字符串要好（因为字符串是不可变的）

2 - 我有一个小函数，只有在相关的搜索文本框中有内容时才会将搜索项添加到SQL上。

3 - 如果需要将WHERE或AND添加到SQL中，我需要解决这个问题。

4 - 如果搜索框中没有任何内容，则会返回所有记录

Points to note:
1 - I've used a StringBuilder to dynamically build up the sql command text. This is better than concatenating strings (because strings are immutable)
2 - I have a little function that only adds search items onto the SQL if the relevent search textbox has something in it.
3 - I need to work out if the words " WHERE " or " AND " need to be added to the SQL.
4 - If none of the search boxes have anything in them then all records will be returned

这篇关于如何从数据库中搜索两个文本框并在gridview上显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！