add property from sql data

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 5 years and 3 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
Filters
Posts: 5
Last visit: Fri Feb 14, 2020 12:43 am

add property from sql data

Post by Filters »

I need to add property to each record when I get result from sql query or add header so I can work with that data later
can some one help me how to do this just i'm new to powershell

Code: Select all

function gettables
{
    $ServerInstance = "****** "
    $Database = "**********"
    $ConnectionTimeout = 30
    $Query = "SELECT TABLE_NAME FROM ****.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';"
    $QueryTimeout = 120
    $conn = new-object System.Data.SqlClient.SQLConnection
    $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance, $Database, $ConnectionTimeout
    $conn.ConnectionString = $ConnectionString
    $conn.Open()
    $cmd = new-object system.Data.SqlClient.SqlCommand($Query, $conn)
    $cmd.CommandTimeout = $QueryTimeout
    $ds = New-Object system.Data.DataSet
    $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$da.fill($ds)
    $conn.Close()
    return $ds.Tables[0]
}
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: add property from sql data

Post by jvierra »

The results of your query has a header for each column.

Here is the safest and fastest way to get a table.

Code: Select all

function Get-Table{
    param(
        [Parameter(Mandatory)]
        $ServerInstance,
        [Parameter(Mandatory)]
        $Database
    )
    $connStr = 'Server={0};Database={1};Integrated Security=True' -f $ServerInstance, $Database
    $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';"
    $dt = New-Object System.Data.DataTable
    
    Try{
        $conn = new-object System.Data.SqlClient.SQLConnection($connStr)
        $conn.Open()
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $sql
        $rdr = $cmd.ExecuteReader()
        [void]$dt.Load($rdr)
        $conn.Close()
        $dt
    }
    Catch{
        Throw $_
    }
}
Get-Table -ServerInstance ALPHA -Database master
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: add property from sql data

Post by jvierra »

A even better way to do this is this. This will allow you to use any SQL to query any database in any server you have access to.

Code: Select all


function Get-Table{
    param(
        [Parameter(Mandatory)]
        $ServerInstance,
        [Parameter(Mandatory)]
        $Database,
        [Parameter(Mandatory)]
        $Query
    )
    $connStr = 'Server={0};Database={1};Integrated Security=True' -f $ServerInstance, $Database
    $dt = New-Object System.Data.DataTable
    
    Try{
        $conn = new-object System.Data.SqlClient.SQLConnection($connStr)
        $conn.Open()
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $sql
        $rdr = $cmd.ExecuteReader()
        [void]$dt.Load($rdr)
        $conn.Close()
        $dt
    }
    Catch{
        Throw $_
    }
}
$sql = 'SELECT * FROM INFORMATION_SCHEMA.TABLES'
Get-Table -ServerInstance ALPHA -Database master -Query $sql | 
    Where{$_.TABLE_TYPE -eq 'BASE TABLE'}
Filters
Posts: 5
Last visit: Fri Feb 14, 2020 12:43 am

Re: add property from sql data

Post by Filters »

ty for help you saved me a lot of time ;)
This topic is 5 years and 3 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked