Is there a better way to map Objects from a database query to an object?

Question

I would like to know if there is a better way to solve this problem that I am overlooking. (I'm looking for a second opinion)

I want to create a generic and easy way to bind objects to database reader queries using "Oracle.DataAccess.Client".

In order to do this I initially wanted to create an object which inherited from OracleCommand; however, OracleCommand is a sealed object.

To deal with this I decided to create an extension method which attempts to map objects to generic columns in the database for each row.

EDIT : In my scenario, I know what the database will look like; however, I will not know where the database is before run time. i.e. The database may have been transferred ahead of time and the end user will specify the credentials for the database at run time.

Here is the implementation:

public static T[] Bind<T>(this OracleCommand oe, Binding binding, CommandBehavior Behavior = CommandBehavior.Default)
    {
        List<T> ret = new List<T>();

        using (var reader = oe.ExecuteReader(Behavior))
        {
            while (reader.Read())
            {
                T unknownObj = (T)Activator.CreateInstance(typeof(T));
                for (int i = 0; i < binding.GetBindCount(); i++)
                {
                    var propinfo = unknownObj.GetType().GetProperties().ToList();
                    var prop = propinfo.Find((p) => p.Name == binding.GetBindValue(i, true));
                    prop.SetValue(unknownObj, reader[binding.GetBindValue(i, false)]);
                }
                ret.Add(unknownObj);
            }
        }
        return ret.ToArray();
    }
}

public class Binding
{

    List<BindingMap> _map = new List<BindingMap>();


    public void AddBind(String VariableName, String ColumnName)
    {
        _map.Add(new BindingMap(VariableName, ColumnName));
    }
    public String GetBindValue(int index, bool IsVariable = true)
    {
        var a = _map.ToArray();
        return (IsVariable) ? a[index].Variable : a[index].Column;
    }

    public int GetBindCount()
    {
        return _map.Count;
    }
}

public class BindingMap
{
    public String Column;
    public String Variable;

    public BindingMap(String v, String c)
    {
        Variable = v;
        Column = c;
    }
}

Is there a better way to do this that I've overlooked, or is this a sound?

The way it would be used in real code is like this :

 static void Main()
    {           
        Binding b = new Binding();
        b.AddBind("CreatedBy", "Create_by");


        using (var Conn = new OracleConnection())
        {
            Conn.ConnectionString = od.Options.GetConnectionString();
            using (var Command = new OracleCommand())
            {
                Command.Connection = Conn;
                Command.CommandText = "Select * From Accounts";

                Conn.Open();

                var a = Command.Bind<Account>(b);
                foreach (Account e in a)
                {
                    Console.WriteLine(e.CreatedBy);
                }
            }
        }
        Console.Read();
    }

    public class Account
    {
        public String CreatedBy
        {
            get;
            set;
        }

    }

Answer 1

As a slightly better way, you could designate the bound property like Telerik does: with a Linq expression. Here is the usage. Instead of :

AddBind("CreatedBy", "Created_by");

You would write

AddBind( x => x.CreatedBy, "Created_by");

You get a slightly stronger typing opportunity. The signature of AddBind would be:

public void AddBind<T>(Expression<Func<Account, T>> variable, string columnName) {
    // ...
}

But I would not go into the way of generic functions. I'd rather overload a non-generic function :

public void AddBind(Expression<Func<Account, double>> variable, string columnName) {
    // Add binding for double
}

public void AddBind(Expression<Func<Account, DateTime>> variable, string columnName) {
    // Add binding for DateTime
}

// ...

The type of binding would then be selected according to the type of your mapped object. This prevents you from misnaming your properties, so you keep the possibility of performing name changes in the Account class without breaking your bindings.

The column name has still to be a string, sorry.

Of course, the way then to generalize is to make your BindingMap generic. (Taking your business class as a type parameter)

class BindingMap<BusinessClass> {
  // ....

    public void AddBind(Expression<Func<BusinessClass, double>> variable, string columnName) {
        // Add binding for double
    }

    public void AddBind(Expression<Func<BusinessClass, DateTime>> variable, string columnName) {
        // Add binding for DateTime
    }

    // ... 
};

I leave as an exercice to you the problem of digging the property descriptor out of the expression :)