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

This is the second part of a two-part article. In the previous part we focused on developing database independent applications using ASP.NET 2.0. In this part, we will extend the functionality of the data access layer to the stored procedure level.

Contributed by
Rating: 5 stars5 stars5 stars5 stars5 stars / 15
February 21, 2007
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement

A downloadable zip file is available for this article.

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

blog comments powered by Disqus
ASP.NET ARTICLES

- Implementing ASP.NET 4.0 Page.MetaDescriptio...
- ASP.Net Development Tips
- Intro to Sessions in ASP.Net
- Google Maps API Introduction in ASP.NET usin...
- Creating an ASP.NET 3.5 Gridview Image Galle...
- Encrypt QueryString in ASP.NET 3.5 using VB....
- ASP.NET 3.5 Drop Down List Controls
- Connect to Access Database with ASP.Net
- Secure Audio Streaming with ASP.Net and Flash
- Dynamic Sitemap and Navigation in ASP.Net
- Implement Gzip and Deflate Compression in AS...
- Run ASP.Net in Ubuntu with Apache
- ASP.Net Mono Website Contact Forms
- ASP.Net URL Rewriting Methods
- Murach`s ASP.NET 4 Web Programming with C# 2...

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
 
 
 

ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 5 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials