How to Generate Stored Procedure Automatically From Visual Studio

In this tutorial, I would explain to you how to generate stored automatically from Visual Studio without having to write codes.

Let’s assume you have created the customer’s table in Microsoft SQL Server Managment Studio. This is shown in Figure 1.

Figure 1: Customer Table Definition in MS SQL Server

You already know that it would not be easy to write the four stored procedure by writing it manually. So we are going to generate the following stored procedures:

SelectCustomer
UpdtateCustomer
DeleteCustomer
InsertCustomer

Now let’s go to Visual Studio and start a new project. I have already done that. I am using Visual Studio 2015, but it woud also work with Visual Studio 2013 and 2017.

Right-click on your project and click on you project in Solution Explore
Click on Add New Item
In the dialog box, Select Data on the left and Select DataSet from the list of items. This is shown in Figure 2

Figure 2: Add a DataSet to the Project

Give it a name and Click on Add ( i named it CustomerData). The DataSet designer opens as shown in Figure 4.

Right-click and select Add, the Select Table Adapter

In the Database Connection window, Create a New database connection (or you can select from existing on if you have previously created one.

Click on Next. Click on Next again

Choose Create new Stored Procedure and Click Next

In this window, you can either enter the statement (Select * From Customers) or you can use the Query Builder to select the table

If you click on Query Builder, you can select the Customers table and then click on the * for select all columns

After you close the Query Builder, you should have your Select Statement written out for you.

Click on Next
The next window gives you option to name your stored procedures

About Naming Stored Procedures

Use SelectCustomers, InsertCustomer, UpdateCustomer and DeleteCustomer. Note that the select statement uses the plural for the tablename while others uses singular
This is because, the select statements selects multiple records at a time which other procedures executes on a single record at a time.

Click on Next

Make sure to uncheck the last checkbox that says: ‘Create methods to send updates directly to the database’

Click on Next to generate the stored procedures

If you have gotten to this point, then all the four store procedure has been generated in the SQL database.
Click on Finish to go back to the Dataset designer window

Now, let’s head back to SQL Server Managment Studio to check is the Stored Procudures have been generated.
If you expand the databases > Programability > Stored Procedures, you will see that the four procedures have been created successfully.