SQL Server Compact Edition 3.5 can be used to create applications that are useful for a number of business uses such as: Portable applications; Occasionally connected clients and embedded applications and devices. SQL Server Compact differs from other SQL Servers in that there is just one file which can be password protected and features 128-bit file level encryption. It is referential integrity compliant; supports multiple connections; has transactions support with rich data types.

In this tutorial by Jayaram Krishnaswamy, various scenarios where you may need to connect to SQL Server Compact using Visual Studio IDE (both 2008 and 2010) are described in detail. Connecting to SQL Server Compact 4.5 using Visual Studio 2010 Express (free version of Visual Studio) is also described. The connection is the starting point for any database related program and therefore mastering the connection task is crucial to work with the SQL Server Compact.

(For more resources on Microsoft, see here.)

If you are familiar with SQL Server you already know much of SQL Server Compact. It can be administered from SSMS and, using SQL Syntax and ADO.NET technology you can be immediately productive with SQL Server Compact.It is free to download (also free to deploy and redistribute) and comes in the form of just one code-free file. Its small foot print makes it easily deployable to a variety of device sizes and requires no administration. It also supports a subset of T-SQL and a rich set of data types. It can be used in creating desktop/web applications using Visual Studio 2008 and Visual Studio 2010. It also comes with a sample Northwind database.

Download details

Microsoft SQL Server Compact 3.5 may be downloaded from this site here. Make sure you download detailed features of this program from the same site. Also several bugs have been fixed in the program as detailed in the two SP’s. Link to the latest service pack SP2 is here. By applying SP2 the installed version on the machine is upgraded to the latest version.

Connecting to SQL Server Compact from Windows and Web projects

You can use the Server Explorer in Visual Studio to drag and drop objects from SQL Server Compact provided you add a connection to the SQL Server Compact. In fact, in Visual Studio 2008 IDE you can configure a data connection without even starting a project from the View menu as shown here.

When you click Add Connection… the following window will be displayed.

This brings up the Add Connection dialog shown here.

Click Change… to choose the correct data source for SQL Server Compact. The default is SQL Server client.

The Change Data Source window is displayed as shown.

Highlight Microsoft SQL Server Compact 3.5 and click OK.

You are returned to Add Connection where you can browse or create a database or, choose also from a ActiveSync connected device such as a Smart phone which has a SQL Server Compact for devices installed. Presently connect to one on the computer (My Computer default option)-the sample database Northwind.

Click Browse….

The Select SQL Server Compact 3.5 Database File dialog opens where your sample database Northwind is displayed as shown.

Click Open. The database file is entered in the Add Connection dialogue. You may test the connection. You should get a Test connection succeeded message from Microsoft Visual Studio.

Click OK. The Northwind.sdf file is displayed as a tree with Tables and View as shown in the next figure.

Right click Northwind.sdf in the Server Explorer above and click Properties drop-down menu item.

You will see the connection string for this conneciton as shown here.