On the Windows platform, SQL Server can serve most any need for storing relational data. SQL Server is a top quality DBMS system and offers just about everything developers could want: CRUD services, transactionality, concurrency, programmability, and a large ecosystem of tools. As an enterprise-level application, SQL Server runs as a service. When run as a separate application, SQL Server may live on a physically distant machine and require a connection and data exchange. While this extra layer of complexity is acceptable, and often required, in enterprise applications, it sometimes proves to be too much for simpler scenarios such as storing relational data on mobile devices.
Mobile devices, however, are currently the most popular scenario where developers feel the need for something simpler and more manageable than a full installation of SQL Server. As a trainer and consultant, I often build quick prototypes of applications that need to read and write to a database. When I pass my code to students or customers, it would be much easier to just offer a database file. This is not something that can happen with the complex, service-based nature of SQL Server.
For years, Microsoft Access was used as a relational database for simple applications. However, Access was never designed to serve as a database engine outside single-user, local applications. When deployed as the data store of a Web application, it shows serious performance issues, the most dramatic of which is the bottleneck in writing. To offer something simpler than the full SQL Server, Microsoft introduced SQL Server Express. That product fixed all the issues of Access, and can definitely be used as the back-end engine in simple and low-volume applications. But one big issue remained SQL Server Express doesn't support x-copy deployment well which is precisely what made Access a common choice. In simple applications, often all that you want is the ability to copy a database on a server, and continue without having to deal with configuration.
Microsoft has now released SQL Server Compact Edition 4.0 (SQLCE), and this is the definitive response to the demand for having a simple and lightweight database for developers who need to do simple things. SQLCE supports databases as files. Both the SQLCE runtime and database files can be packaged and deployed with applications without relying on a database administrator. Compatible with .NET 3.5 and later, the runtime of SQLCE is in-process, multithreading, and multiuser. More importantly, it starts up with the hosting application, whether ASP.NET or Windows.
SQLCE database files are characterized by the SDF file extension and must be created and managed entirely in code. In other words, there's no console application (such as SQL Server Management Studio) to help you organize multiple tables, indexes, and stored procedures. You do most of this work specifically design, editing, and running queries from the user interface of Visual Studio 2010 Service Pack 1. SQLCE is the default database for WebMatrix and it works in medium- or partial-trust environments, which makes it a good fit for simple and/or mobile scenarios.
As far as programming is concerned, SQLCE offers a T-SQL syntax. Although this is inferior to full SQL Server, it is powerful enough to cover some of the most-advanced common scenarios. For example, SQLCE supports paging queries and offers an API to retrieve metadata information out of the database. In addition, when used with Entity Framework, SQLCE supports server-generated columns (such as identity columns) and GUID rows. Finally, the database files can be encrypted using the SHA2 encryption algorithm. SQLCE has a query syntax and internal model of the database files similar to SQL Server, which makes moving from a solution based on SQLCE to one based on full SQL Server a smooth transition.
You can code against a SQLCE database using either low-level ADO.NET commands or the increasingly popular code-first approach. Code-first means that you create a set of entities and relationships as plain C# classes and then use a mix of conventions and code to map these classes to SQLCE database tables.
The latest version of SQLCE (version 4.0) is not compatible with Windows Phone. This simply means that you can't leverage the SQLCE engine and design tools of Visual Studio, but reading and writing relational data from within a Windows Phone application is still possible. Windows Phone 7.5 supports a LINQ-to-SQL API that can create a SQLCE database file in the isolated storage of the device and operate on it.
Overall, the best approach for mobile developers is not so different from what you do when using SQLCE, say, in a WebMatrix application. You create plain C# classes and use an attribute to mark them as tables and their properties as columns. Next, based on the information in the connection string, you create a new database programmatically or read an existing one which can be an existing SDF file created with SQLCE in another application. Finally, you use LINQ-to-SQL to prepare queries, and the data context object to arrange writes and deletions.
In the end, even though the SQLCE runtime is not supported in Windows Phone, learning how to code against SQLCE database files will help when you do Windows Phone database development. In Windows Phone, while you can't rely on the SQLCE engine, you do have an API to read and write SQLCE files.
SQLCE is the real deal the lightweight DBMS system that developers have been waiting for ever since the limitations of Access first became clear. As such, SQLCE may finally sound the death knell for the popular Microsoft Access.
Dino Esposito is a frequent contributor to Dr. Dobb's. His most recent book, Programming Microsoft ASP.NET MVC was published by Microsoft Press.