Upgrading to the Enterprise Library Data Access Application Block
Normally I have been one to jump right into the latest technologies, unless I think the newer method would take longer to implement and manage than it is worth. Hence why I will most likely never do unit based testing and development (honestly, my clients do not want me to develop their app twice, they just want to be up and running). When the Data Application Blocks were release some years ago I was the first to jump on this. I had been developing my own version for about year as it was. Encapsulating commonly used code is the key to being a pragmatic programmer in my opinion. If you look at my code you will see SQLHelper all over the place.
Last Summer Microsoft released the Enterprise Library as part of their Patterns and Practices push. At first I was all for it, until I saw they took away my favorite one line of code to get or set my data, no more SQLHelper! I said, phooey, I am not upgrading unless I can get back to 1 line of code again. I mean there are really two steps and multiple lines of code to the new blocks and I saw that as too much work. So let's compare what you need to do to get a recordset from a database.
First the SQLHelper way:
Dim dr As SqlDataReader = SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, _
"spGetMyRecords", New SqlParameter("@RecordId", RecordId))
Pretty nice and simple, 1 line of code! Now let's see how the Enterprise Library wants us to do the same thing:
' Create the Database object, using the default database service. The
' default database service is determined through configuration.
Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database _
= DatabaseFactory.CreateDatabase()
Dim dbCommandWrapper As DBCommandWrapper = _
db.GetStoredProcCommandWrapper("spGetMyRecords")
If Not IsNothing(arParam) Then
Dim aParam As SqlParameter
'Pass the Parameters to the Array
'Note to reader you will see where this comes from later. I have basically passed an array of parameters to this method.
For Each aParam In arParam
dbCommandWrapper.AddInParameter(aParam.ParameterName, aParam.DbType, aParam.Value)
Next
End If
' DataReader that will hold the returned results
Dim dr As SqlDataReader = Nothing
dr = db.ExecuteReader(dbCommandWrapper)
Yikes! Where did my simple 1 line call go? I saw this and said to heck with this, I have work to do. This does not look any better than just doing it the long hand way (see most tutorials), and after all when you have a calculator, why do complicated division by hand?
Well after a year I have finally softened my heart, and wrote my own wrapper functions! Now I am back to one line of code! I have a BaseController class that I use to derive any class that interacts with the database, typically these are created for each table in the database. In this class I created a series of functions to handle common routines I do over and over. I finally broke down and spent about a half hour or so yesterday figuring out how to configure the Enterprise Library Data Application Blocks, and the basic methodology. Then I spent another half hour or so creating these overloaded methods to allow me to use the Enterprise Library Application Blocks and doing it using 1 line of code.
So now getting my recordset looks something like this:
Dim dr As SqlDataReader = ExecuteReader("spGetMyRecords", CommandType.StoredProcedure, _
New SqlParameter("@RecordId", RecordId))
I hope this will help you become a more pragmatic programmer and leverage a really cool and tested library to interact with your database(s). These are my starter methods; I will most likely expand these as needed in the future.
#Region " Data Application Block Handlers "
Public Function ExecuteReader(ByVal sqlCommand As String, _
ByVal ct As CommandType) As SqlDataReader
Return ExecuteReader(sqlCommand, ct, Nothing)
End Function
Public Function ExecuteReader(ByVal sqlCommand As String, ByVal ct As CommandType, _
ByVal arParam() As SqlParameter) As SqlDataReader ' Create the Database object, using the default database service. The
' default database service is determined through configuration.
Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database _
= DatabaseFactory.CreateDatabase()
Dim dbCommandWrapper As DBCommandWrapper = _
db.GetStoredProcCommandWrapper(sqlCommand)
If Not IsNothing(arParam) Then
Dim aParam As SqlParameter
'Pass the Parameters to the Array
For Each aParam In arParam
dbCommandWrapper.AddInParameter(aParam.ParameterName, aParam.DbType, aParam.Value)
Next
End If
' DataReader that will hold the returned results
Dim dr As SqlDataReader = Nothing
dr = db.ExecuteReader(dbCommandWrapper)
Return dr
End Function
Public Function ExecuteDataSet(ByVal sqlCommand As String, _
ByVal ct As CommandType) As DataSet
Return ExecuteDataSet(sqlCommand, ct, Nothing)
End Function
Public Function ExecuteDataSet(ByVal sqlCommand As String, _
ByVal ct As CommandType, ByVal arParam() As SqlParameter) As DataSet
' Create the Database object, using the default database service. The
' default database service is determined through configuration.
Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database _
= DatabaseFactory.CreateDatabase()
Dim dbCommandWrapper As DBCommandWrapper = _
db.GetStoredProcCommandWrapper(sqlCommand)
If Not IsNothing(arParam) Then
Dim aParam As SqlParameter
'Pass the Parameters to the Array
For Each aParam In arParam
dbCommandWrapper.AddInParameter(aParam.ParameterName, aParam.DbType, aParam.Value)
Next
End If
' DataReader that will hold the returned results
Dim ds As DataSet = Nothing
ds = db.ExecuteDataSet(dbCommandWrapper)
Return ds
End Function
Public Function ExecuteNonQuery(ByVal sqlCommand As String, _
ByVal ct As CommandType)
ExecuteNonQuery(sqlCommand, ct, Nothing)
End Function
Public Function ExecuteNonQuery(ByVal sqlCommand As String, ByVal ct As CommandType, _
ByVal arParam() As SqlParameter)
' Create the Database object, using the default database service. The
' default database service is determined through configuration.
Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database _
= DatabaseFactory.CreateDatabase()
Dim dbCommandWrapper As DBCommandWrapper = _
db.GetStoredProcCommandWrapper(sqlCommand)
If Not IsNothing(arParam) Then
Dim aParam As SqlParameter
'Pass the Parameters to the Array
For Each aParam In arParam
dbCommandWrapper.AddInParameter(aParam.ParameterName, aParam.DbType, aParam.Value)
Next
End If
db.ExecuteNonQuery(dbCommandWrapper)
End Function
Public Function ExecuteScalar(ByVal sqlCommand As String, ByVal ct As CommandType)
Return ExecuteScalar(sqlCommand, ct, Nothing)
End Function
Public Function ExecuteScalar(ByVal sqlCommand As String, ByVal ct As CommandType, _
ByVal arParam() As SqlParameter)
' Create the Database object, using the default database service. The
' default database service is determined through configuration.
Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database _
= DatabaseFactory.CreateDatabase()
Dim dbCommandWrapper As DBCommandWrapper = _
db.GetStoredProcCommandWrapper(sqlCommand)
If Not IsNothing(arParam) Then
Dim aParam As SqlParameter
'Pass the Parameters to the Array
For Each aParam In arParam
dbCommandWrapper.AddInParameter(aParam.ParameterName, aParam.DbType, aParam.Value)
Next
End If
Dim ret As Integer = db.ExecuteScalar(dbCommandWrapper)
Return ret
End Function
#End Region