Share via

SQL Server Compact Edition and Visual Studio

  • Article

Let me start with a brief description of what SQL Server Compact Edition is and something about its internals.

SQL Server Compact Edition is a file-based database that consists of seven DLLs that are approximately 1.4 MB and does not run as a service to keep the footprint low. When you create a SQL Server Compact Edition database it is saved as .SDF file and SQL Server Compact Edition only save one database per file. This feature helps in managing concurrent access and locking. SQL Server CE also supports multiple connections but limit is 256 connections. One other limitation which might be very crucial if you are using it on devices is the size limit. Max size that SQL Server CE is 4GB.

If you have used SQL Server CE you would have noticed that the connection string is the path to the .sdf file what does that mean. This is because as I earlier said it is file-based database.

To use SQL Server Compact Edition with Visual Studio, you must install both the SQL Server Compact Edition runtime and the SQL Server Compact Edition Tools for Visual Studio.

SQL Server Compact Edition now supports the Data Directory macro when connection is established using SqlCeConnection object. What does that mean and how does it affect me as developer. First let me explain what it means by Data Directory Macro. It means that instead of specifying the entire file path you can use |DataDirectory|\DatabaseName.sdf and it will get resolved automatically. But let me tell you this is followed when the deployment model you are using is private-filed deployment (deployed a part of the project).

Now this is the interesting part. Visual Studio uses this model to deploy your application into the emulator.

Visual Studio creates a copy of the .sdf file each time you build your application and dumps it into the build folder as it uses Data Directory macro to access the file it gets resolved every time. You might have connected to the source database file i.e. the file you included into the project and when you run the application VS will be using the one in the build folder. So if you run insert command form your application and check your source database to see the new rows added you would not find them there.

So don’t think that your INSERT statement did not work just check the copy in the build folder and you will find the new added rows.