Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A custom class for PowerShell 5+ to query a SQL Server
class SQLQuery
{
# Properties
[string]$SQLServer
[string]$Database
[string]$Query
[string]$QueryFile
[string]$Path
[int]$ConnectionTimeout = 5
[int]$CommandTimeout = 600
# Connection string keywords: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx
[string]$ConnectionString
[object]$SQLConnection
[object]$SQLCommand
hidden $SQLReader
[System.Data.DataTable]$Result
[System.Data.DataTable]$Tables
[System.Data.DataTable]$Views
[bool]$DisplayResults = $True
# Constructor -empty object
SQLQuery ()
{
Return
}
# Constructor - sql server and database
SQLQuery ([String]$SQLServer,[String]$Database)
{
$This.SQLServer = $SQLServer
$This.Database = $Database
}
# Constructor - sql server, database and query
SQLQuery ([String]$SQLServer,[String]$Database,[string]$Query)
{
$This.SQLServer = $SQLServer
$This.Database = $Database
$This.Query = $Query
}
# Method
LoadQueryFromFile([String]$Path)
{
If (Test-Path $Path)
{
If ([IO.Path]::GetExtension($Path) -ne ".sql")
{
throw [System.IO.FileFormatException] "'$Path' does not have an '.sql' extension'"
}
Else
{
Try
{
[String]$This.Query = Get-Content -Path $Path -Raw -ErrorAction Stop
[String]$This.QueryFile = $Path
}
Catch
{
$_
}
}
}
Else
{
throw [System.IO.FileNotFoundException] "'$Path' not found"
}
}
# Method
[Object] Execute()
{
If ($This.SQLConnection)
{
$This.SQLConnection.Dispose()
}
If ($This.ConnectionString)
{
}
Else
{
$This.ConnectionString = "Server=$($This.SQLServer);Database=$($This.Database);Integrated Security=SSPI;Connection Timeout=$($This.ConnectionTimeout)"
}
$This.SQLConnection = [System.Data.SqlClient.SqlConnection]::new()
$This.SQLConnection.ConnectionString = $This.ConnectionString
Try
{
$This.SQLConnection.Open()
}
Catch
{
return $(Write-host $_ -ForegroundColor Red)
}
Try
{
$This.SQLCommand = $This.SQLConnection.CreateCommand()
$This.SQLCommand.CommandText = $This.Query
$This.SQLCommand.CommandTimeout = $This.CommandTimeout
$This.SQLReader = $This.SQLCommand.ExecuteReader()
}
Catch
{
$This.SQLConnection.Close()
return $(Write-host $_ -ForegroundColor Red)
}
If ($This.SQLReader)
{
$This.Result = [System.Data.DataTable]::new()
$This.Result.Load($This.SQLReader)
$This.SQLConnection.Close()
}
If ($This.DisplayResults)
{
Return $This.Result
}
Else
{
Return $null
}
}
# Method
[Object] ListTables()
{
If ($This.ConnectionString)
{
$TableConnectionString = $This.ConnectionString
}
Else
{
$TableConnectionString = "Server=$($This.SQLServer);Database=$($This.Database);Integrated Security=SSPI;Connection Timeout=$($This.ConnectionTimeout)"
}
$TableSQLConnection = [System.Data.SqlClient.SqlConnection]::new()
$TableSQLConnection.ConnectionString = $TableConnectionString
Try
{
$TableSQLConnection.Open()
}
Catch
{
return $(Write-host $_ -ForegroundColor Red)
}
Try
{
$TableQuery = "Select Name from Sys.Tables Order by Name"
$TableSQLCommand = $TableSQLConnection.CreateCommand()
$TableSQLCommand.CommandText = $TableQuery
$TableSQLCommand.CommandTimeout = $This.CommandTimeout
$TableSQLReader = $TableSQLCommand.ExecuteReader()
}
Catch
{
$TableSQLConnection.Close()
$TableSQLConnection.Dispose()
return $(Write-host $_ -ForegroundColor Red)
}
If ($TableSQLReader)
{
$This.Tables = [System.Data.DataTable]::new()
$This.Tables.Load($TableSQLReader)
$TableSQLConnection.Close()
$TableSQLConnection.Dispose()
}
If ($This.DisplayResults)
{
Return $This.Tables
}
Else
{
Return $null
}
}
# Method
[Object] ListViews()
{
If ($This.ConnectionString)
{
$ViewConnectionString = $This.ConnectionString
}
Else
{
$ViewConnectionString = "Server=$($This.SQLServer);Database=$($This.Database);Integrated Security=SSPI;Connection Timeout=$($This.ConnectionTimeout)"
}
$ViewSQLConnection = [System.Data.SqlClient.SqlConnection]::new()
$ViewSQLConnection.ConnectionString = $ViewConnectionString
Try
{
$ViewSQLConnection.Open()
}
Catch
{
return $(Write-host $_ -ForegroundColor Red)
}
Try
{
$ViewQuery = "Select Name from Sys.Views Order by Name"
$ViewSQLCommand = $ViewSQLConnection.CreateCommand()
$ViewSQLCommand.CommandText = $ViewQuery
$ViewSQLCommand.CommandTimeout = $This.CommandTimeout
$ViewSQLReader = $ViewSQLCommand.ExecuteReader()
}
Catch
{
$ViewSQLConnection.Close()
$ViewSQLConnection.Dispose()
return $(Write-host $_ -ForegroundColor Red)
}
If ($ViewSQLReader)
{
$This.Views = [System.Data.DataTable]::new()
$This.Views.Load($ViewSQLReader)
$ViewSQLConnection.Close()
$ViewSQLConnection.Dispose()
}
If ($This.DisplayResults)
{
Return $This.Views
}
Else
{
Return $null
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.