By fernando on Jan 03, 2012
Intellisense Support in Visual Studio
One of the cool features coming in Connector/NET 6.5 is the support in Visual Studio 2010 of Intellisense for MySql files.
As you probably know, the capability of editing mysql files (in the way of both .mysql raw files included in the solution and stored procedures/functions from Server Explorer) has been around for a while, yet a missing piece of the puzzle was to have advanced completion of sql statements in those files… until now.
Let’s explore the set of features currently implemented as part of Intellisense support.
Note: For completion intellisense features to work a database connection must be properly configured, see images below
|For Server Explorer items, the connection is the root of the elements.|
|For .mysql files, the connection is individually configured using the Connect to MySql toolbar button.|
Also to trigger the completion features you just either use the standard Visual Studio shortcut “Control+J” or start typing the name of an object. Both ways to trigger intellisense work in all syntactical contexts.
a) Table Completion
Table completion means showing the list of tables for the current connection database in the proper syntactical context. Basically this means any part of a sql sentence where the parser expects a table. Some examples of this feature follow:
|After the “from” clause in a select, you can press Control+J and get a list of all the tables from the current connection database.|
|After selecting one of the options, the text appears wrapped in quotes (to cover the case when the table name has whitespaces or is a mysql reserved word).|
|Or you can start typing the name of a table, and the list shows only those items that match.|
|Another example, after an inner join.|
|Another example, in an update statement. Also notice that you can have several sentences on the file, but the previous sentences must be correct syntactically (included being finished with a semicolon).|
|Another example, on the insert statement.|
|As expected, combinations like insert into select, also work.|
|Table completion for a delete statement.|
|For show create table statements.|
|For truncate statements.|
And of course there are many others.
b) Column Completion
Column completion means showing the list of columns for the current connection database table or tables in the proper syntactical context. Basically this means any part of a sql sentence where the parser expects a column. Some examples of this feature follow:
|After a select without from clause you can get, by Control+J, the list of columns for all tables in the current database.|
|If you type c, the lists shown filters out items whose name doesn’t start with the characters typed.|
|Things get more interesting when there is a context restricting the tables to use, like here, after the “*, “.|
|What if you add the same table several times with different alias? Then the list of columns is prefixed with the alias name, not the table name. Also works in where clauses.|
|“Set” clause in Update statements.|
|“Order by” clauses.|
|“On” clause in a left join select statement.|
|Column completion on insert into statements.|
And other cases not showed here.
If you want to see Intellisense within a stored procedure definition, the rest of the stored procedure (excluding the sentence currently being edited) must be syntactically correct.
c) Stored procedure name Completion
Stored procedure name completion means showing the list of stored procedures in the proper syntactical context.
|For the call statement, you get the list of stored procedures; notice that when you highlight one, its signature appears as a tooltip.|
For those of you interested in compiler technology, behind the scenes these intellisense features are powered by an ANTLR generated parser, this parser will also be the foundation for other interesting features…
Anyway, let us know what you think, if there are other things you want to see on Intellisense support or in the Connector/NET in general. (Or you can open a bug / feature request at http://bugs.mysql.com, Connector/NET category).