Articles‎ > ‎

[ Article ] How to configure and use SQL CE 4 with visual studio 2010

Configuring and using Microsoft SQL Server Compact Edition (SQL CE) 4 with visual studio 2010


Microsoft SQL Server Compact Edition (SQL CE) is a compact database produced by Microsoft for applications that run on mobile devices and desktops. Just like SQL Server, it is a relational database. It includes both 32-bit and 64-bit native support. It is free to download and redistribute. 
Microsoft SQL Server Compact Edition shares a common API with the other Microsoft SQL Server editions. It also includes ADO.NET providers for data access using ADO.NET APIs, and built-in synchronization capabilities, as well as support for LINQ and Entity Framework. SQL CE runs in-process with the application which is hosting it. SQL Compact Edition is perfect for smaller databases. No setup or extra security permissions are required for it to run. You don’t even need an administrator privilege on the machine. SQL CE is optimized for an architecture where all applications share the same memory pool. SQL CE runs in-memory within your application and will start-up when you first access a SQL CE database, and will automatically shut down when your application is unloaded. Unlike other compact databases, SQL CE won’t crash or deadlock when used in a multi-threaded server scenario. SQL CE version 4.0 requires .NET framework 4.0. An ODBC driver for SQL CE does not exist. Applications may use SQL CE via OLE DB.

SQL CE database is a single .sdf file, which can be up to 4 GB in size. The .sdf file can be encrypted with 128-bit encryption for data security. SQL CE runtime manages concurrent multi-user access to the .sdf file. The.sdf file can be deployed to the destination system by simply copying it. SQL CE runtime has support for DataDirectories. Applications using an SQL CE database need not specify the entire path to an .sdf file in the connection string. It can be specified as |DataDirectory|\<database_name>.sdf, defining the data directory (where the .sdf database file resides) being defined in the assembly manifest for the application.
To read SQL CE 3.5 database files, we need SQL Server Management Studio 2008 or later. Visual Studio 2010 SP1 can handle CE 4.0 database files. The .sdf (Sqlce Database File) extension is optional and any extension can be used.

Configuring SQL CE 4.0 using Visual Studio 2010

To have SQL CE 4.0 configured using Visual studio 2010 we need to do something extra which I will explain later in this article. Configuring SQL CE 4.0 is much similar to configuring SQL CE 3.5. Here I will explain how to configure SQL CE 3.5 and later we will use the same steps to configure SQL CE 4.0.

How to configure SQL CE 3.5 using Visual Studio 2010.

Go to Visual Studio Server Explorer. On the data connection, right click and say Add connection. Now after you did this, it will show you a window which says Add connection. 

Creating SQL CE compact database

Here you will see a button which says “Change”. Click on that and select Microsoft SQL Server Compact 3.5 and click “Ok”. After you have clicked “OK” it will show you another window which will look like this.

Add connection setting for sqlce

On the add connection window, Click on the create button and it will show you another window which will say “Create new SQL Server Compact Database”. Here in this window, click on the browse and show the location where you need the database. In my case I have browsed to the folder in my solution where I need the database to be. Now keep the rest of the option as such. We are not interested in password and all right now. Password for database is optional. Now the “Create new SQL Server Compact Database” window will close and you will be taken back to the old window where you can see a button which says “Test Connection”. You can click on this to verify whether your database is proper and can be connected properly. Now click “Ok” to complete configuration.
Now go to your project and Do an “Add Existing Item” and browse your database which we just created. Now when you add this database, it will ask for “Database Model” select the Dataset and say Finish. That's it! your configuration is over. Now lets see how to create the connection object.

Now in your project, add reference to System.Data.SqlServerCe. And in your code, refer to System.Data.SqlServerCe and the code will be as below.

SqlCeConnection con = new SqlCeConnection(@"Data Source=AbsolutePathToYourDatabaseDirectory\MyDatabase#1.sdf");
con.Open();
con.Close();

As mentioned before, you don’t have to specify full path of the database instead you can specify DataDirectory to create a connection object as shown below.

SqlCeConnection con =
                new SqlCeConnection(
                    @"Data Source=|DataDirectory|\MyDatabase#1.sdf");

Now run your code and you should be able to create a connection object.
You may get an exception like below

Exception
Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

How to configure SQL CE 4.0 using Visual Studio 2010.

Visual studio 2010 does not come with SQL CE 4.0 installed. To install SQL CE 4.0 you have to download and install it manually. Download and install SQL CE 4.0 from http://www.microsoft.com/en-us/download/details.aspx?id=17876.
Now SQL CE 4.0 is available in your machine but you cannot manage it with Visual studio 2010. To have it managed from visual studio, you have to install Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 CTP2. Let’s see how to install this. It can be installed from the Web Platform Installer 3.0.  After you have downloaded Web Platform Installer 3.0 and start installing, you will get a window as shown below. 

Web platform installer

Go to Products tab and select Database option. You will be able to see Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 click on Add and install that. After you have installed it properly, You should be able to see SQL CE 4.0 in the drop down of Server Explorer. Follow the steps as we did for SQL CE 3.5 and that’s it!

How to insert and read data from SQL CE 4.0

How to create table in SQL CE

Open server explorer and find your database. Expand it to find Tables. Now right click on the Table and say “Create Table”. Now to the window that comes up, add your table name. Here I am giving the name as “Table1”. Now add just one field. In my case I am adding a field say “id”. Here i am not bothered about the type, So I left it as default which is varchar so data which we are inserting will be retrieved as string.

How to insert value to SQL CE

Now in your code, you are going to add a value to the table you just created. Let’s see how.

   SqlCeConnection con = new SqlCeConnection(

                    @"Data Source=|DataDirectory|\MyDatabase#1.sdf");

   con.Open(); 

   // Insert value into table. ExecuteNonQuery is best for inserts.

   int num = 10;

   using (SqlCeCommand com = new SqlCeCommand("INSERT INTO Table1 VALUES(@id)", con))

         {

          com.Parameters.AddWithValue("@id", num);

          com.ExecuteNonQuery();

         } 

   con.Close(); 

How to read value from SQL CE

SqlCeConnection con = new SqlCeConnection(
                    @"Data Source=|DataDirectory|\MyDatabase#1.sdf");
con.Open();
using (SqlCeCommand com = new SqlCeCommand("SELECT id FROM Table1", con))
{
   SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
 int num = Convert.ToInt32(reader.GetString(0));
 Console.WriteLine(num);
}
}
con.Close();


Comments