PowerShell – Database Choise SQLite vs SQLCE

October 12, 2017

 

Over time I wrote some posts relating to SQLCE as a nice standalone Database platform that can by used in PowerShell or Webmatrix on IIS etc.

Now that MS has announced the end of life of Webmatrix Sad smile

“WebMatrix product support ends on November 1st, 2017”

As Webmatrix runs on MSSQLCE we need to look for alternatives…

“MS SQLCE support will end in July 2021”

One of the most obvious alternative is of course SQLite, which is shipped as part of AutoIT Winking smile

 

Besides this, SQLite has much more to offer then SQLCE :

Comparison http://erikej.blogspot.be/2011/01/comparison-of-sql-server-compact-4-and.html

 

As you can see the size limit is 140 TB and unlimited concurrent users !

And it runs on Windows / Linux / Mac … and as well on Mobile devices.

So this will do for a while … Winking smile

 

SOLUTION :

1. Download the appropriate .NET Assembly for your environment

https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

2. Run this PS code :

Add-Type -Path "C:\Temp\.NET SQLite Library\System.Data.SQLite.dll" 

$con = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$con.ConnectionString = "Data Source=C:\Temp\.NET SQLite Library\northwindEF.db"

$con.Open()

$sql = $con.CreateCommand()
$sql.CommandText = "SELECT * FROM Customers"
$adapter = New-Object -TypeName System.Data.SQLite.SQLiteDataAdapter $sql
$data = New-Object System.Data.DataSet

[void]$adapter.Fill($data)

"Rows # :  " + $data.tables.rows.count
$data.tables.rows | Out-GridView # | ft

3. Test it.

Once you run it you can access the SQLite Database lightning fast …

 

image

 

Some more Tips and Tools you can find here :

http://erikej.blogspot.be/2014/08/sqlite-toolbox-40-visual-guide-of.html

http://erikej.blogspot.be/2014/11/using-sqlite-with-entity-framework-6.html

Enjoy !

Advertisements

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 we AutoIT have 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

I will post some more examples and How To’s later on, so stay tuned …


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

April 27, 2017

CLR : The Common Language Runtime (CLR) is a an Execution Environment .

Common Language Runtime (CLR)’s main tasks are to convert the .NET Managed Code to native code, manage running code like a Virtual Machine, and also controls the interaction with the Operating System.

As part of Microsoft’s .NET Framework, the Common Language Runtime (CLR) is managing the execution of programs written in any of several supported languages.

Allowing them to share common object-oriented classes written in any of the languages.

 

Appdomain : To access the CLR environment you need to create an Appdomain Object.

An AppDomain provides an isolated region in which code runs inside of an existing process.

Application domains provide an isolation boundary for security, reliability, and versioning, and for unloading assemblies.

Application domains are typically created by runtime hosts, which are responsible for bootstrapping the common language runtime before an application is run.

 

CLR Runtime Hosts : When would you use CLR Runtime Hosts.

1. To access .NET Class Libraries :

  • System
  • System.Collections
  • System.Data
  • System.Drawing
  • System.IO
  • System.Text
  • System.Threading
  • System.Timers
  • System.Web
  • System.Web.Services
  • System.Windows.Forms
  • System.Xml

2. Accessing custom build .Net Assemblies :

Some Examples (but there are a ton out there)

  • AutoItX3 – The .NET Assembly for using AutoItX
  • JSonToXML libr.
  • XMLRPC Libr.
  • .NETPDF libr.
  • .NETOCR Libr
  • WInSCP Libr.

    3. To Compile .Net Code into an Assembly at Runtime

    4. To Run C# or VB.net Code at Runtime

    5. To Mix AU3 and .Net functionality in your Application

 

Documentation : Where to find info about CLR hosting

First of all you can find a lot on MSDN relating to .Net CLR Hosting and of course here :

AutoIt .NET CLR Framework

 

Examples : All examples are included in the above thread of the AutoIt forum.

1. To access .NET Class Libraries :

Example : “System.Text.UTF8Encoding”

image

Example : “System.IO.FileInfo”

image

Example : “System.Windows.Forms”

image

 

2. Accessing custom build .Net Assemblies :

Example : AutoItX3 .NET Assembly using AutoItX

image

 

3. To Compile .Net Code into an Assembly at Runtime

Example : Compile Code C# and Code VB

image

 

4. To Run C# or VB.net Code at Runtime

Example : Compile Code C# at Runtime

image

 

5. To Mix AU3 and .Net functionality in your Application

All of the above Examples have a mix of au3 functionality and .Net CLR code

 

This CLR.au3 UDF makes a bridge between .Net and Au3, which give a new dimension to our scripting experience.

Enjoy !


Windows Core IoT – Accessing a DotNet DLL assembly on a Raspberry PI3 using Windows Core IoT

January 23, 2017

In this post I showed how to create an Console app using my Visual Studio Light for ARM, to run on a Raspberry PI3 on Windows Core IoT.

Taking it one step further is that you access an DotNet Assembly that was created before, and seem to run as well on X86 as on ARM Smile

So most Assembly DLL’s seem to be architecture independend …

 

image

 

Let’s see here :

Using ILSpy you can see the source of the self made .NET Assembly.

image

 

image

I copied the DLL Assembly to the RPI3, did a remote access from PS IDE and ran the following code.

image

 

As you can see it ran without any issues ! the result was returned as expected … Smile

Enjoy !


Windows Core IoT -Developing a Console App for running on a Raspberry PI3 – Without Visual Studio

December 31, 2016

I was wondering wondering If we could create an App for running on our Rasberry PI3, where Windows 10 Core IoT is installed.

As you might know regular EXE files don’t run on this windows version, because a RPI3 uses an ARM Architecture.

While a normal Windows is based on x86 or x64 Architecture.

 

Solution :

Some years back I created a wrapper for the DotNet Compiler and called it Visual Studio Light

I modified this version that was created in AU3 to compile for ARM architecture.

(Just added the PLATFORM Switch for ARM)

 

So next I compiled this simple Console Application :

class HelloCsharp
{
static void Main()
{
System.Console.WriteLine (“Hello World from C#. on ARM Architecture like Raspberry PI3 running Windows 10 Core IoT”);
}
}

 

image

 

There where some errors popping up, but they seem to be normal.

Because I compiled the exe on a x64 Machine, and the EXE was compiled for and ARM Archtecture.

So it started complaining about this, but don’t worry  Winking smile

Next I copied the EXE for ARM to my RPI3.

image

Logged into a PS session on the RPI3, and it ran without any problems Smile

 

image

 

Enjoy!


PowerShell – Using the .Net Charting Controls

January 4, 2015

Picked this up on the internet Smile Great extension to Powershell and .NET

What you need to get started is the

Microsoft Chart Controls for Microsoft .NET Framework 3.5

The Charting Controls documentation is here :

Microsoft Chart Controls for .NET Framework Documentation

image

Once installed you are ready to go, here is an example script showing the top 5 largest files in a directory.

image

If you hit the “Save button” it will save the graph to the desktop.

Here is the script :

CLS

if([Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization") -eq $null)
{
    #Microsoft Chart Controls are not installed
    [void][reflection.assembly]::Load("System.Windows.Forms, Version=2.0.0.0,`
    Culture=neutral, PublicKeyToken=b77a5c561934e089")
    [void][System.Windows.Forms.MessageBox]::Show("Microsoft Chart Controls for Microsoft .NET 3.5`
    Framework is required", "Microsoft Chart Controls Required")
    #Open the URL
    [System.Diagnostics.Process]::Start("http://www.microsoft.com/downloads/en/`
    details.aspx?familyid=130F7986-BF49-4FE5-9CA8-910AE6EA442C&displaylang=en");
    return $false
}

# load the appropriate assemblies 
[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") 
[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")

   
# create chart object 
$Chart = New-object System.Windows.Forms.DataVisualization.Charting.Chart 
$Chart.Width = 500 
$Chart.Height = 450
$Chart.Left = 40 
$Chart.Top = 30
   
# create a chartarea to draw on and add to chart 
$ChartArea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea 
$Chart.ChartAreas.Add($ChartArea)


# add data to chart 
$Files = dir $env:USERPROFILE -Recurse -File | sort length  | select -last 5 # | % {"$_.Name " + [math]::Round(($_.Length / 1MB),2)}
$FileNames = @(foreach($File in $Files){$File.Name}) 
$FileSize = @(foreach($File in $Files){[math]::Round(($File.Length / 1MB),2)}) 


[void]$Chart.Series.Add("Data") 
$Chart.Series["Data"].Points.DataBindXY($FileNames, $FileSize)

# add title and axes labels 
[void]$Chart.Titles.Add("Top 5 Files - Descending") 
$ChartArea.AxisX.Title = "File Name" 
$ChartArea.AxisY.Title = "File Size - [MB]"

# Find point with max/min values and change their colour 
$maxValuePoint = $Chart.Series["Data"].Points.FindMaxByValue() 
$maxValuePoint.Color = [System.Drawing.Color]::Red 

$minValuePoint = $Chart.Series["Data"].Points.FindMinByValue() 
$minValuePoint.Color = [System.Drawing.Color]::Green

# change chart area colour 
$Chart.BackColor = [System.Drawing.Color]::Transparent

# make bars into 3d cylinders 
$Chart.Series["Data"]["DrawingStyle"] = "Cylinder"

# display the chart on a form 
$Chart.Anchor = [System.Windows.Forms.AnchorStyles]::Bottom -bor [System.Windows.Forms.AnchorStyles]::Right -bor 
                [System.Windows.Forms.AnchorStyles]::Top -bor [System.Windows.Forms.AnchorStyles]::Left

# sorting the dataset
$Chart.Series["Data"].Sort([System.Windows.Forms.DataVisualization.Charting.PointSortOrder]::Descending, "Y")

# add a save button 
$SaveButton = New-Object Windows.Forms.Button 
$SaveButton.Text = "Save" 
$SaveButton.Top = 500 
$SaveButton.Left = 450 
$SaveButton.Anchor = [System.Windows.Forms.AnchorStyles]::Bottom -bor [System.Windows.Forms.AnchorStyles]::Right 
$SaveButton.add_click({$Chart.SaveImage($Env:USERPROFILE + "\Desktop\Chart.png", "PNG")}) 


# save chart to file 
$Chart.SaveImage($Env:USERPROFILE + "\Desktop\Chart.png", "PNG")

$Form = New-Object Windows.Forms.Form 
$Form.Text = "PowerShell Chart" 
$Form.Width = 600 
$Form.Height = 600 
$Form.controls.add($Chart) 
$Form.Add_Shown({$Form.Activate()}) 

$Form.controls.add($SaveButton)

$Form.ShowDialog()

A good way to start is reading here :

http://blogs.technet.com/b/richard_macdonald/archive/2009/04/28/3231887.aspx

You can build some nifty Tools with this ?

image

Enjoy!