PowerShell – Database Choise SQLite vs SQLCE

 

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: