C# - MySQL Query Tool With IntelliSense

Most IDE's provide IntelliSense, which helps developers significantly increase their productivity. It is a feature that has become a part of every day coding for most developers including my self. Most of the SQL editors I use, don't have IntelliSense support when you type a table name. So I decided, I would try and develop a simple query tool for MySQL to try and determine how complicated it was to add basicIntelliSense support.

I started with a blank Window Forms project and added a RichTextBox control to the form. From past experience I knew that this control provides a few methods and properties that I would need. The next step was to determine how to capture a word when the period character was entered. The word I would need to capture was immediately before the period. This got me thinking, do I take the Regular Expression approach or do I try my luck with some string manipulations.

Since I've never been good at regular expressions I though I'd take the string manipulation approach and after some time of thinking I decided on the following approach.

KeyUp Event
The first thing, I needed to do was to add a KeyUp event to the RichTextBox control. This event would be raised when a key was pressed and released giving me the KeyValue for the entered character. I wanted the IntelliSense to become active when the period (.) was entered so a simple control statement was needed.

Next I needed to determine the word that came before the period. The simplest solution I could think of was to get the current position of the period character and iterate backwards through the string until a space character was found. With each iteration I would get the current character in the iteration and append it to a string variable. Let's take a quick look at an example string for a better understanding.

Example String.
SELECT user.

Iterating backward from the point of the period and appending each character to a string variable word until a space was found in the above example would result in the variable word holding the value resu. Since each character was appended backward the word would be in reverse.

With the logic in place I set about writing the code. Listing 1.1 below shows the code that finds a word before a period character.

Listing 1.1

1. if (e.KeyValue == 190)
2. {
3.         RichTextBox rtb = (RichTextBox)sender;
4.         int position = rtb.SelectionStart - 2;
5.         _originalPostion = position;
6.  
7.         string currentChar = Convert.ToString(rtb.Text[position]);
8.         string word = "";
9.  
10.         while (currentChar != " " && position >0)
11.         {
12.                 position--;
13.                 word += currentChar;
14.                 currentChar = Convert.ToString(rtb.Text[position]);
15.         }
16.  
17.         char[] array = word.ToCharArray();
18.         Array.Reverse(array);
19.         word = (new String(array));
20. }

After finding the word, the next step was to position a ListBox next to the word. This was a relatively an easy task, since the RichTextBox control provides a GetPositionFromCharIndex() method that returns a Point object, which holds the X and Y values for a character position. Having previously gotten the position for the period character, I was able to use the mentioned method, to get it's X and Y location relative to the form. A few minor adjustments helped to position the ListBox control, the final result can be seen in the image. Finally, I needed to populate the ListBox control with items.

Since I was trying to provide IntelliSense support for SQL, I wanted to show table field names in the ListBox control. Therefor the word variable would contain the name of a database table. All I had to do was issue an SQL statement to the MySQL server requesting meta data for a table name. MySQL has a simple statement that can be be used to return table field names. Before going any further, I need to point out that the Connector/Net driver needs to be installed. This driver provides the MySql.Data.dll, which is required to establish communication with a MySQL server. You can download this driver from the MySQL website and is relatively easy to find.

SHOW COLUMNS FROM tableName

After issuing the statement above, MySQL will return a dataset with each row containing column meta data. The code below shows how to populate the ListBox control after issuing the SQL statement.

Listing 1.2

1. if (word != "")
2. {
3.     Point p = rtb.GetPositionFromCharIndex(_originalPostion);
4.     _intellisenseBox.Top = p.Y + 22;
5.     _intellisenseBox.Left = p.X + 72;
6.  
7.     try
8.     {
9.         MySqlCommand cmd = new MySqlCommand("SHOW COLUMNS FROM " + word, this._con);
10.  
11.         DataTable dtTable = new DataTable();
12.         MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
13.  
14.         adapter.Fill(dtTable);
15.         _intellisenseBox.DataSource = dtTable;
16.         _intellisenseBox.DisplayMember = "Field";
17.         _intellisenseBox.Height = 100;
18.         _intellisenseBox.Visible = true;
19.     }
20.     catch (MySqlException mySqlEx) { }
21. }

By this point, everything was working fine, however there were still a few things missing from the code. With the ListBox control in place and populated with field names, I needed to add a double click event that would allow me to select a field name from the list and place it at the current cursor position. The code below shows the double click event for the ListBox control.

Listing 1.3

1. private void IntellisenseBoxDoubleClick(object sender, EventArgs e)
2. {
3.     DataRowView row = (DataRowView)_intellisenseBox.SelectedItem;
4.     string field = row["Field"].ToString();
5.  
6.     this.txtQuery.Select(_originalPostion+2, 1);
7.     this.txtQuery.SelectedText = field;
8.     this.txtQuery.Focus();
9.     _intellisenseBox.Visible = false;
10. }

The attached project includes a few more checks that are easy to understand and doesn't require much explanation. Download the source code and test the project for your self. If you have any questions please post a comment. Alternatively you can email me if your shy to post a comment. Download