Using Full-text Search in a C# Windows Forms program

This example will will use the same “msnotes” database as before and combine the earlier Full-text search example with DataGridView example to create a Windows Form program which implements free form text search of a database, displaying the results in a DataGridView. The verbal style is rather terse as it is building on the earlier examples and I am mostly writing this blog as a reminder/learning tool for myself (though if anyone else finds it useful all the better :-)).

 

Design the Form

Create a new Windows Forms project and In the Visual Studio form designer drag a DataGridView, a TextBox and 2 Buttons into the form.

Modify the names and Text properties of the form and buttons to indicate the purpose of the program and that one button will be used for executing a search and one for exiting the program. Optionally set the TextBox Text property to something like “Enter search term”.

Include the SqlClient Namespace

using System.Data.SqlClient;

Add Event code for the buttons

A similar LoadData function to the previous DataGridView example will be developed, but this time the contents of textBox1 will be passed to it:

private void searchButton_Click(object sender, EventArgs e)
{
    // Pass the query from textBox1 to the data load function
    LoadData(textBox1.Text);
}

private void closeButton_Click(object sender, EventArgs e)
{
    Close();
}

Set the Focus and add a Key Handler

- When the form loads add a key press handler which will be needed to check when the Enter key is pressed.
- To make the initial “Enter search term” text to be highlighted when the form loads, set the active control to textBox1.

private void Form1_Load(object sender, EventArgs e)
{
    // add a key press handler in order to check for Enter key being pressed
    textBox1.KeyPress += new System.Windows.Forms.KeyPressEventHandler(CheckKeys);

    // Setting ActiveControl ensures the search text is highlighted
    ActiveControl = textBox1;
}

private void textBox1_TextChanged(object sender, EventArgs e)
{
}

private void CheckKeys(object sender, System.Windows.Forms.KeyPressEventArgs e)
{
    // handle the Enter key in the text box
    if (e.KeyChar == (char)13)
    {
       // Pressing Enter is the same as pressing search button
       searchButton_Click(sender, e);
       e.Handled = true;
    }
}

Add the Data Load function

The LoadData function takes the free form query from the text box and uses it to construct a full-text search query. In a production application the query string should be validated and/or parameters should be used to avoid the possibility of SQL injection attacks.

private void LoadData(string query)
{
    string sqlQueryStr = "SELECT category Category, note_data Note FROM categories, notes " +
           "WHERE notes.note_cat = categories.catidx " +
           "AND CONTAINS(note_data,’\"" + query + "\"’)";

    SqlConnection conn = new SqlConnection();
    try
    {
        // construct a connection string to the database
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.DataSource = ".\\MYSERVER";
        builder.InitialCatalog = "msnotes";
        builder.IntegratedSecurity = true;
        conn.ConnectionString = builder.ConnectionString;

        // Connect
        conn.Open();

        using (SqlDataAdapter myAdapter = new SqlDataAdapter(sqlQueryStr, conn))
        {
            // Use DataAdapter to fill DataTable
            DataTable myTable = new DataTable();
            myAdapter.Fill(myTable);

            // Render data onto the screen
            dataGridView1.DataSource = myTable;
        }
    }
    catch (SqlException e)
    {
        MessageBox.Show("Error accessing database: {0}", e.Message);
    }
    finally
    {
        conn.Close();
    }
}

About these ads
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s