I've always been a stickler about matters relating to building proper data access layers, and one of the areas that seems to get overlooked has been around proper creation of stored procedure parameters. Back in .Net 1.0 and 1.1 I built a data access layer on top of the Data Access Application Block that handled the creation of stored procedure parameters using enums decorated with custom attributes. I had a method in my base QueryPackage class (which I used to inherit from and create groups of logically associated stored procedures) that would accept an enum (the DalParameterType enum) and a value (as object) and create a Stored Procedure Parameter class based on the meta data that decorated the enum value. If you really want to read up on that, it is in my article "Fun with Attribute Based Programming – Extending Enumerations".
In an effort to bring that code up to .Net 2.0, I sat down and instead of just a simple migration, I figured I'd try to take a fresh look at what I was trying to do, and maybe use some new .Net 2.0 features to accomplish the same thing, only better. To make things a little more interesting, I decided to use Visual Basic for this task. Mostly, it was just to prove that you can build good architecture in Visual Basic just as easily as in C# (and maybe tweak anti Visual Basic guys like Sam Gentile a little, since I know he reads this blog. Hopefully he doesn't go blind by accidentally seeing my Visual Basic code here ;)).
The way I've seen most developers handle stored procedure parameters is something like the following (in this case I'm using the Enterprise Library to handle the database communications, but this particular bit of code isn't very different than the pure ADO.Net way). I've trimmed the code down to just the parts we are discussing here, and merged some of the parts into a simple test case.
Dim EmployeeDatabase As EntLib.Database = EntLib.DatabaseFactory.CreateDatabase()
Dim InsertCommand As Data.Common.DbCommand = _
EmployeeDatabase.GetStoredProcCommand("Person.ContactInsert")
InsertCommand.Parameters.Add(New SqlParameter("@FirstName", "Don"))
The code we are really interested in here is the last line, where we create the SqlParameter that is added to the SqlCommand object (DbCommand is the base class for SqlCommand). To do the same thing using my older (.Net 1.1) custom attribute approach I would have done the following:
MyBase.AddParameter(Command, DalParameterType.ContactFirstName, "Don")
Where the class that contains this code inherits from a base class that implements a method call AddParameter. That method builds the SqlParameter based on the meta data (the custom attribute) associated with the enum (in this case DalParamererTpye.EmployeeFirstName and sets the value of the parameter value to "Don".
You can see from both examples, it is relatively easy to create a stored procedure parameter, but the second one has the advantage of encapsulating the generation of the parameter in a method, where you can add some custom transformation code or validation routines. In my case, we were connecting to an Oracle 8 database and using the Microsoft Oracle provider. At that time (and I don't know if things have changed) you couldn't pass booleans as parameters into Oracle stored procedures and we needed a place where we could transform booleans to integers. But I also liked the idea of making sure that a stored procedure parameter was always created the exact same way. Plus, by using an Enum, we got Intellisense and a "Data Dictionary" of all the stored procedure parameters. The down side was that the method was not type safe, so you could accidentally pass in an integer instead of string, and the compiler wouldn't warn you. You wouldn't know until that bit of code was executed and either an exception was thrown, or the type was cast to the "correct" type, and the wrong information was used.
What we are really trying to do here is to create a custom type for each unique stored procedure parameter which matches (or can be transformed into) the type defined in the stored procedure. The reason why developers don't currently go around building these custom types is because it can take a bit of code to write (plus maintain), it adds a little extra over head, and they are use to using implicit contracts with the database (instead of explicit contracts like defined by WSDL in web services). So, if developers are going to be lured into creating explicit contracts, migration needs to be easy, and the amount of "extra" code has to be minimal. Extending the Enterprise Library is a good way to do this (and developers using EntLib are more likely to adopt strongly typed stored procedure parameters, since they are less RAD and more likely enterprise developers). Each custom type needs to carry all the information of a parameter, which is basically, the name, direction, database type, .Net type and value (there are other properties, but those are the big ones). It wasn't until generics was added to .Net 2.0 that we could easily create a base type that handled the majority of the "heavy lifting" and not expose the Value of the type as an object.
To make it easy to migrate from loosely typed parameter world to the strongly type world the code should look as closely like the original as possible. So we would like to end up with something that looks like this:
InsertCommand.Parameters.Add(New Parameter.Contact.FirstName("Don"))
Where Parameter.Contact is the last part of the namespace and FirstName is a class that represents the stored procedure parameter "@FirstName" that is used in the stored procedure Person.ContactInsert. I know where I would like to get to, now how to get there.
If developers are going to use strongly typed parameters, I need to make the code to implement them as small as possible, but also easy to read and understand. Most of the code is the same for all parameters, so I will create a base class that all the specific implementations can inherit from. This way, the only code needed to create the parameter is for the things that vary and we can also set some of the properties to the most common defaults. In this case, the minimum information we need is the .Net type and the name of the parameter, and default the direction to input parameter and derive the database type from the .Net type. To make sure the parameter is strongly typed, I'll take advantage of generics. But the specific implementation does not have to be exposed as a generic type, just the base class. So I would like the code to define a typical input stored procedure parameter should look like this:
Friend Class FirstName
Inherits Generic.DalParameter(Of String)
Sub New(ByVal value As String)
MyBase.New(value, "@FirstName")
End Sub
End Class
Where Generic.DalParameter is the base class that all of our strongly typed parameters derive from. With these 6 lines of code, we now have a the information needed to build a any DbParameter. We can accommodate other types of parameters (output, inputoutput, etc.) by creating various overloaded constructors in the base class DalParameter, and just change the MyBase.New line in the above code. So, if all the work is being done in the base class, I guess we will go there next.
One thing I struggled with is if I should create a brand new base class, or maybe inherit from DbParameter, or even better, inherit from SqlParameter if I wanted to tie this to a specific database. But, SqlParameter is marked NotInheritable (aka sealed), so that settled that, and DbParameter has public constructors that I do not want exposed. But, to keep this base class similar to a DbParameter, I decided to implement the same 3 interfaces that SqlParameter implements, IDbDataParameter, IDataParameter, and ICloneable, but I'll put them in my own interface IDalParameter. DalParameter will have one generic type parameter (I'll call it TParameterType) which will define the type of the Value Property. But, since we are implementing IDataParameter and it has a property Value as an Object type, I'll need to call it something else, so I picked GenericValue. I'm not sure if I like that name, but it will do for now. The only places TParameterType will be referenced is in the constructors (where the value is passed in and I want to enforce the value type here), the private field that holds the value and the property GenericValue. The resulting class is listed below.
Friend MustInherit Class DalParameter(Of TParameterType)
Implements IDalParameter
#Region "Private Fields"
Dim _DbType As Data.DbType
Dim _Direction As Data.ParameterDirection
Dim _IsNullable As Boolean
Dim _ParameterName As String
Dim _SourceColumn As String
Dim _SourceVersion As Data.DataRowVersion
Dim _Value As TParameterType
Dim _Precision As Byte
Dim _Scale As Byte
Dim _Size As Integer
#End Region
#Region "Constructors"
Protected Sub New(ByVal value As TParameterType, _
ByVal name As String)
Me.InitializeParameterToDefaults()
_Value = value
_ParameterName = name
End Sub
Protected Sub New(ByVal value As TParameterType, _
ByVal name As String, _
ByVal dbType As System.Data.DbType)
Me.InitializeParameterToDefaults()
_Value = value
_ParameterName = name
_DbType = [dbType]
End Sub
Protected Sub New(ByVal value As TParameterType, _
ByVal name As String, _
ByVal dbType As System.Data.DbType, _
ByVal direction As System.Data.ParameterDirection)
Me.InitializeParameterToDefaults()
_Value = value
_ParameterName = name
_DbType = [dbType]
_Direction = direction
End Sub
#End Region
#Region "Properties"
Friend Property DbType() As System.Data.DbType _
Implements System.Data.IDataParameter.DbType
Get
Return _DbType
End Get
Set(ByVal Value As System.Data.DbType)
_DbType = Value
End Set
End Property
Friend Property Direction() As System.Data.ParameterDirection _
Implements System.Data.IDataParameter.Direction
Get
Return _Direction
End Get
Set(ByVal Value As System.Data.ParameterDirection)
_Direction = Value
End Set
End Property
Friend ReadOnly Property IsNullable() As Boolean _
Implements System.Data.IDataParameter.IsNullable
Get
Return _IsNullable
End Get
End Property
Friend Property ParameterName() As String _
Implements System.Data.IDataParameter.ParameterName
Get
Return _ParameterName
End Get
Set(ByVal Value As String)
_ParameterName = Value
End Set
End Property
Friend Property SourceColumn() As String _
Implements System.Data.IDataParameter.SourceColumn
Get
Return _SourceColumn
End Get
Set(ByVal Value As String)
_SourceColumn = Value
End Set
End Property
Friend Property SourceVersion() As System.Data.DataRowVersion _
Implements System.Data.IDataParameter.SourceVersion
Get
Return _SourceVersion
End Get
Set(ByVal Value As System.Data.DataRowVersion)
_SourceVersion = Value
End Set
End Property
Friend Property Value() As Object _
Implements System.Data.IDataParameter.Value
Get
Return _Value
End Get
Set(ByVal Value As Object)
_Value = CType(Value, TParameterType)
End Set
End Property
Friend Property GenericValue() As TParameterType
Get
Return _Value
End Get
Set(ByVal value As TParameterType)
_Value = value
End Set
End Property
Friend Property Precision() As Byte _
Implements System.Data.IDbDataParameter.Precision
Get
Return _Precision
End Get
Set(ByVal Value As Byte)
_Precision = Value
End Set
End Property
Friend Property Scale() As Byte _
Implements System.Data.IDbDataParameter.Scale
Get
Return _Scale
End Get
Set(ByVal Value As Byte)
_Scale = Value
End Set
End Property
Friend Property Size() As Integer _
Implements System.Data.IDbDataParameter.Size
Get
Return _Size
End Get
Set(ByVal Value As Integer)
_Size = Value
End Set
End Property
#End Region
#Region "IClonable Memebers"
Friend Function Clone() As Object _
Implements System.ICloneable.Clone
Return Me.MemberwiseClone()
End Function
#End Region
#Region "Private Methods"
Private Sub InitializeParameterToDefaults()
_DbType = DbType.String
_Value = Nothing
_Direction = ParameterDirection.Input
_IsNullable = False
_ParameterName = String.Empty
_Precision = 0
_Scale = 0
_Size = 0
_SourceColumn = String.Empty
_SourceVersion = DataRowVersion.Current
End Sub
#End Region
End Class
Now that we have the DalParameter defined, we just need to add one last thing, a way to easily convert it to the specific DbParameter implementation that is required for the database that is being used. The easiest way to do this is to add some code to the DalParameter class so that it will be implicitly converted to the proper type. I couldn't have done this with Visual Basic and .Net 1.1, as only C# let you overload operates in 1.1, but this was added to Visual Basic in .Net 2.0. The way to declare a block of code to implicitly cast a type to another type is creating an Operator CType function. This function must be declared Public Shared, and you must specify Widening or Narrowing. The difference between a widening and a narrowing conversion is that a widening conversion always returns a value of the converted type and handles all errors. If there is any possibility that a conversion will not succeed, you should use the narrowing option. Since we are handling the conversion, we should not get an exception, and I will use the Widening option. We need to implement an implicit cast for each DbParameter type that needs to be supported. In this example I only created functions for SqlParameter and OleDbParameter.
Public Shared Widening Operator _
CType(ByVal parameter As DalParameter(Of TParameterType)) As SqlParameter
Dim NewSqlParameter As New SqlParameter
NewSqlParameter.DbType = parameter.DbType
NewSqlParameter.Value = parameter.Value
NewSqlParameter.Direction = parameter.Direction
NewSqlParameter.ParameterName = parameter.ParameterName
NewSqlParameter.Precision = parameter.Precision
NewSqlParameter.Scale = parameter.Scale
NewSqlParameter.Size = parameter.Size
NewSqlParameter.SourceColumn = parameter.SourceColumn
NewSqlParameter.SourceVersion = parameter.SourceVersion
Return NewSqlParameter
End Operator
Public Shared Widening Operator _
CType(ByVal parameter As DalParameter(Of TParameterType)) As OleDbParameter
Dim NewOleParameter As New OleDbParameter
NewOleParameter.DbType = parameter.DbType
NewOleParameter.Value = parameter.Value
NewOleParameter.Direction = parameter.Direction
NewOleParameter.ParameterName = parameter.ParameterName
NewOleParameter.Precision = parameter.Precision
NewOleParameter.Scale = parameter.Scale
NewOleParameter.Size = parameter.Size
NewOleParameter.SourceColumn = parameter.SourceColumn
NewOleParameter.SourceVersion = parameter.SourceVersion
Return NewOleParameter
End Operator
If you have code that looks like the line below, it will automatically execute the implicit casting function from above to convert the FirstName type to a SqlParameter and not give you a compiler error.
Dim FirstNameParameter As SqlClient.SqlParameter = New Parameter.Person.FirstName("Don")
So, we should now be able to get the desired syntax to work:
InsertCommand.Parameters.Add(New Parameter.Contact.FirstName("Don"))
But, not so fast there. Because the method DbParameterCollection.Add accepts an Object type as a parameter, the compiler will not implicitly cast the FirstName type to a SqlParameter type. Also, the Add method only does a type check of the value passed in, and does not try to cast the value to a SqlParameter, so at runtime, the above line will throw an InvalidParameterType exception. We have 3 options to correct this situation. We can dim a variable as SqlParameter and set it to an instance of a FirstName class, or cast it inline using the CType function, or create an AddParameter function that accepts a SqlCommand object and a SqlParameter. Because I wanted to keep the amount of code to a minimum, I decided to use the last option, since I already had a similar function in my .Net 1.1 version of strongly typed stored procedure parameters. In the end, the final code looks like this:
MyBase.AddParameter(Command, New Parameter.Person.FirstName(firstName))
Where the above code exists in a class that inherits from SqlQueryPackageBase which contains this code:
Friend Function AddParameter(ByVal command As Data.SqlClient.SqlCommand, _
ByVal parameter As Data.SqlClient.SqlParameter) _
As System.Data.Common.DbParameter
'' The only reason this function exists is for syntatic sugar
'' Since Parameters.Add is overloaded to take an object parameter,
'' the DalParameter isn't implicitly cast to a SqlParameter
'' Use of this function forces the DalParameter to be implicitly
'' cast to a SqlParameter
Return command.Parameters.Add(parameter)
End Function
I'm not totally done with this code, as I have a couple things that I still want to test. I usually create a Data Access Layer dll, and the only publicly exposed members are methods to execute the stored procedures. I'm reconsidering this approach, and might expose these strongly typed parameters, instead of relying on the either creating separate Data Transport Objects, or only accepting the basic .Net classes. Plus, I want to run some performance testing of this code and compare it to my original custom attribute approach, and contrast them both to the standard, loosely typed style that most developers use. Once I do al that, I'll give an update, and probably release the code. I have a couple other enhancements that I'm working on for the data access part of the Enterprise Library, and maybe I'll release them all together. Let me know if any of this is actually interesting to you, as I'm not sure how many people actually care about this topic.