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
– user4317867, Commented Jun 11, 2016 at 1:46

Community · Accepted Answer · 2017-05-23 10:29:50Z

1

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

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Mar 2, 2017 at 11:53

pasbi

2,1911 gold badge23 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell insert object into SQL Server database more elegant solution

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related