HomeASP.NET More about Stored Procedures using ASP.NET...
More about Stored Procedures using ASP.NET 2.0 with the Microsoft Data Access Application Block
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
PrivateFunction SPExecute(ByVal Name AsString) AsString
Try
Dim db As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
Dim v AsString = db.ExecuteScalar(Name, NewObject() {})
Return v
Catch ex As Exception
ThrowNew Exception("Unable to execute:" & ex.Message)
EndTry
EndFunction
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.
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
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 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.p_GetEmployee
(
@empno int
)
AS
select * 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 the Data Access Application Block
ProtectedSub btnConnect_Click(ByVal sender AsObject, ByVal e As System.EventArgs) Handles btnConnect.Click
Try
Dim dr As DataRow = SPExecute("p_GetEmployee", "1001")
Me.lblMsg.Text = dr("ename") & ", " & dr("sal")
Catch ex As Exception
Me.lblMsg.Text = ex.Message
EndTry
EndSub
PrivateFunction SPExecute(ByVal Name AsString, ByVal empno AsString) As DataRow
Try
Dim db As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
Dim ds As DataSet = db.ExecuteDataSet(Name, NewObject() {empno})
Return ds.Tables(0).Rows(0)
Catch ex As Exception
ThrowNew Exception("Unable to execute:" & ex.Message)
EndTry
EndFunction
ProtectedSub Page_Load(ByVal sender AsObject, ByVal e As System.EventArgs) HandlesMe.Load
EndSub
EndClass
Even though the above code works pretty well, not everyone likes the above methodology, because it works with dataset. The other way to make this work is by using OUTPUT parameters. The next section concentrates on this.
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
ThrowNew Exception("Unable to execute:" & ex.Message)
EndTry
EndFunction
ProtectedSub Page_Load(ByVal sender AsObject, ByVal e As System.EventArgs) HandlesMe.Load
EndSub
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.