Creating databases from code

As we start down the code first path, there are a couple of things that could be true. If we already have a database, then we will need to configure our objects to that schema, but what if we do not have one? That is the subject of this recipe, creating a database from the objects we declare.

Getting ready

We will be using NuGet Package Manager to install the Entity Framework 4.1 assemblies.

The package installer can be found at http://www.nuget.org/.

Open the Creating a Database from Code solution in the included source code examples.

How to do it...

  1. First, we write a test which will set up the context for us to use as a starting point for creating the database with the following code:
    using System.Data.Entity;
using System.Linq;
using BusinessLogic;
using DataAccess;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Test.Properties;
namespace Test
{
  [TestClass]
  public class DatabaseCreationTests
  {
    [TestMethod]
    public void ShouldCreateDatabaseOnCreation()
    {
      BlogContext context = new BlogContext(Settings.Default.BlogConnection);
      Assert.IsTrue(context.Database.Exists());
      context.Database.Delete();
      Assert.IsFalse(context.Database.Exists());
      context = new BlogContext(Settings.Default.BlogConnection);
      Assert.IsTrue(context.Database.Exists());
    }
    [TestMethod]
    public void ShouldSeedDataToDatabaseOnCreation()
    {
      System.Data.Entity.Database.SetInitializer<BlogContext>(new BlogContextInitializer());
      BlogContext context = new BlogContext(Settings.Default.BlogConnection);
      Assert.IsTrue(context.Database.Exists());
      context.Database.Delete();
      Assert.IsFalse(context.Database.Exists());
      context = new BlogContext(Settings.Default.BlogConnection);
      context.Database.Initialize(true);
      Assert.IsTrue(context.Database.Exists());
      DbSet<Blog> blogs = context.Set<Blog>();
      Assert.AreEqual(3,blogs.Count());
    }
  }
}
  2. We will need to add a connection setting to the Test project to our database, and make sure that the database name is populated (the database name needs to be typed as it does not exist yet):
    How to do it...
  3. In the DataAccess project, create a new C# class named BlogContext with the following code:
    using System.Data.Entity;
using System.Linq;
using BusinessLogic;
namespace DataAccess
{
  public class BlogContext : DbContext
  {
    public BlogContext(string connectionString)
    : base(connectionString)
    {
      if (this.Database.Exists() && !this.Database.CompatibleWithModel(false)) this.Database.Delete();
      if (!this.Database.Exists()) this.Database.Create();
    }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      base.OnModelCreating(modelBuilder);
    }
    public DbSet<Blog> Blogs { get; set; }
  }
}
  4. In the DataAccess project, create a new C# class named BlogContextInitializer with the following code:
    using System;
using System.Collections.Generic;
using System.Data.Entity;
using BusinessLogic;
namespace DataAccess
{
  public class BlogContextInitializer : IDatabaseInitializer<BlogContext>
  {
    public void InitializeDatabase(BlogContext context)
    {
      new List<Blog>
      {
        new Blog {Id = 1, Title = "One"},
        new Blog {Id = 2, Title = "Two"},
        new Blog {Id = 3, Title = "Three"}
      }.ForEach(b => context.Blogs.Add(b));
      context.SaveChanges();
    }
  }
}

How it works...

On the construction of the context, Entity Framework creates an in-memory version of the expected database model and then tries to connect to that database. If the database is not there, and sufficient rights have been granted to the user, then Entity Framework will create the database. This is done by using the same conventions that the context uses for connecting and retrieving the data. The context defines the metadata schema and then creates the database. There is an additional table that stores the model hash for future comparisons against the model in use.

We are checking for an existing database that is incompatible, and deleting it if found, and then creating one from the objects that we have registered onto the data context with the DbSet properties. You can use the model check to keep the application from starting against a malformed database as well.

Notice that we also call the Initialize method but pass it as true to force the script to run even if the model has not changed. This is for testing purposes, but in a real scenario you would want this code in the start of the application. This will load whatever data we have defined in the initializer. We have given the database three blog entries to seed for the test data, but you can use this to load many other table records. This also ensures that the database gets created correctly every time.

There are some objects which will be static but configured into the database, for example, reference tables or lookup tables come to the mind. These are normally populated by a manual script that needs to be updated every time data is added to the reference tables, or a new lookup is created. We can code these items to be populated when the database is created so the manual update does not need to be run.

There's more...

When we start a green field project, we have that rush of happiness to be working in a problem domain that no one has touched before. This can be exhilarating and daunting at the same time. The objects we define and the structure of our program come naturally to a programmer, but most of us need to think with a different method to design the database schema. This is where the tools can help to translate our objects and intended structure into the database schema if we leverage some patterns. We can then take full advantage of being object-oriented programmers.

Configuration and creation

If you have added configuration for the schema layout of your database, it will be reflected in the database that gets created. This allows you to set up configurations to match any requirements on the schema without sacrificing the object model internal to your application.

Sample data

Testing the database layer has always been complex, but there are tools and strategies which will make it simpler. First, we layer abstractions to allow for unit testing at each level of the application. This will help us cover most of the applications, but there are still integration tests which will need to verify the whole story. This is where database initializers can help us to set up the test so they are brittle and more repeatable.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset