In a previous post I described a little about PowerShell and SQL, how to connect and how to handle different queries using PowerShell.

Now let’s put it all togheter and create a more useful function.

function Get-SQL ([string]$Query,[string]$ConnectionString) {
  $Connection = New-Object System.Data.SQLClient.SQLConnection

  $Connection.ConnectionString = $ConnectionString
  $Connection.Open()

  $Command = New-Object System.Data.SQLClient.SQLCommand
  $Command.Connection = $Connection
  $Command.CommandText = $Query

  $Reader = $Command.ExecuteReader()
  $Counter = $Reader.FieldCount
  while ($Reader.Read()) {
    $SQLObject = @{}
      for ($i = 0; $i -lt $Counter; $i++) {
        $SQLObject.Add(
          $Reader.GetName($i),
          $Reader.GetValue($i)
        );
      }
    $SQLObject
  }
  $Connection.Close()
}

Examples on Running the Function:

PS > Get-SQL -Query "SELECT * FROM Products" `
>> -ConnectionString "server=SQL01;database=DbName;trusted_connection=true;"

Note that if you are planning on taking user input or any input that you don’t control 100% you should use parameterized queries instead.
Consider the following example:

PS > Get-SQL -Query "SELECT * FROM Table1; DELETE From Table WHERE Name = 'Something'" `
>> -ConnectionString "servr=DC01;database=TestDB;trusted_connection=true;"

In the example above all rows in the Table are returned, but as you can see, one row is also deleted.
To avoid this you should use parameterized queries instead. Here’s an example on how you can create a function that uses parameterized queries.

function Get-SQL ([string]$Name,[string]$ConnectionString) {
  $Connection = New-Object System.Data.SQLClient.SQLConnection

  $Connection.ConnectionString = $ConnectionString
  $Connection.Open()

  $Command = New-Object System.Data.SQLClient.SQLCommand
  $Command.Connection = $Connection
  $Command.CommandText = "SELECT * From Computers WHERE Computer = @Name"

  # 2 Additional lines
  $SQLParameter = New-Object System.Data.SqlClient.SqlParameter("@Name", $Name)
  [void]$command.Parameters.Add($SQLParameter)

  $Reader = $Command.ExecuteReader()
  $Counter = $Reader.FieldCount
  while ($Reader.Read()) {
    $SQLObject = @{}
      for ($i = 0; $i -lt $Counter; $i++) {
        $SQLObject.Add(
          $Reader.GetName($i),
          $Reader.GetValue($i)
        );
      }
    $SQLObject
  }
  $Connection.Close()
}