Rocky Lhotka had this over on his site:

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

Most webservers will compress data if the clients support it.

Unfortunately for .net developers the auto generated proxy classes that access webservices don’t include this functionality.

I’ll show you how to achieve this while avoiding changing any code in the auto generated proxy classes.

(These classes utilize ICSharpCode.SharpZipLib.dll so you’ll have to download this)

This following class will deal with decompressing a response.

Imports System.Net
Imports ICSharpCode.SharpZipLib

Public Class UncompressedGzipResponse
    Inherits System.Net.WebResponse

    Private cCompressedGzipResponse As HttpWebResponse

    Public Sub New(ByVal compressedGzipResponse As HttpWebResponse)
        MyBase.New()
        cCompressedGzipResponse = compressedGzipResponse
    End Sub


    Public Overrides Function GetResponseStream() As System.IO.Stream

        Dim compressedStream As System.IO.Stream = New GZip.GZipInputStream(cCompressedGzipResponse.GetResponseStream)
        Dim decompressedStream As New System.IO.MemoryStream
        Dim size As Integer = 2048
        Dim writeData(2048) As Byte
        While True
            size = compressedStream.Read(writeData, 0, size)
            If size > 0 Then
                decompressedStream.Write(writeData, 0, size)
                Dim s As String = System.Text.Encoding.ASCII.GetString(writeData)
                s = s & ""
            Else
                Exit While
            End If
        End While
        decompressedStream.Seek(0, IO.SeekOrigin.Begin)

        Return CType(decompressedStream, IO.Stream)

    End Function

    Public Overrides Property ContentType() As String
        Get
            Return cCompressedGzipResponse.ContentType
        End Get
        Set(ByVal value As String)
            cCompressedGzipResponse.ContentType = value
        End Set
    End Property

    Public Overrides Property ContentLength() As Long
        Get
            Return cCompressedGzipResponse.ContentLength
        End Get
        Set(ByVal value As Long)
            cCompressedGzipResponse.ContentLength = value
        End Set
    End Property

    Public Overrides ReadOnly Property Headers() As System.Net.WebHeaderCollection
        Get
            Return cCompressedGzipResponse.Headers
        End Get
    End Property

End Class

This next class will handle most of the logic so that your proxy class can be as small as possible:

Public Class WebServiceCompressionHelper
    Public Shared Function GetWebRequest(ByVal uri As System.Uri, ByVal output As WebRequest, ByVal compress As Boolean) As WebRequest

        '*** add gzip
        If compress Then
            output.Headers.Item(Net.HttpRequestHeader.AcceptEncoding) = "gzip"
        End If

        Return output
    End Function

    Public Shared Function GetWebResponse(ByVal output As WebResponse, ByVal request As System.Net.WebRequest) As WebResponse

        Dim compressedResponse As HttpWebResponse = Nothing
        If TypeOf output Is HttpWebResponse Then
            compressedResponse = CType(output, HttpWebResponse)
        End If

        If compressedResponse IsNot Nothing AndAlso compressedResponse.ContentEncoding = "gzip" Then
            output = New UncompressedGzipResponse(compressedResponse)
        End If
        Return output
    End Function
End Class

In order to make use of this we need to tap into a few methods in the auto generated proxy.  The easiest way to do this is to extend the proxy class into your own subclass, like so:

Public Class MyService
    Inherits generated.proxy.class.here
    Protected Overrides Function GetWebRequest(ByVal uri As System.Uri) As System.Net.WebRequest
        Return Walshgroup.Webservices.Common.WebServiceCompressionHelper.GetWebRequest(uri, MyBase.GetWebRequest(uri), True)
    End Function
    Protected Overrides Function GetWebResponse(ByVal request As System.Net.WebRequest) As System.Net.WebResponse
        Dim output As System.Net.WebResponse = MyBase.GetWebResponse(request)
        Return Walshgroup.Webservices.Common.WebServiceCompressionHelper.GetWebResponse(output, request)
    End Function
End Class

Now, instead of instantiating your generated proxy class, you will simply instantiate your subclass.

That’s it!