More about Stored Procedures using ASP.NET 2.0 with the Microsoft Data Access Application Block - Retrieving an OUTPUT parameter value from a stored procedure using ASP.NET 2.0 and the Data Access Application Block
(Page 4 of 4 )
In previous sections, we worked with only single values. Now, in this section, we shall look into retrieving a complete row returned by a stored procedure.
Before going for 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.p_GetEmployeeAnnualSal
(
@empno int,
@AnnSal float OUTPUT
)
AS
select @AnnSal = (sal*12) from emp where empno = @empno
RETURN
You can observe that the above stored procedure simply returns a complete row from the “emp” table based on the “empno” sent as parameter.
The following is the complete code needed to execute the above stored procedure using ASP.NET 2.0 through Data Access Application Block
ImportsSystem.Data
ImportsMicrosoft.Practices.EnterpriseLibrary.Data.Sql
ImportsMicrosoft.Practices.EnterpriseLibrary.Data
PartialClass Sample4
Inherits System.Web.UI.Page
Protected Sub btnConnect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConnect.Click
Try
Me.lblMsg.Text = SPExecute("p_GetEmployeeAnnualSal", "1001") & " is annual salary"
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)
db.AddInParameter(cmd, "@empno", SqlDbType.Int, empno)
db.AddOutParameter(cmd, "@AnnSal", SqlDbType.Float, 5)
db.ExecuteNonQuery(cmd)
Return db.GetParameterValue(cmd, "@AnnSal") & ""
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 part to understand from the above is that I am using two methods, “AddInParameter” and “AddOutParameter” to work with parameters while calling the stored procedure. To retrieve the OUTPUT parameter value, I am using the “GetParameterValue” method.
Throughout this contribution, I gave very simple examples and illustrations to deal with stored procedures and parameters using the Data Access Application Block. But there's a lot more to learn. In my upcoming contributions, we shall look into the more advanced routines in the Data Access Application Block (using the wrappers and more). Don’t forget to check back or sign up for a newsletter to notify you!
The entire source code for this article is available in the form of a downloadable zip file. The solution was developed using Microsoft Visual Studio 2005 Professional Edition on Microsoft Windows Server 2003 Enterprise Edition together with Microsoft SQL Server 2005 Developer Edition and Microsoft Enterprise Library for .NET Framework 2.0 (January 2006 version). I didn’t really test the solution with any other/previous editions. If you have any problems in executing the solution, please post in the discussion area.
Any feedback, suggestions, bugs, errors, improvements etc., 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. |
