使用 SQL Server 数据库中的数据在文本框中自动完成 [英] Auto-Complete in textbox using data from a SQL Server database

问题描述

当用户输入文本框来编写查询时，我试图让我的文本框自动完成.这将类似于 SQL Server Management Studio 的做法，并在您键入时提供向下箭头或单击表名或列名的选项.这是我的以下代码.

I am trying to get my textbox to auto-complete when a user types in it to write a query. This would be similar to how SQL Server Management Studio does it and gives the option as you type to arrow down or click on a table name or column name. Here is the following code I have.

public void loadData()
{
    var myConnection = new SqlConnection(DBConnectionBox.Text);
    myConnection.Open();

    AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection();
    string query = @"Select distinct [name] from [INFORMATION_SCHEMA.TABLES]";

    SqlCommand cmd = new SqlCommand(query, myConnection);

    SqlDataReader dr = cmd.ExecuteReader();

    if (dr.HasRows == true)
    {
        while (dr.Read())
            namesCollection.Add(dr["name"].ToString());
    }

    dr.Close();
    myConnection.Close();

    ManualQueryBox.AutoCompleteMode = AutoCompleteMode.Append;
    ManualQueryBox.AutoCompleteSource = AutoCompleteSource.CustomSource;
    ManualQueryBox.AutoCompleteCustomSource = namesCollection;
}

private void ManualQueryBox_KeyUp(object sender, KeyEventArgs e)
{
    loadData();
}

这是我用来抓取文本框中内容并执行它的代码.

This is the code I use to grab what is in the textbox and execute it.

private void ExecuteBtn_Click(object sender, EventArgs e)
{
    this.ClientInfoDGV.DataSource = null;
    this.ClientInfoDGV.Rows.Clear();

    var myConnection = new SqlConnection(DBConnectionBox.Text);

    var ManualCmd = new SqlCommand(ManualQueryBox.Text);
    ManualCmd.Connection = myConnection;
    ManualCmd.CommandType = CommandType.Text;

    SqlDataAdapter SqlAdap = new SqlDataAdapter(ManualCmd);
    DataTable MQRecord = new DataTable();
    SqlAdap.Fill(MQRecord);

    ClientInfoDGV.DataSource = MQRecord;
}

我以前从未做过自动填充，但环顾四周，我看到了一些 asp.net AJAX 控件工具包，但我不完全确定这一切是如何工作的.欢迎任何帮助.

I have never done autofill before but looking around I have see some asp.net AJAX control toolkit, but I am not entirely sure how that all works. Any help is welcome.

更新自动填充代码

public void loadData()
        {
            var myConnection = new SqlConnection(DBConnectionBox.Text);
            myConnection.Open();
            AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection();
            string query = @"Select distinct [Id] from [Clients]";
            SqlCommand cmd = new SqlCommand(query, myConnection);
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows == true)
            {
                while (dr.Read())
                    namesCollection.Add(dr["id"].ToString());

            }

            dr.Close();
            myConnection.Close();

            ManualQueryBox.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            ManualQueryBox.AutoCompleteSource = AutoCompleteSource.CustomSource;
            ManualQueryBox.AutoCompleteCustomSource = namesCollection;
        }

推荐答案

尝试将自动完成模式更改为建议附加:

Try changing autocomplete mode to suggestappend:

ManualQueryBox.AutoCompleteMode=AutoCompleteMode.SuggestAppend;

我认为你不需要每次都在key up事件中加载数据，只要加载一次(可能在表单加载中)并指定文本框的来源即可.

I don't think you need to load the data every time in the key up event as long as you load it once (perhaps in form load) and specify the source of the text box.

这可能有帮助:https://msdn.microsoft.com/en-us/library/system.windows.forms.textbox.autocompletemode(v=vs.110).aspx

这篇关于使用 SQL Server 数据库中的数据在文本框中自动完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！