Powershell insert object into SQL Server database more elegant solution -

i have array of objects ($itemobjectarray), each 1 having these properties , values so:

id          : 1234 location    : usa price       : $500 color       : blue 

i have sql server 2012 table called items column names match object property names so:

table items (    id    location    price    color    primary key (id) ) 

i have created following code insert object sql server table. think code little crude. there more elegant way since column names match object property names?

$itemobjectarray | %{ #step through array     #get property names, match column names     $names = $_ | get-member -membertype properties       foreach($name in $names.name){ #make sql string column names          $cols += $name + ","     }      $cols = $cols.trimend(",") #get rid of last comma      foreach($name in $names.name){         #step through properties, values , build values string         $vals += "'" + $_.$($name) + "',"      }      $vals = $vals.trimend(",") #get rid of last comma      $sqlcommand.commandtext = "insert items ($cols) values ($vals)"     $sqlcommand.executenonquery() #insert      $cols = $null #wipe     $vals = $null } 

you should use prepared statements.

function insertobject($object, $table) {     # set key , value placeholder string.     $ks = ($object.keys | % { "[$_]" }) -join ", "     $vs = ($object.keys | % { "@$_"  }) -join ", "      # $databaseconnection global variable     $command = $databaseconnection.createcommand()     $command.commandtext = "insert $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 , suppress output     [void]$command.executenonquery() }