CODEWITHSUNDEEP
c#wpf
MarlinDOODOO
MarlinDOODOO

Reputation: 65

How do you search database generated listbox items from a textbox

I am trying to implement a search/filter function with a TextBox. The TextBox should search my ListBox which is filled via data from SQL Database.

This is what I have currently tried.

 private void Search_TextChanged(object sender, TextChangedEventArgs e)
{
    GetAreas();
}

private void GetAreas()
{
    using (SqlConnection sqlCon = new SqlConnection(connectionString))
        {
            sqlCon.Open();
            string query_search = "SELECT * FROM tblAllPostCodes WHERE (Town LIKE @Town)";
            SqlCommand com = new SqlCommand(query_search, sqlCon);


            com.Parameters.AddWithValue("@Town", "%" + Search.Text + "%");

            using (SqlDataAdapter adapter = new SqlDataAdapter(com))
            {
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                Area.ItemsSource = dt.DefaultView;
            }
            sqlCon.Close();
        }
}

This is how a get my ListBox Items to show 

public void AreaList()
    {

        DataTable dt = new DataTable();
        SqlDataAdapter adpt = new SqlDataAdapter("SELECT DISTINCT Town from tblAllPostCodes", sqlCon);
        adpt.Fill(dt);

        foreach (DataRow dr in dt.Rows)
        {
            Area.Items.Add(dr["Town"].ToString());
        }

    }

I have tried clearing Items but they end up being cleared on search and not filling with matching text.

Any help would be appreciated I have been working on this for a while now.

Upvotes: 0

Views: 267

Answers (2)

mm8
mm8

Reputation: 169420

You should only have to query the database once and save the results in a collection variable:

private readonly List<string> _allTowns = new List<string>();
public void AreaList()
{
    _allTowns.Clear();
    using (DataTable dt = new DataTable())
    using (SqlDataAdapter adpt = new SqlDataAdapter("SELECT DISTINCT Town from tblAllPostCodes", sqlCon))
    {
        adpt.Fill(dt);
        foreach (DataRow dr in dt.Rows)
        {
            _allTowns.Add(dr["Town"].ToString());
        }
    }
    Area.ItemsSource = _allTowns;
}

You could then simply filter the collection of all items in your GetAreas() method:

private void GetAreas()
{
    Area.ItemsSource = _allTowns.Where(x => x.Contains(Search.Text)).ToArray();
}

Upvotes: 1

Marvin Schuchardt
Marvin Schuchardt

Reputation: 123

Instead of firing an SQL query on each textchange event you should consider to just filter your dataview like: 

DataView dv = new DataView(dt);
dv.RowFilter = String.Format("Town like '%{0}%'", SearchText);
Area.ItemsSource = dv;

The XAML for the Listbox should look like this: 

<ListBox DisplayMemberPath="Town"/>

Upvotes: 0

Related Questions