Working with T-SQL Stored Procedures using ADO.NET - How to execute a simple stored procedure using ADO.NET from ASP.NET
(Page 2 of 4 )
Before going into the ADO.NET code, we need to create a simple stored procedure in SQL Server. Using the "Query Analyzer", execute the following script in the "Northwind" database.
CREATE PROCEDURE dbo.sp_IncrementSalaries
AS
UPDATE emp SET sal = sal + 500
RETURN
The above stored procedure shall simply increase all the salaries of the employees by 500. The stored procedure is named "sp_IncrementSalaries." Now, we need to have ADO.NET access the same thing in ASP.NET.
The following are the steps you need to take to execute a simple stored procedure with ADO.NET:
- Create and open a SQL Server connection (using a "SQLConnection" object).
- Create a SQL Server command (using a "SQLCommand" object).
- Specify the properties to the "SQLCommand" object.
- Assign the "SQLConnection" object to the "SQLCommand" object.
- Execute the stored procedure using the "ExecuteNonQuery" method.
- Close the "SQLConnection" and release all memory resources.
We will just follow the above steps. Start by creating your own web application using Visual Studio.NET (I named mine "SP" for this demonstration). Design your web form (call it "SimpleStoredProcedure") with a single button named "Execute Simple SP."
After designing the form, switch to the code and add the following line at the top.
Imports System.Data.SqlClient
I am importing the namespace "System.Data.SqlClient," as I would like to connect to the Microsoft SQL Server database natively. You can also use other .NET providers based on your requirements. Add the following code to your "Execute Simple SP" button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("Data Source=.;initial
catalog=Northwind;user id=sa")
Dim cmd As New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_IncrementSalaries"
.Connection = cn
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
End Sub
Once you complete the above, you execute your application (by pressing F5) and click on the button "Execute Simple SP." Once it executes successfully, check your table "emp," which should get updated with new salaries (incremented by 500).
You can simplify the above code as follows:
Private Sub Button1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New SqlCommand("sp_IncrementSalaries", New
SqlConnection("Data Source=.;initial catalog=Northwind;user
id=sa"))
With cmd
.CommandType = CommandType.StoredProcedure
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
End Sub
In this article, I shall give preference to clarity (rather than simplicity). So, I shall proceed with the first method from now on.
Next: How to execute a stored procedure with a single parameter using ADO.NET from ASP.NET >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 20 
