July 1, 2014
There a lot of nice Tools and utilities around to work with MS SQL CE, apart from the Webmatrix interface
And of course don’t forget your Powershell !
Here is a shortlist :
SQL CE Schema Script Utility
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]
SQLCE Commandline tool
Bulk Insert Tool
SQL CE Toolbox
Query and Database Management Tools
MS SQL CE Analyzer
LinQPad : Supports SQL CE, SharePoint and SQLite, and more … Must have tool ++++
SQLite and SQL CE together in 1 GUI
Mini SQL Query : Supports SharePoint and SQLite
Open MS SQL Compact using the management Studio
Choose the correct Server Type !
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
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.
$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"
$rdr = $cmd.ExecuteReader()
# $dt | Out-Default | Format-Table
$dt | Out-GridView -Title "My LDF Database Test"
When you get an UPGRADE error, using an older version of SDF database file like this.
You can use this code to upgrade from version 3.x to 4.x database format
$binpath = "C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Desktop\";
$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.
# $cn = new-object "System.Data.SqlServerCe.SqlCeConnection" $connStringCI
For more Tools see shortList here