How to check if sqlParameter is empty?

Question

I have a class, which has many "New()" functions to initiate it with various parameters. I would like to create a New() function that will init the class with no arguments. The problem is there is a "fillData" function which fills the data for the class, and takes in the stored procedure to do the work, but has a stored procedure parameter. I don't want to create a new fillData function, but would like to use the fillData function with no additional need for a stored procedure. My way of going about this was to pass an empty SqlParameter var to the fillData function, but no mater what I do, when I check the params parameter for being empty, it always seems to think there's stuff in there.

I would like to check if this "params" parameter is empty:

Public Sub New()
    'just created this so i could pass something
    'and continue using fillData as is.
    Dim p(1) As SqlParameter 

    Dim db As sqldb.command
    db = New sqldb.command(ConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString)
    fillData("r2m_getAllListingsSorted", p)
End Sub

...

Private Sub fillData(ByVal sp As String, ByVal params As SqlParameter())
    Dim db As sqldb.command
    Dim r As SqlDataReader
    db = New sqldb.command(ConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString)

    'right here, im having the trouble, seems no matter how I try to "params", 
    'the code always defaults to db.executeReader(sp, p)
    'I've tried params.Length = 0 and params.ToString = "" also.
    If params.Value Then
        r = db.executeReader(sp)
    Else
        r = db.executeReader(sp, params)
    End If

How would I modify this so i could continue to use the fillData function, without passing a SqlParameter parameter to it?

Thank you!

score 0 · Accepted Answer · answered May 23 '12 at 16:55

You can make your SqlParameter Optional and give it a default value of Nothing:

Private Sub fillData(ByVal sp As String, 
                            ByVal Optional params As SqlParameter() = Nothing)

Then call the method without the params argument:

fillData("StoredProcName")

You can then check the parameter inside the fillData method to see if it's Nothing before using it.

If params is Nothing Then
  // Do something if there's no params
Else
 // Do something if there is params
End If

Perfect! I wasn't aware of the "Optional" keyword, and always get the use of "Nothing", 0, NULL, DBNULL confused. This worked great, and is clean. Thank you! — russds, May 23 '12 at 17:22

How to check if sqlParameter is empty?

1 Answers1