By Daniel Du
This is continuation of Part 2. In part 2, I create a simplest memory repository, in this part, I would like to create a database repository to make my data persistent. I am using Entity Framework code first programming, which makes it very easy to create the database, actually I do not need to write a single SQL clause to manipulate the database, all stuff can be done in C# with Entity Framework. It is also very flexible if I need to change/reflector my model someday.
Firstly I created a context class, named as SurfaceContext, inherited from DbContext:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using TransferDataModel;
namespace SurfaceStoreServiceWebRole.Models
{
public class SurfaceContext : DbContext
{
public SurfaceContext()
: base("name = SurfaceStoreServiceWebRoleContext")
{
}
public DbSet<TransferTinSurface> TinSurfaces
{
get;
set;
}
public DbSet<TransferTriangleFace> TriangleFace
{
get;
set;
}
}
}
Please note that “SurfaceStoreServiceWebRoleContext” is my connection string, which is defined in web.config:
<add name="SurfaceStoreServiceWebRoleContext" connectionString=
"Data Source=.\SQLEXPRESS;
Initial Catalog=SurfaceStoreServiceWebRoleContext;
Integrated Security=True; MultipleActiveResultSets=True"
providerName="System.Data.SqlClient" />
Here I am using SQL Express, but it is easy to refer to a cloud based SQL Azure database latter.
Next I created my database repository, implementing the ITinSurfaceRepository interface:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity.Infrastructure;
using TransferDataModel;
namespace SurfaceStoreServiceWebRole.Models
{
public class TinSurfaceDatabaseRepository : ITinSurfaceRepository
{
private SurfaceContext db = new SurfaceContext();
public TinSurfaceDatabaseRepository()
{
}
public IEnumerable<TransferTinSurface> GetAll()
{
return db.TinSurfaces.AsEnumerable<TransferTinSurface>();
}
public TransferTinSurface Get(int id)
{
return db.TinSurfaces.Find(id);
}
public TransferTinSurface Add(TransferTinSurface surface)
{
if (surface == null)
{
throw new ArgumentNullException("surface is null");
}
db.TinSurfaces.Add(surface);
db.SaveChanges();
return surface;
}
public TransferTinSurface Remove(int id)
{
TransferTinSurface surf = Get(id);
if (surf == null)
{
return null;
}
db.TinSurfaces.Remove(surf);
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
return null;
}
return surf;
}
public bool Update(TransferTinSurface updatedSurface)
{
var surface = Get(updatedSurface.Id);
if (surface == null)
{
return false;
}
surface.Name = updatedSurface.Name;
surface.Description = updatedSurface.Description;
surface.TriangleFaces = updatedSurface.TriangleFaces;
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
return false;
}
return true;
}
}
}
For test purpose, I also need some initial data and populate them into database, so here is a sample data class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using TransferDataModel;
namespace SurfaceStoreServiceWebRole.Models
{
public static class SampleData
{
static TransferTriangleFace[] triangleFacesOfSurface1 = new TransferTriangleFace[]
{
new TransferTriangleFace{Id = 1,
P1 = new TransferPoint{X = 10, Y = 0, Z = 0},
P2 = new TransferPoint{X = 0, Y=10, Z=0},
P3 = new TransferPoint{X=-10, Y=0,Z=0},
Color = "0xff0000"},
new TransferTriangleFace{Id = 2,
P1 = new TransferPoint{X = -10, Y = 0,Z = 0},
P2 = new TransferPoint{ X = 0,Y = -10,Z = 0},
P3 = new TransferPoint{X = 10, Y = 0, Z = 0} ,
Color = "0x00ff00"}
};
static TransferTriangleFace[] triangleFacesOfSurface2 = new TransferTriangleFace[]
{
new TransferTriangleFace{Id = 1,
P1 = new TransferPoint{X = 10, Y = 10,Z = 0},
P2 = new TransferPoint{X=-10,Y= 0,Z= 0},
P3 = new TransferPoint{X = 15,Y = 0,Z = 0},
Color = "0xff0000"},
new TransferTriangleFace{Id = 2,
P1 = new TransferPoint{X = -10,Y = 0,Z = 0},
P2 = new TransferPoint{X = 0,Y = -15, Z = 0},
P3 = new TransferPoint{ X = 15, Y = 0,Z = 0},
Color = "0x00ff00"}
};
static TransferTinSurface[] sampleTinSurfaces = new TransferTinSurface[]
{
new TransferTinSurface { Name = "surface1",
Description = "This is the first surface",
TriangleFaces = triangleFacesOfSurface1 },
new TransferTinSurface { Name = "DesignSurface",
Description = "This is a design surface",
TriangleFaces = triangleFacesOfSurface2 }
};
public static TransferTinSurface[] TinSurfaces
{
get
{
return sampleTinSurfaces;
}
}
}
}
Now go to Tools menu—> Library package Manager to open Package Manager Console:
And type command “Enable-Migrations”, please note the intelligence
After enabling migration, a file named as “Configurations.cs” will added into a new folder “Migrations”
Seed method is called after migrating the the latest version by running “Update-Database” command in Package Manager, so I just add my simple code to add some initial data into database context from SampleData class:
namespace SurfaceStoreServiceWebRole.Migrations
{
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;
using SurfaceStoreServiceWebRole.Models;
internal sealed class Configuration : DbMigrationsConfiguration<
SurfaceStoreServiceWebRole.Models.SurfaceContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = true;
}
protected override void Seed(SurfaceStoreServiceWebRole.Models.SurfaceContext context)
{
// This method will be called after migrating to the latest version.
// You can use the DbSet<T>.AddOrUpdate() helper extension method
// to avoid creating duplicate seed data. E.g.
context.TinSurfaces.AddOrUpdate(
s => s.Name,
SampleData.TinSurfaces);
context.SaveChanges();
}
}
}
If you have read part 2 of the serials, you will notice that my first version of data model is named as “TinSurface”, and latter I found it is confusing when I came to Civil 3D plugin programing, so I did some refactoring work. Fortunately, that is easy with Entity Framework Code First programing. I just rename the class name in Visual Studio, the refactor tool of VS2012 ensure to change all relevant code, and run a command in Package Manager:
PM> Add-Migration RefactorClassName
PM> Update-Database
That’s it, all stuffs are done automatically, fantastic! “RefactorClassName” is my migration name, you will noticed that a new class is generated in “Migrations” folder:
After that, I check my database to verify whether my seed data is generated or not. Go Server Explorer and add a Data Connection to .\SQLEXPRESS. Two tables are created and some test data are populated.
Next is to switch to the database repository in my TinSurfacesController. Note that calling new TinSurfaceDatabaseRepository() in the controller is not the best design, because it ties the controller to a particular implementation of ITinSurfaceRepository. For a better approach, see Using the Web API Dependency Resolver. I would let it be by now.
static readonly ITinSurfaceRepository repository =
new TinSurfaceDatabaseRepository();
//static readonly ITinSurfaceRepository repository =
// new TinSurfaceMemoryRepository();
Okay, with that I am working with a database now. With test of fiddler like part 2, I can retrieve data from database, and save data into database as well.
Comments
You can follow this conversation by subscribing to the comment feed for this post.