More about Stored Procedures using ASP.NET 2.0 with the Microsoft Data Access Application Block
(Page 1 of 4 )
This is the fifth article in a series focusing on developing applications using ASP.NET 2.0 and the Data Access Application Block available in the Microsoft Enterprise Application Block Library for .NET 2.0. In this article, I shall go through the routines that are used most (with simple examples) to work with stored procedures and parameters in Microsoft SQL Server database. I shall also cover INPUT, OUTPUT and RETURN parameters in this contribution.
If you are new to Microsoft application blocks, I strongly suggest you refer to my first article in this series titled “Developing ASP.NET 2.0 Applications with the Microsoft Data Access Application Block." It gives a solid foundation for beginners.
If you are not aware of how to select and use a proper “database” class, I suggest you go through my second article in this series titled “Connecting to Different Databases using ASP.NET 2.0 with the Microsoft Data Access Application Block.” In this article, I focus on working with Microsoft SQL Server. If you are new to working with stored procedures using the Data Access Application Block, you can refer to my fourth article in this series.
This article works on the same environment as discussed in my previous article. For table structures and more, I suggest you to refer to that article.
Retrieving a single value from a stored procedure using ASP.NET 2.0 and the Data Access Application Block
In my previous article, I introduced several ways to work with stored procedures using the Data Access Application Block. In this section, I shall focus on retrieving a single value from a stored procedure using the Data Access Application Block.
Before going to the code to execute a stored procedure, we need to create a simple stored procedure for this demonstration. The following is the code for the stored procedure I created:
CREATE PROCEDURE dbo.p_GetEmployeeCount
AS
SELECT count(*) from emp
RETURN
The above stored procedure is named “p_GetEmployeeCount” and it simply returns the number of rows available in the “emp” table.
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 _Default
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_GetEmployeeCount") & " Employees exist"
Catch ex As Exception
Me.lblMsg.Text = ex.Message
End Try
End Sub
Private Function SPExecute(ByVal Name As String) As String
Try
Dim db As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
Dim v As String = db.ExecuteScalar(Name, New Object() {})
Return v
Catch ex As Exception
Throw New Exception("Unable to execute:" & ex.Message)
End Try
End Function
EndClass
From the above code, you can observe that I worked with the method “ExecuteScalar,” which is optimized to execute and return single values from databases.
Next: Retrieving a RETURN value from a stored procedure using ASP.NET 2.0 and the Data Access Application Block >>
More ASP.NET Articles
More By Jagadish Chaterjee
