I develop a reporting engine where reports are based on templates. Every template has string with SQL query and every report has specific values for SQL query parameters. To render a report I set parameters and call DataContext.ExecuteQuery method to get list of records. But to catch returned columns I have to know their names and have a class with corresponding properties.
Is it possible somehow to return IEnumerable of anonymous objects from DataContext.ExecuteQuery and then determine their properties using Reflection?
I need a LINQ equivalent for SqlDataReader.GetValues.
Thanks!
Until we have C# 4.0 with dynamiс keyword we can use this solution (slightly modified code from an article Executing arbitrary queries in LINQ to SQL by Octavio Hernández Leal):
public static class DataContextExtension
{
public static IEnumerable<Dictionary<string, object>> ExecuteQuery(this DataContext dataContext, string query)
{
using (DbCommand command = dataContext.Connection.CreateCommand())
{
command.CommandText = query;
dataContext.Connection.Open();
using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
Dictionary<string, object> dictionary = new Dictionary<string, object>();
for (int i = 0; i < reader.FieldCount; i++)
dictionary.Add(reader.GetName(i), reader.GetValue(i));
yield return dictionary;
}
}
}
}
}
This extension method returns IEnumerable of Dictionary<> objects where keys are names of query columns.
Yes, you can do it.
Please have a look at this snippet.
class Program {
static void Main(string[] args) {
var persons = new Person[]{
new Person{Age=22,Name="John Doe",Id=1},
new Person{Age=23,Name="Jack Smith", Id=2},
new Person{Age=34,Name="Sara Parker", Id=3}
};
var anonData = GetAnonTypes(persons);
foreach (var item in anonData as IEnumerable) {
//use reflection to access propties
}
}
static object GetAnonTypes(IEnumerable<Person> persons) {
var query=from p in persons select new{
Id=p.Id,
Name=p.Name
};
return query;
}
}
public class Person {
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
- The compiler cannot open up your sql and determine the properties that should exist.
- Since you want the compiler to do that, I must conclude that you don't really understand anonymous typing.
Don't use LinqToSql for this. Just use your DataReader method.
