Insert, Delete, and Update using Stored Procedure in ASP.NET

 A stored procedure is nothing more than a prepared SQL code that you save so you can reuse the code over and over again. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.

“ SP is a pre-define and pre-compiled set of source code.”

Basic Difference between Stored Procedure and Functions

1. The function must return a value, but in Stored Procedure it is optional (procedure can return zero or n values).
   
   
2. Functions can have only input parameters for it, whereas procedures can have input/output parameters.
   
   
3. Functions can be called from a procedure, whereas procedures cannot be called from a function.
   
   
4. The procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE) statements in it whereas the function allows only SELECT statements in it.
   
   
5. The stored procedure is precompiled execution plan whereas functions are not.

Note: Sometimes we face the question, why we can't execute stored procedure inside a function?

Answer:

1. The stored procedure may contain DML statements.
2. Function can't contain DML statements.

So executing a function inside a stored procedure will never break rule 1, but executing a stored procedure inside a function may break rule 2.

So ultimately strict rule is made that we can't execute stored procedures inside the function.

First, we have to create one Table. E.g. Registration

1. CREATE TABLE UserRegistration (  
2. C_Id int IDENTITY(1, 1) NOT NULL,  
3. C_Name varchar(100) NULL,  
4. C_Age varchar(100) NULL,  
5. C_Country varchar(100) NULL  
6. );  

After that we have to create one stored procedure. E.g. SpMyProcedure.

1. Create procedure SpMyProcedure (  
2. @Id int = null,  
3. @Name varchar(100)= null,  
4. @Age varchar(100)= null,  
5. @Country varchar(100)= null,  
6. @Action varchar(100)= null  
7. ) As begin if @Action = 'Insert' Insert into UserRegistration(C_Name, C_Age, C_Country)  
8. values  
9. (@Name, @Age, @Country) if @Action = 'Update'  
10. Update  
11. UserRegistration  
12. set  
13. C_Name = @Name,  
14. C_Age = @Age,  
15. C_Country = @Countrywhere C_Id = @Id if @Action = 'Delete'  
16. Delete from  
17. UserRegistration  
18. where  
19. C_Id = @Id end  

Now we can call stored procedure from our code like the following for Insert operation.

1. protected void btnSave_Click(object sender, EventArgs e)   
2. {  
3.     string str = "server=Your Server Name; Initial Catalog=Your Database Name; User ID=User Id; Password=Your Password";  
4.     SqlConnection cn = new SqlConnection(str);  
5.     SqlCommand cmd = new SqlCommand("SpMyProcedure", cn);  
6.     cmd.CommandType = CommandType.StoredProcedure;  
7.     cmd.Parameters.AddWithValue("@Action", "Insert");  
8.     cmd.Parameters.AddWithValue("@Name", txtName.Text);  
9.     cmd.Parameters.AddWithValue("@Age", txtAge.Text);  
10.     cmd.Parameters.AddWithValue("@Country", txtCountry.Text);  
11.     cn.Open();  
12.     cmd.ExecuteNonQuery();  
13.     cn.Close();  
14. }  

We can call stored procedure from our code like the following for Delete operation.

1. protected void btnDelete_Click(object sender, EventArgs e)   
2. {  
3.     string str = "server=Your Server Name; Initial Catalog=Your Database Name; User ID=User Id; Password=Your Password";  
4.     SqlConnection cn = new SqlConnection(str);  
5.     SqlCommand cmd = new SqlCommand("SpMyProcedure", cn);  
6.     cmd.CommandType = CommandType.StoredProcedure;  
7.     cmd.Parameters.AddWithValue("@Action", "Delete");  
8.     cmd.Parameters.AddWithValue("@Id", txtId.Text);  
9.     cn.Open();  
10.     cmd.ExecuteNonQuery();  
11.     cn.Close();  
12. }  

We can call stored procedure from our code like the following for the Update operation.

1. protected void btnUpdate_Click(object sender, EventArgs e)   
2. {  
3.     string str = "server=Your Server Name; Initial Catalog=Your Database Name; User ID=User Id; Password=Your Password";  
4.     SqlConnection cn = new SqlConnection(str);  
5.     SqlCommand cmd = new SqlCommand("SpMyProcedure", cn);  
6.     cmd.CommandType = CommandType.StoredProcedure;  
7.     cmd.Parameters.AddWithValue("@Action", "Update");  
8.     cmd.Parameters.AddWithValue("@Name", txtName.Text);  
9.     cmd.Parameters.AddWithValue("@Age", txtAge.Text);  
10.     cmd.Parameters.AddWithValue("@Country", txtCountry.Text);  
11.     cmd.Parameters.AddWithValue("@Id", txtId.Text);  
12.     cn.Open();  
13.     cmd.ExecuteNonQuery();  
14.     cn.Close();  
15. }