如何在PowerShell中遍历SQL查询返回的DataSet结果集？

Let's adjust your script to handle an unknown number of rows (up to 20) from your initial query, replacing those hardcoded row references with a clean loop. We'll also fix a couple of small issues in your existing functions to make them more reliable.

First, Fix the Get-ODBC-Data-Count Function #

Your original function had a parameter ordering issue, and the SQL query string had quoting problems that would break when $var1 has special characters. Here's the corrected version:

function Get-ODBC-Data-Count{
    [CmdletBinding()]
    param(
        [parameter(Mandatory=$true)][string]$var1,
        [string]$query,
        [string]$username='db_user_name',
        [string]$password='db_password'
    )
    # Build the query dynamically (escape single quotes to avoid SQL errors)
    if (-not $query) {
        $escapedVar = $var1.Replace("'", "''")
        $query = "SELECT COUNT(*) FROM [master].[sys].[table_name] WHERE col2 = '$escapedVar';"
    }
    $conn = New-Object System.Data.Odbc.OdbcConnection
    $conn.ConnectionString = "DRIVER={SQL Server};Server=123.456.78.90;Initial Catalog=master;Uid=$username;Pwd=$password;"
    $conn.open()
    $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)
    $ds = New-Object system.Data.DataSet
    (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
    $conn.close()
    # Return just the count value instead of the whole DataTable for easier use
    return $ds.Tables[0].Rows[0][0]
}

Note: We added Replace("'", "''") to escape single quotes in $var1 to prevent SQL syntax errors (and basic injection risks). For better long-term security, consider using parameterized queries instead of string concatenation.

Then, Modify the Main Script to Use a Loop #

Instead of hardcoding $result[0], $result[1], etc., we'll loop through each row in the result set, and limit it to 20 rows as you specified:

# Get the initial list of values
$initialResults = Get-ODBC-Data

# Loop through each row, max 20 rows
$initialResults | Select-Object -First 20 | ForEach-Object {
    $currentValue = $_.col3  # Directly access the column by name for clarity
    $count = Get-ODBC-Data-Count -var1 $currentValue
    
    # Output the results in a readable format
    Write-Host "Count of '$currentValue' is: $count"
}

Full Modified Script #

Putting it all together, here's the complete working script:

function Get-ODBC-Data{
    param(
        [string]$query='SELECT col3 FROM [master].[sys].[table_name]',
        [string]$username='db_user_name',
        [string]$password='db_password'
    )
    $conn = New-Object System.Data.Odbc.OdbcConnection
    $conn.ConnectionString = "DRIVER={SQL Server};Server=123.456.78.90;Initial Catalog=master;Uid=$username;Pwd=$password;"
    $conn.open()
    $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)
    $ds = New-Object system.Data.DataSet
    (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
    $conn.close()
    $ds.Tables[0]
}

function Get-ODBC-Data-Count{
    [CmdletBinding()]
    param(
        [parameter(Mandatory=$true)][string]$var1,
        [string]$query,
        [string]$username='db_user_name',
        [string]$password='db_password'
    )
    if (-not $query) {
        $escapedVar = $var1.Replace("'", "''")
        $query = "SELECT COUNT(*) FROM [master].[sys].[table_name] WHERE col2 = '$escapedVar';"
    }
    $conn = New-Object System.Data.Odbc.OdbcConnection
    $conn.ConnectionString = "DRIVER={SQL Server};Server=123.456.78.90;Initial Catalog=master;Uid=$username;Pwd=$password;"
    $conn.open()
    $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)
    $ds = New-Object system.Data.DataSet
    (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
    $conn.close()
    $ds.Tables[0].Rows[0][0]
}

# Main execution logic
$initialResults = Get-ODBC-Data

# Process up to 20 rows from the initial result set
$initialResults | Select-Object -First 20 | ForEach-Object {
    $value = $_.col3
    $count = Get-ODBC-Data-Count -var1 $value
    Write-Host "Value: '$value' | Total Count: $count"
}

Key Improvements #

• Dynamic Row Handling: Uses ForEach-Object to process every row from the initial query, no hardcoding needed.
• Row Limit: Select-Object -First 20 ensures we never process more than 20 rows, as requested.
• Safer SQL Queries: Escapes single quotes in $var1 to prevent syntax errors when the value contains apostrophes.
• Cleaner Output: Returns just the count value from Get-ODBC-Data-Count instead of the entire DataTable, making the loop logic simpler.

内容的提问来源于stack exchange，提问作者300