C# Specializations by Traits III

Stormy: Specializations by Traits - Object Relational Model

I like SOCI very much. It is a C++ ORM. Especially the way to go from SQL to model, the object-relational mapping, is very convenient. It is based on specializations.

In the earlier Blogs of this series I have shown that it is possible to do real specializations in C#. This is possible using the combination of traits, a dictionary, and (if necessary) extension methods. This is all explained in previous blogs and I will not explain it again. I will only show how this system is really convenient in Object Relational Mapping (ORM). A hundred-lines prototype is developed. Called Stormy (storm was already occupied).

Use case

In ORM the database (relational) is mapped to objects (models, C# classes). So by nature there are many classes all handled the same way (from SQL to the class instance).

Instead of using the ADO.NET SqlDataReader (delivering just abstract fields and not a model), we want to do things like this:

IEnumerable<Cat> cats = connection.Select<Cat>("select * from cat");

where

• Cat is our model, shown below
• connection is any connection to a database
• the SQL statement is shown, literally. I like that.
• so it returns a generic collection of our model

In C# it is not trivial to specialize the Select method per model. But with traits and a register it can be solved.

The model Cat

The model Cat is borrowed from nHibernate (I know nHibernate - it will not map the class below - it wants method to be virtual). Slightly modified (I like this way of specifying attributes):

public class Cat
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public char Sex { get; set; }
    public float Weight { get; set; }
}

The Object Relational mapping

The ORM is done entirely in code, the way SOCI does it. No XML mappings here. We stay with C#. We show here the implementations Map the Cat from (relational) Database to Object, and vice versa.

public class CatConverter : ITypeConversion<Cat>
{
    public Cat fromSql(SqlDataReader reader)
    {
        Cat cat = new Cat();
        cat.Id = (Guid)reader["CatId"];
        cat.Name = (string)reader["name"];
        cat.Sex = (((string)reader["sex"]) + " ")[0];
        cat.Weight = (float)reader["weight"];
        return cat;
    }

    public void fillParameters(Cat cat, SqlCommand command)
    {
        command.Parameters.Add(new SqlParameter("@name", cat.Name));
        command.Parameters.Add(new SqlParameter("@sex", cat.Sex));
        command.Parameters.Add(new SqlParameter("@weight", cat.Weight));
    }
}

Note that it is mapped using SqlDataReader. It can be made more convenient (eliminating casts) of course, but this is how it is done with SqlDataReader, normally.

That SOCI-like type-converter (I renamed the methods from_base to fromSQL and to_base to fillParameters) is an interface ITypeConversion<T>, and is registered (normally) once per model:

Orm.Register<Cat>(new CatConverter());

Main program

We can implement our scenario, converting from database to Cat and vice versa, using generic programming. Our program:

class Program
{
    static void Main(string[] args)
    {
        Orm.Register<Cat>(new CatConverter());

        Connection connection = new Connection(
            @"
                Data Source=localhost\SQLEXPRESS;
                MultipleActiveResultSets=True; 
                Initial Catalog=stormy;
                Integrated Security=SSPI;
            ");

        var cats = connection.Select<Cat>("select * from cat");
        foreach(Cat cat in cats)
        {
            System.Console.WriteLine("Cat {0} named {1}", cat.Id, cat.Name);
        }

        // Create subselection using linq (not related to SpecializationbyTraits)
        var toms = from cat in cats where cat.Name == "Tom" select cat;
        if (toms.Count() > 0)
        {
            Cat tom = toms.First();
            System.Console.WriteLine("Tom found, ID={0}", tom.Id);
        }
        else
        {
            Cat tom = new Cat();
            tom.Name = "Tom";
            tom.Sex = 'm';
            tom.Weight = 5.2F;
            connection.Execute(CatConverter.InsertSql(), tom);
        }
    }
}

We first register our type conversion specialization. We create a connection to the database (hard coded - alternatively you might get it from e.g. appSettings). We can use any SQL statement to fill any list with any (registered) model. For fun we use LINQ here to do a subselection. We can execute SQL to insert, update or delete things. That InsertSql is specified (for convenience, I prefer it close to its parameters) within the converter as well, it can be anywhere, up to you.

public static string InsertSql()
{
    return "insert into Cat(CatId, Name, Sex, Weight) values(NEWID(), @name, @sex, @weight)";
}

Stormy ORM - wrapping it up

Great isn't it? The Stormy code is about hundred lines now. Of course it is not a full-featured ORM as others are, but in many cases more convenient, more simple to understand, it does not create shadow classes, and it does not force you to learn a new criteria language. If you know SQL, you will like it!

The project including sample can be downloaded here.

We're nearly done with C# Specializations. It is really useful. Can be used everywhere. Makes C# closer to C++.

Next:

• geometry...

Stay tuned!