[PowerShell] How to Retrieve Data with ADO.NET

I am primary a C# developer since 2005. Before that I was a VB5/VB6/VB.NET developer. Recent, I changed my role from development side to a consulting side. I found out that I have to do some development by using powershell script. It is because I will pass the program to client and we should try to build with script or any “no compile needed language”. And so I start writing PowerShell script. Because of this, I will write some blog about PowerShell  that I learned from my job. And this is the first one in this series.

My first PowerShell task is, retrieve data from MSSQL.

From my C# knowledge, I would like to retrieve data by using ADO.NET. So I start looking for how I could use C# library in PowerShell Script. And lucky that there is a way.

Below is my first script in PowerShell.

Function GetDataFromMSSQL {
    Write-Host "====================================="
    $sqlConn = New-Object System.Data.SqlClient.SqlConnection
    $sqlConn.ConnectionString = "Server=localhost;Integrated Security=true;Initial Catalog=AdventureWorks2019;"
    try {
        Write-Host "Data Source is connected."
        #$sqlcmd = $sqlConn.CreateCommand()
        $sqlcmd = New-Object System.Data.SqlClient.SqlCommand
        $sqlcmd.Connection = $sqlConn
        #$selectQuery = "SELECT TOP (1000) [SpecialOfferID],[Description],[DiscountPct],[MinQty],[MaxQty] FROM [AdventureWorks2019].[Sales].[SpecialOffer]"
        $sqlcmd.CommandText = $selectQuery

        $sqladp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
        $data = New-Object System.Data.DataSet
        $sqladp.Fill($data) | Out-Null

        $rowCount = $data.Tables[0].Rows.Count
        Write-Host "$rowCount rows returned."
    } catch {
        Write-Host "Data Source cannot be connected."
        Write-Host $Error[0].Exception.Message
    } finally {


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.