PowerShell – Accessing MS SQL (CE) Compact Edition

When you start working using Webmatrix or Windows Mobile, you might encounter a database format which is SQL CE.

This portable file format is a reduced format of MS SQL and written as an .SDF file.

Microsoft SQL Server Compact 4.0 is a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications. SQL Server Compact 4.0 has a small footprint and supports private deployment of its binaries within the application folder, easy application development in Visual Studio and WebMatrix, and seamless migration of schema and data to SQL Server

There are GUI Tools around to access the table and data inside. But I have not seen an ODBC  database driver around to access it. And reuse the data in you application.

That ‘s where PowerShell can come in.

Here is an example to on how to access the data inside the SDF file using native SQL Commands.

Using google you will find a demo database Northwind.sdf easily.

cls
$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (500, 25)

$binpath = "C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Desktop\";
[Reflection.Assembly]::LoadFile("$binpath\System.Data.SqlServerCe.dll") | out-null

$connString = "Data Source=C:\Northwind.sdf"
$cn = new-object "System.Data.SqlServerCe.SqlCeConnection" $connString

# create the command
$cmd = new-object "System.Data.SqlServerCe.SqlCeCommand"
$cmd.CommandType = [System.Data.CommandType]"Text"
$cmd.CommandText = "SELECT  * FROM Customers"
$cmd.Connection = $cn

#get the data
$dt = new-object "System.Data.DataTable"

$cn.Open()
$rdr = $cmd.ExecuteReader()

$dt.Load($rdr)
$cn.Close()

# $dt | Out-Default | Format-Table
$dt | Out-GridView -Title "My LDF Database Test"

image

When you get an UPGRADE error, using an older version of SDF database file like this.

image

You can use this code to upgrade from version 3.x to 4.x database format

cls

$binpath = "C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Desktop\";
[Reflection.Assembly]::LoadFile("$binpath\System.Data.SqlServerCe.dll")

$connStringCI = "Data Source=C:\Northwind.sdf; LCID= 1033; Case Sensitive=true;"

#Set "Case Sensitive" to true to change the collation from CI to CS.
$connStringCS = "Data Source='C:\Northwind.sdf'; LCID= 1033; Case Sensitive=true"

$engine = New-Object "System.Data.SqlServerCe.SqlCeEngine" $connStringCI

# The collation of the database will be case sensitive because of
# the new connection string used by the Upgrade method.
$engine.Upgrade($connStringCS)

# $cn = new-object "System.Data.SqlServerCe.SqlCeConnection" $connStringCI
# $cn.Open()

For more Tools see shortList here

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: