Powershell and SQL parameters. If empty string, pass DBNull

Question

Hi,

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.

Answer 1

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 2

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

Answer 3

can someon help me conver this into powershell:

con = new SqlConnection("Data Source=testserver1; Initial Catalog=Igors_Test; Integrated Security=SSPI"); cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "iz_sp_InsertStudent";

        cmd.Parameters.Add(new SqlParameter("@status", SqlDbType.VarChar, 50)).Value = txtFN.Text;
        cmd.Parameters.Add(new SqlParameter("@Message", SqlDbType.VarChar, 50)).Value = txtLN.Text;

        con.Open();

        cmd.ExecuteNonQuery();