add property from sql data

Ask your Windows 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.
Locked
Filters
Posts: 5
Joined: Mon Nov 05, 2018 7:44 am

add property from sql data

Post by Filters » Wed Nov 28, 2018 3:13 am

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

User avatar
jvierra
Posts: 13687
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: add property from sql data

Post by jvierra » Wed Nov 28, 2018 3:30 am

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

User avatar
jvierra
Posts: 13687
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: add property from sql data

Post by jvierra » Wed Nov 28, 2018 3:39 am

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
Joined: Mon Nov 05, 2018 7:44 am

Re: add property from sql data

Post by Filters » Wed Nov 28, 2018 4:25 am

ty for help you saved me a lot of time ;)

Locked