Walkthrough: Working with SQL Server Compact in Visual Studio

Article
02/13/2013

SQL Server Compact is an embedded database engine that enables easy database storage. It does not require you to run a setup process or to install a database server. It can be used with medium-trust applications running in a Web hosting environment. SQL Server Compact is a good choice for use in development, testing, and low-traffic production scenarios.

This walkthrough shows how to get started using SQL Server Compact in Visual Studio. The walkthrough guides you through creating a database, adding a table, creating an Entity Framework model layer, and displaying data using the GridView control. Entity Framework is used in this walkthrough to show that SQL Server Compact works with existing .NET Framework data frameworks, including frameworks that do object-relational mapping. You are not required to use Entity Framework in order to use SQL Server Compact.

Prerequisites

In order to complete this walkthrough, you will need:

Visual Studio 2010 SP1

Installing SQL Server Compact

This section explains how to install SQL Server Compact and the Visual Studio tools that support it. If you installed Visual Studio 2010 SP1 using the Microsoft Web Platform Installer, you can skip this section because you already have the required tools. If you installed Visual Studio 2010 SP1 by downloading it from MSDN, you should follow this procedure.

To install SQL Server Compact

If you do not already have the Web Platform Installer, download it using the following link: Microsoft Web Platform Installer.
Run the Web Platform Installer.
Click the Products tab, and then select Database.
Find Microsoft SQL Server Compact 4.0 and then click Add.
Find Microsoft SQL Server Compact 4.0 Tools and then click Add.
Click Install to complete the installation.

Creating an ASP.NET Web Forms Project

After you have installed Visual Studio 2010 SP1, SQL Server Compact, and Visual Studio tools for SQL Server Compact, you can create an ASP.NET Web application. For this walkthrough, you will create a Web Forms Web application project. However, you can use SQL Server Compact with any type of Web project in Visual Studio.

To create a Web Forms project

Start Visual Studio.
In the File menu, click New Project.
In the New Project dialog box under Installed Templates, select the language to use (Visual C# or Visual Basic).
Select Web.
Select the ASP.NET Web Application template.
In the Name box, enter a name for the application, such as MySqlCeWebApp.
Specify the location for the application files.
Click OK.

Creating a SQL Server Compact Database

Next you will create a SQL Server Compact database file and add it to your project.

To create a SQL Server Compact database

In Solution Explorer, right-click the App_Data folder, select Add, and then click New Item.
In the Add New Item dialog box, select SQL Server Compact 4.0 Local Database.
In the Name box, enter the name FlowerShop.sdf.

Notice that SQL Server Compact files have the .sdf file-name extension.
Click Add to create the database.

Adding a Table

Next you will add a Products table to your database.

To add a table to the database

In Solution Explorer, in the App_Data folder, double-click the FlowerShop.sdf file to open the database in Server Explorer.
In Server Explorer, under the FlowerShop.sdf node, right-click Tables and then click Create Table.
In the New Table dialog box, name the table Products.
Add new columns as shown in the following table:

Column Name

Data Type

Length

Allow Nulls

Unique

Primary Key

ID

int

4

No

No

Yes

Name

nvarchar

100

Yes

No

No

UnitPrice

money

19

Yes

No

No

UnitsInStock

int

4

Yes

No

No
Set the Identity property of the ID column to True.
Click OK to create the table.
In Server Explorer, right-click the Products table and then click Show Table Data.
Enter the following sample data:

ID

Name

UnitPrice

UnitsInStock

1

Red Roses

29.99

500

2

Yellow Roses

29.99

450

3

Lilies

19.99

759

4

Orchids

39.99

389

5

Daisies

19.99

1100

6

Tulips

29.99

568

Column Name	Data Type	Length	Allow Nulls	Unique	Primary Key
ID	int	4	No	No	Yes
Name	nvarchar	100	Yes	No	No
UnitPrice	money	19	Yes	No	No
UnitsInStock	int	4	Yes	No	No

ID	Name	UnitPrice	UnitsInStock
1	Red Roses	29.99	500
2	Yellow Roses	29.99	450
3	Lilies	19.99	759
4	Orchids	39.99	389
5	Daisies	19.99	1100
6	Tulips	29.99	568

Creating an Entity Framework Model Layer

Now that you have a SQL Server Compact database that contains sample data, you can create an Entity Framework model layer that will provide a way to easily query and update data. As noted earlier, Entity Framework is used in this walkthrough to show that SQL Server Compact works with existing .NET Framework data frameworks. You are not required to use Entity Framework in order to use SQL Server Compact.

To create an Entity Framework model layer

In Solution Explorer, right-click your solution, select Add, and then click Add Item.
In the Add New Item dialog box, select ADO.NET Entity Data Model.
In the Name box, enter FlowerShop.edmx as the name for the data model file.
Click Add.

This adds the data model file to the project and opens a wizard that allows you to create the Entity Framework model.
In the Entity Data Model Wizard, select Generate from database and then click Next.
Select the database file you just created and click Next.
Select the option to import tables from the database and then click Finish.

Visual Studio opens the Entity Framework designer and displays a Product entity that maps to the Products table in the database.
On the Build menu, click Build Solution to compile your project.

You must compile the project so that Visual Studio can include classes from the data model in IntelliSense.

Adding a Page to Display Data

You will now create a Web page that contains a GridView control to let you display and edit your database data.

To add a page for displaying data

In Solution Explorer, right-click the solution name, select Add, and then click Add Item.
In the Add New Item dialog box, select Web Form using Master Page.
In the Name box, enter Products.aspx and then click Add.
Select the default master page and then click OK.
On the Products.aspx page, enter following line of markup in the content section marked MainContent:

<h2>Products</h2>
On the next line, add a GridView control.
Expand the GridView Tasks panel and select New Data Source.

The Data Source Configuration Wizard starts.
In the Data Source Configuration Wizard, under Where will the application get data from?, select Entity and then click OK.
In the Configure ObjectContext step, select Named Connection, select FlowerShopEntities, and then click Next.
In the Configure Data Selection step, under EntitySetName, select Products.
Select Enable automatic updates and then click Finish.
In the GridView Tasks panel, select Enable Editing.

This causes an Edit link to appear in each row of the grid.
Press Ctrl+F5 to run the application.
Browse to the /Products.aspx page.

The data grid is displayed.
Click the Edit link in one of the rows, make a change, and then click Update.

When you click Update, the GridView control submits the updated values and passes them to the data model. The data model persists them using the Entity Framework and saves them in the SQL Server Compact database.