Hi, In this post I would like to share details on how to generate Stored Procedures for performing CRUD operations. I’ll be using visual studio for this accomplishing this task.
Step1:
Right click on the project name in Visual studio. Add a new item. Now under visual c# —> Data —>Dataset and then add the file.
Step2:
Right click on the xsd window and click on Add —> Table Adapter
Step 3:
Now choose data connection window appears and if we already have a connection string defined in web.config, It appears in the drop down. If not, click on Add New Connection and click next.
Step 4:
Now choose comand type window appears and there select **Create New Stored Procedures ** and click on next
Step 5:
Now in the new window, please provide your sql statement for a single table.
Note: We cannot use joins or multiple select statements here for generating code for Crud operations on multiple tables
Step 6:
Now in the **Create Stored Procedure ** window, please provide name for the proc we want to create.
Step 7:
Click on the Preview SQL Script.
Note: If we dont want to create these procedures in sql server, you can simply copy the content and then cancel from here
Step 8:
It generates a big sql file with all crud operations on that table.
If you are only interested on the queries, just copy queries that you need and execute them in Sql server.
Note we need to customize this procedure code, If we want any enhancements.
Step 9:
Click on finish and then go back to Sql server and under stored procedures, we can our procedures created
Also we can delete xsd file which we added
But this really helped me in generating multiple procedure quickly instead of manually typing each param.
Thanks,
Pavan Kumar Aryasomayajulu
Keywords: sql, sql server, stored procedures, Generating Sql Procedures for Crud, Visual Studio