Here is a handy custom class I created for PowerShell 5+ that can query an SQL database. The class creates a SQL connection using the System.Data.SqlClient.SqlConnection class in .Net, and gives you full access to this object for information or to manipulate parameters. The class can also query the list of tables and views available in the database to help you prepare your SQL query.

Here’s a quick how-to:

Instantiate the Class

Create an instance of the object. You can create an empty instance like this:

$SQLQuery = [SQLQuery]::new()

You can then specify the SQL Server name and Database name:

$SQLQuery.SQLServer = "SQL-01\Inst_SCCM"
$SQLQuery.Database = "CM_ABC"

And then add a query:

$SQLQuery.Query = "Select top 5 * from v_R_System"

Or you can pass these parameters when you create the object, like:

$SQLQuery = [SQLQuery]::new("SQL-01\Inst_SCCM","CM_ABC")

Or..

$SQLQuery = [SQLQuery]::new("SQL-01\Inst_SCCM","CM_ABC","Select top 5 * from v_R_System")

Execute the Query

Now execute the query to return the results:

$SQLQuery.Execute()

The result will be displayed in the console window, or you can pipe to GridView for easier viewing:

$SQLQuery.Execute() | Out-GridView

The query results will be stored to the object in the Result parameter so you can retrieve them later.

$SQLQuery.Result

The results are stored in a DataTable format with rows and columns which is an ideal format for SQL query results.

Load a Query from a File

You can load in a SQL query from a “.sql” file like so, passing the location of the file to the method:

$SQLQuery.LoadQueryFromFile("C:\Scripts\SQLScripts\OSD_info.sql")

Then execute the query as before.

Change the Connection String

By default, the connection string will be created for you, but you can add your own custom connection string using the ConnectionString parameter:

$SQLQuery.ConnectionString = "Server=SQL-01\inst_sccm;Database=CM_ABC;Integrated Security=SSPI;Connection Timeout=5"

Timeout Values

There are two timeout parameters which can be set:

$SQLQuery.ConnectionTimeout
$SQLQuery.CommandTimeout

The ConnectionTimeout parameter is the maximum time in seconds PowerShell will try to open a connection to the SQL server.

The CommandTimeout parameter is the maximum time in seconds PowerShell will wait for the SQL query to execute.

Get a List of Views or Tables in the Database

The following two methods can be used to retrieve a list of views or tables in the database:

$SQLQuery.ListViews()
$SQLQuery.ListTables()

The views or tables will be stored to the parameter of the same name in the object, so you can retrieve them again later.

You can filter the list to search for a particular view or table, or group of. On the command line you could do:

$SQLQuery.ListViews()
$SQLQuery.Views.Rows | where {$_.Name -match "v_Client"}

Or as a one-liner:

$SQLQuery.ListViews().Where({$_.Name -match "v_Client"})

You could also output to GridView for filtering:

$SQLQuery.ListViews() | Out-GridView

Hide the Results

By default, the Execute(), ListViews() and ListTables() methods will return the results to the console after execution. You can turn this off by setting the DisplayResults parameter to $False. This scenario may be useful for scripting where you may not wish to display the results right away but simply have them available in a variable.

$SQLQuery.DisplayResults = $False

Access the SQL Objects

You can access the SQLConnection object, or the SQLCommand object to view or set parameters, or execute any of the methods contained in those objects. These objects are only available once the SQL query has been executed.

You can re-use the same SQLQuery object to run other queries; only the results for the most recent query will be stored in the object itself.

SQLQuery Custom Class