Handy SQL Function in PowerShell

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()
}

Rating 3.00 out of 5
[?]

One thought on “Handy SQL Function in PowerShell

  1. Pingback: PowerShell.nu » Blog Archive » Another Handy SQL function in PowerShell

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Anti-Spam Protection by WP-SpamFree