Increment variable in Foreach-Object

Question

I want insert some AD attributes with PowerShell into a SQL table. So far so good:

$insert = @'
    INSERT INTO [mdb].[dbo].[import](id,userid)
    VALUES ('{0}','{1}')
'@
 
Try {
    
    $connectionString = 'Data Source=serverdb;Initial Catalog=mdb;Integrated Security=SSPI'
    $conn = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $conn.Open()
    $cmd = $conn.CreateCommand()

    $counter = 0

    Get-ADUser -Filter * -SearchBase "OU=company,DC=company,DC=state,DC=de" | Select @{Name="ID";Expression={ $global:counter; $global:counter++ }},SamAccountName |`
        ForEach-Object  {           
            $cmd.CommandText = $insert -f $counter,$_.SamAccountName
            $cmd.ExecuteNonQuery()
        }

    $conn.Close()
}
Catch {
    Throw $_
}

The output from get-ADUser is right, but the insert throws an error, that the primary key has duplicates. The incrementing must be wrong.

Can anybody help? THANKS!

Your query is open to SQL Injections. Use parameters for your query, not string manipulation. — alroc, Nov 05 '20 at 13:08

score 0 · Answer 1 · answered Nov 05 '20 at 10:47

0

ID is starting from 1 and not 0, is this normal ?

Furthermore, why are you creating an user defined property (ID) and don't use it ?

You can avoid the ugly global scope too (starting counter from 0 here) :

#### $counter = 0     <--- No more usefull

Get-ADUser -Filter * -SearchBase "OU=company,DC=company,DC=state,DC=de" | Select SamAccountName |`
ForEach-Object -Begin { $counter = 0 } -Process {
            $cmd.CommandText = $insert -f $counter,$_.SamAccountName
            $cmd.ExecuteNonQuery()
            $counter++
            }

I am not an SQL specialist, so this is probably not an anwser. But, you have here a nicer code ;)

answered Nov 05 '20 at 10:47

CFou

978
3
13

Hi, thanks. Thats the way i used before. It starts with 0 but for the further entries it seems that is is not incrementing: Violation of PRIMARY KEY constraint ... Cannot insert duplicate key in object '.The duplicate key value is (0). – Hunni15 Nov 05 '20 at 12:12
When you run text formatting only (`$insert -f $counter,$_.SamAccountName`) and `$counter++`, the string result is incrementing well. Have you tried a debug to check if the CommandText is valid before `$cmd` executes the request ? I'm afraid I cannot help more about this – CFou Nov 05 '20 at 12:47
1

I guess that the key(s) simply still exist, you better fix this from the SQL side and [Auto increment primary key in SQL Server](https://stackoverflow.com/a/10992074/1701026) (or is this already on, and you also try to manually set this?) – iRon Nov 05 '20 at 12:54

marsze · Answer 2 · 2020-11-05T13:10:04.657

You should always use parameters when inserting data with SQL. (Why?) In short: It's more secure, more performant, more robust and easier to use.

Put parentheses around the operation (++$counter) to return the value after increasing it. (Use $counter++ if you want zero-based ids, ++$counter if you want 1-based ids.)

$cmd.CommandText = "
    INSERT INTO [mdb].[dbo].[import](id,userid)
    VALUES (@id, @userId)
"
$counter = 0
Get-ADUser -Filter * -SearchBase "OU=company,DC=company,DC=state,DC=de" | foreach {
    $cmd.Parameters.Clear()
    $cmd.Parameters.AddWithValue("id", (++$counter))
    $cmd.Parameters.AddWithValue("userId", $_.SamAccountName)
    $cmd.ExecuteNonQuery()
}

Increment variable in Foreach-Object

2 Answers2