MS SQL (CE) Compact Edition – Tools

July 1, 2014

There a lot of nice Tools and utilities around to work with MS SQL CE, apart from the Webmatrix interface

image

And of course don’t forget your Powershell !

Here is a shortlist :

SQL CE Schema Script Utility

http://exportsqlce.codeplex.com/

image

Also has commandline utils that can convert SQLCE to SQLite

Usage : (To script an entire database to SQLite format)

ExportSQLCE.exe [SQL CE Connection String] [output file location] [sqlite]

http://sqlcetoolbox.codeplex.com/

image

SQLCE Commandline tool

http://sqlcecmd.codeplex.com/

image

Bulk Insert Tool

http://sqlcebulkcopy.codeplex.com/

image

SQL CE Toolbox

http://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1/

image

Query and Database Management Tools

MS SQL CE Analyzer

http://sqlcequery.codeplex.com/

image

CompactView

http://sourceforge.net/p/compactview/home/Home/

image

LinQPad : Supports SQL CE, SharePoint and SQLite, and moreMust have tool ++++

http://www.linqpad.net/

image

SQLite and SQL CE together in 1 GUI

image

Mini SQL Query : Supports SharePoint and SQLite

http://blog.bendsoft.com/2013/01/minisqlquery-with-sharepoint-connector-3-0/

http://blog.bendsoft.com/2013/01/minisqlquery-with-sharepoint-connector-3-0/

image

Open MS SQL Compact using the management Studio
Choose the correct Server Type !

image

Some issues in later versions of the SQL CE edition :

Just to add a brief update now that SQL CE 4.0 is out. SQL management studio 2008 does not support SQL CE 4.0.

There are some helpful tools at sqlcetoolbox.codeplex.com.

Also see social.msdn.microsoft.com/Forums/en-US/sqltools/thread/… and

weblogs.asp.net/scottgu/archive/2011/01/11/…


Accessing MS SQL Compact Edition – using Powershell

July 1, 2014

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 !!