Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Automatically Generate Stored Procedures with Visual Studio

, 7 Jan 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Steve Wellens
EndWell Software, Inc.
United States United States
I am an independent contractor/consultant working in the Twin Cities area in Minnesota. I work in .Net, Asp.Net, C#, C++, XML, SQL, Windows Forms, HTML, CSS, etc., etc., etc.

Comments and Discussions

 
QuestionCreate Stored Proc using visual studio PinmemberParag Wankhade4-May-14 20:12 
QuestionProbably you would require more aspirins... Pinmemberdews turner28-Jul-13 18:05 
AnswerRe: Probably you would require more aspirins... PinmemberSteve Wellens28-Jul-13 19:06 
GeneralNice and Easy steps PinmemberSohel_Rana11-Jan-10 22:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 7 Jan 2010
Article Copyright 2009 by Steve Wellens
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid