How to add a SQL update statement to powershell after a foreach loop

Question

I'm a noob with PowerShell. and have a question. I have a working PS1 script that runs a SQL query to get a ID parameter, then creates a PDF from a Crystal Report, form another program based on that returned SQL query and then, loops through the returned parameters and repeats. What I need is to add an SQL update statement to the PS1 script, that updates a table based on the same ID that is returned from the SQL query, after the PDF is created. Before moving on to the next ID, or after the script creates all the PDF's based on the Id's. This is what I need to add to the script.

Update DB2.dbo.table2 
SET DB2.dbo.table2.Field_01 = 1 
FROM DB2.dbo.table2 
WHERE (DB2.dbo.Table2.ID = {ID})

The SP1 script that works looks like this.

    $serverName="MAIN"
    $databaseName="DB1"
    $sqlCommand="select ID from ID_Query"

        $connectionString = "Data Source=$serverName; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$databaseName" 

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    $connection.Close()



                foreach ($Row in $dataSet.Tables[0].Rows)
                { 
                                $commandLine= -join(@'
"C:\Program Files (x86)\CR_Program\Program_name\CR_Printer.exe" -report="C:\Crystal_report.rpt" -exportformat=PDF -exportfile="c:\test\{Parameters.ID}.pdf" -parameters"
'@,$($Row[0]),@'
"
'@)
     cmd /c $commandLine
  }

I hoping to mark a column field_01 to 1, so the script does not create another Crystal repoort.pdf for the same ID, as the ID will be marked to 1 then the query that runs wont see it.

or maybe there a better way to do this.

Thanks.

Fourat · Accepted Answer · 2019-03-29 15:01:30Z

1

You can add a timestamp to the filename along with the first field of your DataTable (query results) supposing it's an ID :

# After $connection.Close()
$crPrinter = "C:\Program Files (x86)\CR_Program\Program_name\CR_Printer.exe"
$report = "-report='C:\Crystal_report.rpt'"
foreach ($Row in $dataSet.Tables[0].Rows)
{ 
    $now = [DateTime]::Now.ToString("yyMMddHHmmss")
    $outputFile = "-exportfile='c:\test\Report_id_$Row[0]_$now.pdf' -parameters"
    $commandLine= [string]::Format("{0} {1} {2}", $crPrinter, $report, $outputFile)
    cmd /c $commandLine
    Start-Sleep 1 # Sleeps the script to offset a second
}

answered Mar 29, 2019 at 15:01

Fourat

2,4476 gold badges41 silver badges60 bronze badges

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

Collectives™ on Stack Overflow

How to add a SQL update statement to powershell after a foreach loop

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