Apr 24

Read SQL tables and its content using PowerShell

When I first started with PowerShell, I mostly used to export my content to CSV files, and then work with these, but after a while, I figured it would be best to store the data in a SQL Database.

Using powershell to import the data to my tables is a lot better than having csv files all over the place 🙂

Importing the data is one thing, but using it is also very important, so I made two functions to read the tables I have in my database, and one to get me the  information in the table using dynamic parameter.
By using a dynamic parameter, I dont have to know the table name, as it selects it from the list of available tables in my database.

First you need to connect to your SQL server and database:
(Local SQL Express installation is what I am running)

$DBServer = "$env:UserDomain\SQLEXPRESS"
$DBName = "TmJr"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True;"

After connecting to my Database I can use the two functions created to get my information:

Function Get-Tables {
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlConnection.Open()
$sqlCommand.CommandText = "SELECT * FROM sys.Tables"
$tempSQL = $sqlCommand.ExecuteReader()
$Global:Table=New-Object System.Data.DataTable
$Global:Table.Load($tempsql)
$Global:Table | Select-Object Name, create_date,modify_Date,type_desc
$sqlConnection.Close()
}

This would give me a list looking like this:

 

 

 

 

And to work with the information within one of the tables, I would use my second function Get-TableData

Function Get-TableData {
[CmdletBinding()]
param
()

## Create the Customer Collection (remmeber dynamic parameters needs to expand the property when creating the collection.)

DynamicParam {
# Set the dynamic parameters' name
$ParameterName = 'Table'

# Create the dictionary
$RuntimeParameterDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary

# Create the collection of attributes
$AttributeCollection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]

# Create and set the parameters' attributes
$ParameterAttribute = New-Object System.Management.Automation.ParameterAttribute
$ParameterAttribute.Mandatory = $true
$ParameterAttribute.Position = 1

# Add the attributes to the attributes collection
$AttributeCollection.Add($ParameterAttribute)

# Generate and set the ValidateSet
$arrSet = Get-Tables | Select-Object -expandproperty Name
$ValidateSetAttribute = New-Object System.Management.Automation.ValidateSetAttribute($arrSet)

# Add the ValidateSet to the attributes collection
$AttributeCollection.Add($ValidateSetAttribute)

# Create and return the dynamic parameter
$RuntimeParameter = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName, [string], $AttributeCollection)
$RuntimeParameterDictionary.Add($ParameterName, $RuntimeParameter)
return $RuntimeParameterDictionary
}

begin {
# Bind the parameter to a friendly variable
$Global:Table = $PsBoundParameters[$ParameterName]
}

process {
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlConnection.Open()

$sqlCommand.CommandText = "SELECT * FROM $Global:Table"
$tempSQL = $sqlCommand.ExecuteReader()
$Global:TableData=New-Object System.Data.DataTable
$Global:TableData.Load($tempsql)
$Global:TableData
$sqlConnection.Close()

}
}

If you would read more about Dynamic paramters in PowerShell, I recommend this blogpost:
https://blogs.technet.microsoft.com/pstips/2014/06/09/dynamic-validateset-in-a-dynamic-parameter/

Apr 13

The new Windows Admin Center

The new Windows Admin Center (former Project Honolulu)

Apr 13

PowerShell script to export events to screen and or to file from one or multiple machines

https://blogs.technet.microsoft.com/samdrey/2018/04/13/powershell-script-to-export-events-to-screen-andor-to-file-from-one-or-multiple-machines/

Apr 13

Installing and Configuring Domain Controllers with Windows Server 2016 Core

Installing and Configuring Domain Controllers with Windows Server 2016 Core

Apr 08

5 Youtube channels to learn and adapt in the IT World

https://blog.devolutions.net/2017/10/5-youtube-channels-to-learn-and-adapt-in-the-it-world

Apr 04

How can I install System Center Configuration Manager (Current Branch) version 1802 on Windows Server 2016 with SQL Server 2017 – Part 1

https://www.windows-noob.com/forums/topic/16114-how-can-i-install-system-center-configuration-manager-current-branch-version-1802-on-windows-server-2016-with-sql-server-2017-part-1/?tab=comments#comment-61281

Apr 04

How can I install System Center Configuration Manager (Current Branch) version 1802 on Windows Server 2016 with SQL Server 2017 – Part 2

https://www.windows-noob.com/forums/topic/16116-how-can-i-install-system-center-configuration-manager-current-branch-version-1802-on-windows-server-2016-with-sql-server-2017-part-2/

Apr 03

A new command to find all of your SQL instances

a new command to find all of your sql instances

Mar 22

Recent updates to Project Honolulu and PowerShell Core 6.0

https://cloudblogs.microsoft.com/windowsserver/2018/03/21/recent-updates-to-project-honolulu-and-powershell-core-6-0/?utm_source=t.co&utm_medium=referral

Mar 17

Using O365 APIs and Powershell to access Intune messages.

Using O365 APIs and PowerShell to access Intune messages from Message center in Office 365 (Part 1)