Creating a Visual Studio.NET add-in to write SQL statements

Visual Studio.NET allows you to create an add-in that will write all those tedious SQL statements for you. We'll show you how, step-by-step.

Want to avoid the tedium of cranking out repetitious SqlCommand parameters? Just create an add-in that you can run directly from the Visual Studio.NET IDE to write all of those UPDATE, DELETE, and INSERT statements for you. The great thing about this technique is that the data objects don’t have to be associated with a WinForm.

Because I want to keep the scope of this article narrow, I won't be discussing the code to retrieve the table schema, although it is included in the complete code download. Note that the code generator writes only the INSERT statement and does not take into consideration such things as identity columns, which I will explore in future articles.

Where's that wizard?
Microsoft has provided a wizard to create the skeleton for a Visual Studio add-in. To start the wizard, you create a new project, selecting the Extensibility Projects template located under Other Projects. Type in the name, as shown in Figure A, and click Open.

Figure A
Starting the wizard

Step one in the wizard is to select the language you’ll be using to develop your add-in. For this article, I am using VB.NET. This will enable me to migrate macros into the add-in easily, since macros can be written only in VB.

Step two is to select an application host. I select only Microsoft Visual Studio .NET. The add-in won't be useful in the Macro Editor because you can't write database code in it.

Step three is to provide the name of the add-in and a description. These are displayed in the Add-In Manager and will help the user identify the add-in.

Step four, as shown in Figure B, involves selecting add-in options. Select the first check box to create an item on the Tools menu that will enable the user to access the add-in. Also, check the option to allow all users access to the add-in. No point in keeping the tool to yourself. Don't select the options concerning never showing a modal dialog and the one that deals with loading your add-in when the host loads. Loading your add-in when the host loads will only make development difficult. The object will be loaded in memory by Visual Studio, which won’t allow you to overwrite it with a new compile.

Figure B
UI options

Step five is to define the About Box, which you can skip. And finally, step six provides a summary view of your selections. Double-check them and click Finish if they are correct. Otherwise, use the Back button to change any necessary settings.

The skeleton project
When the wizard completes, you will have two projects. One is your add-in skeleton and the other a Setup project for distributing your add-in. Although I won’t be discussing the Setup project in this article, it will come in handy once the add-in is ready for distribution.

In the add-in project, there will be a Connect.vb class with an OnConnection method, which is called by Visual Studio when the add-in is being called to set up or load. If you look at the code in Figure C, you will see a function that sets the applicationObject variable. This variable is the link to the Automation Object Model for Visual Studio and enables you to control the project and modify the code files. You'll also see a function that creates the menu item that runs your add-in

Figure C
Functions to set applicationObject and create a menu item to run add-in

Putting some meat on the bones
Now it's time to flesh out the add-in and make it do some real work. When the user clicks on the menu item for the add-in, the Exec method will be called; this is where you put your code. Since I like to separate my code out, I prefer to implement it in another class and have that class called from the Exec method, as shown in Figure D. There is a call to set the Data Tool Extension (DTE) property of the CodeEditor class, which I will address later. The other line of interest is the SelectTable.RunForm() routine, a shared method that handles instantiating and displaying the form.

Figure D
Separating code

The shared RunForm method both instantiates and shows the form (Figure E), which is shown nonmodal so that the user can still interact with Visual Studio. If this capacity is not required, you can make the form modal by changing .Show() to .ShowDialog().

Figure E
The form

Retrieving the database schema
The form provides a text box for the user to input the ConnectionString to access the database being developed. It would be ideal if the add-in could find this in the code. But since it will likely be in a configuration file, the user will have to paste it in. The tool could be improved by enabling it to save previous ConnectionStrings for quick reuse or even to provide a navigator so that the user can point and click to the database.

Now, the user clicks on the Connect To Database button, which makes use of a DataModel class. This class specifies the desired database and retrieves the table and column schema for that database. I will not address the details of this operation except to say that it makes use of the Information Schema views provided by Microsoft SQL Server. Currently, the DataModel class supports only SQL Server and table definitions.

Future articles will explore the details of making a more robust DataModel class that can work with other object types, such as stored procedures and even with other database vendors.

The Tables drop-down list is populated from the table list retrieved via the DataModel class. The user can select a table that will be auto-coded. Clicking the Insert Text At Cursor button calls the DataModel class to get the associated columns for the table and passes that to the CodeGenerator class. The CodeGenerator then uses the schema definition to generate the INSERT command and related parameters code lines.

Generating the code
The CodeGenerator class encompasses the logic to turn the schema definition into working code. Public properties set the table name that is being generated, the name of the SqlCommand variable to be used in the generated code, and the DataTable with the column schema definition. The only public method is GenerateInsertCode(), which generates the INSERT SQL statement with parameters, as well as every Parameters.Add() command associated with the INSERT statement. The GenerateInsertCode method uses other private methods in the class to generate the individual components of the code. There is a method to create the INSERT SQL statement, the Parameters.Add commands, and two commands to write out the SqlDataType and parameter size.

Inserting into the editor
Looking back at the code for the SelectTable form, you can see that the Insert Text button called the CodeGenerator and then passed the output to the CodeEditor using the InsertTextAtCursor method. This is a simple way to write the generated code into the project. It begins by defining an EditPoint that is used to insert text into the editor. Getting the appropriate EditPoint can be accomplished a few ways. I have employed the ActivePoint that gets the current location of the cursor in the ActiveDocument. Start with the DTE that is the root object for Automation of Visual Studio. Remember that the DTE was passed to the class from the Exec method in Connect.vb. From there, a simple chain of calls from ActiveDocument, Selection, ActivePoint gives you the location of the cursor. A final call to CreateEditPoint returns an EditPoint to use for inserting our text.

As noted, the InsertTextAtCursor method inserts the code where the cursor is located. Be aware, though, that this method might not work for you if you want to generate many lines of code and sprinkle them about the project. That task would require use of the CodeModel and CodeElements in the Automation Object Model. Another downside of the Automation Object Model is that it does not currently operate on VB.NET projects, so the project being generated must be developed in C#.

Using the add-in
Now that your add-in is complete, you need to test it. I suggest debugging your project with the debugger. You will see that it launches another instance of Visual Studio, and you can then use the Add-In Manager to start your add-in. You will need to do this particularly if you don’t see the menu item for your add-in under Tools. When using the Add-In Manager, don’t set it for startup, since this will cause your compiled code to be loaded the next time you start Visual Studio, thereby prohibiting your ability to recompile.

If you make changes to the code to add more menu commands, you will need to reset the registry so that your add-in is called again to set up the user interface. The add-in wizard will have created a .reg file in your project that you can use to handle this registry change.

Taking the add-in a step further
Although I like to think everything I create sparkles like a diamond, I have to admit that this one is a diamond in the rough. Here are a few of the ways the add-in could be improved.

First, ConnectionString should be remembered between invocations, providing multiple values from which the user can pick. Second, it should be determined what the SqlCommand and SqlConnection variables are named, either by capturing the information from the user or finding it in the code. Finally, since changes in code occur as a matter of course, it would be beneficial if the add-in could locate the previously generated INSERT and SqlParameters code and replace it with updated code based on the new design of the database.

Want to see more VB.NET tricks? Post a comment telling us what you want to see or e-mail us.


Editor's Picks