Export (0) Print
Expand All

Walkthrough: Working with SQL Server Compact in Visual Studio

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.

In order to complete this walkthrough, you will need:

  • Visual Studio 2010 SP1

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

  1. If you do not already have the Web Platform Installer, download it using the following link: Microsoft Web Platform Installer.

  2. Run the Web Platform Installer.

  3. Click the Products tab, and then select Database.

    Installing SQL Server Compact using Web PI
  4. Find Microsoft SQL Server Compact 4.0 and then click Add.

  5. Find Microsoft SQL Server Compact 4.0 Tools and then click Add.

  6. Click Install to complete the installation.

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

  1. Start Visual Studio.

  2. In the File menu, click New Project.

  3. In the New Project dialog box under Installed Templates, select the language to use (Visual C# or Visual Basic).

  4. Select Web.

  5. Select the ASP.NET Web Application template.

  6. In the Name box, enter a name for the application, such as MySqlCeWebApp.

  7. Specify the location for the application files.

  8. Click OK.

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

To create a SQL Server Compact database

  1. In Solution Explorer, right-click the App_Data folder, select Add, and then click New Item.

  2. In the Add New Item dialog box, select SQL Server Compact 4.0 Local Database.

  3. In the Name box, enter the name FlowerShop.sdf.

    Notice that SQL Server Compact files have the .sdf file-name extension.

  4. Click Add to create the database.

Next you will add a Products table to your database.

To add a table to the database

  1. In Solution Explorer, in the App_Data folder, double-click the FlowerShop.sdf file to open the database in Server Explorer.

  2. In Server Explorer, under the FlowerShop.sdf node, right-click Tables and then click Create Table.

  3. In the New Table dialog box, name the table Products.

  4. 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

  5. Set the Identity property of the ID column to True.

  6. Click OK to create the table.

  7. In Server Explorer, right-click the Products table and then click Show Table Data.

  8. 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

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

  1. In Solution Explorer, right-click your solution, select Add, and then click Add Item.

  2. In the Add New Item dialog box, select ADO.NET Entity Data Model.

  3. In the Name box, enter FlowerShop.edmx as the name for the data model file.

  4. Click Add.

    This adds the data model file to the project and opens a wizard that allows you to create the Entity Framework model.

  5. In the Entity Data Model Wizard, select Generate from database and then click Next.

  6. Select the database file you just created and click Next.

  7. 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.

    Entity Framework designer
  8. 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.

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

  1. In Solution Explorer, right-click the solution name, select Add, and then click Add Item.

  2. In the Add New Item dialog box, select Web Form using Master Page.

  3. In the Name box, enter Products.aspx and then click Add.

  4. Select the default master page and then click OK.

  5. On the Products.aspx page, enter following line of markup in the content section marked MainContent:

    <h2>Products</h2>

  6. On the next line, add a GridView control.

  7. Expand the GridView Tasks panel and select New Data Source.

    GridView Tasks dialog

    The Data Source Configuration Wizard starts.

  8. In the Data Source Configuration Wizard, under Where will the application get data from?, select Entity and then click OK.

  9. In the Configure ObjectContext step, select Named Connection, select FlowerShopEntities, and then click Next.

  10. In the Configure Data Selection step, under EntitySetName, select Products.

  11. Select Enable automatic updates and then click Finish.

  12. In the GridView Tasks panel, select Enable Editing.

    This causes an Edit link to appear in each row of the grid.

  13. Press Ctrl+F5 to run the application.

  14. Browse to the /Products.aspx page.

    The data grid is displayed.

    Web page showing finished grid
  15. 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.

Community Additions

Show:
© 2014 Microsoft