[Powershell] Executing an sql query and returning a disconnected data using Powershell


Below is a script that will execute an sql query and returns a disconnected data (DataTable) using Powershell.

<#
.SYNOPSIS
        Queries a data source and returns a disconnected data (DataTable)

.DESCRIPTION
        Queries a data source and returns a disconnected data (DataTable)

.NOTES
        File Name: Get-DbDataTable.ps1
        Author: Randy Aldrich Paulo

.EXAMPLE
        Get-DbDataTable "Data Source=.;Integrated Security=SSPI;Initial Catalog=DatabaseName" "SELECT TOP 10 * From Table1"
#>

function Get-DbDataTable
(
        [string]$connectionString,
        [string]$query,
        [System.Data.CommandType]$commandType=[System.Data.CommandType]::Text,
        [string]$providerName = "System.Data.SqlClient"       
)
{
        #Initialize
        $databaseFactory = [System.Data.Common.DbProviderFactories]::GetFactory($providerName)
        $dbConnection = $databaseFactory.CreateConnection()
        $dbConnection.ConnectionString = $connectionString

        #Create Command
        $dbCommand = $databaseFactory.CreateCommand()
        $dbCommand.Connection = $dbConnection
        $dbCommand.CommandText =$query
        $dbCommand.CommandType = $commandType

        #Create Data Adapter
        $dbAdapter = $databaseFactory.CreateDataAdapter();
        $dbAdapter.SelectCommand = $dbCommand

        $dataTable = New-Object System.Data.DataTable

        try
        {
                #Fill with Results
                $dbAdapter.Fill($dataTable)
        }
        catch { 
                throw 
                }
        finally { 
                        if($dbConnection.ConnectionState -ne [System.Data.ConnectionState]::Closed) 
                        { 
                                $dbConnection.Close() 
                        }
                }
        return $dataTable
}