Microsoft has released several versions of their Data Access Blocks, but they started mixing all their blocks together at some point, so if you wanted to use the DAB, you had to also use the Configuration blocks, and you had to use the Encryption blocks, and those needed the logging blocks etc.
So in many of my projects I have kept with one of the earlier versions.
But, there was a problem. Basically in order to pass parameters, the easiest way to do it was in an ordinal position way, such as:
Public Shared Sub Whatever(SomeParamsHere)
Dim storedParams() As SqlParameter
storedParams = SqlHelperParameterCache.GetSpParameterSet(ConnectionString, "Whatever")
storedParams(0).Value = 1
storedParams(1).Value = 2
storedParams(2).Value = 3
storedParams(3).Value = "abc"
SqlHelper.ExecuteNonQuery(ConnectionString, _
CommandType.StoredProcedure, _
"ItProjectInfo_General_Update", _
storedParams)
End Sub
Obviously, this code is hard to read because we don't know which parameters are getting which values w/o opening up the stored procedure and looking ourselves.
So I wrote some extra code to allow of working with SqlParameters in the form of Dictionary(Of String, SqlParameter). Calling the same function would look like this:
Public Shared Sub Whatever(ByVal SomeParamsHere)
Dim storedParams() As New Dictionary(Of String, SqlParameter)
storedParams = SqlHelperParameterCache.GetSpParameterCollection(ConnectionString, "Whatever")
storedParams.Item("@id").Value = 1
storedParams.Item("@num").Value = 2
storedParams.Item("@cat").Value = 3
storedParams.Item("@name").Value = "abc"
SqlHelper.ExecuteNonQuery(ConnectionString, _
CommandType.StoredProcedure, _
"ItProjectInfo_General_Update", _
storedParams)
End Sub
This works for output parameters as well.
In order to make this work you need to edit the SqlHelper.vb code.
Inside the SqlHelperParameterCache class you need to add:
Public Overloads Shared Function GetSpParameterCollection(ByVal connectionString As String, ByVal spName As String) As System.Collections.Generic.Dictionary(Of String, SqlParameter)
Dim parameters() As SqlParameter = GetSpParameterSet(connectionString, spName, False)
Dim paramsCol As System.Collections.Generic.Dictionary(Of String, SqlParameter)
paramsCol = SqlHelper.ParamArrayToDict(parameters)
Return paramsCol
End Function
And inside SqlHelper class you need to add the following methods:
#Region " Dictionary Support Helpers "
Friend Shared Function ParamArrayToDict(ByVal params() As SqlParameter) As Dictionary(Of String, SqlParameter)
Dim dict As New System.Collections.Generic.Dictionary(Of String, SqlParameter)(StringComparer.CurrentCultureIgnoreCase)
For i As Integer = 0 To params.Length - 1
dict.Add(params(i).ParameterName, params(i))
Next
Return dict
End Function
Friend Shared Function ParamDictToArray(ByVal dict As Dictionary(Of String, SqlParameter)) As SqlParameter()
Dim params(dict.Count - 1) As SqlParameter
Dim i As Integer
For Each p As SqlParameter In dict.Values
params(i) = p
i += 1
Next
Return params
End Function
#End Region
Public Overloads Shared Function ExecuteDataset(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal commandParameters As Dictionary(Of String, SqlParameter)) As DataSet ' Execute Dataset with generic dictionary support
Dim parametersArray() As SqlParameter
parametersArray = ParamDictToArray(commandParameters)
Return ExecuteDataset(connectionString, commandType, commandText, parametersArray)
End Function
Public Overloads Shared Function ExecuteNonQuery(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal commandParameters As Dictionary(Of String, SqlParameter)) As Integer
Dim parametersArray() As SqlParameter
parametersArray = ParamDictToArray(commandParameters)
Return ExecuteNonQuery(connectionString, commandType, commandText, parametersArray)
End Function ' ExecuteNonQuery with generic dictionary support
Public Overloads Shared Function ExecuteScalar(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal commandParameters As Dictionary(Of String, SqlParameter)) As Object
Dim parametersArray() As SqlParameter
parametersArray = ParamDictToArray(commandParameters)
Return ExecuteScalar(connectionString, commandType, commandText, parametersArray)
End Function ' ExecuteScalar with generic dictionary support