Powershell insert object into SQL Server database more elegant solution

Question

I have an array of objects ($itemObjectArray), each one having these properties and some values like so:

ID          : 1234
Location    : USA
Price       : $500
Color       : Blue

I have a SQL Server 2012 table called Items with column names that match the object property names like so:

TABLE Items (
   ID
   Location
   Price
   Color
   PRIMARY KEY (ID)
)

I have created the following code to insert the object into the SQL Server table. I think my code is a little crude. Is there a more elegant way to do this since the column names match the object property names?

$itemObjectArray | %{ #step through array
    #get property names, which match column names
    $names = $_ | Get-Member -membertype properties 

    Foreach($name in $names.name){ #make SQL string for column names
         $cols += $name + ","
    }

    $cols = $cols.trimend(",") #get rid of last comma

    Foreach($name in $names.name){
        #step through properties, get values and build values string
        $vals += "'" + $_.$($name) + "'," 
    }

    $vals = $vals.trimend(",") #get rid of last comma

    $sqlCommand.CommandText = "INSERT INTO Items ($cols) VALUES ($vals)"
    $sqlCommand.ExecuteNonQuery() #insert

    $cols = $Null #wipe
    $vals = $Null
}

This might receive more feedback on the CodeReview site. StackOverflow is for assistance with programming questions (my code errors and I'm unsure how to fix it) — user4317867, Jun 11 '16 at 01:46
I would use something like [this](https://gallery.technet.microsoft.com/scriptcenter/Import-Large-CSVs-into-SQL-216223d9). — Bacon Bits, Jun 11 '16 at 19:17

score 1 · Answer 1 · edited May 23 '17 at 10:29

You should use prepared statements.

function InsertObject($object, $table)
{
    # set up key and value placeholder string.
    $ks = ($object.keys | % { "[$_]" }) -join ", "
    $vs = ($object.keys | % { "@$_"  }) -join ", "

    # $DatabaseConnection is a global variable
    $command = $DatabaseConnection.CreateCommand()
    $command.CommandText = "INSERT INTO $table ($ks) VALUES ($vs);"

    foreach ($key in $object.Keys)
    {
        $value = $object[$key]
        if ($value -eq $null) {
            $value = [DBNull]::Value
        }

        [void]$command.Parameters.AddWithValue("@$key", $value)
    }

     # exec statement and suppress output
    [void]$command.ExecuteNonQuery()
}

Powershell insert object into SQL Server database more elegant solution

1 Answers1