SQLServerCE

Database‎ > ‎

SQLServerCE Tips

Microsoft SQL Server CE is a free file based SQL serverver provided by Microsoft.

Updated 1/16/2022

Index

What is SQLServerCE?
Install SQLite and SQL Server Compact Toolbox
Create a CE database
Server Management Studio for CE?

I wrote a family tree program on .NET framework, using SQL server. Unfortunately I cannot give the app for my children, since they have to buy and install SQL server. I searched for a file based SQL server and found only two are available under .NET framework: SQLServerCE and SQLite. Both of them have Entity Framework 6 tools available. I tried to use SQLite first but the datatype it supports is limited (see https://www.sqlite.org/datatype3.html). In particular, DateTime is TEXT type and the easiest one is to use the ISO8601 string format YYYY-MM-DD HH:MM:SS.SSSS. This is too cumbersome for a family tree program. On the otherhand, SQL Server Compact (4.0 SP1 at this time) is offered by Microsoft and free. (see here. More convenience is provided by using NuGet package EntityFrameowrk.SqlserverCompact (currently 6.4.4) which contains both Microsoft.SqlserverCompact and EntityFramework.SqlServerCompact.

Top

Install SQLite and SQL Server Compact Toolbox

SQL Server Manager is the essential tool for SQL server database. Unfortunately SQL Server CE 4.0 is not supported. SQLite and SQL Server Compact toolbox is the one you need so that you can run script to create a database and tables (see here). It is provided as Visual Studio Extension. Currently supports VS2017, 2019, 2022. Visual Studio Menu -> Extnsions -> Manage Extensions. Then Online -> SQLite and SQL Server Compact Toolbox. You select Install. Close Visual Studio and it starts installing. After installation, it will appear in Visual Studio -> SQLite/SQL Server Compact Tool Box. This works fine under VS2019. Currently it crashes VS2022 when you try to add connection.

Top

Create a CE database

Since a CE database is a file-based, you have to use a special command to create a CE database in a file system. Here is the command (here). After created the database, you can use "VisualStudio SQLiteSQL Server Compact Toolbox" to create tables. The command "sqlcecmd40.exe" is available from https://github.com/ErikEJ/SqlCeCmd/releases.


  sqlcecmd40 -d "Data Source=C:\aw.sdf" -e create

Note that you replace "c:\aw.sdf" with your own.

Top

Server Management Studio for CE?

Unfortunately Microsoft stopped support for CE4.0 under SSMS from SSMS 2012. LINQPad5 supports SQLCE4.0 query (setting Language to SQL) Home