Database Independent Development using ASP.NET 2.0: Dealing with Stored Procedures
(Page 1 of 6 )
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.
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
Next: Adding parameters to the cache >>
More ASP.NET Articles
More By Jagadish Chaterjee
