Create an object from a database row

Question

Say I'm creating a data access layer for an application. Usually I have a class definition for each type of object that is stored in the database. Of course, the actual access to the data retrieves the data in the form of a data set, a typed or untyped data set, or, as a rule, with the data needed to create one object in a row in the results.

How are you going to instantiate objects in the data layer? Will there be a constructor accepting datarow? If so, how would you do this type of security? Or do you have a list of constructors from one parameter for each field that you want to create, even if there can be many fields? Would you mark this constructor "internal"?

Answer 1

I highly recommend you use the ORM tool. Even simple projects can use ORM quickly and easily ... in particular, look at the Castle ActiveRecord tool (which sits on top of NHibernate to simplify model declarations).

Answer 2

If you are not happy with DataRow or SqlDataReader, you should look at an ORM system such as Linq to Sql or nHibernate, instead of reinventing the wheel yourself.

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

Answer 3

I did this 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 simply replace all 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 4

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

NHibernate and Castle ActiveRecord can handle very complex queries and combine through class relationships and the careful Expression class (which you can add to query methods) or the use of the Hibernation Query Language (HQL).

You can use any of this data on Google, check the official documentation or watch the stunning summer of NHibernate screencasts.

Answer 5

As an alternative to NHibernate and Castle, you can take a look at SubSonic . It also uses ActiveRecord, but it’s more like a Swiss army knife than NHibernate.

EDIT:

Here is a sample from SubSonic's 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 more 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); } 

Answer 6

Will more complex queries be supported as a data source? This can, for example, join several other tables, at least to determine which records should be returned in a particular situation? And will they allow you to add your own methods to the classes that they use?