Chris May

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