如何循环从 PowerShell 运行的 SQL 的结果(system.Data.DataSet 对象)

问题描述

我正在尝试从 Power Shell(在我的 Windows 7 64 位桌面上)运行 SQL，并且远程数据库主机是 MS SQL Server 2012.

I am trying to run a SQL from Power Shell(which is on my windows 7 64 bit desktop) and the remote database host is MS SQL Server 2012.

我通过调用函数 Get-ODBC-Data 来运行 SQL1，它会给我一个字符串类型的列.它可以有未知的行数(最多 20 行).然后我将这些列值中的每一个用作第二个函数 Get-ODBC-Data-Count 的参数 ($var1).此函数中的 SQL2 Get-ODBC-Data-Count 将在 where 子句中使用 $var1 给我计数.

I am running SQL1 by calling function Get-ODBC-Data which will give me single column of type string. It can have unknown number of rows (up to 20). Then I am using each of these column values as parameter ($var1) to the second function Get-ODBC-Data-Count. The SQL2 in this function Get-ODBC-Data-Count will give me count using $var1 in where clause.

代码是:

        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{
           [parameter(Mandatory=$true)][string]$var1,
           param(
           [string]$query=$('
                        SELECT COUNT(*)
                        FROM [master].[sys].[table_name]
                                        WHERE col2 = '$($var1)'
                                        ;
        '),
           [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]
        }

$result = Get-ODBC-Data

$count_1 = Get-ODBC-Data-Count -var1 $result[0][0]
Write-Host "`$count_1[0]:" $count_1[0];
Write-Host "Message: Count of " $result[0][0] " is" $count_1[0];

$count_2 = Get-ODBC-Data-Count -var1 $result[1][0]
Write-Host "`$count_2:" $count_2[0];
Write-Host "Message: Count of " $result[1][0] " is" $count_2[0];

$count_3 = Get-ODBC-Data-Count -var1 $result[2][0]
Write-Host "`$count_3:" $count_3[0];
Write-Host "Message: Count of " $result[2][0] " is" $count_3[0];

如果我知道 SQL1 结果中的行数，则此代码有效.

This code works if I know number of rows in the result of SQL1.

我的问题:如何修改此代码以便处理 SQL1 结果中未知数量的行，并且我可以为 SQL1 的每一行调用函数 Get-ODBC-Data-Count?

My Question: How can I modify this code so unknown number of rows in result of SQL1 will be handled and I can call function Get-ODBC-Data-Count for each row of SQL1?

推荐答案

这里有很多问题.您正在构建 SQL 字符串.不要这样做！请改用 SQL 参数！你不必要地重复了很多代码.您正在使用数据表，我会避免使用，至少在 powershell 中是这样.您没有重复使用数据库连接.

There are a lot of issues here.You're building SQL strings. Don't do this! Use SQL parameters instead!You're repeating a lot of code unessescarily.You're using Data Tables, which I would avoid, at least in powershell.You're not re-using the database connection.

在使用 SQL 时，总是非常努力地避免带有内部查询的循环.尝试考虑是否可以改写 SQL.

Always try really hard to avoid loops with a query inside when working with SQL. Try and think if you can rewrite the SQL instead.

试试这个 SQL:

SELECT
col2,
COUNT(<thePrimaryKeyColumnOfTheTable>)
FROM [master].[sys].[table_name]
GROUP BY col2

这应该会给你 col2 的所有不同值的计数.

That should give you the count of all the different values of col2.

这篇关于如何循环从 PowerShell 运行的 SQL 的结果(system.Data.DataSet 对象)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！