Introduction to BootFX's Object Relational Mapping Functionality

Introduction

BootFX is an Open Source .NET application framework that has been in development since 2000. It originated from work to support a book that myself and Karli Watson wrote for Wrox Press, called .NET Enterprise Development. It has been used in commercial products such as Topaz Filer, Web-based services such as Twitter Ad Box and .NET 247, and a decent collection of private and public sector organisations. It is equally well-suited for developing Web and desktop applications.

BootFX is primarily an object-relational mapping database tier, although over time, user interface components and features to help manage and run the application have been added. The intention of this article is to take the reader through how to build the framework library, generate an entity tier, and create and run a sample application.

There are a number of modules to the BootFX system - the one that we are using here is the BootFX Common library.

Building

The downloads for BootFX are hosted on Google Code. Visit http://www.bootfx.com/ to be redirected to the appropriate location within Google Code. From there, download the latest source code package.

The source package is a Visual Studio .NET 2005 package. If you open it in Visual Studio 2008, it will prompt you to convert. The package is created directly from our build system, so you will be prompted with regards to source control. You can permanently remove all source code bindings when prompted by Visual Studio.

The project is configured to build only if a key container called BootFX is installed on the machine. Key containers were deprecated in .NET 2.0, although personally, I have found these the easiest way to work with key files with Visual Studio. (You will find that the projects have the relevant warning disabled in the build settings.) Therefore, you have two options - you can either create a key file and install it in the container, or you can change the project settings to use another approach. (For example: see http://blogs.msdn.com/akukreja/archive/2008/05/02/use-key-name-or-containers-sign-net-assembly.aspx.)

To create and install the key file, open the Visual Studio Tools command prompt and execute:

sn -k BootFX.snk

...and then:

sn -i BootFX.snk BootFX

To assist with working with multiple projects that use BootFX, the solution is configured to copy all binaries into a folder called c:\Sluice\BootFX\Common\bin. This is done by an executable called BootFX.Common.Sluice.exe. The solution is configured to build this project first and manually create and copy it to c:\Sluice. From there, build events in the other project reference the executable to copy the build output.

The "sluice" utility can be used with any output assembly, provided its name is of the form <company>.<project>[<otherParts>]<.dll|.exe>.

The solution should now compile and create the BootFX assemblies.

Creating a Sample Project

A sample SQL Server 2005 database can be downloaded from the Google Code site. Visit http://www.bootfx.com/ and look for BootFX.Sample-Database.zip in the downloads.

Using DBUtil

DBUtil is the utility application that generates the entities for use with BootFX. This connects to a SQL Server database (MySQL is supported through an optional database), reads the metadata, and uses .NET's CodeDom library to emit classes. You can connect DBUtil to a database and generate classes any number of times in order that you can change the database schema as you develop.

DBUtil works by generating two classes per table - one of the classes (designated the "base" class) is regenerated every time DBUtil is asked to generate classes. The other class is generated only the first time. This allows you to add customisations to the generated code without it being overwritten by subsequent generation operations. In addition, the base class is inherited from BootFX's Entity class. This diagram illustrates this:

DBUtil maintains a project file, which is a simple XML file. It is imperative that the project file is included in your application's source tree; otherwise, it will be impossible to share customizations with the rest of the project team.

When DBUtil starts, you will be asked to create or open a project:

Choose Create a new project. When DBUtil starts, select Project - Connect to Database from the menu. You will be presented with a dialog that allows you to connect to the database. Enter the credentials for the database that you wish to use.

DBUtil will then present a list of the tables in the database.

By default, tables are set not to be generated (hence the red X next to each). You can switch the generation on and off by selecting the table in the tree and changing the Generate project in the property panel to True.

You will notice in the tree that two names are given per table - for example, "Company" and "Companies". BootFX allows you to provide aliases to table and column names such that if you have particularly obscure naming in the database, you can map this to easier-to-work-with names in code. By default, DBUtil will attempt to create singular forms of English plural names.

Turn on generation for the Companies and Projects tables.

Methods and Properties Generated by DBUtil

DBUtil generates a set of properties and methods as part of its default operation. In addition, it will embed the entire set of metadata into the code using attributes. (This means that the original database schema is available to running BootFX applications. Future articles will detail functionality in BootFX that makes use of this capability.)

Each class starts with attributes that tell the BootFX runtime that it is an entity class. Entities are also marked as serializable, by default. In this example, a default sort specification is also given:

[Serializable()]
[Entity(typeof(ProjectCollection), "Projects")]
[SortSpecification(new string[] {
        "Name"}, new BootFX.Common.Data.SortDirection[] {
        BootFX.Common.Data.SortDirection.Ascending})]
public class Project : ProjectBase
{

Within the base class, properties that map one-to-one with database columns are provided, for example:

[EntityField("Name", System.Data.DbType.String, 
  BootFX.Common.Entities.EntityFieldFlags.Common, 64)]
public string Name
{
    get
    {
        return ((string)(this["Name"]));
    }
    set
    {
        this["Name"] = value;
    }
}

The Entity class within BootFX handles the storage of values in memory, which is done by using two arrays - one to store the values, and one to store flags that describe the state of the data.

In addition, when BootFX detects foreign key relationships in the database, it will provide properties and methods to help navigate the hierarchy. For example, this property on Project provides access to the parent Company instance (the created entity is cached):

[EntityLinkToParent("Company", 
  "FK_Projects_Companies", typeof(Company), 
  new string[] { "CompanyId"})]
public Company Company
{
    get
    {
        return ((Company)(this.GetParent("Company")));
    }
    set
    {
        this.SetParent("Company", value);
    }
}

Methods are generated as well as properties. These are commonly static methods that return entities that match certain values in the database. By default, you get a method to return a single instance from a known ID, or all entities:

public static Project GetById(int projectId)
{
    return BootFX.Common.Generated.Project.GetById(projectId, 
                  BootFX.Common.Data.SqlOperator.EqualTo);
}

public static Project GetById(int projectId, 
       BootFX.Common.Data.SqlOperator projectIdOperator)
{
    BootFX.Common.Data.SqlFilter filter = 
      new BootFX.Common.Data.SqlFilter(typeof(Project));
    filter.Constraints.Add("ProjectId", projectIdOperator, projectId);
    return ((Project)(filter.ExecuteEntity()));
}

...and:

public static ProjectCollection GetAll()
{
    BootFX.Common.Data.SqlFilter filter = 
      BootFX.Common.Data.SqlFilter.CreateGetAllFilter(typeof(Project));
    return ((ProjectCollection)(filter.ExecuteEntityCollection()));
}

The SqlFilter class will be explained later.

You also get exactly one method per column:

public static ProjectCollection GetByCompanyId(int companyId)
{
    return BootFX.Common.Generated.Project.GetByCompanyId(
            companyId, BootFX.Common.Data.SqlOperator.EqualTo);
}

public static ProjectCollection GetByCompanyId(int companyId, 
       BootFX.Common.Data.SqlOperator companyIdOperator)
{
    BootFX.Common.Data.SqlFilter filter = 
         new BootFX.Common.Data.SqlFilter(typeof(Project));
    filter.Constraints.Add("CompanyId", 
                           companyIdOperator, companyId);
    return ((ProjectCollection)(filter.ExecuteEntityCollection()));
}

DBUtil will also detect indexes. It will then generate methods in one of two modes. If it detects the index is set to be unique, it will create a method that returns a single object rather than a collection, for example:

public static Project GetByJobNumber(int jobNumber)
{
    return BootFX.Common.Generated.Project.GetByJobNumber(
                  jobNumber, BootFX.Common.OnNotFound.ReturnNull);
}

public static Project GetByJobNumber(int jobNumber, 
              BootFX.Common.OnNotFound onNotFound)
{
    BootFX.Common.Data.SqlFilter filter = 
           new BootFX.Common.Data.SqlFilter(typeof(Project));
    filter.Constraints.Add("JobNumber", 
      BootFX.Common.Data.SqlOperator.EqualTo, jobNumber);
    Project results = ((Project)(filter.ExecuteEntity()));
    return results;
}

If the index has multiple columns, it will create a method that takes the equal number of parameters. This has two advantages - firstly, it makes it very easy to add methods to the model by adding indexes. Secondly, because it is easier to use the indexes, it encourages the developer to remember to add indexes to the database.

The last trick that DBUtil has up its sleeve with regards to code generation is the ability to map columns to enumerations in code. For example, if you have a status column in the database stored as an integer that you want mapped to an enumeration called - for example - CacheItemStatus, set the value in DBUtil:

...and code will be generated to match (this affects all relevant properties and method parameters):

[EntityField("Status", System.Data.DbType.Int32, 
  BootFX.Common.Entities.EntityFieldFlags.Common)]
[DatabaseDefault(
  BootFX.Common.Data.Schema.SqlDatabaseDefaultType.Primitive, 0)]
public CacheItemStatus Status
{
    get
    {
        return ((CacheItemStatus)(this["Status"]));
    }
    set
    {
        this["Status"] = value;
    }
}

Adding the Files to Your Project

Now that you have an understanding of how code generation works in DBUtil, let's have a look at how the code generation works.

DBUtil is not (currently) integrated in with Visual Studio, which means that you need to operate the two applications in concert. (Although we had plans many years ago to provide DBUtil as an add-in, the working method described here works well enough.)

For the sample project, I have created a blank Visual Studio solution with two projects - a class library and a console application. The class library is used for all UI agnostic work. The purpose of the console application will be to run BootFX code later. In the class library project, I have created a folder called Entities.

Select Project - Settings from the menu. This will show the options dialog. Change the namespace to be BootFX.Sample and the Output entities to this folder value to be the real folder path of the Visual Studio project's Entities folder.

The other items can remain the same. Click OK, and then select Project - Generate Code from the menu. Back in Visual Studio, you will find that the items are now available. You can now include them in the project:

The !Base folder contains the base classes. (Sometimes it is helpful to be able to regenerate all of the entity base classes, and having them self-contained in this way makes checking them out from source control far easier.)

The other requirement is to add the BootFX.Common.dll assembly reference to both projects, e.g.:

Starting the Runtime and Using the Entities

We're now ready to start working with our entities.

In the console application, create the following class. We'll fill in the operation of DoRun in a moment:

namespace BootFX.Sample
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                DoRun();
            }
            catch (Exception ex)
            {
                Console.WriteLine("------------------------------");
                Console.WriteLine(ex);
            }
            finally
            {
                if (System.Diagnostics.Debugger.IsAttached)
                    Console.ReadLine();
            }
        }
    }
}

In addition, add the BootFX.Common and BootFX.Common.Data namespaces to the using statements at the top of the class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BootFX.Common;
using BootFX.Common.Data;

BootFX needs to have its internal state initialised via a method called Runtime.Start. Secondly, we need to tell BootFX where to find the database that we are using. Add these calls to the DoRun method:

private static void DoRun()
{
    // start the runtime...
    Runtime.Start("BootFX", "Sample", 
      "Console", 
      typeof(Project).Assembly.GetName().Version);

    // define the default database connection...
    Database.SetDefaultDatabase(typeof(SqlServerConnection), 
      "data source=(local);initial catalog=sample;" + 
      "integrated security=sspi");

We're now ready to start using the entities. The easiest call to make is one that grabs all of the companies and renders the names to the screen. This can be done using this code:

// walk the companies...
foreach (Company company in Company.GetAll())
    Console.WriteLine(company.Name);

Running the application gives us this:

This can be enhanced like so:

// walk the companies...
foreach (Company company in Company.GetAll())
{
    Console.WriteLine(company.Name);

    // walk the projects...
    foreach (Project project in company.GetProjectItems())
        Console.WriteLine("\t{0} --> {1}", 
                project.JobNumber, project.Name);
}

The output is now as follows:

Changing Data

At this point, you should be able to see the simplicity of reading the entity data. Changing the data in the database is also straightforward.

The Entity base class is able to keep track of changes that have been made to the column-mapped property values and patch those values back into the database.

To update data, all you have to do is set the property values and call the SaveChanges method. For example, this code loops through every project and sets the SharePointUrl property:

// walk...
foreach (Project project in Project.GetAll())
{
    // set the sharepoint URL...
    project.SharePointUrl = string.Format(
      "http://myserver/projects/{0}/", project.JobNumber);

    // save changes to the project...
    project.SaveChanges();
}

BootFX is able to handle database transactions through an approach of attaching an open transaction to the executing thread. Whenever new commands are created on the database connection, if a transaction is attached to the thread, the command's Transaction property is set.

If we take the above code, we can wrap it in a thread-bound transaction by adding code to setup, commit, or rollback the transaction, like this:

// create a connection and bind it to the thread...
Database.BeginTransaction();
try
{
    // walk...
    foreach (Project project in Project.GetAll())
    {
        // set the sharepoint URL...
        project.SharePointUrl = string.Format(
          "http://myserver/projects/{0}/", 
          project.JobNumber);

        // save changes to the project...
        project.SaveChanges();
    }

    // commit and then unbind the transaction...
    Database.Commit();
}
catch (Exception ex)
{
    // rollback (then unbind) and throw...
    Database.Rollback(ex);
    throw new InvalidOperationException(
      "The update operation failed.", ex);
}

For insert operations, the entity keeps track of whether the ID values have been set. If they have not been set, and the entity is flagged as having auto-increment columns, BootFX will issue an insert operation.

To round off the available CRUD operations, to delete an entity, call the MarkForDeletion method and then call SaveChanges.

Creating a SQL Filter and Working with Set-based Data

To round off the discussion, it's worth looking at the SqlFilter class and the SqlStatement class.

SqlFilter is used to load a set of entities from the database. The principle is that you give it exactly one entity type to work with and then constrain the data down from everything in the table to the subset that you want. You can dig around the generated code to find examples of SqlFilter, but we'll take a look at one now.

If you use just the Constraints property and provide simple column matching expressions, you can guarantee that the statement will work irrespective of the database type that it is connected to - for example, the same code will work on SQL Server or MySQL. However, there is an option to create a "free constraint" on the table. This allows you to create expressions that get written into the resultant "where" clause, but you lose the SQL-variant-agnostic feature.

Here's a quick example:

private static CompanyCollection GetMagicCompanies(string projectSubset)
{
    SqlFilter filter = Company.CreateFilter();

    // constraint by companies that start with 'E'...
    filter.Constraints.Add("name", 
       SqlOperator.StartsWith, "e");

    // the constrain by passing in the value of the project subset...
    filter.Constraints.AddFreeConstraint("companyid in (select" + 
       " companyid from projects where name like @subset)");
    filter.ExtraParameters.Add("subset", DbType.String, 
      "%" + projectSubset + "%");

    // run...
    return (CompanyCollection)filter.ExecuteEntityCollection();
}

Under the hood, the SqlFilter class extends SqlStatementCreator. The purpose of this class is to dynamically emit a SqlStatement. SqlStatement instances are the basis of all database activity in BootFX. They essentially wrap and generate the specific .NET types, such as System.Data.SqlClient.SqlCommand. If you add the following code to GetMagicCompanies, you can see the statement generation in action:

// output the statement...
Console.WriteLine("---------------------------------");
Console.WriteLine(filter.GetStatement().CommandText);
Console.WriteLine("---------------------------------");

...for example:

You can see from the example that BootFX is correctly managing patching parameters into the statement, adding the order by statement and formatting the column and table names for the relevant SQL variant (SQL Server, in this case).

Use IntelliSense to examine the static methods available on the Database class. You will notice that these allow for selection of scalar values, selection of set-based tables as DataTable or DataSet instances, or execution of non-query statements.

Conclusion

In this article, we have seen how to use the powerful object relational mapping functionality in the BootFX application framework. We looked at how to build the library, include the assemblies in our own code, start the runtime, and select out change data within the database.