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

Durgaprasad Yadav

11 Comments

Join the conversation! Your thoughts help the community grow.

Lokesh Nagoji5y ago
Hello bro how to use grideview in sp
Ketan Shah6y ago
Maximum stored procedure,Function, trigger or view nesting level exceeded( limit 32
Umesh Daiya6y ago
Before insert check C_Name already exists if exists show message duplicate else insert using store procedure
सुजाता चौहान7y ago
How to retrieve data form database using store procedure?
Ajay Krish7y ago
For Delete procedure it shows error as we need to pass values for other parameters also.. So i have passed empty parameters and it has executed successfully.. but i need to know whether any other solution available without passing empty parameters.. Plz any one reply!! Thanks in Advance : )
asha pawar8y ago
Without store procedure how we can perform crud operations in connected and disconnected architecture. when id is autoincremented and update delete data by using these id??/
Amit Lele9y ago
Thanks it's very helpful for beginners :)
Vikas Gupta9y ago
A page can have only one server-side Form tag.
Teddy Kurian11y ago
Good start Elmrabah Abdelillah :)
Elmrabah Abdelillah11y ago
thks
Debendra Dash11y ago
good start......

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

Function must return a value, but in Stored Procedure it is optional (procedure can return zero or n values).
Functions can have only input parameters for it, whereas procedures can have input/output parameters.
Functions can be called from procedure, whereas procedures cannot be called from function.
Procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE) statement in it whereas function allows only SELECT statement in it.
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:

Stored Procedure may contain DML statements.
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?