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 pre-define and pre-compiled set of souce code.”

Basic Difference between Stored Procedure and Functions

  1. 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 procedure, whereas procedures cannot be called from function.

  4. Procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE) statement in it whereas function allows only SELECT statement in it.

  5. Stored procedure is precompiled execution plan whereas functions are not.

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

Answer:

  1. Stored Procedure may contain DML statements.
  2. Function can't contain DML statements.

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

So ultimately strict rule is made that we can't execute stored procedures inside 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 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. }  
Here, I had focused on following points:
  • What is a Stored Procedure?
  • How to create a stored procedure.?
  • How to create a SQL Server stored procedure with parameters.?
  • How to Insert, Update, Delete Using Stored Procedure in ASP.NET?