This tutorial will go over how to setup a SQL Compact Database and displaying it on your Website using Visual Studios 2012. In most cases, you may need a small database for testing or holding small amounts of data for your lightweight web application. Using SQL Compact Databases along with Visual Studios makes it easy to edit your database as well as implementing it onto your site. Let’s get started.

sql compact example

Starting your Project

In order to complete this tutorial you must have SQL Compact Database installed and connected with Visual Studios. More likely you already have it loaded with Visual Studios but if you don’t, you can download it through the Microsoft Web Platform Installer.

Start Visual Studios 2012 and go to File < Start New Project and choose Web < ASP.NET Web Forms Application. We will be creating a Pet Shop with a variety of pets so lets name our project PetShop

sql compact step 1

Creating a SQL Compact Database

To create the SQL Database, right-click App_Data folder in Solution Explorer, click New Item> and select SQL Server Compact 4.0 Local Database. Name your project something like PetShop or PetInventory so it will be easy to link to and remember.

Next add a table to your new database by Double-Clicking the database file. In Server Explorer, Right-click the Tables line item, click Create Tables and name it Products.

sql compact step 2

Add new columns to inside the table. Since we are making an inventory list, we should name each row as follows: ID, Name, UnitPrice, UnitsInStock. Click OK to confirm the table. In Server Explorer, right-click the Products table you created and click Show Table Data. Here is where you can input the actual data that will be displayed into the columns that you just recently created. Fill in the information you would like to see be displayed.

Tie the DB to the Entity Framework

Now we will tie the Database to an Entity Framework so we can easily query and update the data in our Products Table. Go to Solution Explorer and right-click on your Solution and Add New Item. In the Dialog box, select ADO.NET Entity Data Model. Pick a name for your new edmx file and click Add.

A Auto Wizard process starts that will gather the information. First select Generate from database and click Next. Select the database file you just created and click Finish. You will see a designer view that will show the Products table in the database. Now you must Build your solution by going to Build < Build Solution to gather the information together.

Displaying your Database on your Site

In most cases you will want to show Database items on your site. You can either choose to Create a new Web Form using Master Page to put it on a new page or place it in one of your other .ASPX files. You can display your Database by using the GridView Control. The code is provided below.

We will use the GridView Tasks Panel to dynamically set and auto update our table data. View your code in Design View (the bottom of the code window in Visual Studio) and expand the GridView Tasks Panel on the top right hand side of the displayed table. In the pop-out, select New Data Source. The Data Source Configuration Wizard will start. Select Entity and click OK. In the Named Connection field, select the file you created and click Next.

sql compact step 3

Under EntitySetName, select Products and Enable automatic updates. If you want to allow editing of the table. Select the Enable Editing in the GridView Tasks Panel. Preview your new table and application by pressing CTRL + F5

Wrapping It Up

If you want to set up a small test environment, we really highly recommend using this approach. It’s quick and easy to setup your database as well as edit it automatically. Try testing out different ways of applying your database to your front-end design.