Working with NULL, OUTPUT and RETURN in ADO.NET - How to retrieve the value returned by a stored procedure using ADO.NET from ASP.NET: the stored procedure
(Page 4 of 4 )
Every stored procedure returns an integer value back to the calling environment (or ADO.NET in this scenario). This happens even if you don’t use a RETURN statement. By default it returns zero (if no RETURN is used).
We can use the RETURN statement to return integer values according to our requirements. In this demonstration, I would like to return a value showing whether an employee exists or not using a RETURN statement. Let us see how it works.
Before going to the ADO.NET code, we need to create a simple stored procedure in SQL Server which returns a value. Using “Query Analyzer,” execute the following script in “Northwind” database.
CREATE PROCEDURE dbo.sp_emp_isEmployeeExist
(
@empno int
)
AS
IF EXISTS (SELECT ename FROM emp WHERE empno =@empno)
RETURN 1
ELSE
RETURN 0
According to the above stored procedure, you can observe that I am sending a value 1 if the employee is found or 0 if no employee is found. It simply accepts “Empno” (“@empno”) as a parameter (which needs to be searched). The stored procedure is named “sp_Emp_isEmployeeExists.” Now, we need to go to ADO.NET to access the same in ASP.NET. The next section accomplishes this.
How to retrieve the value returned by a stored procedure using ADO.NET from ASP.NET: the code
We need to call the stored procedure defined in the previous section. Add a new web form (call it “ReturnValueDemo”) to an already existing project (as specified in my previous article). Add a button named “Receive RETURN value.”
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 “Receive RETURN value” button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
Dim name As String
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_emp_isEmployeeExist"
Dim paramName As New SqlParameter
With paramName
.ParameterName = "@ReturnValue"
.SqlDbType = SqlDbType.Int
.Direction = ParameterDirection.ReturnValue
End With
.Parameters.Add("@empno", 1201)
.Parameters.Add(paramName)
.Connection = cn
.Connection.Open()
.ExecuteNonQuery()
name = IIf(paramName.Value = 0, "Not found", "found")
'release resources
.Connection.Close()
.Dispose()
End With
Me.lblName.Text = name
End Sub
Set the start page, execute your application (by pressing F5) and click on the button “Receive RETURN value.” Once it executes successfully, it returns a value which would be either “found” or “not found.”
The most important issue in the above code is that I used “ParameterDirection.ReturnValue” as part of “Direction” property. You can also see the use of IIF function used to simply notate our own true/false values.
Summary
Even though, I am using only the “SQLCommand” object throughout all of my examples, you can still achieve the same results using “SQLDataReader” or “SQLDataAdapter.” The best and the most efficient approach to working with stored procedures is working with “SQLCommand.” Again, I say, depending on your needs (such as working with data tables) we need to shift to the most efficient way.
I developed the application using Microsoft Windows Server 2003 Standard Edition with Microsoft Visual Studio.NET 2003 Enterprise Architect and Microsoft SQL Server 2000. If anything does not work, please drop me a line so that I can guide you. The entire solution for this article is freely available in the form of a zip downloadable.
In the next article, we shall wok with data tables, data sets, and so on using stored procedures. If possible, we shall also look into stored functions and compare both.
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. |
