PowerShell – Report Designer Assembly in a .NET GUI

June 4, 2019

If you need a Report Designer that has all basic functionality needed.

And can connect to multiple Data Sources…

image

Print Preview

image

And more …

image

This is the way to go.

Prerequisites :

Download the Assembly from here 

(if you don’t find it there you can email the developer and he will send it over)

Save the Assembly somewhere locally.

SOLUTION :

This script will trigger the GUI that give you full functionality in PowerShell in less then 10 lines of code !

This is because PS can access .NET Assemblies Smile

CLS

Add-Type -AssemblyName System.Windows.Forms
Add-Type -Path "C:\_\Apps\_PowerShell\_GUI Forms\.NET Report Builder\MySql.Data.dll"
Add-Type -Path "C:\_\Apps\_PowerShell\_GUI Forms\.NET Report Builder\ReportDesigner5.dll"


$rd = New-Object ReportDesigner5.Designer


#$rd.Dock = [System.Windows.Forms.DockStyle]::Fill
$rd.ShowDialog()
$rd.ShowProperty()
$rd.ShowReportTree()

image

One TIP : Winking smile

– You also need to run it in PS x86 bit for the moment.

– You need to play around with the ZOOM scalar to get the report visible 

Alternatively you can as well download the EXE  here :

That has the same possibilities if you don’t want to use PS.

image

A new version was released on my request to add SQLite as a data source Smile

image

image

Keep in mind that you need to reference 2 additional Assemblies for this version :

1. QRCoder.dll version 1.3.5

2. SQLIte.dll version 1.0.109

Many thanks the to developer to create this nice Reporting tool and providing the Assembly !

Enjoy !


PowerShell – Retrieve a SSRS Report in a .NET GUI

May 5, 2019

Ever wanted to create you Offline Reporting Engine based on SSRS using PowerShell !

The objective is to run the RDL reports server side and display in .NET GUI client side.

PREREQUISITES :

You first need to download the .NET Assemblies using the SSRS redistributables.

Download here :

2008 / 2010 / 2012 or 2015 for a newer version see here

SOLUTION :

The solution is based on .Net Scripting in PowerShell using a GUI.

REMOTE processing mode :

CLS
# [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.ReportViewer.WinForms”)
Add-Type -Path "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.WinForms\12.0.0.0__89845dcd8080cc91\Microsoft.ReportViewer.WinForms.dll"

#Windows.Forms for viewing dialog box
# [void][System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
Add-Type -AssemblyName "System.Windows.Forms"

# for credentials, if needed
# [void][System.Reflection.Assembly]::LoadWithPartialName("System.Net")
Add-Type -AssemblyName System.Net

$credential = Get-Credential

$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer;
$rv.ServerReport.ReportServerCredentials.NetworkCredentials = $credential

$rv.ProcessingMode = "Remote"

$rv.ServerReport.ReportServerUrl = "http://YourServer/Reportserver"
$rv.ServerReport.ReportPath = "/YourReportName”

$rv.Height = 600;
$rv.Width = 800;
$rv.RefreshReport();

#---------------------
# Show as Dialog Using Windows Form
#---------------------
# Create a new form
$form = New-Object Windows.Forms.Form;

# Form Properties
$form.Height = 610;
$form.Width= 810;
$form.Controls.Add($rv);
$rv.Show();
$form.ShowDialog();

#---------------------
# Export to Excel
#---------------------
#now let’s try exporting to Excel
$mimeType = $null;
$encoding = $null;
$extension = $null;
$streamids = $null;
$warnings = $null;

$bytes = $rv.ServerReport.Render("Excel”, $null,
[ref] $mimeType,
[ref] $encoding,
[ref] $extension,
[ref] $streamids,
[ref] $warnings);
$file = "C:\Temp\SampleReport.xls”;
$fileStream = New-Object System.IO.FileStream($file,
[System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);
$fileStream.Close();

#l Open up our excel application
$excel = New-Object -comObject Excel.Application
$excel.visible = $true;
$excel.Workbooks.Open($file) | Out-Null;

#---------------------
# Export to PDF
#---------------------
$bytes = $rv.ServerReport.Render("PDF”, $null,
[ref] $mimeType,
[ref] $encoding,
[ref] $extension,
[ref] $streamids,
[ref] $warnings);
$file2 = "C:\Temp\SampleReport.pdf”;
$fileStream = New-Object System.IO.FileStream($file2,
[System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);
$fileStream.Close();
[System.Diagnostics.Process]::Start($file2)
image

The result is the report in your own GUI that runs locally and is processed centrally, when you use REMOTE

If the report has Parameters it will prompt you to enter them just like in the Web Form.

image

After the GUI is close the script will be EXPORT the content to EXCEL and PDF too.

You will have the options to EXPORT to all supported formats, or add your own extension if you are working server side.

image

LOCAL processing mode :

Most interesting is that you can run the Report Viewer Control in LOCAL processing mode !

Which means that you can create a Local Reporting Engine that runs the RDL reports from you client in a PowerShell GUI

image

And additionally you can add any kind of .Net support Data Source, like in this case an SQLite database !

Which is not supported by server side SSRS…

How cool is that ! Surprised smile

For more info see here

Happy Scripting Smile

Enjoy!


Windows 10 – DotNet Development NO Visual Studio

October 16, 2018

Recently I needed to develop an VPN Builder for Windows 7 for the L2TP protocol.

To automate the VPN entries on the client and to hide the Private Shared Key for the user.

 

PowerShell can do this…  But does not support the cmdlet Add-VpnConnection onWindows 7

It is only available for Windows 10 Sad smile

 

SOLUTION :

1. Download the DotRas Library from the Nuget site

https://www.nuget.org/packages/DotRas.for.Win7/

image

Tip : Winking smile

When you have saved the .nupkg package you can just open this using 7-Zip

image

Extract the nice documentation file DotRas.chm that is included.

image

And extract the DotRas.dll (for your appropriate .Net version 2.0 or 4.0)

In my case I used the DotNet 2.0 version

 

2. VB.Net Source code

On the net you can find relevant source code to build your application for L2TP, if you want to have a head start.

https://blog.cjwdev.co.uk/2011/05/19/automate-creation-of-an-l2tp-vpn-with-pre-shared-key-and-automatically-use-windows-credentials/

And modify to your own needs ….

 

3. Compile your application

You can use the commandline compiler VBC.exe that is part of your local DotNet installation on any Windows machine.

Or you can use my Visual Studio Light wrapper  Smile

 

%systemroot%\Microsoft.NET\Framework\v2.0.50727\vbc.exe /nowarn /nologo /quiet /debug- /t:Winexe VPN_L2TP_Builder.vb /r:DotRas.dll -out:VPN_L2TP_Builder.exe -win32resource:VPN.res

 

As you can see I needed to reference the DotRas.dll Assembly and I used the custom Resources to assing a custom ICON and File Properties.

See here on how to build a custom Resource file using Resource Hacker

 

4. Result

image

 

As you can see we don’t need 10 ton of software, just to build a simple functional DotNet Application Smile

 

Some extra tips for L2TP errors here  Winking smile

 

TIP : 

To avoid an “appcrash” you need to make sure that you compile it on the same OS as where the .EXE is going to be used !

In this case it is Windows 7 !

 

Enjoy!


Windows – Server 2012r2 cannot install .Net Framework 3 / 3.5

September 21, 2017

This error when you try to install Microsoft .Net Framework 3 or 3.5 on a server running Windows Server 20012 R2.

Using Windows Server 2012 RC from the VHD, and selecting the .NET Framework 3.5 Feature, gives error message “The source files could not be found”

clip_image002

This issue continues even if you manually download the application and try to install it.

Solution :

To solve this issue, you need to install the .Net 3.5 framework from the Windows 2012 R2 installation DVD. The files are located on “Source\sxs”.

Note: To improve the speed you can copy the files from the DVD to any folder on the server.

For this example, the files were copy to “C:\temp\sxs”.

Open a CMS windows as an “Administrator” and paste the following command:

C:\Windows\system32>dism.exe /online /enable-feature /featurename:NetFX3 /Source :C:\temp\sxs /LimitAccess /all

Note: Modify the /Source attribute if needed.

If the process was successful, you will see the following message

clip_image004


AutoIT – Meets PowerShell using .NET Common Language Runtime (CLR) Framework

August 9, 2017

Since AutoIT has got access the to the .NET Common Language Runtime (CLR) Framework 

We can access the PowerShell Automation Classes as well.

This is really powerfull stuff … because we can reuse .NET code as well as all the native CMDlet’s and scripts.

As you can see the script runs an PowerShell command that lists all running processes on your machine. And return the Output to a .NET Grid.

image

Which runs inside our Unmanaged Host, isn’t that cool …Smile

We can access native .NET Classes as well because PS runs on top off it.

Next is that you could access any kind of Cloud Web Service using the multitude of PS Modules that are available.

As well as accessing Custom Build Assemblies.

How to :

For more information on how to access .NET Classes see the above post about .NET Common Language CLR Framework.

Or Join the most friendly and helpful AutoIT Community on the net Smile

See here for some Examples and use cases :

Download the full story here : NET CLR Framework for AutoIT

I will post some more examples in the AutoIT Forum, so stay tuned …


Oracle – ASP.Net Error [im003] Error

October 8, 2014

Error [im003] specified driver could not be loaded due to system error  5 (oracle in oracle9i)

I had a strange error when accessing an Oracle database over ODBC using an ASP.NET (4.0 framework) Application running on IIS 6.0.

The ASP.net page could not load the database tables while the ODBC manager gave no errors ?

image

In the eventviewer I saw a lot of ASP.NET 4.0.30319.0 – Event ID 1309

image

This showed definitely a security issue on the Oracle home directory.

image

Solution :

Oracle 9.2 Client software requires that you give the Authenticated User privilege to the Oracle Home by following these steps:

1. Log on to Windows as a user with Administrator privileges.

2. Launch Windows Explorer from the Start Menu and and navigate to the ORACLE_HOME folder. This is typically the “Ora92” folder under the “Oracle” folder (i.e. C:\Oracle\Ora92).

3. Right-click on the ORACLE_HOME folder and choose the “Properties” option from the drop down list. A “Properties” window should appear.

4. Click on the “Security” tab of the “Properties” window.

5. Click on “Authenticated Users” item in the “Name” list (on Windows XP the “Name” list is called “Group or user names”).

6. Clear the “Read and Execute” box in the “Permissions” list under the “Allow” column (on Windows XP the “Permissions” list is called “Permissions for Authenticated Users”).

7. Re-check the “Read and Execute” box under the “Allow” column (this is the box you just unchecked).

8. Click the “Advanced” button and in the “Permission Entries” list make sure you see the “Authenticated Users” listed there with:

· Permission = Read & Execute
· Apply To = This folder, subfolders and files

If this is NOT the case, edit that line and make sure the “Apply onto” drop-down box is set to “This folder, subfolders and files”. This should already be set properly but it is important that you verify this.

9. Click the “OK” button until you close out all of the security properties windows. The cursor may present the hour glass for a few seconds as it applies the permissions you just changed to all subfolders and files.

10. Reboot your computer to assure that these changes have taken effect. (no need for this in my case).

11. Re-execute the application and it should now work.

This made it works like a charm !

Reason was that IIS user did not have enough permission on the folders and subfolders ?


PowerShell – Run a .Net Assembly DLL from in Memory

September 7, 2014

In my previous post I showed you how to load a .NET Assembly DLL without registering in the GAC.

Using the technique found here you can run the .NET Assembly DLL from Memory !

Run this script from the above site using your new .NET DLL (see my previous post).

CLS

function Out-CompressedDll
{
    [CmdletBinding()] Param (
        [Parameter(Mandatory = $True)]
        [String]
        $FilePath
    )

    $Path = Resolve-Path $FilePath

    if (! [IO.File]::Exists($Path))
    {
        Throw "$Path does not exist."
    }

    $FileBytes = [System.IO.File]::ReadAllBytes($Path)

    if (($FileBytes[0..1] | % {[Char]$_}) -join '' -cne 'MZ')
    {
        Throw "$Path is not a valid executable."
    }

    $Length = $FileBytes.Length
    $CompressedStream = New-Object IO.MemoryStream
    $DeflateStream = New-Object IO.Compression.DeflateStream ($CompressedStream, [IO.Compression.CompressionMode]::Compress)
    $DeflateStream.Write($FileBytes, 0, $FileBytes.Length)
    $DeflateStream.Dispose()
    $CompressedFileBytes = $CompressedStream.ToArray()
    $CompressedStream.Dispose()
    $EncodedCompressedFile = [Convert]::ToBase64String($CompressedFileBytes)

    Write-Verbose "Compression ratio: $(($EncodedCompressedFile.Length/$FileBytes.Length).ToString('#%'))"

    $Output = @"
`$EncodedCompressedFile = @'
$EncodedCompressedFile
'@
`$DeflatedStream = New-Object IO.Compression.DeflateStream([IO.MemoryStream][Convert]::FromBase64String(`$EncodedCompressedFile),[IO.Compression.CompressionMode]::Decompress)
`$UncompressedFileBytes = New-Object Byte[]($Length)
`$DeflatedStream.Read(`$UncompressedFileBytes, 0, $Length) | Out-Null
[Reflection.Assembly]::Load(`$UncompressedFileBytes)
"@

    Write-Output $Output
}

# See Example on the site
# Out-CompressedDll .\test.dll | Out-File LoadDll.ps1

Out-CompressedDll "C:\\_\\Apps\\Hello World.dll" | Out-File .\LoadDll.ps1

Invoke-Item ($Env:USERPROFILE+"\LoadDll.ps1")

This will generate a new PS1 Script which will contain the compressed DLL code as a binary string.

image

Add your static method to call whatever you specified in your DLL.

image

Run it to see that is works.

So bottom line is that you don’t need to register in the GAC and don’t need to copy the DLL to your machines in order to run it Smile

Isn’t that great for a scripter !