C# 自动完成

C# AutoComplete

提问by corymathews

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

[001] 最后，第一中间

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]... 才能显示条目。所以问题是，即使我先输入 firstname ，我也希望它完成。所以如果一个条目是

[001] Smith, John D

[001] 史密斯，约翰 D

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

如果我开始输入 John，那么这个条目应该会出现在自动完成的结果中。

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.

该查询是使用 like 语句的简单搜索查询。如果我们不使用自动完成功能而只是将结果扔到一个数组中，则会返回正确的结果。

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.

{0} 是搜索字符串的占位符。

采纳答案by Steven Richards

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 OnTextChangedevent. That's probably your best bet.

有些人通过覆盖OnTextChanged事件实现了自己的自动完成功能。这可能是你最好的选择。

For example, you can add a ListBoxjust below the TextBoxand set its default visibility to false. Then you can use the OnTextChangedevent of the TextBoxand the SelectedIndexChangedevent of the ListBoxto display and select items.

例如，您可以在ListBox正下方添加一个TextBox并将其默认可见性设置为 false。然后就可以使用 的OnTextChanged事件TextBox和 的SelectedIndexChanged事件ListBox来显示和选择项目。

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.

您可以轻松完成更多操作：将文本附加到文本框、捕获额外的键盘命令等等。

回答by Damovisa

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

如果您正在运行该查询（{0}被输入的字符串替换），您可能需要：

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

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

LIKE仍然需要%角色......是的，你应该使用参数而不是相信用户的输入:)

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

此外，您似乎正在返回该Name列，但在该Details列上进行查询。因此，如果有人输入“John Smith”，如果不在Details列中，您将无法得到想要的东西。

回答by Jim Scott

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

如果您决定使用基于用户输入的查询，请确保使用 SqlParameters 以避免 SQL 注入攻击

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

回答by noelicus

Here's an implementation that inherits the ComboBoxcontrol 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.

这是一个继承ComboBox控件类的实现，而不是用新控件替换整个组合框。当您在文本框中键入时，它会显示自己的下拉列表，但单击以显示下拉列表的处理方式与以前一样（即不使用此代码）。因此，您可以获得适当的本机控制和外观。

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;
    }
}

回答by Sherif Hamdy

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

两种方法在使用 SQL 的 autoComplete textBox 控件中成功：

but you should do the following:

但您应该执行以下操作：

a- create new project

a- 创建新项目

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

b-将组件类添加到项目并删除co​​mponent1.designer“根据您给组件类的名称”

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

c- 下载“下载示例 - 144.82 KB”并打开它并从 AutoCompleteTextbox.cs 打开 AutoCompleteTextbox 类
d- 如图所示全选并将其复制到当前组件类

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

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

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

e- Final - 运行项目并停止以查看工具箱中的新 AutoCompleteTextbox。

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

现在您可以添加以下两个方法，您可以将 SQL 与它们一起使用

1- in Form_Load

1- 在 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

2- 在 TextChanged 事件中

 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;

    }

回答by mike

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; //the data table from your data source
    private string msDisplayCol = "name"; //displayed text
    private string msIDcol = "id"; //ID or primary key

    public frmTestAutocomplete(DataTable aoCompleteList, string sDisplayCol, string sIDcol)
    {
        InitializeComponent();

        maoCompleteList = aoCompleteList
        maoCompleteList.CaseSensitive = false; //turn off case sensitivity for searching
        msDisplayCol = sDisplayCol;
        msIDcol = sIDcol;
    }

    private void frmTestAutocomplete_Load(object sender, EventArgs e)
    {

            testCombo.DisplayMember = msDisplayCol;
            testCombo.ValueMember = msIDcol; 
            testCombo.DataSource = maoCompleteList;
            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;
}