Powershell and SQL options. If empty string, skip DBNull

Question

I got this parameter:

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

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

I tried:

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

But this gives me an error:

Calling an ExecuteNonQuery exception with arguments "0": "Failed to convert the parameter value from ResultPropertyValueCollection to string."

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

How can I do that?

Thanks.

Answer 1

In PowerShell, you can treat empty / empty lines as logical.

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

So ... said you can do:

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

But I would rather consider this as a function:

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

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; } 

Answer 3

Always add + "" at the end of db values ​​...

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