Automatic stored procedure generation using Visual Studio

I was recently working in classic ASP using a client's in-house content management system. I had worked on this before and recalled the production of the stored procedures to be one of the most laborious parts unless some third-party tools like Code Author were used.

I wondered if there was a way of generating stored procedures using Visual Studio, and sure enough there is....

Under Add new item select Dataset, and name it whatever you want. A new .xsd window will appear. Right click in this and choose Add > TableAdapter. In the next window point to your database.

And then there's the important bit:

Select Create new stored procedures. In the next window insert a 'SELECT * FROM MyTable' statement and click Next. Then you can name the Select, Insert, Update and Delete sprocs. At this point I chose Preview SQL Script which allowed me to cut and paste the generation code into SQL Server Management Studio. You could of course continue with the process and allow VS to do the generation for you.

At the end, delete the .xsd file as you don't need it.