An Introduction to SQL Server Compact Edition (SQLCE 4.0)

Good news for small application developers, Microsoft released compact version of SQLSERVER (SQLCE 4.0) for desktop ,web as well as for mobiles and recently with rel 4.0 it support ASP.Net.This embedded database format is a huge relief from cumbersome and hectic installation of sql server .Although this was from long back but its reliability and features introduced in 4.0  is awesome. So this post is all about SQLCE , its latest feature and a small how to to end up.

What is SQLCE and scenario to use?
 

Sql Server Compact Edition (SQLCE) is a embedded database with (1.7 to 2 Mb approx.) for Mobile, Desktop and Asp.Net based projects. Generally the target audience/project for SQLCE are
 

• Small/Mid level office application.
• For hassle free installation of Database (Copy and Paste option)

More detailed information can be found at SQLCE Team Blog

What is the Latest Version?

Latest Version: SQLCE 4.0 Download

As of Microsoft, SQL Server Compact 4.0 enables new scenarios and includes a host of new features, including the following:
 

• SQL Server Compact 4.0 is the default database for Microsoft WebMatrix,.
• For enhanced development and debugging capabilities, including designer support.
• Visual Studio can be used to develop ASP.NET web applications and websites using SQL Server Compact 4.0.
• Enabled to work in the medium or partial trust environments in the web servers
• Private deployment of SQL Server Compact further simplified by having all the required managed assemblies in a single private folder in the install location of SQL Server Compact.
• Stronger data security with the use of the SHA2 encryption algorithms for encrypting the databases.
• T-SQL syntax enhancement by adding support for OFFSET and FETCH that can be used to write paging queries and new APIs like SqlCeConnection.GetSchema that provides the metadata information of the database.
• Support for ADO.NET Entity Framework with the code-first programming model.

Some Facts:
 

•  Supports Data up to 4 GB.
•  File based code free Database
•  Supports concurrent multiple connection.
•  Runs in process of Application (Other MS sql Editions used to have separate services)
•  SQL CE databases reside in a single .sdf file.
•  The .sdf files / Database can be password protected as well as encrypted.
•  SQL CE databases are ACID-compliant. Like Microsoft SQL Server,
•  SQL CE supports transactions, referential integrity constraints, locking as well as multiple connections to the database store

Advantages
 

•  Very Small footprint (2 to 2.05 MB).
•  File based Database with enhanced security option.
•  All you need is Copying of binary to your installation path.
•  Easily Upgradeable to higher versions (MSSQL Express.)

Limitations
 

•  Doesn't support Stored Procedures, also they don't have plan for it. Go through the nice article from Steve Lasker.

Jump Start with SQLCE:
 

Make sure that you have downloaded the latest version of SqlCE as mentioned above and installed.
 

•  Open VS and Right click to the project window and add a new Item to the project.

•  Select the DATA option and select Local Database option. Provide a database name and click OK

•  On click you will find a file with SDF extension bellow.

 

To browse the Database, Right Click on Model.sdf and click Open. Then you will be able to get the table details. To add a new table Right click on Tables and click on Add table. In this case I have created 2 tables as shown in figure.
 

 

Creating a connection from C# project

The System.Data.SqlServerCe namespace is the .NET Compact Framework Data Provider for SQL Server CE.  WithSystem.Data.SqlServerCe, you can create and manage SQL Server Mobile databases on a smart device and also establish connections to SQL Server databases.

 

1.        public void Connect()
2.        {
3.            string FileName = "Model.sdf";
4.            SqlCeConnection con = new SqlCeConnection(@"Data Source=|DataDirectory|\" + filename + ".sdf;Password=123");
5.            con.Open();
6.        }

 

Retrieving Data

The following code performs simple Data retrieval from the SDF file based on the sqlcecommand.
 

1.        private void RetriveData()
2.        {
3.            string FileName = "Model.sdf";
4.            SqlCeConnection con = new SqlCeConnection(@"Data Source=|DataDirectory|\" + FileName + ".sdf;Password=123");
5.            con.Open();
6.            
7.            SqlCeCommand comm = new SqlCeCommand("Select * from EmployeeMaster", con);
8.            SqlCeDataAdapter sqlceda = new SqlCeDataAdapter(comm);
9.            
10.            DataSet dsData = new DataSet("EMPLOYEE");
11.            sqlceda.Fill(dsData);
12.            gvEmpMaster.DataSource = ds.Tables[0];
13.  
14.        }

 

Conclusion and Source Code

Well as of today many of the open source CMS provider such as mojoPortal,umberco adopted the SQLCE as their database for its hassle free deployment. As its recent release 4.0 is ready for ASP.Net LOB applications it will find a place among the low end development.

The sample application stimulates a typical windows based application using sqlCE. This application consist of a wireframe database (model.sdf) which will be used to create other files and based on it the user can insert data and later retrieve it.