Database Independent Development using ASP.NET 2.0: Dealing with Stored Procedures

I strongly suggest that you go through the first part of this series, if you are new to database independent development in .NET 2.0.

The entire solution (source code) for this article is available as a free download (in the form of a zip). All the applications in this series have been developed using Microsoft Visual Studio 2005 Professional Edition on Microsoft Windows Server 2003 Standard Edition together with Microsoft SQL Server 2005 Express Edition and Oracle 10g Express Edition as the database. I didn't really test any of the code in any other tools/IDEs/servers/editions/versions. If you have any problems, please feel free to post in the discussion area.

Defining a class to hold the details of a stored procedure parameter

When we deal with stored procedures, we quite often work with parameters as well. To hold the information of a parameter, I would like to define my own class as follows:

Imports System.Data.Common

Public Class SPParameter

   Public ParameterName As String
   Public ParameterValue As Object
   Public ParameterDataType As DbType
   Public ParameterSize As Integer
   Public ParameterDirectionUsed As ParameterDirection

   Public Sub New()

   End Sub

   Public Sub New(ByVal passedParameterName As String, ByVal passedValue As Object, Optional ByVal passedSQLType As DbType = Nothing, Optional ByVal passedSize As Integer = Nothing, Optional ByVal passedDirection As ParameterDirection = ParameterDirection.Input)
     ParameterName = passedParameterName
     ParameterValue = passedValue
     ParameterDataType = passedSQLType
     ParameterSize = passedSize
     ParameterDirectionUsed = passedDirection
   End Sub

End Class

The above class simply holds the name and value of the stored procedure parameter along with other information like type, size and direction (IN, OUT, IN OUT or RETURN).

Developing a separate class to hold parameter information is very helpful if you would like to scale your layer (data access layer) to meet multi-tier architectures and requirements. For example, you can make the above class serializable by modifying it as follows:

Imports System.Xml.Serialization
Imports System.Data.Common

<XmlRoot("SPParameter")> _
Public Class SPParameter

   <XmlElement("ParameterName", GetType(String))> _
   Public ParameterName As String

   <XmlElement("ParameterValue", GetType(Object))> _
   Public ParameterValue As Object

   <XmlElement("ParameterDataType", GetType(DbType))> _
   Public ParameterDataType As DbType

   <XmlElement("ParameterSize", GetType(Integer))> _
   Public ParameterSize As Integer

   <XmlElement("ParameterDirectionUsed", GetType(ParameterDirection))> _
   Public ParameterDirectionUsed As ParameterDirection

.
.

End Class

Adding parameters to the cache

A stored procedure may have more than one parameter to be accepted. Before executing a stored procedure, we need to "cache" all those parameters in an object to serve the same while executing the stored procedure.

In this scenario, I would like to have the "cache" be determined as an "ArrayList" object. The declaration of that object is as follows:

   Dim alSPParamCache As ArrayList = Nothing

I would like to have each of the parameters to be added to the above "cache" object using a separate method as follows:

Public Sub SPParameterAdd(ByVal ParamName As String, ByVal ParamValue As Object, Optional ByVal ParamDirection As ParameterDirection = ParameterDirection.Input, Optional ByVal ParamDataType As DbType = Nothing, Optional ByVal ParamSize As Integer = Nothing)
   If alSPParamCache Is Nothing Then
     alSPParamCache = New ArrayList
   End If
   Dim objParam As New SPParameter
   With objParam
     .ParameterName = ParamName
     .ParameterValue = ParamValue
     .ParameterDirectionUsed = ParamDirection
     .ParameterDataType = ParamDataType
     .ParameterSize = ParamSize
   End With
   alSPParamCache.Add(objParam)
End Sub

The entire parameter cache must be cleared after successful execution of a stored procedure (or even manually when necessary). The following is the method which is meant for that:

Public Sub SPParameterClearCache()
   If Not alSPParamCache Is Nothing Then
     alSPParamCache.Clear()
     alSPParamCache = Nothing
   End If
End Sub

Executing a stored procedure as part of the Data Access Layer

The previous sections were dedicated to dealing with stored procedure parameters. Now, we shall deal with the execution of a stored procedure. The following is the method which executes a stored procedure based on the parameters added to the cache:

Public Sub SPExecute(ByVal SPName As String)
   Dim cmd As DbCommand = GetDBCommand()
   AddSPParamFromCache(cmd)
   cmd.CommandText = SPName
   cmd.CommandType = CommandType.StoredProcedure
   Try
     cmd.Connection.Open()
     cmd.ExecuteNonQuery()
   Catch ex As Exception
     Throw New Exception(ex.Message & "-->Stored Procedure:" & SPName)
   Finally
     If cmd.Connection.State = ConnectionState.Open Then
       cmd.Connection.Close()
     End If
     cmd.Dispose()
     SPParameterClearCache()
   End Try
End Sub

The only new statement from the above code is the following:

   AddSPParamFromCache(cmd)

It is a separate method from the same class, which is defined as follows:

Private Sub AddSPParamFromCache(ByRef cmd As DbCommand)
   If alSPParamCache Is Nothing Then
     Exit Sub 'no parameters to add
   End If
   For Each objSPParam As SPParameter In alSPParamCache
     Dim p As DbParameter = cmd.CreateParameter
     With p
       .ParameterName = objSPParam.ParameterName
       .Value = objSPParam.ParameterValue
       .Direction = objSPParam.ParameterDirectionUsed
       .DbType = objSPParam.ParameterDataType
       .Size = objSPParam.ParameterSize
     End With
     cmd.Parameters.Add(p)
   Next
End Sub

Before executing the stored procedure, we need to add all the parameters (available as part of the cache) to the command object. The above method simply adds all the parameters available in the cache "alSPParamCache" (of type ArrayList) to the Command object passed to it.

Returning result sets (or a set of rows) by executing a stored procedure

Once we know about executing the stored procedures using parameters, we can easily retrieve the entire information from the database. Now, let us bring a set of rows from the database in the form of a dataset, by executing a stored procedure as follows:

Public Function SPgetDataSet(ByVal SPName As String) As DataSet
   Dim ds As New DataSet
   Dim da As DbDataAdapter = GetDBDataAdapter()
   Dim cmd As DbCommand = GetDBCommand()
   AddSPParamFromCache(cmd)
   cmd.CommandText = SPName
   cmd.CommandType = CommandType.StoredProcedure
   da.SelectCommand = cmd
   Try
     da.Fill(ds)
   Catch ex As Exception
     Throw New Exception(ex.Message & "-->Stored Procedure:" & SPName)
   Finally
     If cmd.Connection.State = ConnectionState.Open Then
       cmd.Connection.Close()
     End If
     cmd.Dispose()
     da.Dispose()
     SPParameterClearCache()
   End Try
   Return ds
End Function

The above method returns a dataset which is filled using a DataAdapter. You can even return a "DataTable" object using a separate method as follows:

Public Function SPgetDataTable(ByVal SPName As String) As DataTable
   Dim dt As New DataTable
   Dim da As DbDataAdapter = GetDBDataAdapter()
   Dim cmd As DbCommand = GetDBCommand()
   AddSPParamFromCache(cmd)
   cmd.CommandText = SPName
   cmd.CommandType = CommandType.StoredProcedure
   da.SelectCommand = cmd
   Try
     da.Fill(dt)
   Catch ex As Exception
     Throw New Exception(ex.Message & "-->Stored Procedure:" & SPName)
   Finally
     If cmd.Connection.State = ConnectionState.Open Then
       cmd.Connection.Close()
     End If
     cmd.Dispose()
     da.Dispose()
     SPParameterClearCache()
   End Try
   Return dt
End Function

In other words, you can further reduce the above code (which returns the "DataTable" object) by rewriting it as follows:

Public Function SPgetDataTable(ByVal SPName As String) As DataTable
   Dim ds As DataSet = SPgetDataSet(SPName)
   If ds.Tables.Count = 0 Then
     Return Nothing
   Else
     Return ds.Tables(0)
   End If
End Function

In the above code, I am simply reusing the existing method "SPgetDataSet" to decrease several lines of code!

Returning a single row or single value by executing a stored procedure

Now that we know about returning a set of rows, it is time to retrieve a single row. Let us go through the following method:

Public Function SPgetDataRow(ByVal SPName As String) As DataRow
   Dim dt As DataTable = SPgetDataTable(SPName)
   If dt.Rows.Count > 0 Then
     Return dt.Rows(0)
   Else
     Return Nothing
   End If
End Function

You can observe from the above method that I am simply reusing the previous method "SPgetDataTable" (which returns a set of rows) and returning only the first row. Returning a single value is a bit different from above. Let us consider the following method:

Public Function SPgetGetValue(ByVal SPName As String) As String
   Dim cmd As DbCommand = GetDBCommand()
   AddSPParamFromCache(cmd)
   cmd.CommandText = SPName
   cmd.CommandType = CommandType.StoredProcedure
   Try
     cmd.Connection.Open()
     Return cmd.ExecuteScalar & ""
   Catch ex As Exception
     Throw New Exception(ex.Message & "-->Stored Procedure:" & SPName)
   Finally
     If cmd.Connection.State = ConnectionState.Open Then
       cmd.Connection.Close()
     End If
     cmd.Dispose()
     SPParameterClearCache()
   End Try
End Function

In the above code, you can understand that I am using the "ExecuteScalar" method of the Command object to improve the performance while retrieving a single value from the database.

Testing the execution of stored procedures (in ASP.NET) using DAL methods

To execute the methods in the DAL, you need to create an object as follows:

Dim db As DALib.DataAccess

   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
     Dim ProvName As String = ConfigurationManager.AppSettings("Provider-
Type").ToString
     Dim ConnString As String = ConfigurationManager.ConnectionStrings
("ConnectionString").ConnectionString.ToString
     db = New DALib.DataAccess(ProvName, ConnString)
End Sub

Prior to the above, make sure that your web.config file is equipped with the proper provider type and connection string as follows:

<appSettings>
   <add key="Provider-Type" value="System.Data.SqlClient"/>
   <!-- <add key="Provider-Type" value="Oracle.DataAccess.Client"/> -->
</appSettings>

<connectionStrings>
   <add name="ConnectionString" connectionString="Data
Source=.sqlexpress;initial catalog=northwind;user
id=sa2;password=eXpress2005"/>
   <!--<add name="ConnectionString" connectionString="Data Source=xe;user
id=northwind;password=tiger"/> -->
</connectionStrings>

Please be aware that you may have to modify the above configurations according to your requirements.

To execute a stored procedure using the DAL object, the following single line would be enough:

   db.SPExecute("StoredProcedureName")

To execute a stored procedure with two parameters, the following three lines would be enough:

   db.SPParameterAdd("@parametername1", "value1")
   db.SPParameterAdd("@parametername2", "value2")
   db.SPExecute("StoredProcedureName")

You can even provide much more parameter information as follows:

   db.SPParameterAdd("@parametername1", "value1",
Data.ParameterDirection.Input, Data.DbType.Int16)

To receive a data table and present the same using GridView, you can make it two lines as follows:

   Me.GridView1.DataSource = db.SPgetDataTable("StoredProcedureName")
   Me.GridView1.DataBind()

And finally, you can retrieve a single value using the following:

   Me.Label1.Text = db.SPgetGetValue("StoredProcedure")

You can expect the third (and final) part of this series soon. I hope you enjoyed the article and any comments, suggestions, feedback, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com