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.

2 comments for “Care and Feeding of SQLParameters and SQLHelper

  1. Paul
    August 22, 2012 at 1:58 pm

    Is it possible to allow open-ended declartion of the array size, instead of providing the count?

    so instead of
    SqlParameter[] arParams = new SqlParameter[11];

    perhaps something like
    SqlParameter[] arParams = new SqlParameter[];

    Miscounting can be such a pain in the butt (I’ve got some of these with 20-40 parameters being fed in!), and the ability to not have to declare the count would rock.

    • Cliff Richardson
      September 19, 2012 at 7:05 pm

      Paul,

      sorry for the really late reply. I must have missed the notification.

      As far as keeping the array count dynamic; I have not found a way to make the array dynamic. So far I’ve found the SQLParameter collection is pretty touchy, in that it has no variations. I just tried to create a dynamic array and it doesn’t allow to insert items at all, they have to be previously declared.

      You still have to type out the physical variable names any way so I’m not sure you’re going to get around this in a way that isn’t going to cause you more work.

      -Cliff

Leave a Reply