4 Easy steps to use SQL Server Compact (CE) in Visual Studio 2010 Express SP1

SQL Server Compact Edition is an embedded database that enables all functionality of SQL Server yet it is a simple and single file based. That means you don’t have to install the SQL Server. Simply copy the database file with .sdf extension and easily distribute it where ever you want to. Very useful in development environments where you can do all development, debug, test the application before moving to production where you are using SQL Server. If your data is not so big you can also use it in production environment instead of using MS Access.  Currently each sdf file can be up to 4Gb. Plenty of space to save tons of data. You can even run a real eCommerce store with 4Gb data. This will give easy entry point into SQL Server technologies and provide you with easy path to future upgrade to a full blown version of

SQL Server seamlessly.

What you need

You will need the Visual Web Developer 2010 Express SP1. You can get it from here

You will need SQL CE Tools for Visual Studio download. SQL CE Tools for VS gives you

• Create new SQL CE dB from the IDE
• Edit/Populate the dB
• Use the Entity Framework (EF) designer to create model layers against SQL CE databases and/or use EF Code First to define model layers in code, then create a SQL CE database from them
• Deploy SQL CE databases to remote servers using Web Deploy
• Convert SQL CE to full SQL Server databases

Create a new SQL CE Database from VS 2010 SP1

Click on the App_data folder in the project tree and select “Add Item”. “Choose SQL Server 4.0 Compact Local database” and click OK. You will a new database file called Database1.sdf is created and added to the App_Data folder.

Now you can right click and choose “Open” to go to the Database Explorer view. Here right click on the Tables under the Database1.sdf and you will see a menu item “Create”. Choose it. You will see the following dialog. Here you can specify your table name, and the columns and datatype one by one. Finally click OK to save the newly create into Database1.sdf.

Once you created the database and added a Table along with the needed columns with proper datatypes and assigned the primary-key etc, you are ready to access the table to populate it with initial data.

Click on the Table “Students” that you just created and choose “Show Table Data”. VS 2010 uses SQL Express libraries to open-up the table in view/edit mode so you can type in the data manually into each of the columns thus creating as many rows as you like.

Here I added data for five rows. Each row has three columns. The first column is “id”. I made it type “int” and Primary Key with “AutoIncrement” property set to true. That will enable auto creation of values into this column when ever I create a new row.

How to create index on SQL Server CE table column

You can use Visual Web Developer SP1 to create index on a SQL Server CE Database table. Lets take the above table and lets create an index on the string (nvarchar) field “Name”. Right click on “indexes” below the “columns” under the Students table. Choose “Create Index”

you will see the dialog box below where you can type the index information such as name of the index and choose the fields that the index will be built on.

What files are needed to deploy ASP.NET Application that uses SQL Server CE database?

The following picture shows all the files that are needed to deploy an ASP.NET application that accesses a SQL Server Compact 4.0 database file. All these DLLs go into the bin folder below the application root.

System.Data.SqlServerCe.dll

This file contains System.Data.SqlServerCe Namespace and all classes in that namespace. The System.Data.SqlServerCe namespace is the managed data provider for SQLServer CE. This namespace is a collection of classes that provide access to  databases. By using System.Data.SqlServerCe, you can create, manage, and synchronize  databases from a smart device or a computer. The classes include all the ADO.NET classes such as SqlCeConnection, SqlCeDataAdapter, SqlCeCommand, SqlCeDataReader.

System.Data.SqlServerCe.Entity.dll – Contains System.Data.SqlServerCe.Entity namespace classes needed to build Entity Framework on top of SQLServer CE. Only needed when ADO.NET Entity Framework is being used to access data from a SQL Server Compact database.

sqlceer40EN.dll – Contains the SQL Server Compact errors, and if the application is displaying SQL Server Compact errors then this DLL is needed.

sqlcecompact40.dll – Used for database management activities like compacting the database or shrinking it.

sqlceoledb40.dll – The OLE DB provider for Compact and is needed by native (C++) applications.

sqlceca40.dll – Used for replicating data with SQL Server using merge replication or remote data access.

Conclusion

SQLServer Compact Edition gives you all the functionality of regular SQL Server at zero cost. You can build you application in SQLServerCE and test it on development before moving to production environment where you can choose to have SQL Server. Small database driven applications can actually use SQLServer CE in production environment. Its file based, easily transportable and best of all free! Its great way to build small web applications that need a nice relational database without spending a lot on buying regular SQL Server licence.