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.
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:
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
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.
So this is how to generate all your stored procdures from Visual Studio withou writing a single line of code! Isn’t ist amazing!
Do leave a comment if you have any challenges following this tutorial