C# - Build Your Own ORM - Part 3 - Select

In the previous post we built a simple function to read the current row of an OdbcDataReader into a simple object. This time we are going to combine that with a new function that will allow us to easily execute SQL and return a list of resulting objects. Because we want the Select function to return a list of objects of any type we need we will be making use of generic methods. This will allow us to specify what type of objects we want when calling the Select function. Secondly, we want to use parameterized SQL queries so our Select function will also be a variable parameter function. The logic is very simple. We create the usual OdbcCommand object, fill in the parameters passed by the callee, execute, create a list, and iterate through the query results creating new objects, populating them, and adding them to the list. The resulting code is shown below. 
public static System.Collections.Generic.List<T> Select<T>(System.Data.Odbc.OdbcConnection conn, string sql, params object[] param_vals) where T : new()
  {
    System.Collections.Generic.List<T> res = null;
    T obj;
    System.Data.Odbc.OdbcDataReader data;
    System.Data.Odbc.OdbcCommand cmd;
    int c;

    // prepare sql query and add parameter values if necessary
    cmd = new System.Data.Odbc.OdbcCommand(sql, conn);
    if (param_vals!=null && param_vals.Length>0)
    {
      for (c = 0; c < param_vals.Length; c++)
      {
        cmd.Parameters.AddWithValue(c.ToString(), param_vals[c]);
      }
    }

    // execute query and convert result into an object list
    data = cmd.ExecuteReader();
    if (data.HasRows)
    {
      res = new System.Collections.Generic.List<T>();
      while (data.Read())
      {
        obj = new T();
        Populate_Object(obj, data);

        res.Add(obj);
      }
    }
    data.Close();

    return res;
  }
We can add this function to the "orm" class we created in the previous post and end up with the following code. 
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);
        }
      }
    }
  }

  public static System.Collections.Generic.List<T> Select<T>(System.Data.Odbc.OdbcConnection conn, string sql, params object[] param_vals) where T : new()
  {
    System.Collections.Generic.List<T> res = null;
    T obj;
    System.Data.Odbc.OdbcDataReader data;
    System.Data.Odbc.OdbcCommand cmd;
    int c;

    // prepare sql query and add parameter values if necessary
    cmd = new System.Data.Odbc.OdbcCommand(sql, conn);
    if (param_vals!=null && param_vals.Length>0)
    {
      for (c = 0; c < param_vals.Length; c++)
      {
        cmd.Parameters.AddWithValue(c.ToString(), param_vals[c]);
      }
    }

    // execute query and convert result into an object list
    data = cmd.ExecuteReader();
    if (data.HasRows)
    {
      res = new System.Collections.Generic.List<T>();
      while (data.Read())
      {
        obj = new T();
        Populate_Object(obj, data);

        res.Add(obj);
      }
    }
    data.Close();

    return res;
  }
}
So how do we use this? Below is some code based on the database we mentioned in the previous post. 
System.Data.Odbc.OdbcConnection conn;
System.Collections.Generic.List<job> jobs;
System.DateTime start_date, end_date;

conn = new System.Data.Odbc.OdbcConnection("Driver={SQL Server Native Client 11.0}; Server=(LocalDB)\\v11.0; Database=JobBuddy.Db; Trusted_Connection=Yes;");
conn.Open();

jobs = orm.Select<job>(conn, "select * from job order by application_date");

jobs = orm.Select<job>(conn, "select title from job where agent_id=1");

jobs = orm.Select<job>(conn, "select * from job where title like ?", "%Developer%");

start_date = new DateTime(2013, 2, 1);
end_date = new DateTime(2013, 4, 1);
jobs = orm.Select<job>(conn, "select * from job where application_date>? and application_date<?", start_date, end_date);

conn.Close();
As you can see from the various SQL sample calls I've included, it's a very easy to use function. As before there are many ways we could optimize and customize it but that's up to you and your situation. Remember I'm not trying to write some generic be-all end-all ORM library here. Next post let's try the equivalent UPDATE function.

Popular Posts