More about Stored Procedures using ASP.NET 2.0 with the Microsoft Data Access Application Block

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.

Retrieving a RETURN value from a stored procedure using ASP.NET 2.0 and the Data Access Application Block

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!

Retrieving a complete row from a stored procedure using ASP.NET 2.0 and 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

ImportsSystem.Data

ImportsMicrosoft.Practices.EnterpriseLibrary.Data.Sql

ImportsMicrosoft.Practices.EnterpriseLibrary.Data

PartialClass Sample3

    Inherits System.Web.UI.Page

    Protected Sub btnConnect_Click(ByVal sender As Object, 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

        End Try

    End Sub

    Private Function SPExecute(ByVal Name As String, ByVal empno As String) As DataRow

        Try

            Dim db As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)

            Dim ds As DataSet = db.ExecuteDataSet(Name, New Object() {empno})

            Return ds.Tables(0).Rows(0)

        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

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.

Retrieving an OUTPUT parameter value from a stored procedure using ASP.NET 2.0 and 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 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.