Sql query result as an array of objects powershell

Question

I'm trying to operate with the result of this query to then run an update query on specific values of the result. What i'm trying to do is to get all the values from the table and then check if those values are between 1 and 5 and turn those to null. Since i can't do this in one update query, i'm doing first a select and then operate on the singular values that i get from the result, but the query returns me a dataset result which i can't operate with in PowerShell (or at least i don't know how). What can i do? The main objective of this should be an update to all the columns of the table on the db to change the columns with values between 1 and 5 and turn them into null values Here is the code:

$SQLServer = "Server\SQLEXPRESS"
$SQLDBName = "Prova"
$SqlQuery = "Select * from table_2 where id=1"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; trusted_connection=true;"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$Dataset = New-Object System.Data.DataSet
$SqlAdapter.Fill($Dataset)
$array=$Dataset.Tables[0]
$SqlConnection.Close()

HINT: DataSet has Tables which also has Rows which also have Columns... — Shaneis
– Shaneis, Commented Apr 5, 2018 at 13:01
You might want to take a look at the 'Invoke-SQLCmd' in the 'sqlserver' module. It helps with small jobs where you don't feel like creating all the ADO.NET objects yourself. This is totally something you can do in SQL Server. I don't know exactly what you're changing. Here is a stab: CREATE TABLE dbo.table_2 (id tinyint NULL); INSERT INTO dbo.table_2 (id) VALUES (1), (2), (6), (5), (7); UPDATE dbo.table_2 SET id = Null OUTPUT deleted.id WHERE id BETWEEN 1 and 5; — Adam
– Adam, Commented Apr 5, 2018 at 14:59
i'll try to explain, pratically i have a table with 10 columns and 1 row. The row as all the columns filled with numbers. What i need to do is to take the numbers from that row, check if the numbers are between a range of my choice (example: range between 1 and 5) and change those numbers who respect the statement with NULL. Since i cant do all that in one query, i doing first a select and then checking all numbers to then put it back to do db with an update — JustNejih
– JustNejih, Commented Apr 5, 2018 at 15:08

CodeNagi · Accepted Answer · 2018-04-06 09:56:50Z

A fellow few-months old newbie here(me), ill try to give this a shot!

You can actually loop through the rows of the dataset you have, and access the properties (columns) in those rows, modify it and then dynamically create an update statement and execute it on your server.

The main part is presented below, the rest are just the functions i defined myself. Not sure if this is what you had in mind but my testing setup went something like this. (Note please execute/define the functions first in your powershell session before you run the code below)

 # SET VARIABLES
        $Serv = <Your Server>
        $DB   = <Your DB>
        $TSQL = "SELECT * FROM TestTBL"


# Target Results table from SQL
    $MainResultsTable = (GetSQLData $Serv $DB $TSQL).Tables[0]

#Get Column names
    $Colnames = ($MainResultsTable.Rows | gm -MemberType NoteProperty,Property).Name


# Loop through each row of data from SQL results
    foreach($row in $MainResultsTable.Rows)
    {
        # Construct the TSQL update statement. Using an array to construct the multi column updates.
            $TSQLUpdate = "UPDATE TestTBL SET "
            $TSQLUpdateArr =@()
            foreach($Col in $Colnames)
            {
                # We don't need to update the ID
                    if($Col -ne 'ID')
                    {
                        $TSQLUpdateArr += "$Col = $(EvaluateColumnData $row.$Col)`n" 
                    }
            }
            # join the columns with the corresponding end of TSQL where the target ID is specified
                $TSQLUpdate += $($TSQLUpdateArr -join ",").ToString() + " WHERE ID = $($row.ID);"

            # Execute the update on SQL server
             UpdateSQL $Serv $DB $TSQLUpdate
    }

Putting a few snippets of the functions I wrote for SQL here too. [Open to optimization and critics to make this faster or more 'semanticy']

# Define custom user function to set the values to be used for updating
function EvaluateColumnData()
{
    param( $data )

    if($data -le 5){ return "NULL" }
    else { return $data }
}

# Get data from SQL
function GetSQLData()
{
    param( $tgtServ,$tgtDB,$tgtTSQL )
    # Create connection obj
    $SqlConnection                  = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "server="+$tgtServ+";database="+$tgtDB+";trusted_connection=true;"

    # Open SQL connection
    $SqlConnection.open()

    # Create TSQL CMD object and pass the connection object
    $SQLCommand                     = New-Object System.Data.SQLClient.SQLCommand
    $SQLCommand.Connection          = $SqlConnection

    # TSQL statement to be executed
    $SQLCommand.CommandText         = $tgtTSQL
    $SQLCommand.CommandTimeOut      = 0

    # Container/adapter for SQL result
    $resultAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLCommand)

    # DataSet where the results are dumped
    $resultDS      = New-Object System.Data.DataSet
    $resultAdapter.Fill($resultDS) | Out-Null
    $SqlConnection.Close()

    return ,$resultDS
}

# Execute TSQL statement without results
function UpdateSQL()
{
    Param( $tgtServ,$tgtDB,$tgtTSQL )
    $ServerConn                  = New-Object System.Data.SQLClient.SQLConnection                               
    $ServerConn.ConnectionString = "server="+$tgtServ+";database="+$tgtDB+";trusted_connection=true;"
    $ServerConn.Open()                               
    $ServerCMD                   = New-Object System.Data.SQLClient.SQLCommand
    $ServerCMD.Connection        = $ServerConn
    $ServerCMD.CommandText       = $tgtTSQL
    $ServerCMD.CommandTimeOut    = 0
    $ServerCMD.ExecuteNonQuery() | out-null
    $ServerConn.Close() 
}

Hope this helps. There are a lot of things out there you can read(which im still reading lol) which offers better explanation, I suggest focusing on the basics.

Recommended reading: DataTables, PS objects/Custom objects, hashtable, Functions.

After a little bit of research i find out that i cant actually do all of this with a simple update query. At first i thought i couldnt do that since there were to many values to check but then i found that i could use the switch statemant with the case added to the update query to work through the rows and values. Thanks to everyone for the help

Collectives™ on Stack Overflow

Sql query result as an array of objects powershell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related