Working with T-SQL Stored Procedures using ADO.NET - How to execute a stored procedure with multiple parameters using ADO.NET from ASP.NET
(Page 4 of 4 )
Before going into the ADO.NET code, we need to create a stored procedure with multiple parameters in SQL Server. Using "Query Analyzer," execute the following script in the "Northwind" database.
CREATE PROCEDURE dbo.IncrementSalaryFor
(
@empno int,
@increment float
)
AS
UPDATE emp SET sal = sal + @increment WHERE empno = @empno
RETURN
The above stored procedure will simply increase the salary of a particular employee based on the values sent through parameters. The stored procedure is named "sp_IncrementSalariesFor" and the parameters are named "@empno" and "@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 a "SQLConnection" object).
- Create a SQL Server command (using a "SQLCommand" object).
- Specify the properties to the "SQLCommand" object.
- Create two parameters to handle our communication (using the "SQLParameter" object).
- Add the two parameters to the "SQLCommand" object.
- Assign the "SQLConnection" object to "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 "StoredProcedureWithMultipleParam") to the already created project in the previous section. Add a button named "Execute SP with Multiple 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 Multiple 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_IncrementSalariesFor"
.Parameters.Add("@empno", 1001) 'specify accordingly
.Parameters.Add("@increment", 1001) 'specify
accordingly
.Connection = cn
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
End Sub
You can observe the above code for simplifying the "parameter" objects.
I developed the application using Microsoft Windows Server 2003 Standard Edition with Microsoft Visual Studio.NET 2003 Enterprise Architect and Microsoft SQL Server 2000. Even though I didn't test on all versions, the code in this article should work with any Windows OS (supporting .NET) and any .NET version with any SQL Server version. If anything could not work, please drop me line so that I can guide you.
In the next article, we shall deal with more interesting topics like OUTPUT parameters, returning values from database to ADO.NET, and so on. Watch for it or sign up for a newsletter to get notified.
Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 20 
