More about Stored Procedures using ASP.NET 2.0 with the Microsoft Data Access Application Block - Retrieving a RETURN value from a stored procedure using ASP.NET 2.0 and the Data Access Application Block
(Page 2 of 4 )
In the previous section, I simply issued a SELECT statement. There could be times where you really need to make use of a RETURN statement in stored procedures. This section handles this scenario by retrieving that value.
Before going to the code to execute a stored procedure, we need to create a simple stored procedure (with a single parameter) for this demonstration. The following is the code for the stored procedure I created:
CREATE PROCEDURE dbo.isEmployeeExist
(
@empno int
)
AS
if exists (select empno from emp where empno = @empno)
begin
return 1
end
else
begin
return 0
end
You can observe that the above stored procedure simply returns 1 if the employee exists or 0 if no employee exists.
The following is the complete code needed to execute the above stored procedure using ASP.NET 2.0 through the Data Access Application Block
ImportsSystem.Data
ImportsMicrosoft.Practices.EnterpriseLibrary.Data.Sql
ImportsMicrosoft.Practices.EnterpriseLibrary.Data
PartialClass Sample2
Inherits System.Web.UI.Page
Protected Sub btnConnect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConnect.Click
Try
Dim r As Integer = SPExecute("isEmployeeExist", "1001")
If r = 1 Then
Me.lblMsg.Text = "Exists"
Else
Me.lblMsg.Text = "Does not exist"
End If
Catch ex As Exception
Me.lblMsg.Text = ex.Message
End Try
End Sub
Private Function SPExecute(ByVal Name As String, ByVal empno As String) As String
Try
Dim db As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
Dim cmd As SqlClient.SqlCommand = db.GetStoredProcCommand(Name, New Object() {empno})
db.ExecuteNonQuery(cmd)
Dim v As String = cmd.Parameters("@return_value").Value
Return v
Catch ex As Exception
Throw New Exception("Unable to execute:" & ex.Message)
End Try
End Function
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
EndClass
The most important issue to remember from the above code is that I am using “@return_value,” which is already built for the purpose!
Next: Retrieving a complete row from a stored procedure using ASP.NET 2.0 and the Data Access Application Block >>
More ASP.NET Articles
More By Jagadish Chaterjee
