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

PowerShell – Import-Pssession is not Importing Cmdlets for O365 Exchange Online

August 30, 2017

 

I noticed that after connecting to O365 Exchange Online using PowerShell, the connection was done OK.

But I could recognize any of the Cmdlets after connecting Sad smile

 

SOLUTION :

$LiveCred = Get-Credential -credential "patrick.taels@plastiflex.com"
   $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $LiveCred -Authentication Basic -AllowRedirection
Import-Module (Import-PSSession $Session -WarningAction SilentlyContinue -AllowClobber) -Global 

 

As you can notice you need to add the Import-Module( … –AllowClobber) –Global Command and Parameters …

Enjoy !


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 …


PowerShell – Download Module from Gallery

May 3, 2017

Option 1 :

Use a system that has a PS 5.0 or higher and save it there.

Copy it over and install it on a PS 3.0 or 4.0 version.

Inspect

PS> Save-Module -Name SqlServer -Path <path>

Install

PS> Install-Module -Name SqlServer

clip_image002

Option 2 :

Install the GUI : PackageManagement PowerShell Modules Preview – March 2016

https://www.microsoft.com/en-us/download/details.aspx?id=51451

clip_image004


AutoIT – Accessing PowerShell using COM Object

January 23, 2017

AutoIT is COM enabled and more … so we can use the the SAPIEN ActiveXPosh COM Object to run Powershell code.

Or even to access .NET Assemblies …

You can use this COM object to run in any COM Compatible scripting host, like VBScript, Word, Excel, Etc …

 

First you need to download the FREE PS Com object here :

Look at the bottom of the site after login for SUPPORT next SUPPORT DOWNLOADS, Next see folder FREE COMPONENTS
http://www.sapien.com/downloads#

 

Here are some examples translated to AU3

;**************************************************************************
; Copyright (c) SAPIEN Technologies, Inc. All rights reserved
; This file is part of the PrimalScript 2007 Code Samples.
;
; File: ActiveXposh.vbs
;
; Comments:
;
; Disclaimer: This source code is intended only as a supplement to
;   SAPIEN Development Tools and/or on-line documentation.
;   See these other materials for detailed information
;   regarding SAPIEN code samples.
;
; THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
; KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
; IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
; PARTICULAR PURPOSE.
;**************************************************************************

#AutoIt3Wrapper_UseX64=N

Dim $ActiveXPosh

Const $OUTPUT_CONSOLE = 0
Const $OUTPUT_WINDOW = 1
Const $OUTPUT_BUFFER = 2

Func CreateActiveXPosh()
Dim $success

; Create the PowerShell connector object
$ActiveXPosh = ObjCreate("SAPIEN.ActiveXPoSH")
$success = $ActiveXPosh.Init(False) ;Do not load profiles
If $success <> 0 then
Consolewrite( "Init failed" & @CR)
endif
If $ActiveXPosh.IsPowerShellInstalled Then
Consolewrite( "Ready to run PowerShell commands" & @CR)
Else
Consolewrite( "PowerShell not installed" & @CR)
EndIf
; Set the output mode
$ActiveXPosh.OutputMode = $OUTPUT_CONSOLE
EndFunc


Func DownloadFile($URL,$Destination)
Local $Return
Dim $Command
Dim $FSO

;Download a file with PowerShell
$ActiveXPosh.Execute("$Client = new-object System.Net.WebClient")
;Note that variables are preserved between calls
; Construct a $Command
$Command = "$Client.DownloadFile('" & $URL & "','" & $Destination & "')"
ConsoleWrite ("Downloading ..." & @CR)
$ActiveXPosh.Execute($Command)

$FSO = ObjCreate("Scripting.FileSystemObject")
If $FSO.FileExists($Destination) Then
ConsoleWrite ("File transfer complete" & @CR)
Else
ConsoleWrite ("File Transfer failed" & @CR)
EndIf
Return $Return
EndFunc

Func ListServices()
Dim $outtext
; Set the $OUTPUT mode to $BUFFER
$ActiveXPosh.OutputMode = $OUTPUT_BUFFER
$ActiveXPosh.Execute("Get-WmiObject -class Win32_Service | Format-Table -property Name, State")

; Get the $OUTPUT line by line and add it to a variable
For $str In $ActiveXPosh.OUTPUT()
$outtext = $outtext & $str
$outtext = $outtext & @CRLF
Next

; Alternatively you can get the $OUTPUT as a single String
; $outtext = $ActiveXPosh.OutputString

ConsoleWrite ($outtext & @CR)
$ActiveXPosh.ClearOutput() ; Empty the $OUTPUT $BUFFER
EndFunc

; Create the actual Object
CreateActiveXPosh()

$Status = $ActiveXPosh.GetValue("(Get-Service UPS).Status")
if($Status = "Stopped") then
ConsoleWrite ("UPS Service is stopped" & @CR)
else
ConsoleWrite ("UPS Service is " & $Status & @CR)
EndIf


; List all running processes using PowerShell
$ActiveXPosh.Execute("Get-Process")

; Check if WinWord is running using PowerShell
if $ActiveXPosh.Eval("get-process winword") = 1 Then
ConsoleWrite ("Microsoft Word is running" & @CR)
Else
ConsoleWrite ("Microsoft Word is not running" & @CR)
EndIf

DownloadFile ("[url="http://izzy.org/scripts/PowerShell/activexposh.pdf","C:\Temp\activexposh.pdf"]http://izzy.org/scripts/PowerShell/activexposh.pdf","C:\Temp\activexposh.pdf[/url]")

; Use ListServices to show all services in this machine using PowerShell()
ListServices()

ConsoleWrite ("Version " & $ActiveXPosh.GetValue("$PSHOME") & @CR)

$ActiveXPosh = ""

 

Enjoy !


Windows IoT Core – How to check the PowerShell Version and DotNet Version on

December 31, 2016

Just run this command and check your version.

 

image

 

As you can see that Windows 10 IoT core has PSEdition already installed.

Not sure yet if this is the same as DotNet Core ?

 

Best check for regular updates using the Web Interface.

image


Windows Core IoT – Accessing Raspberry PI3 – WMI Classes remotely using WBEM

December 30, 2016

Start using Powershell on Windows IoT on a Raspberry PI3

If you check all the available COM objects that are installed on the Windows 10 Core IoT, we see that WMI and WBEM is installed.

List all COM Objects like this.

image

This enables opportunities for Scripters to connect remote to access all available WMI classes Smile.

But unfortunately I always got an error message like this :

The RPC server is unavailable 0x800706BA

Solution :

First connect using WSMAN to connect to the RPI3 using PS

See my previous post Running Powershell Scripts on Windows 10 Core IoT installed on a Raspberry PI3

image

 

Once the Firewall is set correct, you have the way open to access the WMI classes remotely, without having to first access the local PI3 using WSMAN Winking smile

So let’s test it accessing the EventLogs on Windows IoT

image

Now that we have remote access we can use PS to list all EventLog Errors in a GRID to check what is going wrong.

image

 

All log files are located here :

image

You can also use other Scripting languages like VBScript

strComputer = “rpi3”
Set objSWbemLocator = CreateObject(“WbemScripting.SWbemLocator”)
Set objSWbemServices = objSWbemLocator.ConnectServer(strComputer, _
“Root\CIMv2”, _
“rpi3\administrator”, _
“P@ssW0rd”)

Set colItems = objSWbemServices.ExecQuery(“Select * from Win32_ComputerSystem”,,48)

For Each objItem in colItems
Wscript.Echo strComputer & ” responded.”
Wscript.Echo “Manufacturer: ” & objItem.Manufacturer
WScript.Echo “Model: ” & objItem.Model
Wscript.Echo “Status ” & objItem.Status
Wscript.Echo “SystemType: ” & objItem.SystemType
next

image

Or AU3 to remotely access the WMI Classes on the PI3 Smile

image

 

$StrCmp = "rpi3"
$StrUser = "rpi3\administrator"
$StrPswd = "P@ssW0rd"

$wbemFlagReturnImmediately = 0x10
$wbemFlagForwardOnly = 0x20

Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

$strComputer = "rpi3"
$objSWbemLocator = ObjCreate("WbemScripting.SWbemLocator")
$objSWbemServices = $objSWbemLocator.ConnectServer($StrCmp, "root\cimv2", $StrUser, $StrPswd)


$colItems = $objSWbemServices.ExecQuery("Select * from Win32_ComputerSystem", "WQL", $wbemFlagReturnImmediately + $wbemFlagForwardOnly)

For $objItem in $colItems
          ConsoleWrite($strComputer & " responded." & @CRLF)
          ConsoleWrite("Manufacturer: " & $objItem.Manufacturer & @CRLF)
          ConsoleWrite("Model: " & $objItem.Model & @CRLF)
          ConsoleWrite("Status " & $objItem.Status & @CRLF)
          ConsoleWrite("SystemType: " & $objItem.SystemType & @CRLF)
next

Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

Build your first Console App for an ARM Architecture:

If you are interested in taking it a few steps further, you can here how to build a Console App EXE for IoT.

Happy Scripting !