How to loop over the result (system.Data.DataSet object) of SQL run from PowerShell

Question

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.

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.

The code is:

        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];

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

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?

gnud · Accepted Answer · 2018-05-18 20:44:19Z

1

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.

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.

Try this SQL:

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

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

answered May 18, 2018 at 20:44

gnud

78.9k5 gold badges68 silver badges79 bronze badges

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

5 Comments

Bacon Bits Over a year ago

COUNT(<thePrimaryKeyColumnOfTheTable>) No, you should just use COUNT(*).

300 Over a year ago

Hi gnud. Thank you for your suggested solution and other more important highlights related to making it better solution. Unfortunately, my problem is that this script will be used in an internal tool that expects output in a specific format: Single row with at most 2 columns. In order to avoid creating separate script for each row of SQL1's result, I am using a feature. Per this feature, I can loop over the result of 1st sql and run 2nd SQL for each loop iteration. Each run of SQL2 from loop must give result is required format: single row, max 2 columns :-(

gnud Over a year ago

Well, instead of looping though the results of SQL1, you can loop through the results on my sql. For each row, you can output the two columns.

300 Over a year ago

Thank you again. Yes I think that is something that'll work. I am trying to find how it can be implemented using PowerShell and will update here the outcome.

300 Over a year ago

I was able to make it work using your suggested solution with a few tweaks.

Collectives™ on Stack Overflow

How to loop over the result (system.Data.DataSet object) of SQL run from PowerShell

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related