Tech Off Thread

5 posts

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.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.