Working with Stored Procedures using ASP.NET 2.0 with 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 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:

<configuration>
      <appSettings/>
      <connectionStrings>
            <add name="AdventureWorks" connectionString="Database=
AdventureWorks;Server=(local)SQL2k5;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>

      </connectionStrings>

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:

Executing a stored procedure from ASP.NET 2.0 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_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.

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_IncAllSalaries") & " rows affected"

        Catch ex As Exception

            Me.lblMsg.Text = ex.Message

        End Try

    End Sub

    Private Function SPExecute(ByVal Name As String) As Integer

        Try

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

            Dim RowsAffected As Integer = db.ExecuteNonQuery(CommandType.StoredProcedure, Name)

            Return RowsAffected

        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 “ExecuteNonQuery,” which accepts two parameters, “CommandType” and  the name of the stored procedure.  It in turn returns the number of rows affected.

Executing a stored procedure with parameters from ASP.NET 2.0 using the Data Access Application Block

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.

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

            Me.lblMsg.Text = SPExecute("p_IncEmployeeSal", 1001) & " rows affected"

        Catch ex As Exception

            Me.lblMsg.Text = ex.Message

        End Try

    End Sub

    Private Function SPExecute(ByVal Name As String, ByVal ParamValue As Integer) As Integer

        Try

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

            Dim RowsAffected As Integer = db.ExecuteNonQuery(Name, New Object() {ParamValue})

            Return RowsAffected

        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 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 As Integer = db.ExecuteNonQuery(Name, New Object() {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.

Executing a stored procedure with parameters from ASP.NET 2.0 using the Data Access Application Block: a different approach

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:

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

            Me.lblMsg.Text = SPExecute("p_IncEmployeeSal", 1001) & " rows affected"

        Catch ex As Exception

            Me.lblMsg.Text = ex.Message

        End Try

    End Sub

    Private Function SPExecute(ByVal Name As String, ByVal ParamValue As Integer) As Integer

        Try

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

            Using cmd As New SqlClient.SqlCommand

                With cmd

                    .CommandType = CommandType.StoredProcedure

                    .CommandText = Name

                    Dim p As New SqlClient.SqlParameter

                    p.ParameterName = "empno"

                    p.Value = ParamValue

                    .Parameters.Add(p)

                    Dim RowsAffected As Integer = db.ExecuteNonQuery(cmd)

                    Return RowsAffected

                End With

            End Using

        Catch ex As Exception

            Throw New Exception("Unable to execute:" & ex.Message)

        End Try

    End Function

EndClass

Executing a stored procedure with parameters from ASP.NET 2.0 using the Data Access Application Block: the recommended approach

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:

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

            Me.lblMsg.Text = SPExecute("p_IncEmployeeSal", 1001) & " rows affected"

        Catch ex As Exception

            Me.lblMsg.Text = ex.Message

        End Try

    End Sub

    Private Function SPExecute(ByVal Name As String, ByVal ParamValue As Integer) As Integer

        Try

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

            Dim cmd As Common.DbCommand = db.GetStoredProcCommand(Name)

            db.AddInParameter(cmd, "Empno", DbType.Int32, ParamValue)

            Dim RowsAffected As Integer = db.ExecuteNonQuery(cmd)

        Catch ex As Exception

            Throw New Exception("Unable to execute:" & ex.Message)

        End Try

    End Function

EndClass

Executing a stored procedure which returns a set of rows using ASP.NET 2.0 and the Data Access Application Block

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:

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

            Me.GridView1.DataSource = SPExecute("p_EmployeeList").Tables(0)

            Me.GridView1.DataBind()

        Catch ex As Exception

            Me.lblMsg.Text = ex.Message

        End Try

    End Sub

    Private Function SPExecute(ByVal Name As String) As DataSet

        Try

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

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

            Return ds

        Catch ex As Exception

            Throw New Exception("Unable to execute:" & ex.Message)

        End Try

    End Function

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.