JayData.org

Prepare an ASP.NET Web API Project with EF database

Author: Peter Aron Zentai December 9th, 2012


This post is a part of the series “How to use ASP.NET Web API with JayData”. The content below should be trivial for most experienced MVC 4 and EntityFramework developers and as such it’s meant to be a jumpstart for beginners. Our goal is to setup a simple SQL CE 4.0 backed Todo database with one single Todo entity in it.

Although these steps use the Model first approach we could have either opt in for the Code First approach as well. Using an EDMX file however simplifies and automates the task of adding controllers that manage data.

Create ASP.NET Web API Project

Create an ASP.NET MVC 4 project, select Web API.

image

Create SDF database file

Add a new sdf file to the App_Data folder and name it Todo.sdf

image

Add a new Entity Data model

Add a new Entity Data Model file in the Models folder, name it Todo.edmx

image

Select empty model on the next screen. The entity designer opens up. From the Toolbox drag an Entity to the surface and name it TodoModel. This step also create the TodoModelContainer class that we will use to access the database.

Define Todo entity

image

The Id field provisioned for us, so just add three new fields: Task, DueDate and Completed. Right clicking on the field name select properties to adjust data type. Set DateTime for DueDate and Boolean for Completed. Task will have the default String type, we just don’t modify it. .

Generate database script

Right click on the surface and select “Generate database from model”.

image

A dialog pops with Todo.sdf selected as default database connection. Press Next and Finish all the way. A new file Todo.edmx.sqlce will be generated. This is the sql statement file that if we execute will create the database schema in the SQL CE 4.0 database.

Execute database script

Get SQL CE Toolbox from Microsoft. After the install you can access it from the VIEW / Other Windows menu in Visual Studio.

When it opens, right click on TodoModelContainer and select Open SQL Editor

image

Copy the content of the file Todo.edmx.sqlce, then press Execute.

image

Repeat last three steps after every change in the schema.

Add demo data

Double click on the Todo.sdf file to reveal Data Connections. Open Tables, and right click Todoes, select “Show Table Data”

image

image

There you go!

Now if you just create a new instance of the TodoContainer class it will use this new database by default. Consult your web.config file’s <connectionStrings> block to view how these two are paired up.