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);
}}
Comments
Post a Comment