SQL Server Compact 4.0 Tooling Support in Visual Studio 2010 SP1 and Visual Web Developer Express 2010 SP1
Quick Navigation Links:
Visual Studio Designers that do not work with SQL Server Compact 4.0
Overview
The Ultimate, Premium and Professional editions of Visual Studio 2010 SP1, and the Visual Web Developer Express 2010 SP1 has support for tooling for SQL Server Compact 4.0. The Visual Studio 2010 SP1 and the Visual Web Developer Express 2010 SP1 can be installed using the Microsoft Web Platform Installer 3.0. After installing the Web Platform Installer 3.0, the screen as shown below will come up. Click on the Add button to install the Visual Studio 2010 SP1 and the Visual Web Developer Express 2010 SP1.
Screenshot 1: Web Platform Installer 3.0 with the option to install Visual Studio 2010 SP1 or Visual Web Developer Express 2010 SP1.
Note that selecting and installing the Visual Studio 2010 SP1 or the Visual Web Developer Express 2010 SP1 also installs the SQL Server Compact 4.0 components that are needed for the tooling support. These components are called as Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 and SQL Server Compact 4.0. The Web Platform Installer automatically detects whether these components are already installed on the machine, and if they are not installed, the Web Platform Installer will install them. There is no need to separately select the SQL Server Compact 4.0 components in the Web Platform Installer.
Advantages of using Visual Studio 2010 SP1 or Visual Web Developer Express 2010 SP1 to develop applications for SQL Server Compact 4.0
The Visual Studio 2010 SP1 can be used to develop ASP.NET web applications for Compact 4.0. The different designers that provide functionality for Compact 4.0 is as given below:
Server Explorer and Query Designer: After installing the Compact 4.0 components for Visual Studio 2010 SP1, a new option to connect to SQL Server Compact 4.0 databases will appear in the Change Data Source dialog. This option will be in-addition to the existing option to connect to the SQL Server Compact 3.5 databases. After connecting to a SQL Server Compact 4.0 databases, the different objects in it can be navigated through in the Server Explorer and queries can be run in the Query Designer window.
Screenshot 2: Change Data Source dialog for selecting SQL Server Compact 4.0 data source.
Transact-SQL Editor: Start the Transact-SQL Editor from the Data menu and connect to a SQL Server Compact 4.0 database in the Connect to Server dialog shown below by specifying the path to the database file or by selecting the New Database option in the Database file dropdown field. Note that when the path to the database file is specified, the version of file is auto-detected, and depending on the version of the file either SQL Server Compact 3.5 or SQL Server Compact 4.0 runtime engine is used to connect to the file. This behavior is different from that in the Add Connection and Change Data Source dialogs of the Server Explorer where the data source has to be explicitly set to Compact 3.5 or 4.0. The Create New SQL Server Compact Database dialog provides options for creating either Compact 3.5 or Compact 4.0 database file as shown in the screenshot below.
Screenshot 3: Connecting to a SQL Server Compact 4.0 database in the Transact-SQL Editor connection window.
Screenshot 4: Creating a new SQL Server Compact 4.0 database.
Designers in the VB and C# ASP.NET Web application or web site projects: Designers can be used in the VB and C# based ASP.NET Web Application or Web Site projects to generate datasets or entities from a Compact database and these can be bound to controls on the ASP.NET web page. The workflow is same as that used for SQL Server and more information about the controls are at the SqlDataSource and the EntityDataSource web control pages. In the Add New Item dialog box a new item template for SQL Server Compact 4.0 Local Database is available to add a new empty Compact 4.0 database file to the project.
Click once deployment: In the prerequisites dialog of the click once deployment there is a checkbox for Compact 4.0 and if the VB or C# Windows projects uses Compact 4.0 the click once publishing for Compact 4.0 will work if the Compact 4.0 entry is checked in the prerequisites window.
Screenshot 5: The prerequisites dialog for Click Once deployment.
Opening WebMatrix projects in Visual Studio 2010 SP1: A WebMatrix project can be opened in Visual Studio 2010 SP1 or in Visual Web Developer Express 2010 SP1 by clicking on the Visual Studio option in the WebMatrix IDE. In addition, if the required SQL Server Compact 4.0 components that are needed for Visual Studio 2010 SP1 are not installed on the machine, a message will pop-up to start the Web Platform Installer 3.0 and the Web Platform Installer 3.0 will install all the required components on the machine.
Visual Studio Designers that do not work with SQL Server Compact 4.0
The following wizards do not work with Compact 4.0 in the Windows project system. Developers can manually add reference to the ADO.NET provider for Compact 4.0 (System.Data.SqlServerCe) to develop programs for Compact 4.0 in the Windows projects:
- The Data Source Configuration Wizard that is used to configure datasets.
- The Configure Data Configuration wizard that is used to setup the syncing of data and schema with SQL Server using Sync FX
- The Entity Data Model wizard that is used to generate entities from a Compact database.
Fixing installation issues
If you face any issues in installing the SQL Server Compact 4.0 components do the following:
Go to Start à Control Panel à Programs à Programs and Features to see the list of all the applications and software installed on your machine. Another way to navigate is to open the Windows Explorer and copy in – Control Panel\Programs\Programs and Features – in the address bar at the top. Check to see whether the following are installed. On a 32-bit machine the name will show as Microsoft SQL Server Compact 4.0 x86 ENU:
Screenshot 6: Entries for SQL Server Compact in the Add/Remove Programs.
If the Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 is not installed type in SP1 in the search bar at the top right in the Web Platform Installer. This will bring up all the components that have SP1 in their name. Click on the Add button in front of Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 to install it. The Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 can be only installed if there is an existing installation of Visual Studio 2010 SP1 or Visual Web Developer Express 2010 SP1. Similarly if you have trouble installing SQL Server Compact 4.0, type in SQL Server Compact 4.0 in the search bar and click on the Add button to install it. The information about the different executable files for installing different SQL Server Compact 4.0 components is at MSDN Online.
Screenshot 7: Searching for all products with SP1 in their name.
Screenshot 8: Highlighting Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0.
Screenshot 9: Searching for SQL Server Compact 4.0 and highlighting SQL Server Compact 4.0.
Regards,
Ambrish Mishra
Program Manager – SQL Server Compact