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

CREATE TABLE UserRegistration (
C_Id int IDENTITY(1, 1) NOT NULL,
C_Name varchar(100) NULL,
C_Age varchar(100) NULL,
C_Country varchar(100) NULL
);

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

Create procedure SpMyProcedure (
@Id int = null,
@Name varchar(100)= null,
@Age varchar(100)= null,
@Country varchar(100)= null,
@Action varchar(100)= null
) As begin if @Action = 'Insert' Insert into UserRegistration(C_Name, C_Age, C_Country)
values
(@Name, @Age, @Country) if @Action = 'Update'
Update
UserRegistration
set
C_Name = @Name,
C_Age = @Age,
C_Country = @Countrywhere C_Id = @Id if @Action = 'Delete'
Delete from
UserRegistration
where
C_Id = @Id end

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

protected void btnSave_Click(object sender, EventArgs e)
{
    string str = "server=Your Server Name; Initial Catalog=Your Database Name; User ID=User Id; Password=Your Password";
    SqlConnection cn = new SqlConnection(str);
    SqlCommand cmd = new SqlCommand("SpMyProcedure", cn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Action", "Insert");
    cmd.Parameters.AddWithValue("@Name", txtName.Text);
    cmd.Parameters.AddWithValue("@Age", txtAge.Text);
    cmd.Parameters.AddWithValue("@Country", txtCountry.Text);
    cn.Open();
    cmd.ExecuteNonQuery();
    cn.Close();
}

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

protected void btnDelete_Click(object sender, EventArgs e)
{
    string str = "server=Your Server Name; Initial Catalog=Your Database Name; User ID=User Id; Password=Your Password";
    SqlConnection cn = new SqlConnection(str);
    SqlCommand cmd = new SqlCommand("SpMyProcedure", cn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Action", "Delete");
    cmd.Parameters.AddWithValue("@Id", txtId.Text);
    cn.Open();
    cmd.ExecuteNonQuery();
    cn.Close();
}

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

protected void btnUpdate_Click(object sender, EventArgs e)
{
    string str = "server=Your Server Name; Initial Catalog=Your Database Name; User ID=User Id; Password=Your Password";
    SqlConnection cn = new SqlConnection(str);
    SqlCommand cmd = new SqlCommand("SpMyProcedure", cn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Action", "Update");
    cmd.Parameters.AddWithValue("@Name", txtName.Text);
    cmd.Parameters.AddWithValue("@Age", txtAge.Text);
    cmd.Parameters.AddWithValue("@Country", txtCountry.Text);
    cmd.Parameters.AddWithValue("@Id", txtId.Text);
    cn.Open();
    cmd.ExecuteNonQuery();
    cn.Close();
}

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?