使用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;

我认为您不必每次在按键事件中都加载数据，只要您加载一次(也许以表单加载方式)并指定文本框的来源即可.

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.

这可能会有所帮助: 查看全文