HomeASP.NET Working with Stored Procedures using ASP.N...
Working with Stored Procedures using ASP.NET 2.0 with Microsoft Data Access Application Block
This is the fourth article in a series focusing on developing applications using ASP.NET 2.0 and the Data Access Application Block available in “Microsoft Enterprise Application Block Library for .NET 2.0.” In this article, I shall go through the routines most used (with simple examples) for working with stored procedures in a Microsoft SQL Server database.
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 do not know how to select and use the 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.
Setting up the environment for this contribution
Before we begin, modify your web.config in such a way that it looks something like the following:
As I wanted to demonstrate stored procedures which don’t have data in AdventureWorks, I created a table named “emp” with the following columns and data types:
Empno (int)
Ename (nvarchar(50))
Sal (float)
Deptno (int)
I pushed the following sample rows into that table:
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_IncAllSalaries
AS
update dbo.emp set emp.sal = emp.sal + 500
The above stored procedure is named “p_IncAllSalaries” and accepts no parameters. It simply increments all values available in the column “sal” by 500.
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) AsInteger
Try
Dim db As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
Dim RowsAffected AsInteger = db.ExecuteNonQuery(CommandType.StoredProcedure, Name)
Return RowsAffected
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 “ExecuteNonQuery,” which accepts two parameters, “CommandType” and the name of the stored procedure. It in turn returns the number of rows affected.
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_IncEmployeeSal
(
@empno int
)
AS
update emp set emp.sal = emp.sal + 500 where empno = @empno
The above stored procedure is named “p_IncEmployeeSal” and accepts a single parameter named “empno” of type integer. It simply increments the “sal” value by 500 for the given “empno” 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.
PrivateFunction SPExecute(ByVal Name AsString, ByVal ParamValue AsInteger) AsInteger
Try
Dim db As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
Dim RowsAffected AsInteger = db.ExecuteNonQuery(Name, NewObject() {ParamValue})
Return RowsAffected
Catch ex As Exception
ThrowNew Exception("Unable to execute:" & ex.Message)
EndTry
EndFunction
EndClass
From the above code, you can observe that I again worked with the same method, “ExecuteNonQuery,” but with a different set of parameters (as it is overloaded). Let us look at the following, most important statement:
Dim RowsAffected AsInteger = db.ExecuteNonQuery(Name, NewObject() {ParamValue})
The first parameter to the method “ExecuteNonQuery” is the name of the stored procedure; the second is an array of parameter values. You can send any number of parameter values (according to the definition of the stored procedure) by including all of them within the pair of braces, separated with commas.
In this section, we shall modify the code given in the previous section to make it more readable. This approach may be required when you want to provide additional information about the parameter you are passing to the stored procedure.
I shall use the same stored procedure given in the previous section. The following is the complete modified code to execute the same stored procedure:
In this section, we shall modify the code given in the previous section to make it more readable and efficient. In this part, we will be dealing with the Data Access Application Block to add parameters (input parameters).
I shall use the same stored procedure given in the previous section. The following is the complete modified code to execute the same stored procedure:
In this section, we shall look at a stored procedure which returns a set of rows. Those rows will be returned in the form of a dataset.
Before going to the code to execute a stored procedure, we need to create a simple stored procedure (which returns a set of rows) for this demonstration. The following is the code for the stored procedure I created:
CREATE PROCEDURE dbo.p_EmployeeList
AS
SELECT empno,ename from emp
RETURN
The above stored procedure simply returns a set of rows based on the SELECT statement provided. The following is the complete code to execute the above stored procedure and display all the rows using a GridView control:
PrivateFunction SPExecute(ByVal Name AsString) As DataSet
Try
Dim db As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
Dim ds As DataSet = db.ExecuteDataSet(Name, NewObject() {})
Return ds
Catch ex As Exception
ThrowNew Exception("Unable to execute:" & ex.Message)
EndTry
EndFunction
EndClass
Throughout this contribution, I gave very simple examples and illustrations of how to deal with stored procedures using the Data Access Application Block. But there is a lot more to learn. In my upcoming contribution, we shall look into the more advanced routines in the Data Access Application Block (along with output parameters in stored procedures). 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.