Samstag, 20. November 2010

Eval-Selection in ISE editor, result in editor pane

Here is a little demo showing, who to use ISEs editor pane to display untruncated, unwrapped results.

Run the script in ISE.
Open a new editor.
Tpye dir and press F7:



            
            
function Eval-Selection            
{            
    # Bernd Kriszio 2010-11-21            
    # http://pauerschell.blogspot.com/            
    # twitter @bernd_k            
                
                    
    $editor = $psise.CurrentFile.Editor            
            
    if ($editor.SelectedText)            
    {            
        $inputScript = $editor.SelectedText            
        $editor.InsertText('')            
        $editor.InsertText($inputScript)            
        $result = Invoke-expression $inputScript | out-String            
        $editor.insertText("`r`n")            
        $editor.InsertText($result)            
            
    }            
    else            
    {            
        $inputScript = $editor.Text            
        $EndLine =  $editor.LineCount             
        $EndColumn = $editor.GetLineLength($EndLine) + 1            
        $editor.SetCaretPosition($EndLine, $EndColumn)            
        $result = Invoke-expression $inputScript | out-String            
        $editor.insertText("`r`n")            
        $editor.InsertText($result)            
    }            
}                    
                    
             
$psISE.CurrentPowerShellTab.AddOnsMenu.Submenus.Add("Eval-Selection", {Eval-Selection} ,  'f7')

Edited
After playing a while, I modified the rules for the case, when nothing is selected. Now I use just the single line, the caret is in. Further I added the -width 1000 parameter to make it realy an improvement about usual output-pane results.

And I have a lot of crazy ideas, what to do if the caret is in an empty line. I don't implement them. Next month I wouldn't remember them myself.

Keep things simple

Bernd

This is the version, I added to my profile:

function Eval-Selection            
{            
    # Bernd Kriszio 2010-11-21            
    # http://pauerschell.blogspot.com/            
    # twitter @bernd_k            
                
                    
    $editor = $psise.CurrentFile.Editor            
                
    # if nothing is selected just use the line the caret is in            
    if (!$editor.SelectedText)            
    {            
        $caretLine = $editor.CaretLine            
        $caretLineEnd = $editor.GetLineLength($caretLine) + 1                
                    
        $editor.Select($caretLine, 1, $caretLine, $caretLineEnd)            
    }            
            
    # if something is selected use it.            
    if ($editor.SelectedText)            
    {            
        $inputScript = $editor.SelectedText            
        $editor.InsertText('')            
        $editor.InsertText($inputScript)            
        $result = Invoke-expression $inputScript | out-String -width 1000            
        if ($editor.CaretColumn -ne 1)            
        {            
            $editor.insertText("`r`n")            
        }            
        $editor.InsertText($result)            
            
    }            
}            

Dienstag, 16. November 2010

Determine Start and End of a Selection in PowerShell Ise

Hello, I' m just working on a new inline outpout mode for SQLPSX a very ambious project to access SQL databases based on PowerShell.

There ISEs Output pane doesn't always satisfy my needs, because it truncates and wraps output but you can put your Output into the current or new Editor panes and that works fine. Today I focus on using the current editor pane. 

My goal is to insert the output exactly at the starting of the line below the selection if there is one or at the end of the editor.

First I found it difficult to get the start and end of a selection, but than I found a clever work around. I can even determine, if a selection was done from left to right or from right to left.

But don't use this possibility. It would be really confusing.

Here is my test code, with which I designed (honestly redesigned) the logic to evaluate the output location in SQLPSX (next release).

Have fun studying it. Just put the code below it into an ISE editor, select some text (or nothing) and press F5.

Bernd


function Get-InfoAboutCurentFilesEditor            
{            
    $editor          = $psIse.CurrentFile.Editor            
    $LineCount       = $editor.LineCount            
    $CaretLine       = $editor.CaretLine            
    $caretColumn     = $editor.CaretColumn            
    $CaretLineLength = $editor.GetLineLength($CaretLine)            
    $CaretAtLineEnd  = $caretColumn -eq $CaretLineLength + 1            
    $CaretInLastLine = $CaretLine -eq $LineCount            
    $SelectedText    = $editor.SelectedText            
    $hasSelection    = $SelectedText -ne ''            
                
    if ($hasSelection)            
    {            
        # delete selected text            
        $editor.InsertText('')            
        $StartLine =  $editor.CaretLine            
        $StartColumn = $editor.CaretColumn            
        # reinsert the text            
        $editor.InsertText($SelectedText)            
        $EndLine =  $editor.CaretLine            
        $EndColumn = $editor.CaretColumn            
        # restore the selection            
        $editor.select($StartLine, $StartColumn, $EndLine, $EndColumn)             
        $reverse =  $CaretLine -eq  $StartLine -and $caretColumn -eq $StartColumn            
    }            
    else            
    {            
        $StartLine =  $EndLine = $LineCount            
        $StartColumn = $EndColumn = $editor.GetLineLength($EndLine) + 1            
        $reverse = $False            
    }            
                
    if (  $EndLine -lt  $LineCount)            
    {            
        $outputLine = $EndLine + 1            
        $outputColumn = 1            
        $NeedsNewLine = $False            
    }            
    else            
    {            
        $outputLine = $EndLine            
        $outputColumn = $EndColumn            
        if ($EndColumn -ne 1)            
        {            
            $NeedsNewLine = $True            
            $outputColumn = $editor.GetLineLength($EndLine) + 1            
        }            
        else            
        {            
            $NeedsNewLine = $False            
        }            
    }            
@"
DisplayName:     $($psIse.CurrentFile.DisplayName) 
LineCount:       $LineCount

CaretLine:       $CaretLine
CaretColumn:     $caretColumn
CaretLineLength: $CaretLineLength
CaretAtLineEnd:  $CaretAtLineEnd
CaretInLastLine: $CaretInLastLine

hasSelection:    $hasSelection
reverse:         $reverse

outputLine:      $outputLine
outputColumn:    $outputColumn
NeedsNewLine:    $NeedsNewLine
"@            
}            
            
Get-InfoAboutCurentFilesEditor

Montag, 15. November 2010

New ISE work arounds -- determine start and end of textselection

I just guess that ISE is made by some Trappist monks. One never hears anything about future versions.
At most some closed Change Request at Microsoft Commit.
I get a little tired hoping, they will release anything soon.
If I need something, I'm going to write some dirty workaround.
Current problem: you can select text within the editor from left to right or from right to left ending with the caret at different positions.
Only the holy men didn't publish the API to get the start and the end of the selection. You can query only the caret position.
What I want acchieve, is to be able to insert in the line just below the current selection.
Well the following work around sets the caret to the beginning of the selection.
Put the code into an ise editor select testa and press f5, than repeat it by selecting the tesxt in the oposite direction and press again f5:


$editor = $psise.CurrentFile.Editor            
 $currentselection = $editor.SelectedText            
 if ($currentselection)            
 {            
    $initialCaretLine = $editor.CaretLine            
    $initialCaretColumn = $editor.CaretColumn            
    $editor.InsertText('')            
    $selstartline  = $editor.CaretLine            
    $selstartColumn = $editor.CaretColumn             
    $editor.InsertText($currentselection)            
    $selEndline  = $editor.CaretLine            
    $selEndColumn = $editor.CaretColumn             
    $editor.Select($selstartline, $selstartColumn, $selEndline, $selEndColumn)            
                
    "Selection is ($selstartline, $selstartColumn, $selEndline, $selEndColumn)"            
    "Initial Caret Position ($initialCaretLine, $initialCaretColumn)"            
 }            
            
            
# testa            
# testb            



Hope this really helps

Bernd


Sonntag, 14. November 2010

I can code Bad Influenced style too

I hope there is a better way to do this, but if PowerShell enforces the use of work arounds, I'll use them.
Please, if you find something more elegant, please drop a note.

Well to make SQLPSX one of the most powerfull tools to extract data from SQL-Server or Oracle databases I just want to transform a datarow into a tabular representation.


$datarow | Format-Table -auto

seems a good start, but as described in http://poshoholic.com/2010/11/11/powershell-quick-tip-creating-wide-tables-with-powershell/

you need to pass the result to Out-String -width 10000 or as many characters you want.
Sorry can't say -1 for unlimited. (Perhaps in V3, if we cry load enough?)

Oops not all columns included, lets add -property *

$datarow | Format-Table -auto -property * | Out-String -width 10000

Oops what happens now?  At the end you get some unwanted addition columns:

RowError, RowState, Table, ItemArray,  HasErrors

A problem well described in https://connect.microsoft.com. With no work around supplied.

Go there and vote for it.

And here is the code, that gives me the wanted result. I agree, it is ugly, but its result is fine:


$columns = ''            
            
                            foreach ($i in 0.. ($res[0].Table.columns.count -1))            
                            {            
                                if ($columns) { $columns +=  ', '+ $res[0].Table.Columns[$i].ColumnName }            
                                else { $columns = $res[0].Table.Columns[$i].ColumnName}            
                            }            
            
                            $c = '($res | ft -Property ' + $columns + ' -auto | Out-string -width 10000 -stream ) -replace " *$", ""-replace "\.\.\.$", "" -join "`r`n" '            
                            $text = Invoke-expression $c            

I hope this helps

Bernd

Samstag, 13. November 2010

SQLPSX Release 2.3 is real tool for SQL-Server

Well it took some time, until Chad Miller published the current version of SQLPSX.
Concerning SQL-Server I dare say, it is a real tool to work with, with some unique features.

You get a lot of options for output format, e.g. list, which showes you the contents of the columns above each other. That helps, if you have varchar fields containing multiple lines. While the usual grid format only displays the first line and the text format looses column alignment, here you get nearly the complete content.
Nearly? Yes using ISE Output window, some wise man desided that truncating the output to current width of the Output window is what users have to want.

OK. With the limitations of the output window you have two options. First you can tweet about this last millenium decision. Second you can pray to Microsoft Connect and send a change Request for an option to programmatically set the witdth of the output pane).
Or you just ignore the two options and write a workaround.

The workaround I invented is the output format isetab. (Yes you find it in SQLPSX 2.3)
The trick is not using the Output pane for displaying the result, but to create a new editor and display it there in its whole witdh.

Working with the new mode, I soon end with lots of ofen editors and nearly as a joke to a coworker I designed the idea of an inline output format. I put the result into the calling editor, just below the calling selection or at the end, if there is none.

First trial was just 4 lines and it just felt good.

You can adjust the princip, if you have a function, which you invoke by ISE menu, which produces some wide output. Instead of sending the output to the output pane, you can use something similar to:


$text = "your code goes here>"            
         $editor = $psise.CurrentFile.Editor            
         $caretLine = $editor.CaretLine            
         $lineCount = $editor.LineCount            
         if (-not $selectedEditor.SelectedText)            
         {            
            $lineend = $editor.GetLineLength($lineCount) + 1            
            # Write-Host "Nothing Selected  Linecount: $lineCount LineEnd: $lineend"            
            if ($lineend -eq 0) {$lineend = 1}            
            # set selection to the end of the editor            
            $editor.Select($lineCount, $lineend, $lineCount, $lineend)            
         }            
         elseif ($caretLine -lt $lineCount)            
         {            
            # Write-Host "Usual case"            
            # set selection to the start of the next line            
            $editor.Select($caretLine + 1, 1, $caretLine + 1, 1)            
         }            
         else            
         {            
            $lineend = $editor.GetLineLength($lineCount) + 1            
            # Write-Host "At end  Linecount: $lineCount LineEnd: $lineend"            
            if ($lineend -eq 0) {$lineend = 1}            
            # set selection to the end of the editor            
            $editor.Select($lineCount, $lineend, $lineCount, $lineend)            
         }            
         $editor.InsertText($text)            



You can load this into an ISE editor and run it using F5. You see the line your code goes here added at the end.

Bernd

Freitag, 28. Mai 2010

A Problem with Remove-Item $folder -recurse -force

I'm working with some testsenario, where I have repeatedly to remove a folder structure of about 3500 files of depth 8.

Remove-Item $folder -recurse -force

sometimes does not remove all the files. In some cases it yields the message, that a folder can't be deleted, because it is not empty, but when checking the folder is empty. In other not all files are removed.

To reach a clean state, I wrote the following script:


function Remove-ItemRecursiveBruteForce            
{            
    param(            
        [Parameter(Position = 0, Mandatory=$true, ValueFromPipeline=$false)]            
        [string]$folder,            
        [Parameter(Position = 1, Mandatory=$False, ValueFromPipeline=$false)]            
        $maxiterations = 20            
    )            
            
    $iteration = 0            
    while (  $iteration++ -lt   $maxiterations)            
    {            
        if (Test-Path $folder)            
        {            
            Remove-Item $folder -recurse -force -EA SilentlyContinue            
        }            
        else            
        {            
            "$folder deleted in $iteration iterations"            
            break            
        }            
    }            
    if (Test-Path $folder)            
    {            
        "$folder not empty after $iteration iterations"            
    }            
            
}            
            


I observe that it needs randomly between 2 and 11 iterations to remove my folder.

I'm using W7-32 bit and use PowerShell-ISE to run the script.

Is this problem just me, or is it OS specific. Any explanations?

Mittwoch, 19. Mai 2010

1. Attemp to use .NET isolated storage with modeless WPK windows

 

The following attempt is not working with modeless windows (replace -show #-asjob    by -asjob). Using -show it works. Please run the code, do some selection within GUI, finally call  Get-value to check the result.


ipmo WPK                        
            
                        
$env:WPKResult = "auto"            
            
function action            
{            
    param (            
        $value            
    )            
    $env:WPKResult = $value            
                
    $changedProperty = $value            
    Write-Host "Value of $changedProperty changed to $value"                        
            
    try {            
    $useriStorage = [System.IO.IsolatedStorage.IsolatedStorageFile]::GetUserStoreForAssembly()            
    $file = New-Object System.IO.IsolatedStorage.IsolatedStorageFileStream("SQLSettigs.xml",[System.IO.FileMode]::Create,$useriStorage)            
    $xml = New-Object System.Xml.Serialization.XmlSerializer($changedProperty.GetType())            
    $xml.Serialize($file,$changedProperty)            
    $file.Close()            
    $useriStorage.Close()            
    }            
    catch {            
        $env:WPKResult = 'error'            
    }            
            
}            
                          
New-StackPanel {                        
    New-RadioButton -Content "auto"     -GroupName Results -IsChecked $True -On_Click { action "auto" }                        
    New-RadioButton -Content "list"     -GroupName Results -On_Click { action "list" }                        
    New-RadioButton -Content "table"    -GroupName Results -On_Click { action "table" }            
    New-RadioButton -Content "grid"     -GroupName Results -On_Click { action "grid" }            
    New-RadioButton -Content "variable" -GroupName Results -On_Click { action "variable" }            
    New-RadioButton -Content "csv"      -GroupName Results -On_Click { action "csv" }            
    New-RadioButton -Content "file"     -GroupName Results -On_Click { action "file" }            
} -show #-asjob                        
                        
            
function Get-value            
{            
    $useriStorage = [System.IO.IsolatedStorage.IsolatedStorageFile]::GetUserStoreForAssembly()            
    try {            
        $file = New-Object System.IO.IsolatedStorage.IsolatedStorageFileStream("SQLSettigs.xml",[System.IO.FileMode]::Open,$useriStorage)            
    }            
    catch {            
        Write-Host "UISettings.xml is not found"            
        return            
    }                        
    $xmlReader = New-Object System.Xml.XmlTextReader($file)            
    $xml = New-Object System.Xml.Serialization.XmlSerializer('abc'.GetType())            
    $newISEOptions = $xml.Deserialize($xmlReader)            
    $newISEOptions            
}

Sonntag, 16. Mai 2010

Please help - I want to send back a keystroke to the ISE window

Hello WPF friends, I need a little help. I want to add a button which sends F7 keystroke to the ISE-window from which I run this little WPK script.


ipmo WPK                        
                        
$env:WPKResult = "auto"                          
New-StackPanel {                        
    New-RadioButton -Content "auto"     -GroupName Results -IsChecked $True -On_Click { $env:WPKResult = "auto" }                        
    New-RadioButton -Content "list"     -GroupName Results -On_Click { $env:WPKResult = "list" }                        
    New-RadioButton -Content "table"    -GroupName Results -On_Click { $env:WPKResult = "table" }            
    New-RadioButton -Content "grid"     -GroupName Results -On_Click { $env:WPKResult = "grid" }            
    New-RadioButton -Content "variable" -GroupName Results -On_Click { $env:WPKResult = "variable" }            
    New-RadioButton -Content "csv"      -GroupName Results -On_Click { $env:WPKResult = "csv" }            
    New-RadioButton -Content "file"     -GroupName Results -On_Click { $env:WPKResult = "file" }            
    New-Button -Content "execute Sql"  -On_click { New-Button "I want to send F7 key-stroke to calling ISE" -show }                        
} -asjob                        
                        

This script is intended to be run in ISE and to add some cool GUI to SQLPSX.

Thanks

Bernd

Mittwoch, 12. Mai 2010

A try in WPK (one way and only once)

The best I found in WPK seems to be the the following:

ipmo WPK            
            
$option = "To File" # this value might be prompted by the user            
             
             
New-StackPanel {            
    New-RadioButton -Content "To Text" -GroupName Results -IsChecked $True -On_Click {             
        $Resource.Result = "To Text"             
        $info = $window | Get-ChildControl info            
        $info.text = $Resource.Result            
        $env:WPKResult = $Resource.Result            
        }            
    New-RadioButton -Content "To Grid" -GroupName Results -On_Click {              
        $Resource.Result = "To Grid"             
        $info = $window | Get-ChildControl info            
        $info.text = $Resource.Result            
        $env:WPKResult = $Resource.Result            
        }            
    New-RadioButton -Content "To File" -GroupName Results -On_Click {              
        $Resource.Result = "To File"             
        $info = $window | Get-ChildControl info            
        $info.text = $Resource.Result            
        $env:WPKResult = $Resource.Result            
    }            
    New-RadioButton -Content "To CSV" -GroupName Results -On_Click {              
        $Resource.Result = "To CSV"             
        $info = $window | Get-ChildControl info            
        $info.text = $Resource.Result            
        $env:WPKResult = $Resource.Result            
    }            
    New-textBox -Name info             
    } -On_Loaded {            
            $info = $window | Get-ChildControl info            
            $info.text = $Resource.Result            
    }-asjob -resource @{Result = $option}            
            

I can modify the value within GUI and query it outside

$env:WPKResult
 
I misuse the processes environment, which is shared between the runspaces. Hmm better than pidgeon carrying USB sticks, but it is restricted to strings.

I guess I have to really dive into PowerBoots  (and that is the polite version ;-) of my opinion about WPK)

Have some fun trying to find further work arounds.

Bernd

 
 
 

A small Async PowerBootsWindow

Hello,
here I'll show a little async PowerBoots Gui, which is able to modify variables in the main thread.

ipmo PowerBoots            
            
$options = @{ "Results" = "To Text" }            
             
Boots {             
    StackPanel {            
        RadioButton -Content "To Text" -GroupName Results -IsChecked $True -On_Click { $options.Results = "To Text" }            
        RadioButton -Content "To Grid" -GroupName Results -On_Click {  $options.Results = "To Grid" }            
        RadioButton -Content "To File" -GroupName Results -On_Click {  $options.Results = "To File" }            
        RadioButton -Content "To CSV" -GroupName Results -On_Click {  $options.Results = "To CSV" }            
                }            
      } -async            
            
            

Is it possible to do the same thing with WPK?

A viewer for Crystal Reports with PowerShell (partially working)

Hello,
this time I tried to build a small Report Viewer using the components from Visual Studio 2008 with PowerShell. It shows that using PowerShell here makes some things rather easy.

I'm using this to display old Reports made with Crystal Reports 8.5 using ODBC Datasources to SQL-Server.

Reports based on views works fine, I can set database and formulars.

But I have still a problem with reports based on stored procedures. Setting $table.location doesn't work anymore as in the past. I go the error table not found. Any hints welcome.

And here is the code if you want to try something like this:

Function Show-CrystalReport            
{            
    param(            
        $reportPath,             
        $servername,             
        $databasename,             
        $userId,             
        $password,            
        $RecordSelectionFormula,            
        $formulas,            
        $parameters            
        )            
                    
            
    [reflection.assembly]::LoadWithPartialName('CrystalDecisions.Shared')            
    [reflection.assembly]::LoadWithPartialName('CrystalDecisions.CrystalReports.Engine')            
    [reflection.assembly]::LoadWithPartialName('CrystalDecisions.Windows.Forms')            
            
    $report = New-Object CrystalDecisions.CrystalReports.Engine.ReportDocument            
    $report.load($reportPath)            
            
    $report.ParameterFields | % {             
        if ($parameters.keys -contains $_.Name)            
        {            
            $report.SetParameterValue($_.Name, $parameters[$_.Name])            
        }            
     }               
            
    $report.DataDefinition.FormulaFields | % {            
        if ($formulas.keys -contains $_.Name)            
        {            
            $_.Text = $formulas[$_.Name]            
        }            
    }            
            
    if ($RecordSelectionFormula) {            
        $report.RecordSelectionFormula = $RecordSelectionFormula            
    }            
            
    foreach ($Table in $report.Database.Tables)            
    {            
        $table            
        $tli = $Table.LogonInfo            
        $li = $tli.ConnectionInfo            
        Write-host "location : $($table.location)"            
                    
        $li.ServerName = $servername            
        $li.DatabaseName = $databasename             
        $li.UserID = $userId            
        $li.Password = $password            
        $Table.ApplyLogOnInfo($tli)            
        # the following doesn't work as in the past            
        if ( $table.location -contains '.')            
        {            
            $table.location -match  '(.*)\.(.*)'            
            $table.location = "$($databasename).dbo.$($matches[2])"            
        }            
        $table.location             
                   
    }            
            
    $rv = New-Object CrystalDecisions.Windows.Forms.CrystalReportViewer            
    $rv.ReportSource = $report            
    $rv.Dock = [System.Windows.Forms.DockStyle]::Fill            
            
            
    $form = New-Object Windows.Forms.Form            
                 
    $form.Height = 810            
    $form.Width= 1210            
    $form.Controls.Add($rv)            
    $rv.Show()            
    $form.ShowDialog()            
            
    $rv.Dispose()            
    $report.Dispose()            
    $form.Dispose()            
            
}