![](\"https:\/\/2.bp.blogspot.com\/-NxRaCXItink\/Wz-BpGwYCQI\/AAAAAAAACAc\/dzXJ2i40XtsV_yPsp80r6m7czvUAeXHdwCLcBGAs\/s1600\/How-to-Generate-Stored-Procedure-From-Visual.jpg\")
\u00a0<\/a><\/div>\n<\/p>\n
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:<\/p>\n
- \n
- SelectCustomer<\/li>\n
- UpdtateCustomer<\/li>\n
- DeleteCustomer<\/li>\n
- InsertCustomer<\/li>\n<\/ul>\n
<\/p>\n
Now\u00a0 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.<\/p>\n
- \n
- Right-click on your project and click on you project in Solution Explore<\/li>\n
- Click on Add New Item<\/li>\n
- In the dialog box, Select Data on the left and Select DataSet from the list of items. This is shown in Figure 2<\/li>\n<\/ol>\n
<\/p>\n
![](\"https:\/\/2.bp.blogspot.com\/-Fct0q3aMiF8\/Wz-HZkNCFYI\/AAAAAAAACAo\/YyU3KQKEpwMkAu6qL2OE_dDxLtBBJOoGQCLcBGAs\/s640\/Choose%2BDataset.jpg\")
\u00a0<\/a><\/div>\nFigure 2: Add a DataSet to the Project<\/div>\n<\/p>\n
Give it a name and Click on Add ( i named it CustomerData). The DataSet designer opens as shown in Figure 4.<\/p>\n
<\/p>\n
![](\"https:\/\/4.bp.blogspot.com\/-SUsU4yV3NlE\/Wz-LL1HZ8LI\/AAAAAAAACA0\/HbuI-3QjTm8yCFxObJbXcGIDntfF1Qf0wCLcBGAs\/s640\/Dataset-Designer.jpg\")
<\/a><\/div>\n<\/p>\n
Right-click and select Add, the Select Table Adapter<\/p>\n
<\/p>\n
![](\"https:\/\/4.bp.blogspot.com\/-n8ffJYzaazg\/Wz-LjmRkyZI\/AAAAAAAACA8\/gg9s6z-7GcY7lTG9zgobsfVnGOxiJEeuQCLcBGAs\/s640\/Add%2BTable%2BAdapter.jpg\")
<\/a><\/div>\n<\/p>\n
In the Database Connection window, Create a New database connection (or you can select from existing on if you have previously created one.<\/p>\n
<\/p>\n
![](\"https:\/\/4.bp.blogspot.com\/-kVTGdKcbVWs\/Wz-NB3TtEZI\/AAAAAAAACBI\/eQS5SeeLqrAqKfIKpj0-5_NYYxZyv8hqgCLcBGAs\/s640\/Database%2BConnection.jpg\")
<\/a><\/div>\n<\/p>\n
Click on Next. Click on Next again<\/p>\n
<\/p>\n
![](\"https:\/\/3.bp.blogspot.com\/-k-NmXOlp6CU\/Wz-PbtCSZdI\/AAAAAAAACBU\/r5P5JsmaCWkrgzw1oh9gQ1TFktuSjxdCgCLcBGAs\/s640\/Choose%2BCreate%2BNew%2BStored%2BProcedure.jpg\")
<\/a><\/div>\n<\/p>\n
Choose Create new Stored Procedure and Click Next<\/p>\n
<\/p>\n
![](\"https:\/\/4.bp.blogspot.com\/-eF9bUgbABAw\/Wz-Px08OmqI\/AAAAAAAACBc\/xGFgZ9AQeEMvLnwPQGalBJo1Uhv0o4oNwCLcBGAs\/s640\/Enter%2Bthe%2BSelect%2BStatement.jpg\")
<\/a><\/div>\n<\/p>\n
In this window, you can either enter the statement (Select * From Customers) or you can use the Query Builder to select the table<\/p>\n
<\/ins>
\nIf you click on Query Builder, you can select the Customers table and then click on the * for select all columns<\/p>\n<\/p>\n
![](\"https:\/\/1.bp.blogspot.com\/-4R1qfl5Cu0Y\/Wz-QhNyWnlI\/AAAAAAAACBo\/ZfsZUGXmtvQJE0H7pEVIIxE_-ZhQzDoKACLcBGAs\/s640\/Use%2BQuery%2BBuilder%2Bto%2BSelect%2Ba%2BTable.jpg\")
<\/a><\/div>\n<\/p>\n
After you close the Query Builder, you should have your Select Statement written out for you.<\/p>\n
<\/p>\n
![](\"https:\/\/3.bp.blogspot.com\/-HraBHSUVL6A\/Wz-RO6Lr1VI\/AAAAAAAACBw\/KanQZceXB0M-2GKUwd5bwLitMaawdPBWACLcBGAs\/s640\/Select%2BStatement%2BWritten%2Bfor%2BYou.jpg\")
<\/a><\/div>\n<\/p>\n
Click on Next
\nThe next window gives you\u00a0 option to name your stored procedures<\/p>\n<\/p>\n
![](\"https:\/\/1.bp.blogspot.com\/-2AcfMmDw2n4\/Wz-Rpo7kK3I\/AAAAAAAACB4\/s3xctdTfdOoB_BbruaQvzwixwDiAoAVzQCLcBGAs\/s640\/You%2Bcan%2BEnter%2Bthe%2BNames%2Bof%2Bthe%2BStored%2BProcedure.jpg\")
<\/a><\/div>\n<\/p>\n
<\/p>\n
About Naming Stored Procedures<\/b><\/p>\n
Use SelectCustomers, InsertCustomer, UpdateCustomer and DeleteCustomer. Note that the select statement uses the plural for the tablename while others uses singular
\nThis is because, the select statements selects multiple records at a time which other procedures executes on a single record at a time.<\/p>\n<\/p>\n
![](\"https:\/\/3.bp.blogspot.com\/-IpVD-p3g7Mk\/Wz-SV1Nj5eI\/AAAAAAAACCE\/_YrIEll09iAe3xra4dK0EG3c30aez9XWQCLcBGAs\/s640\/Stored%2BProcedure%2BNamed.jpg\")
<\/a><\/div>\n<\/p>\n
Click on Next<\/p>\n
<\/p>\n
![](\"https:\/\/3.bp.blogspot.com\/-6U4FtA-kqkw\/Wz-SsMVBXBI\/AAAAAAAACCM\/jyNktpyV8ZoidrfKVgVQX4J1qUN16lqegCLcBGAs\/s640\/Uncheck%2Bthe%2Blast%2BCheckbox.jpg\")
<\/a><\/div>\n<\/p>\n
Make sure to uncheck the last checkbox that says: ‘Create methods to send updates directly to the database’<\/p>\n
Click on Next to generate the stored procedures<\/p>\n
<\/p>\n
![](\"https:\/\/1.bp.blogspot.com\/-aFmyykNPk-w\/Wz-TDrnCWXI\/AAAAAAAACCU\/4cnLMgD9VVsZsMqM_fHc6pnGYcY5DE7bACLcBGAs\/s640\/Click%2Bon%2BFinis.jpg\")
<\/a><\/div>\n<\/p>\n
If you have gotten to this point, then all the four store procedure has been generated in the SQL database.
\nClick on Finish to go back to the Dataset designer window<\/p>\n<\/p>\n