C# AutoComplete

问题:

I am trying to add an autocomplete feature to a textbox, the results are coming from a database. They come in the format of

[001] Last, First Middle

Currently you must type [001]... to get the entries to show. So the problem is that I want it to complete even if I type the firstname first. So if an entry was

[001] Smith, John D

if I started typing John then this entry should show up in the results for the auto complete.

Currently the code looks something like

AutoCompleteStringCollection acsc = new AutoCompleteStringCollection();
txtBox1.AutoCompleteCustomSource = acsc;
txtBox1.AutoCompleteMode = AutoCompleteMode.Suggest; 
txtBox1.AutoCompleteSource = AutoCompleteSource.CustomSource; 

....

if (results.Rows.Count > 0)
    for (int i = 0; i < results.Rows.Count && i < 10; i++) 
    {
        row = results.Rows[i];
        acsc.Add(row[\"Details\"].ToString());
    }
}

results is a dataset containing the query results

The query is a simple search query using the like statement. The correct results are returned if we do not use the autocomplete and just toss the results into an array.

Any advice?

EDIT:

Here is the query that returns the results

SELECT Name from view_customers where Details LIKE \'{0}\'

With {0} being the placeholder for the searched string.

回答1:

The existing AutoComplete functionality only supports searching by prefix. There doesn\'t seem to be any decent way to override the behavior.

Some people have implemented their own autocomplete functions by overriding the OnTextChanged event. That\'s probably your best bet.

For example, you can add a ListBox just below the TextBox and set its default visibility to false. Then you can use the OnTextChanged event of the TextBox and the SelectedIndexChanged event of the ListBox to display and select items.

This seems to work pretty well as a rudimentary example:

public Form1()
{
    InitializeComponent();


    acsc = new AutoCompleteStringCollection();
    textBox1.AutoCompleteCustomSource = acsc;
    textBox1.AutoCompleteMode = AutoCompleteMode.None;
    textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
}

private void button1_Click(object sender, EventArgs e)
{
    acsc.Add(\"[001] some kind of item\");
    acsc.Add(\"[002] some other item\");
    acsc.Add(\"[003] an orange\");
    acsc.Add(\"[004] i like pickles\");
}

void textBox1_TextChanged(object sender, System.EventArgs e)
{
    listBox1.Items.Clear();
    if (textBox1.Text.Length == 0)
    {
    hideResults();
    return;
    }

    foreach (String s in textBox1.AutoCompleteCustomSource)
    {
    if (s.Contains(textBox1.Text))
    {
        Console.WriteLine(\"Found text in: \" + s);
        listBox1.Items.Add(s);
        listBox1.Visible = true;
    }
    }
}

void listBox1_SelectedIndexChanged(object sender, System.EventArgs e)
{
    textBox1.Text = listBox1.Items[listBox1.SelectedIndex].ToString();
    hideResults();
}

void listBox1_LostFocus(object sender, System.EventArgs e)
{
    hideResults();
}

void hideResults()
{
    listBox1.Visible = false;
}

There\'s a lot more you could do without too much effort: append text to the text box, capture additional keyboard commands, and so forth.

回答2:

If you decide to use a query that is based on user input make sure you use SqlParameters to avoid SQL Injection attacks

SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = \"SELECT Name from view_customers where Details LIKE \'%\" + @SearchParam + \"%\'\";
sqlCommand.Parameters.AddWithValue(\"@SearchParam\", searchParam);

回答3:

Here\'s an implementation that inherits the ComboBox control class, rather than replacing the whole combo-box with a new control. It displays its own drop-down when you type in the text box, but clicking to show the drop-list is handled as before (i.e. not with this code). As such you get that proper native control and look.

Please use it, modify it and edit the answer if you would like to improve it!

class ComboListMatcher : ComboBox, IMessageFilter
{
    private Control ComboParentForm; // Or use type \"Form\" 
    private ListBox listBoxChild;
    private int IgnoreTextChange;
    private bool MsgFilterActive = false;

    public ComboListMatcher()
    {
        // Set up all the events we need to handle
        TextChanged += ComboListMatcher_TextChanged;
        SelectionChangeCommitted += ComboListMatcher_SelectionChangeCommitted;
        LostFocus += ComboListMatcher_LostFocus;
        MouseDown += ComboListMatcher_MouseDown;
        HandleDestroyed += ComboListMatcher_HandleDestroyed;
    }

    void ComboListMatcher_HandleDestroyed(object sender, EventArgs e)
    {
        if (MsgFilterActive)
            Application.RemoveMessageFilter(this);
    }

    ~ComboListMatcher()
    {
    }

    private void ComboListMatcher_MouseDown(object sender, MouseEventArgs e)
    {
        HideTheList();
    }

    void ComboListMatcher_LostFocus(object sender, EventArgs e)
    {
        if (listBoxChild != null && !listBoxChild.Focused)
            HideTheList();
    }

    void ComboListMatcher_SelectionChangeCommitted(object sender, EventArgs e)
    {
        IgnoreTextChange++;
    }

    void InitListControl()
    {
        if (listBoxChild == null)
        {
            // Find parent - or keep going up until you find the parent form
            ComboParentForm = this.Parent;

            if (ComboParentForm != null)
            {
                // Setup a messaage filter so we can listen to the keyboard
                if (!MsgFilterActive)
                {
                    Application.AddMessageFilter(this);
                    MsgFilterActive = true;
                }

                listBoxChild = listBoxChild = new ListBox();
                listBoxChild.Visible = false;
                listBoxChild.Click += listBox1_Click;
                ComboParentForm.Controls.Add(listBoxChild);
                ComboParentForm.Controls.SetChildIndex(listBoxChild, 0); // Put it at the front
            }
        }
    }


    void ComboListMatcher_TextChanged(object sender, EventArgs e)
    {
        if (IgnoreTextChange > 0)
        {
            IgnoreTextChange = 0;
            return;
        }

        InitListControl();

        if (listBoxChild == null)
            return;

        string SearchText = this.Text;

        listBoxChild.Items.Clear();

        // Don\'t show the list when nothing has been typed
        if (!string.IsNullOrEmpty(SearchText))
        {
            foreach (string Item in this.Items)
            {
                if (Item != null && Item.Contains(SearchText, StringComparison.CurrentCultureIgnoreCase))
                    listBoxChild.Items.Add(Item);
            }
        }

        if (listBoxChild.Items.Count > 0)
        {
            Point PutItHere = new Point(this.Left, this.Bottom);
            Control TheControlToMove = this;

            PutItHere = this.Parent.PointToScreen(PutItHere);

            TheControlToMove = listBoxChild;
            PutItHere = ComboParentForm.PointToClient(PutItHere);

            TheControlToMove.Show();
            TheControlToMove.Left = PutItHere.X;
            TheControlToMove.Top = PutItHere.Y;
            TheControlToMove.Width = this.Width;

            int TotalItemHeight = listBoxChild.ItemHeight * (listBoxChild.Items.Count + 1);
            TheControlToMove.Height = Math.Min(ComboParentForm.ClientSize.Height - TheControlToMove.Top, TotalItemHeight);
        }
        else
            HideTheList();
    }

    /// <summary>
    /// Copy the selection from the list-box into the combo box
    /// </summary>
    private void CopySelection()
    {
        if (listBoxChild.SelectedItem != null)
        {
            this.SelectedItem = listBoxChild.SelectedItem;
            HideTheList();
            this.SelectAll();
        }
    }

    private void listBox1_Click(object sender, EventArgs e)
    {
        var ThisList = sender as ListBox;

        if (ThisList != null)
        {
            // Copy selection to the combo box
            CopySelection();
        }
    }

    private void HideTheList()
    {
        if (listBoxChild != null)
            listBoxChild.Hide();
    }

    public bool PreFilterMessage(ref Message m)
    {
        if (m.Msg == 0x201) // Mouse click: WM_LBUTTONDOWN
        {
            var Pos = new Point((int)(m.LParam.ToInt32() & 0xFFFF), (int)(m.LParam.ToInt32() >> 16));

            var Ctrl = Control.FromHandle(m.HWnd);
            if (Ctrl != null)
            {
                // Convert the point into our parent control\'s coordinates ...
                Pos = ComboParentForm.PointToClient(Ctrl.PointToScreen(Pos));

                // ... because we need to hide the list if user clicks on something other than the list-box
                if (ComboParentForm != null)
                {
                    if (listBoxChild != null &&
                        (Pos.X < listBoxChild.Left || Pos.X > listBoxChild.Right || Pos.Y < listBoxChild.Top || Pos.Y > listBoxChild.Bottom))
                    {
                        this.HideTheList();
                    }
                }
            }
        }
        else if (m.Msg == 0x100) // WM_KEYDOWN
        {
            if (listBoxChild != null && listBoxChild.Visible)
            {
                switch (m.WParam.ToInt32())
                {
                    case 0x1B: // Escape key
                        this.HideTheList();
                        return true;

                    case 0x26: // up key
                    case 0x28: // right key
                        // Change selection
                        int NewIx = listBoxChild.SelectedIndex + ((m.WParam.ToInt32() == 0x26) ? -1 : 1);

                        // Keep the index valid!
                        if (NewIx >= 0 && NewIx < listBoxChild.Items.Count)
                            listBoxChild.SelectedIndex = NewIx;
                        return true;

                    case 0x0D: // return (use the currently selected item)
                        CopySelection();
                        return true;
                }
            }
        }

        return false;
    }
}

回答4:

If you\'re running that query (with {0} being replaced by the string entered), you might need:

SELECT Name from view_customers where Details LIKE \'%{0}%\'

LIKE still needs the % character... And yes, you should use parameters rather than trusting the user\'s input :)

Also, you seem to be returning the Name column, but querying on the Details column. So if someone types in \"John Smith\", if that\'s not in the Details column you won\'t get what you want back.

回答5:

THIS WILL GIVE YOU THE AUTOCOMPLETE BEHAVIOR YOU ARE LOOKING FOR.

The attached example is a complete working form, Just needs your data source, and bound column names.

using System;
using System.Data;
using System.Windows.Forms;

public partial class frmTestAutocomplete : Form
{

    private DataTable maoCompleteList;
    private const string MC_DISPLAY_COL = \"name\";
    private const string MC_ID_COL = \"id\";

    public frmTestAutocomplete()
    {
        InitializeComponent();
    }

    private void frmTestAutocomplete_Load(object sender, EventArgs e)
    {
        using (clsDataAccess oData = new clsDataAccess())
        {

            maoCompleteList = oData.PurificationRuns;
            maoCompleteList.CaseSensitive = false; //turn off case sensitivity for searching

            testCombo.DisplayMember = MC_DISPLAY_COL;
            testCombo.ValueMember = MC_ID_COL; 
            testCombo.DataSource = GetDataTableFromDatabase();
            testCombo.SelectedIndexChanged += testCombo_SelectedIndexChanged;
            testCombo.KeyUp += testCombo_KeyUp; 
        }
    }


    private void testCombo_KeyUp(object sender, KeyEventArgs e)
    {
        //use keyUp event, as text changed traps too many other evengts.

        ComboBox oBox = (ComboBox)sender;
        string sBoxText = oBox.Text;

        DataRow[] oFilteredRows = maoCompleteList.Select(MC_DISPLAY_COL + \" Like \'%\" + sBoxText + \"%\'\");

        DataTable oFilteredDT = oFilteredRows.Length > 0
                                ? oFilteredRows.CopyToDataTable()
                                : maoCompleteList;

        //NOW THAT WE HAVE OUR FILTERED LIST, WE NEED TO RE-BIND IT WIHOUT CHANGING THE TEXT IN THE ComboBox.

        //1).UNREGISTER THE SELECTED EVENT BEFORE RE-BINDING, b/c IT TRIGGERS ON BIND.
        testCombo.SelectedIndexChanged -= testCombo_SelectedIndexChanged; //don\'t select on typing.
        oBox.DataSource = oFilteredDT; //2).rebind to filtered list.
        testCombo.SelectedIndexChanged += testCombo_SelectedIndexChanged;


        //3).show the user the new filtered list.
        oBox.DroppedDown = true; //do this before repainting the text, as it changes the dropdown text.

        //4).binding data source erases text, so now we need to put the user\'s text back,
        oBox.Text = sBoxText;
        oBox.SelectionStart = sBoxText.Length; //5). need to put the user\'s cursor back where it was.


    }

    private void testCombo_SelectedIndexChanged(object sender, EventArgs e)
    {

        ComboBox oBox = (ComboBox)sender;

        if (oBox.SelectedValue != null)
        {
            MessageBox.Show(string.Format(@\"Item #{0} was selected.\", oBox.SelectedValue));
        }
    }
}

//=====================================================================================================
//      code from frmTestAutocomplete.Designer.cs
//=====================================================================================================
partial class frmTestAutocomplete
{
    /// <summary>
    /// Required designer variable.
    /// </summary>
    private System.ComponentModel.IContainer components = null;

    /// <summary>
    /// Clean up any resources being used.
    /// </summary>
    /// <param name=\"disposing\">true if managed resources should be disposed; otherwise, false.</param>
    protected override void Dispose(bool disposing)
    {
        if (disposing && (components != null))
        {
            components.Dispose();
        }
        base.Dispose(disposing);
    }

    #region Windows Form Designer generated code

    /// <summary>
    /// Required method for Designer support - do not modify
    /// the contents of this method with the code editor.
    /// </summary>
    private void InitializeComponent()
    {
        this.testCombo = new System.Windows.Forms.ComboBox();
        this.SuspendLayout();
        // 
        // testCombo
        // 
        this.testCombo.FormattingEnabled = true;
        this.testCombo.Location = new System.Drawing.Point(27, 51);
        this.testCombo.Name = \"testCombo\";
        this.testCombo.Size = new System.Drawing.Size(224, 21);
        this.testCombo.TabIndex = 0;
        // 
        // frmTestAutocomplete
        // 
        this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
        this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
        this.ClientSize = new System.Drawing.Size(292, 273);
        this.Controls.Add(this.testCombo);
        this.Name = \"frmTestAutocomplete\";
        this.Text = \"frmTestAutocomplete\";
        this.Load += new System.EventHandler(this.frmTestAutocomplete_Load);
        this.ResumeLayout(false);

    }

    #endregion

    private System.Windows.Forms.ComboBox testCombo;
}

回答6:

Two methods were successful in the autoComplete textBox control with SQL:

but you should do the following:

a- create new project

b- add Component class to project and delete component1.designer \"according to the name you give to component class\"

c- download \"Download sample - 144.82 KB\" and open it and open AutoCompleteTextbox class from AutoCompleteTextbox.cs
d- select all as illustrated in the image and copy it to current component class

http://i.stack.imgur.com/oSqCa.png

e- Final - run project and stop to view new AutoCompleteTextbox in toolBox.

Now you can add the following two method that you can use SQL with them

1- in Form_Load

private void Form1_Load(object sender, EventArgs e)
  {            
   SqlConnection cn = new SqlConnection(@\"server=.;database=My_dataBase;integrated security=true\");
   SqlDataAdapter da = new SqlDataAdapter(@\"SELECT [MyColumn] FROM [my_table]\", cn);
   DataTable dt = new DataTable();
   da.Fill(dt);

   List<string> myList = new List<string>();
    foreach (DataRow row in dt.Rows)
       {
          myList.Add((string)row[0]);
       }

   autoCompleteTextbox1.AutoCompleteList = myList;
    }  

2- in TextChanged Event

 private void autoCompleteTextbox_TextChanged(object sender, EventArgs e)
        {           
         SqlConnection cn = new SqlConnection(@\"server=.;database=My_dataBase;integrated security=true\");
         SqlDataAdapter da = new SqlDataAdapter(@\"SELECT [MyColumn] FROM [my_table]\", cn);
         DataTable dt = new DataTable();
         da.Fill(dt);

     List<string> myList = new List<string>();
      foreach (DataRow row in dt.Rows)
        {
          myList.Add((string)row[0]);
        }

   autoCompleteTextbox2.AutoCompleteList = myList;

    }