How to open SQL Compact Edition file(*.sdf) in Visual Studio or LINQPad?

I try to look for some tools to open a sample database attached with application development sample on the net. I have found that the file has an extension .sdf. It make me strange at first because I do not really know what that file extension would be. Would that possible a database file or just a flat file? It is exactly as what I thought, it is an extension file for an SQL Server Compact Edition. A sample of Northwind.sdf (SQL Compact Edition 3.5) file can be download from my backup here.

Microsoft SQL Server Compact (SQL CE) is a compact relational database produced by Microsoft for applications that run on mobile devices and desktops. Prior to the introduction of the desktop platform, it was known as SQL Server for Windows CE and SQL Server Mobile Edition

This guide is provide the step on how to open the file content using either extension is Visual Studio or standalone application call LINQPad. I made these to share with you all in case that you need to see the data, schema of that SQL Compact Edition look like.

Currently there are two possibility to open .sdf (SQL Compact Edition):

  • Using Visual Studio toolbox

Visual Studio logo
It is support in Visual Studio 2015, 2013, 2012, 2010. You need to download SQL Server Compact/SQLite Toolbox from Microsoft or from my backup and install it into your PC.

 

Here are the step to integrated into Visual Studio:

      • Download SQL Server Compact/SQLite Toolbox for Visual Studio

You can download toolbox for Visual Studio from Microsoft Web site or from my own copy of SqlCeToolbox.4.5.0.3.vsix in case of previous link does not works.

      • Install toolbox extension

Before installing it is a must to close your Visual Studio if you are currently open first otherwise you can not see the change effect to your IDE, then double click on the file you have just downloaded to proceed to the setup process.

SQL Compact Toolbox

      • Install SQL Server Compact runtime

The Toolbox requires one of these SQL Server Compact runtime MSIs to be installed, if you want to connect to a SQL Server Compact database file:
Download 3.5 SP2 – installed with VS 2010
Download 4.0 SP1 – installed with VS 2012 (No runtimes are installed with VS 2013 and VS 2015)

      • Open Visual Studio to see toolbox extension being installed

Once the toolbox is installed, now it is ready for the feature of opening *.sdf file extension in Visual Studio.

SQL Compact Toolbox-002

 

It is time to try it out by your self from Northwind.sdf compact edition sample in Visual Studio 2012.

      • Right click on Data Connections to create new connection

SQL Compact Toolbox-004

      • Click on Add SQL Server Compact Edition 3.5 connection…

SQL Compact Toolbox-003

      • Browse to the sample file which you just downloaded

SQL Compact Toolbox-005

      • Finally, you can view the content of .sdf(SQL Server Compact Edition 3.5)

SQL Compact Toolbox-006

 

  • Using LINQPad

LinqPad logo

You have already seen the possibility to open SQL Compact Database in Visual Studio using ToolBox. This section I will show you another alternative with a super light weight executable around 15MB which has same capability as SQL Compact Toolbox in opening *.sdf file extension. It is free without expiration for a Standard Edition. That program is call LINQPad.

 

      • Where to get LINQPad?

The program is base on .Net Framework, so before being able to use the stand alone program, your PC need to have .Net Framework prior install.

Up-to-Date, there have two version available where you can download it from their official website at www.linqpad.net or with my personal backup LINQPad 4 for .NET Framework 4.0 / 4.5 and LINQPad 5 for .NET Framework 4.6

      • How to install?

 

        1. By clicking on the file you just downloadLINQPad4-001
        2. Choose where your files will be installed to. I think we just leave it to their default settingLINQPad4-002
        3. Associate file extension to be able to open with LINQPadLINQPad4-003
        4. Finally stage is to check “Launch LINQPad”, follow by clicking Finich ButtonLINQPad4-005

 

      • How to open .sdf file?

 

Note:

If you got error while linking to sample database similar like "the required DbProvider registration is not present" or "Unable to find Requested .Net Framework Data Provider. It may not install". You would need to download Microsoft SQL Server Compact 3.5 Service Pack 2 run time for Windows Desktop and install in your PC.

        1. Click on Add ConnectionLINQPad4-OPEN-001
        2. Then choose Default(LINQ to SQL)LINQPad4-OPEN-002
        3. Choose provider in our case is SQL CE 3.5, then click on Browse to search for the sample file that I had mention above (Northwind.sdf). You can click on Test button to see whether your setting are correctly works. In any case, alert message will be shown. Once the test is running smooth, just click on button Ok to go through database exploration.LINQPad4-OPEN-003
        4. Finally, we can explore all their tablesLINQPad4-OPEN-004

 

Now, you see it is not complicate if we knew the tools. If you have trouble on following my guideline, please fill free to contact me.