sql-server - Powershell将对象插入SQL Server数据库更优雅的解决方案

Question

我有一个对象数组($itemObjectArray)，每个对象都有这些属性和一些值，如下所示:

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

我有一个名为 Items的SQL Server 2012表，其列名称与对象属性名称匹配，如下所示:

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

我创建了以下代码，将对象插入SQL Server表中。我认为我的代码有点粗糙。由于列名与对象属性名匹配，是否有更优雅的方法？

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

Answer 1

您应该使用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()
}