Inserting PowerShell values in array into SQL Server table in one connection

Question

If I want to insert the values in the array in one connection, how can i modify the code below? So far i get the error "There is already an open DataReader associated with this Command which must be closed first" unless I put the $Connection.Open() and $Connection.Close() inside the for loop, which will cost me the speed of a single connection.

$list = 'aaa','bbb','cccc','ddddd','eeeee','ffff'
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
foreach($i in $list) {
    $sql ="if not exists (select 1 from [bfs] where [key] = '$i' ) 
        begin 
            insert bfs
            select '$i'
        end
    " 
$Command.CommandText = $sql
$Command.ExecuteReader()
}
$Connection.Close()

squillman · Accepted Answer · 2018-07-03 15:19:22Z

2

Try using ExecuteNonQuery() instead of ExecuteReader(). ExecuteNonQuery() does not build a DataReader.

...
$Command.CommandText = $sql
$Command.ExecuteNonQuery()
...

edited Jul 3, 2018 at 15:19

answered Jul 3, 2018 at 15:16

squillman

13.7k3 gold badges45 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

Inserting PowerShell values in array into SQL Server table in one connection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related