PowerShell Tricks and Tips, Vol 1 - 10/12/2011

Here is what I learned:
  1. Dealing with DBNull. Thanks to Andrew.Fryer
    # Declare a handy global variable
    $global:DBNull = [System.DBNull]::Value
    # Now you can test variables for DBNull
    if($global:DBNull.Equals($parameterToTest)) { ... }
    
  2. REF Parameters. Thanks to everyone who ever stuggled with this one
    # Little Demo Function
    function TestRefParameters {
      # OK now declare a reference parameter as an argument
      Param( [REF] $RefParam)
      # Set it! Notice you must specify the '.Value' property
      $RefParam.Value = "Test String"
    }
    
    $myRefIWantSetInFunction = "Initial Value"
    # Notice we must pass it wrapped in parenthesis () with [REF]
    TestRefParameters ([REF]$myRefIWantSetInFunction)
    # Write Results
    Write-Host $myRefIWantSetInFunction
    
  3. Read your paramters from configuration (like .NET). Thanks to R Keith Hill
    function ReadConfig {
        Param([string] $configPath, [string] $configFilename)
    
        $configpath = $runningpath + '\' + $configFilename
        if(!(test-path $configpath))
        {
            Write-Host 'Configuration File Not Found: ' + $configpath
            exit 1
        }
        $global:appSettings = @{}
        $config = [xml](get-content $configpath)
        foreach ($addNode in $config.configuration.appsettings.add) {
           # Array case
           if ($addNode.Value.Contains(‘,’)) {
              $value = $addNode.Value.Split(‘,’)
              for ($i = 0; $i -lt $value.length; $i++) {
                 $value[$i] = $value[$i].Trim()
              }
            }
            # Scalar case
            else {
              $value = $addNode.Value
             }
             $global:appSettings[$addNode.Key] = $value
        }
    }
    
    If your '.config' file looks like
    <?xml version="1.0"?>
    <configuration>
      <startup>
        <supportedRuntime version="v2.0.50727" safemode="true"/>
        <requiredRuntime version="v2.0.50727" safemode="true"/>
      </startup>
      <appSettings>
        <!-- Plain types have no comma -->
        <add key='Version' value='1.0' />
        <!-- Array Types are comma separated -->
        <add key='ListOfNumbers' value='One,Two,Three' />
      </appSettings>
    </configuration>
    
    The to use one of the values:
    # Find the path where the script is executing
    $runningpath = Split-Path -Parent $MyInvocation.MyCommand.Path
    # Read the configuration
    ReadConfig $runningpath 'myConfig.config'
    # Get the version parameter
    $version = $appSettings["Version"]
    # Get the second element from the array parameter, notice zero based
    $two = $appSettings["ListOfNumbers"][1]
    
  4. Execute a stored procedure. Thanks to everyone who contributed Here is an outline of a function to call an SP
    function MySqlStoredProcedure {
        Param([string] $connnectionString, [{type}] $parameter1, ...)
    
        # Set SP Name
        $StoredProcedureName = 'pMyProc'
    
        # SQL Objects
        $conn = new-Object System.Data.SqlClient.SqlConnection($connnectionString)
        $conn.Open()
    
        $cmd = new-Object System.Data.SqlClient.SqlCommand($StoredProcedureName, $conn)
        $cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
    
        # # # Put Parameters Here
    
        #execution
        $cmd.ExecuteNonQuery()
    
        # Get any output parameters here
    
        $conn.Close()
    }
    
    Here are some parameter examples:
    # Notice these are preceded with [void] to avoid extra stuff in the output...
    
    # Here is how the return value from an SP is expressed
    [void] $cmd.Parameters.Add("@return_value", [System.Data.SqlDbType]"Int")
    $cmd.Parameters["@return_value"].Direction = [System.Data.ParameterDirection]"ReturnValue"
    
    # Here is an output parameter
    [void] $cmd.Parameters.Add("@ErrorCode", [System.Data.SqlDbType]"Int")
    $cmd.Parameters["@ErrorCode"].Direction = [System.Data.ParameterDirection]"Output"
    
    # A Varchar(18)
    [void] $cmd.Parameters.Add("@Para1", [System.Data.SqlDbType]"varchar", 18)
    $cmd.Parameters["@Para1"].Value = $taskname
    
    # A BigInt
    [void] $cmd.Parameters.Add("@BigId", [System.Data.SqlDbType]"bigint")
    $cmd.Parameters["@BigId"].Value = $documentId
    
    # Passing DBNull as a value, notice we use the value from above
    [void] $cmd.Parameters.Add("@NullableParam", [System.Data.SqlDbType]"int")
    $cmd.Parameters["@NullableParam"].Value = $global:DBNull
    
  5. Lastly, exit codes are our friends. Please use the UNIX convention of ZERO (0) is ok, and anything else is BAD
    # Script can't continue and we'll also signal the error
    if(Some bad thing is true) {
    	Write-Error -Category Category -CategoryActivity "What the script was doing"  -message "What went wrong"
    	exit 64
    }
    

Blog ID: 2011 10-12_Blog_130