SQL Server Compact Edition, previously known as SQL Server Everywhere, is a lightweight and free database that is ideal for small applications with a single user. Unlike databases like MS Access or SQL Server Express, SQL Server Compact Edition has a disk footprint of less than 2 MB. One of its key advantages is that it does not require a server process, making deployment and distribution of applications incredibly easy.

Compared to SQL Server Express, SQL Server Compact Edition differs in several ways. SQL Server Express is a multi-user, out-of-process database server that runs as a system service. On the other hand, SQL Server Compact Edition is an in-process component that loads within the client application’s memory space. The database engine of SQL Server Compact Edition is based on SQL Server 2005 Mobile Edition. Additionally, SQL Server Compact Edition has a maximum database size limit of 4GB and does not support stored procedures, XML data types, or CLR integration.

To get started with SQL Server Compact Edition, you can download it from the official Microsoft website. Once downloaded, you can create a basic “Hello World” application that demonstrates how to connect to a SQL Server Compact Edition database file and display data on a GridView control.

Hello World Application

Let’s create a new Windows Forms Application and add a reference to the System.Data.SqlServerCe assembly. This assembly will be added to the Global Assembly Cache (GAC) during installation, making it easily accessible.

Next, we’ll create a data source for our sample application. Click on the Data menu and select “Add new data source”. Choose “Database” and click “Next”. In the Add Connection dialog, select “Microsoft SQL Server Compact Edition” as the Data Source and browse for the location of the database file. Once the database file is selected, test the connection and save the data source.

After configuring the data source, add a GridView control to the form and configure its Data Source property to point to the Customers table in the database. Compile and run the application, and you will see that the customer data is loaded onto the GridView control.

Conclusion

This basic application serves as an introduction to SQL Server Compact Edition. It demonstrates the process of creating a data source, connecting to the database, and retrieving data. SQL Server Compact Edition is a lightweight and easy-to-use database option for small applications with a single user.