Developing Windows Store App Part 1: Database Model


This is the first of several blog posts about developing Windows 8 Store app. The first blog post will show how to implement database model which will be used in our application. If you want to get more information about the application we are developing please see the previous announcment post. Before we start with the tutorial, several requirements must be satisfied:

1. You need to have installed at least Visual Studio 2012 Express

2. You need to have at least SQL Server 2008 or 2012 Express.

Our database model is very simple. We have three tables: Places, Dishes and Towns. Relation of those tables shows the picture below.

SQL database will also be provided by the end of the blog post. Now that we have database model on the paper, we can start with implementation. We will use Entity Framework 5, and Code First technology in order to implement db model.
Start Visual Studio 2012, choose File->New->Project. Choose ClassLibrary similar as picture below.

After we created the empty Class Library project, we can start with implementation of the entities. According to db model shoed earlier, we are going to create three .NET classes: Dish, Town, Place. In order to do that:

  • Right Click at the Project
  • Add New Class
  • Give name: Dish

Repeat the process for Town and Place. Text below shows the implementation of classes.
Town class:

public partial class Town
{
public int? TownID { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public string Image { get; set; }
//relation 0-oo
//many places can be in one Town
public ICollection Places { get; set; }
}

Dish class:

public partial class Dish
{
    public int? DishID { get; set; }
    public string Name { get; set; }
    public string Slogan { get; set; }
    public string Description { get; set; }
    public string Image { get; set; }
    //relation 0-oo
    //many places can have one dish
    public ICollection<Place> Places { get; set; }
}

Place class:

public partial class Place
{
    public int? PlaceID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public int? DishID { get; set; }
    public string Slogan { get; set; }
    public string Type { get; set; }
    public int? TownID { get; set; }
    public string Image { get; set; }
    //one relation to Dish
    public Dish PlaceDish { get; set; }
    //one relation to Dish
    public Town PlaceTown { get; set; }
}

Since we are using Code First, we need to specify the relation between entities too. That’s why we have put the last two properties in Place class, and also IColletion property of the previos classes. This is enough information that Code First make relation between tables on SQL Server.  By default PlaceID is primary key for Place table, as well as DishId and TownID for corresponded tables.
Now that we have entities, we need to implement DBContex, central class for handling all operation and transaction against database. Before we implement DBContext we need to add Entity Framework reference. Since EF is separated from the .NET we can use NuGet to accomplish this.
1. Right click on Reference, choose Manage NuGet package

In Search box type: Entity Framework and hit enter. Package will appear in the list. Click Install button. Picture below shows Manage NuGet Package dialog. Accept license agreement, and close the dialog.

CExplorer DataContext class:

public class CExplorerContext: DbContext
{
    public CExplorerContext() : base("cexplorer_db")
    {
        Configuration.LazyLoadingEnabled = false;
    }

    //entity sets
    public DbSet<Place> Places { get; set; }
    public DbSet<Dish>  Dishes  { get; set; }
    public DbSet<Town>  Towns  { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //crating one to many relation between Town and Place
        modelBuilder.Entity<Place>()
            .HasRequired(x => x.PlaceTown)
            .WithMany(d => d.Places)
            .WillCascadeOnDelete(true);

        ////crating one to many relation between Dish and Place
        modelBuilder.Entity<Place>()
            .HasRequired(x => x.PlaceDish)
            .WithMany(d=>d.Places)
            .WillCascadeOnDelete(true);

        base.OnModelCreating(modelBuilder);
    }
}

As you can see, we have overridden ModelCreation virtual method and implement relation between entities. This is classic one to many relation.

At the end of the implementation, we need to provide connection string to SQL Server, it must be with the same name as we specified in the implementation od the DBContext.

1. Add New Item: App.config file and put the following configuration.

Based on SQL instance name, change the connection string. Database could not exist on the SQL server. If there is a database on the SQL with the same name, EF will add table in to existing database.

Before we end the today’s post, lets quickly create Console Test application to make sure we implemented correct model.

1. File->New->Add New Project
2. Console Application

Like previous add Entity Framework by NuGet.
Add reference of EFModel.
Open Program.cs and put the following implementation:

class Program
{
    static void Main(string[] args)
    {

        CExplorerContext ctx = new CExplorerContext();

        var dish = new Dish() { DishID = 1, Description = "Description", Name = "DishName", Slogan = "Slogan", Image = "Default.jpg" };
        var town= new Town(){TownID=1, Name= "TownName", Description="Description", Image=""};
        ctx.Dishes.Add(dish);
        ctx.Towns.Add(town);
        ctx.SaveChanges();
        var place = new Place() { PlaceID = 1, Name = "Place Name", Description = "DEscription", Image = "default.jpg", Slogan = "Slogan title", DishID = 1, TownID = 1 };
        ctx.Places.Add(place);
        ctx.SaveChanges();

        var plc = ctx.Places.FirstOrDefault();
        Console.WriteLine("Place from Database");
        Console.WriteLine("Place from database Name:{0}", plc.Name);
        Console.Read();

    }
}

After we run, output console shows that the entities are inserted successively on database.

In other words Cexplorer_DB database is created on the SQL server, and entities are inserted from the test.

Code First approach is very powerful, we only need to specified connection string, and everything is created just like we do, in Model First approach.  Complete source code for this Demo can be found here.

About Bahrudin Hrnjica

PhD in Mechanical Engineering, Microsoft MVP for .NET. Likes .NET, Math, Mechanical Engineering, Evolutionary Algorithms, Blogging.

Posted on 14/09/2012, in .NET, SQL, Windows 8 and tagged , , . Bookmark the permalink. 8 Comments.

  1. Thank You sir.. attached source code are not found…

  2. next one please!

  3. Awesome posts you post on your blog, i have shared this post on my twitter

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s