Powershell and SQL parameters. If empty string, pass DBNull

Question

I got this parameter:

$objDbCmd.Parameters.Add("@telephone", [System.Data.SqlDbType]::VarChar, 18) | Out-Null;
$objDbCmd.Parameters["@telephone"].Value = $objUser.Telephone;

Where the string $objUser.Telephone can be empty. If it's empty, how can I convert it to [DBNull]::Value?

I tried:

if ([string]:IsNullOrEmpty($objUser.Telephone)) { $objUser.Telephone = [DBNull]::Value };

But that gives me the error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Failed to convert parameter value from a ResultPropertyValueCollection to a String."

And if I convert it to a string, it inserts an empty string "", and not DBNull.

How can this be accomplished?

Thanks.

Iowa

Answer 1

In PowerShell, you can treat null/empty strings as a boolean.

$x = $null
if ($x) { 'this wont print' }

$x = ""
if ($x) { 'this wont print' }

$x = "blah"
if ($x) { 'this will' }

So.... having said that you can do:

$Parameter.Value = $(if ($x) { $x } else { [DBNull]::Value })

But I'd much rather wrap this up in a function like:

function CatchNull([String]$x) {
   if ($x) { $x } else { [DBNull]::Value }
}

Find

Very interesting. Thanks Josh. I'll test this later today and then I'll let you know if it solved my problem. – Iowa

Answer 2

I don't know about powershell, but in C# I would do something like this:

if ([string]::IsNullOrEmpty($objUser.Telephone))
{
 $objDbCmd.Parameters["@telephone"].Value = [DBNull]::Value;
}
else
{
 $objDbCmd.Parameters["@telephone"].Value = $objUser.Telephone;
}

Bunko

That does work in PowerShell (with one minor tweak - edited to call a static member using PowerShell syntax). That is what I've done in several scripts and it works properly. – Carlyle

That would be a solution yes. But I was hoping for a "simpler" solution that doesn't require that much code. Like a function that converts an empty string to DBNull. I got like 60 parameters... – Iowa

Answer 3

Always append +"" at the end of db values...

$command.Parameters["@EmployeeType"].Value= $ADResult.EmployeeType + ""

Acetone

Answer 4

Many years later, let me clarify:

Josh's answer shows a helpful simplification for testing strings for emptiness (relying on PowerShell's implicit to-Boolean conversion^[1]), but it is unrelated to Tommy's (the OP's) problem.

Instead, the error message

"Failed to convert parameter value from a ResultPropertyValueCollection to a String."

implies that it is the non-null case that caused the problem, because $objDbCmd.Parameters["@telephone"].Value expects either a string value or [DBNull]::Value, whereas $objUser.Telephone is of type [ResultPropertyValueCollection], i.e. a collection of values.

Thus, in the non-null case, a string value must be assigned, which must be derived from the collection; one option is to take the first collection element's value, another would be to join all values with a separator to form a single string, using, e.g., [string]::Join(';', $objUser.Telephone) or, if joining the elements with spaces is acceptable (not a good idea with multiple phone numbers), simply with "$($objUser.Telephone)".^[2]

Detecting an empty collection via [string]:IsNullOrEmpty() actually worked, despite the type mismatch, due to how PowerShell implicitly stringifies collections when passing a value to a [string] typed method parameter.^[2]

Similarly, using implicit to-Boolean conversion works as expected with collections too: an empty collection evaluates to $false, a non-empty one to $true (as long as there are either at least two elements or the only element by itself would be considered $true^[1])

Therefore, one solution is to use the first telephone number entry:

$objDbCmd.Parameters["@telephone"].Value = if ($objUser.Telephone) {
    $objUser.Telephone[0].ToString()  # use first entry
  } else {
    [DBNull]::Value
  }

^{Note: If $objUser.Telephone[0] directly returns a [string], you can omit the .ToString() call.}

In PowerShell v7+ you can alternatively shorten the statement via a ternary conditional:

$objDbCmd.Parameters["@telephone"].Value =
  $objUser.Telephone ? $objUser.Telephone[0].ToString() : [DBNull]::Value

---

^{[1] For a comprehensive summary of PowerShell's automatic to-Boolean conversions, see the bottom section of this answer.}

^{[2] When implicitly converting a collection to a string, PowerShell joins the stringified elements of a collection with a single space as the separator by default; you can override the separator with the automatic $OFS variable, but that is rarely done in practice; e.g., array 'foo', 'bar' is converted to 'foo bar'; note that this conversion does not apply when you call the collection's .ToString() method explicitly, but it does apply inside expandable (interpolating) strings, e.g., "$array".}

Tonnie