Invoke SQL Queries

Running SQL queries from powershell can be extremely powerfull. However some implementation I have see often result with off object types such as DBNull. This is my method that seems to work quite nicely for me.


$tsqlQuery = @"
Select AccountName
From SUSDB.dbo.tbDownstreamServerTarget
"@
Try {
   $conn.Open()
}
Catch  {
   $PSCmdlt.ThrowTerminatingError($_)
}

$command = $conn.CreateCommand()
$command.CommandText = $tsqlQuery
$table = new-object System.Data.DataTable
$data = $command.ExecuteReader()
$table.load($data)

$conn.Close()
$conn.Dispose()

Working with .NET Types, Assemblies, Etc

Here are some tricks and snippets that I find quite useful when dealing with objects, types and using .NET assemblies, etc.

Find all loaded Assemblies

[AppDomain]::CurrentDomain.GetAssemblies()

 

Adding Assemblies


Add-Type -AssemblyName Windows.Forms

Add-Type -AssemblyName "Microsoft.SqlServer.SMO, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

#With Partial Name (deprecated) 
[Reflection.Assembly]::LoadWithPartialName('Microsoft.UpdateServices.Administration') 

#Targeted version by file(Much better in terms of reliability) 
[Reflection.assembly]::LoadFile('C:\Windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.UpdateServices.Administration\v4.0_4.0.0.0__31bf3856ad364e35\Microsoft.UpdateServices.Administration.dll') 

#Targeted version by Assembly String 
[Reflection.Assembly]::Load('Microsoft.UpdateServices.Administration, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35') 

Note: You cannot unload assemblies from the application domain so once they’re in, they’re in. Note also to find the Assembly Strings use:

[AppDomain]::CurrentDomain.GetAssemblies() | Select FullName

 

Find Method Overloads

If you call a method without the brackets powershell will show you a list of all the different overload options available


[String]::Compare

OverloadDefinitions
-------------------
static int Compare(string strA, string strB)
static int Compare(string strA, string strB, bool ignoreCase)

 

Find Hidden Properties
There’s often more than get-member will normally show, hidden properties and methods:

$p | GM -Force

 

Get a List of TypeNames

$p.PSObject.TypeNames