Monday, 11 June 2012

DataContext ExecuteQuery extension method returning dynamic objects

This is an extension method that returns enumeration of dynamic objects based on SQL query ResultSet.

public static class DataContextExtensions
{
  public static IEnumerable<dynamic> ExecuteQuery(this DataContext ctx, string query, DbParameter[] parameters = null)
  {
    using (DbCommand cmd = ctx.Connection.CreateCommand())
    {
      cmd.CommandText = query;
      if (parameters != null) cmd.Parameters.AddRange(parameters);
      ctx.Connection.Open();
      using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
      {
        while (rdr.Read())
        {
          dynamic row = new DynamicRow();
          for (int i = 0; i < rdr.FieldCount; i++)
          {
            row[rdr.GetName(i)] = rdr[i];
          }
          yield return row;
        }
      }
    }
  }
}
//DynamicRow class is similiar to ExpandoObject but with addition of indexer
public class DynamicRow : DynamicObject
{
  private readonly Dictionary<string, object> _data = new Dictionary<string, object>();
  public object this[string propertyName]
  {
    get
    {
      object result = null;
      TryGetMember(propertyName, out result);
      return result;
    }
    set { TrySetMember(propertyName, value); }
  }
  public override bool TryGetMember(GetMemberBinder binder, out object result)
  {
    return TryGetMember(binder.Name, out result);
  }
  private bool TryGetMember(string propertyName, out object result)
  {
    return _data.TryGetValue(propertyName.ToLower(), out result);
  }
  public override bool TrySetMember(SetMemberBinder binder, object value)
  {
    return TrySetMember(binder.Name, value);
  }
  private bool TrySetMember(string propertyName, object value)
  {
    _data[propertyName.ToLower()] = value;
    return true;
  }
}

And this is how you can use it

[Test]
public void TestDataContextDynamic()
{  
  var connString = @"Persist Security Info=False; Integrated Security=SSPI; Server={0}; Database={1}; Connect Timeout={2}; Application Name={3}";
  var dataContext = new DataContext(new SqlConnection(connString));
  var result = dataContext.ExecuteQuery("SELECT TOP 10 Column1, Column2 FROM Table1");
  foreach (var row in result)
  {
    Console.WriteLine("{0},{1}", row.Column1, row.Column2);
  }
}

No comments:

Post a Comment