Care and Feeding of SQLParameters and SQLHelper

I chased my tail for hours on this one, but finally found the combination that works.  Here’s the basic layout

Function GetProjects(ID as int64, Name as string)

Dim arParams As SqlParameter() = New SqlParameter(2) {}

arParams(0) = New SqlParameter("@ID", IIf(ID = 0, _
    DBNull.Value, ID))
arParams(1) = New SqlParameter("@Name", IIf(Name = "", _
    DBNull.Value, Name))

Return CType(SqlHelper.ExecuteReader(ConnectionString, _
    CommandType.StoredProcedure, _
    GetFullyQualifiedName("GetAT_LU_Positions"), _
    arParams), IDataReader)

End Function

To instantiate the SQLParameter array you must declare the number of variables available.  In this case, there are two.

To build the parameters I’ve included code to null out the variables if they are not used.  This becomes important if you are creating multiple overloads of the function (in this case the GetProjects function).

When calling the SQLHelper there are several overload possibilities.  In this case we want the overload with the following options: 1-connection, 2-command type, 3-command name, 4-SQL Parameters.  There is one version of the overload that accepts options 1,3, and 4 and will work without an error, but the problem is nothing will be returned.  Obviously, that’s a problem.

This is a very open ended scenario, but the important piece for me was getting the SQLHelper set up and properly called and this was the way to do it.  If you are implementing SQLParameters and SQLHelper and have questions for your particular routine, let me know.