C# - Build Your Own ORM - Part 2 - Table to Object

Last post I talked about why I think it's worth building your own ORM. In this post we are going to get started by implementing a core function that simply takes a datareader and uses it to populate a given object. We're going to skip a lot of fancy code like LINQ, generics, variable parameter functions, attributes, and interfaces to keep the code simple and unoptimised.

First let's get our database sorted. I'll be using MS SQL Express so the SQL included will be in the form of T-SQL script though I'll be using the OdbcDataReader since I can't recall the last time I worked at a place that only used one particular database.

Here is the T-SQL script for the simple table we are going to read from and its data...

CREATE TABLE [dbo].[job]
(
  [id] INT NOT NULL PRIMARY KEY IDENTITY,
  [title] NVARCHAR(MAX) NULL,
  [agent_id] INT NULL,
  [application_date] DATETIME NULL
)

INSERT INTO [dbo].[job] ([title], [agent_id], [application_date]) VALUES ('C# Developer', 1, '2013-01-01 00:00:00')
INSERT INTO [dbo].[job] ([title], [agent_id], [application_date]) VALUES ('Java Developer', 1, '2013-02-15 00:00:00')
INSERT INTO [dbo].[job] ([title], [agent_id], [application_date]) VALUES ('Tester', 2, '2013-03-29 13:00:00')

Now let's define a simple class that mimics the table layout (listed below). For the time being we'll use simple public fields rather than properties.

public class job
{
  public int id;
  public string title;
  public int agent_id;
  public System.DateTime application_date;
}

Now, with the use of reflection, it's very easy to produce a function that iterates through a datareader's columns, attempts to find matching fields in a given object, and transfers the values accordingly.

public class orm
{
  public static void Populate_Object(object obj, System.Data.Odbc.OdbcDataReader data)
  {
    int c;
    string col_name;
    object col_val;
    System.Reflection.FieldInfo obj_field;
    System.Type obj_type;

    if (obj!=null && data!=null)
    {
      obj_type=obj.GetType();
      
      // iterate through the datareader columns and look for matching object fields
      for (c = 0; c < data.FieldCount; c++)
      {
        col_name = data.GetName(c);

        // look for an object field with the same name as the current column
        obj_field = obj_type.GetField(col_name);
        if (obj_field != null)
        {
          // take the actual value for the current column and...
          col_val = data.GetValue(c);
          // write it into the matching object field
          obj_field.SetValue(obj, col_val);
        }
      }
    }
  }
}

Something to take note of is that the datareader does not need to have all the columns that match the object's fields, ie "select * from job". The originating query could include calculated columns, missing fields, or extra columns with no matching field. For example, "select id, getdate() as application_date from job" would result in job objects populated only with the "id" field and the current date in the "application_date" field.

For completeness here's some code making use of our function. Note that you'll have to supply your own connection string. I'm going to assume you're already familiar with .Net's database API so I won't bother to explain it, suffice it to say that you should end up with an ArrayList containing all the data in the "job" table.

System.Data.Odbc.OdbcConnection conn;
System.Data.Odbc.OdbcCommand cmd;
System.Data.Odbc.OdbcDataReader data;
job j;
System.Collections.ArrayList jobs;

conn = new System.Data.Odbc.OdbcConnection("...");
conn.Open();
cmd = new System.Data.Odbc.OdbcCommand("select * from job", conn);
data = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
jobs = new System.Collections.ArrayList();
while (data.Read())
{
  j = new job();
  orm.Populate_Object(j, data);
  jobs.Add(j);
}
data.Close();
conn.Close();

There are certainly many ways we could improve our "Populate_Object" function. It doesn't take much thinking or tinkering to realise it won't handle database nulls and other more exotic database column types. Also, we are limited to cases where the column names exactly match the object's field names and we haven't made any use of generics, variable parameter functions, and other language features, but here's where we gain an advantage over off-the-shelf ORM solutions. We only have to implement those features that are actually required and can do so in whatever fashion we feel is most appropriate. As we progress I'm hoping it'll become obvious that implementing a lot of these features is not as difficult as the pro-ORM lot make it out to be.

Next post, we will hide the routine datareader code to make it much easier to work with SQL.