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.
Create SDF database file
Add a new sdf file to the App_Data folder and name it Todo.sdf
Add a new Entity Data model
Add a new Entity Data Model file in the Models folder, name it Todo.edmx
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
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”.
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
Copy the content of the file Todo.edmx.sqlce, then press Execute.
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”
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.