Working with T-SQL Stored Procedures using ADO.NET - How to execute a stored procedure with a single parameter using ADO.NET from ASP.NET
(Page 3 of 4 )
Before going into the ADO.NET code, we need to create a stored procedure with a single parameter in SQL Server. Using "Query Analyzer," execute the following script in the "Northwind" database.
CREATE PROCEDURE dbo.sp_IncrementSalariesBy
(
@increment int
)
AS
UPDATE emp SET sal = sal + @increment
RETURN
The above stored procedure shall simply increase all salaries of the employees by the value (or parameter) provided by the calling program. The stored procedure is named "sp_IncrementSalariesBy" and the parameter is named "@increment." Now, we need to have ADO.NET access the same 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 the "SQLConnection" object).
- Create a SQL Server command (using the "SQLCommand" object).
- Specify the properties to the "SQLCommand" object.
- Create a new parameter to handle our communication (using the "SQLParameter" object).
- Add the parameter 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. Add a new web form (call it "StoredProcedureWithSingleParam") to the already created project in the previous section. Add a button named "Execute SP with Single Parameter."
After designing the form, switch to the code and add the following line at the top.
Imports System.Data.SqlClient
Add the following code to your "Execute SP with Single Parameter" 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_IncrementSalariesBy"
Dim paramIncrement As New SqlParameter
With paramIncrement
.ParameterName = "@increment"
.SqlDbType = SqlDbType.Int
.Value = 300
End With
.Parameters.Add(paramIncrement)
.Connection = cn
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
End Sub
Set the start page, execute your application (by pressing F5), and click on the button "Execute SP with Single Parameter." Once it executes successfully, check your table "emp," which should get updated with new salaries (incremented by 300).
In the above code, I simply hard coded the value 300. You can get the user specified value using a text box and assign it directly, to make it really dynamic.
You can proceed to the next section with tips on simplifying the above code.
Next: How to execute a stored procedure with multiple parameters using ADO.NET from ASP.NET >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 19 
