CODEWITHSUNDEEP
powershell
Cataster
Cataster

Reputation: 3521

Getting results from table sequentially in foreach?

I have the following powershell script that is supposed to run a process command on the cubes after ordering their sequence in the table

param($App_input, $Script) #%2 and %3 arguments from cmd line input;

Function Query($Query) {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=$Server;Initial Catalog=$Database;Integrated Security=SSPI" 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
$SqlCmd.Connection = $SqlConnection 
$SqlCmd.CommandText = $Query 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
$SqlAdapter.SelectCommand = $SqlCmd 
$DataSet = New-Object System.Data.DataSet 
$a=$SqlAdapter.Fill($DataSet)
$SqlConnection.Close() 
$DataSet.Tables[0] }

#$cube = Query "SELECT DISTINCT cube_name, sequence FROM [dbo].[$cubeTable] WHERE [application] = '$App_input' ORDER BY [sequence]" | Select -ExpandProperty cube_name;

$Table = Query "SELECT * from [dbo].[$cubeTable]"

                  # -ExpandProperty trims the column name, so it only outputs the value, not with header!

$App = Query "SELECT DISTINCT[application] FROM [dbo].[$cubeTable] WHERE [application] = '$App_input'" | Select -ExpandProperty application;

    $i=0
    $CUBE = @()

    foreach($row in $Table | where { $_.application -match $App })
    {   
        $i++
        $CUBE += $row.Item("cube_name")
        write-host " > CUBE $($i):" $CUBE[$CUBE -1]
        cmd /c "runPowerShell.bat $Script $CUBE"    #where $Script is Process.ps1 script that processes on each CUBE selected from table in order
    }

This is the hypothetical table:

Table

for example: if i were to pass in the app parameter and the script parameter as such:

someBatchFile.bat SSP Process.ps1

the script first should check that the loop is only returning results where [application] column = SSP, so avoiding the 3rd row in this case. however, the where clause i have $_.application -match $App for some reason is not having any effect, and the powershell returns all rows regardless...

after that check is done, the script should order the rows (from 2, 2, 1 to 1, 2, 2) returning CUBE2, CUBE1 (just one time since its distinct) which is passed in each iteration to $CUBE in the cmd line.

essentially, in this examples, the iterations should look like this:

cmd /c "runPowerShell.bat $Process.ps1 CUBE2"

cmd /c "runPowerShell.bat $Process.ps1 CUBE1"

I know order by would be helpful in this case, but i dont know how can i use that in the case of foreach and $row.Item

Upvotes: 0

Views: 822

Answers (3)

Esperento57
Esperento57

Reputation: 17462

try this

$App = Query "SELECT DISTINCT cube_name FROM [dbo].[$cubeTable] WHERE [application] = '$App_input'"
$i=0

$App | %{

    $i++
    $CUBE=$_.Item("cube_name")
    " > CUBE $i : $CUBE"
    cmd /c "runPowerShell.bat $Script $CUBE"
}

Upvotes: 0

Cataster
Cataster

Reputation: 3521

I figured it out:

I could have just done the checks in $Table

$Table = Query "SELECT * from [dbo].[$cubeTable] WHERE [application] = '$App_input' AND [active] = 'TRUE' ORDER BY [sequence]"

    $i=0
    $CUBE = @()

    foreach($row in $Table)
    {   
        $i++
        $CUBE += $row.Item("cube_name")
        write-host " > CUBE $($i): $($CUBE[-1])`n"
        cmd /c "runPowerShell.bat $Script $($CUBE[-1])" #where $Script is Process.ps1 script that processes on each CUBE selected from table in order
    }

Upvotes: 0

Kirill Pashkov
Kirill Pashkov

Reputation: 3236

Your Query functions returns DataTable type object which means $Table variable is DataTable too.

For DataTable type variable you want to use Select() method to get filtered results. Like so:

$Table.Select("application = '$App'")

Also. Check out your whole code, this things looks really wierd for me. 

param($App_input, $Script)

$App = Query "SELECT DISTINCT[application] FROM [dbo].[$cubeTable] WHERE [application] = '$App_input'" | Select -ExpandProperty application;

You are passing $application to the script to next pass it for query that should return application value from database table while that query's filter condition is equals to application value.

You could just use application value already.

Upvotes: 0

Related Questions