Create object from database row

Question

Let's say I'm building a data access layer for an application. Typically I have a class definition for a each kind of object that is stored in the database. Of course, the actual data access retrieves data in the form of a datareader, typed or untyped dataset, or similar, usually with the data needed to create one object per row in the results.

How would you go about creating your object instances in the data layer? Would have a constructor that accepts a datarow? If so, how would you make that type-safe? Or would you have your constructor list out one parameter for each field you want to instantiate, even if there could be many fields? Would you mark this constructor 'internal'?

Answer 1

If you aren't content with DataRow or SqlDataReader, you should look at an ORM system like Linq to Sql or nHibernate, instead of re-inventing the wheel yourself.

(By the way, this is called the "ActiveRecord" pattern)

Answer 2

I highly encourage you to use an ORM tool. Even simple projects can make use of ORM quickly and quietly... in particular, look at Castle's ActiveRecord tool (which sits on top of NHibernate to simplify model declaration).

Answer 3

Will those support more complex queries as the source for the data? That might, for example, join in several other tables even if only to determine which records should be returned in a particular situation? And will they let you add your own methods to the classes they use?

Answer 4

I have accomplished this by using reflection. Where I name the column from the Select statement of the object.

This assumes you have a Templated helper class. If you want to put it on the object yourself you can just replace all the T with the object.

This is an example:

private T ObjectFromRow(DataRow row)
{
    Type t = typeof(T);

    T newObj = (T)Activator.CreateInstance(t);


    System.Reflection.PropertyInfo[] properties = t.GetProperties();

    for (int i = 0; i < properties.Length; i++)
    {
        if (!properties[i].CanWrite)
        {
            continue;
        }

        if (!row.Table.Columns.Contains(properties[i].Name))
        {
            continue;
        }

        if (row[properties[i].Name] == DBNull.Value)
        {
            continue;
        }

        if (properties[i].PropertyType == typeof(string))
        {
            properties[i].SetValue(newObj, row[properties[i].Name], null);
        }
        else if (properties[i].PropertyType == typeof(double))
        {
            properties[i].SetValue(newObj, double.Parse(row[properties[i].Name].ToString()), null);
        }
        else if (properties[i].PropertyType == typeof(int))
        {
            properties[i].SetValue(newObj, int.Parse(row[properties[i].Name].ToString()), null);
        }
        else if (properties[i].PropertyType == typeof(DateTime))
        {
            properties[i].SetValue(newObj, DateTime.Parse(row[properties[i].Name].ToString()), null);
        }
        else if (properties[i].PropertyType == typeof(bool))
        {
            properties[i].SetValue(newObj, bool.Parse(row[properties[i].Name].ToString()), null);
        }
    }

    return newObj;
}

Answer 5

@Joel (re: complex queries, joins, etc)

The NHibernate and Castle ActiveRecord tool can handle very complex queries and joins via class relationships and a thorough 'Expression' class (which you can add to the query methods) or the use of the 'Hibernate Query Language' (HQL).

You can Google any of these details, check the official documentation, or see the awesome Summer of NHibernate screencasts.

Answer 6

As an alternative to NHibernate & Castle you can take a look at SubSonic. This also uses ActiveRecord but is more of a Swiss Army knife than NHibernate.

EDIT:

Here is a sample from the SubSonic documentation:

Simple Select with string columns

            int records = new Select("productID").
                 From("Products").GetRecordCount();

            Assert.IsTrue(records == 77);

Simple Select with typed columns

            int records = new Select(Product.ProductIDColumn, Product.ProductNameColumn).
                From<Product>().GetRecordCount();
            Assert.IsTrue(records == 77);

And some further examples:

Standard Deviation

    const double expected = 42.7698669325723;

    // overload #1
    double result = new
        Select(Aggregate.StandardDeviation("UnitPrice"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #2
    result = new
        Select(Aggregate.StandardDeviation(Product.UnitPriceColumn))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #3
    result = new
        Select(Aggregate.StandardDeviation("UnitPrice", "CheapestProduct"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #4
    result = new
        Select(Aggregate.StandardDeviation(Product.UnitPriceColumn, "CheapestProduct"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

And some Wildcard methods:

 [Test]
        public void Select_Using_StartsWith_C_ShouldReturn_9_Records() {


            int records = new Select().From<Product>()
                .Where(Northwind.Product.ProductNameColumn).StartsWith("c")
                .GetRecordCount();
            Assert.AreEqual(9, records);
        }