Automatically Generate Stored Procedures with Visual Studio

This is one of those tucked-away features in Visual Studio that, once you find it, can make you slap your forehead so hard that it hurts.
Warning: You may need an aspirin after reading this.

Notes

I'm using Microsoft Visual Studio Team System 2008. It also works with the Express editions (thanks to @bhitalks for checking this). 

I'm also using SQL Server Express 2005. To get this to work on my system, I had to download and install: SQLSysClrTypes.msi.

Step 1

Right mouse-click the App_Code folder in the application and select "Add New Item..." When the "Add New Item" window appears, select DataSet. It doesn't matter what you name the DataSet but remember it for later.

Step 2

Right mouse click in the XSD window and select "Add, TableAdapter…"

Step 3

After a few seconds, the "Choose Your Data Connection" window should appear. Choose, or create, a connection and click "Next".

Step 4

Here's the magic. In the "Choose a Command Type" window, choose "Create new stored procedures" and click "Next."

Step 5

Enter a SQL select statement for a single table and click "Next."

Step 6

The "Create the Stored Procedures" window appears. Rename the stored procedures to something meaningful and click "Next", or go ahead and click "Finish." Since I selected the Employees table, I renamed the procedures with the Employee prefix. You can click Preview SQL Script to see what is going to be run:

Step 7

You should see the Wizard Results window… click "Finish" one more time and the Stored Procedures will be created.

Step 8

Delete the .XSD file from the App_Code directory that was created in Step 1. It's not needed.

You Are Done

Here's how the generated Stored Procedures look in SQL Server Management Studio:

The generated SQL code is very clean. Of course, you can modify it to fit your requirements.

Now, ain't that better than typing them by hand?

I hope someone finds this useful.

Steve Wellens