Tech Off Thread

5 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

MSH + SQL Server

Back to Forum: Tech Off
  • User profile image
    joshnuss


    Hi niners,

    Came up with a little MSH script (MSH is sooo cool) that uses sql to retrieve a list of object that can be used together with all the other command line tools.

    A simple call:

    $connectionString = "Server=(local)\SQLEXPRESS;Database=AdventureWorks;Integrated Security='SSPI'"
    $sqlText = "SELECT * FROM Production.Product"

    Get-Sql $connectionString $sqlText

    Format as table

    Get-Sql $connectionString $sqlText | format-table

    Export CSV

    Get-Sql $connectionString $sqlText | export-csv "test.csv"

    Loop Thru Items

    $products = (Get-Sql $connectionString $sqlText)

    foreach ($product in $products) {
     write-host $product.Name
    }

    Heres the actual function:

    function Get-Sql {
     param ([string]$conn, [string]$sql)
     
     # setup connection
     $connObj = new-object System.Data.SqlClient.SqlConnection
     $connObj.ConnectionString = $conn
     # setup command
     $cmdObj = new-object System.Data.SqlClient.SqlCommand
     $cmdObj.Connection = $connObj
     $cmdObj.CommandText = $sql

     $connObj.Open()
     
     $reader = $cmdObj.ExecuteReader()
     while ($reader.Read()) {
      $result = new-object System.Management.Automation.MshObject
      
      for ($i=0;$i -lt ($reader.FieldCount) ;$i++) {
       $fld = new-object System.Management.AUtomation.MshNoteProperty $reader.GetName($i), $reader[$i]
       $result.MshObject.Members.Add($fld);
      }
      
      $result
      
     }


     $reader.Close()
     $reader.Dispose()

     $connObj.Close();
    }


    Enjoy

  • User profile image
    W3bbo

    Can you omit the .Close/.Dispose methods in Monad scripts since the application ends when the script finishes executing, and the connection classes automatically close themselves.

  • User profile image
    joshnuss

    I still suggest using Close & Dispose. Its a good habit, especially if the script may be reused in a different context.

    I probably should add a Dispose call for the command object too.

  • User profile image
    Sven Groot

    If anything it's not necessary to call both Close and Dispose on the reader. They do the same thing.

  • User profile image
    Maurits

    Still a good habit to call Dispose on all IDisposables, and Close() for everything Close()-able.

    Better to take out the trash too often than not often enough.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.