c# - How to convert a data reader to dynamic query results

Question

I have a View that typically gets query results from a WebMatrix Query (IEnumerable<dynamic> data type), and displays the results in a table:

@model MySite.Models.Entity
@foreach(var row in Model.Data)
{
    <tr>
        @foreach (var column in row.Columns)
        {
            <td>@column<span>:</span> @row[column]</td>
        }
    </tr>
}

Here's my model where I query the database:

public class Entity
{
    public dynamic Data {get; set; }
    public Entity(String table)
    {
        if (table == "User" || table == "Group)
        {
            WebMatrix.Data.Database db = new WebMatrix.Data.Database();
            db.Open(ConString);
            Data = db.Query("SELECT * FROM " + table);
        }
        else
        {
            using (OdbcConnection con = ne4w OdbcConnection(ConString))
            {
                OdbcCommand com = new OdbcCommand("Select * From " + table);
                command.CommandType = System.Data.CommandType.Text;
                connection.Open();
                OdbcDataReader reader = command.ExecuteReader();

Here's all the different things I've tried from reading various other posts:

                // Atempt 1
                Data = reader;
                // Error in view, 'Invalid attempt to call FieldCount when reader is closed' (on 'var row `in` Model.Data')

                // Atempt 2
                Data = reader.Cast<dynamic>;
                // Error: 'Cannot convert method group "Cast" to non-delegate type "dynamic". Did you intend to invoke the method?

                // Atempt 3
                Data = reader.Cast<IEnumerable<dynamic>>;
                // Error same as Atempt 2

                // Atempt 4
                Data = reader.Cast<IEnumerable<string>>;
                // Error same as Atempt 2
            }
        }
    }
}

I'm looking for the best way to get the reader object to a IEnumerable<dynamic> object.

Please note this is a simplified example, and while the reason for the two query types is not obvious, they are necessary in my code.

Answer 1

You're missing basic C# syntax.

Data = reader;
// You cant do this. You have to loop the reader to get the values from it.
// If you simply assign reader object itself as the data you wont be 
// able to get data once the reader or connection is closed. 
// The reader is typically closed in the method.

Data = reader.Cast<dynamic>;
// You should call the Cast method. And preferably execute the resulting query. 
// As of now you're merely assigning method reference to a variable
// which is not what you want. 
// Also bear in mind that, as I said before there's no real benefit in casting to dynamic

Data = reader.Cast<IEnumerable<dynamic>>;
// Cast method itself returns an IEnumerable. 
// You dont have to cast individual rows to IEnumerable

Data = reader.Cast<IEnumerable<string>>;
// Meaningless I believe. 
// The data you get from database is not always strings

The major mistake you make is not calling the method. This is what you want:

Data = reader.Cast<IDataRecord>().ToList();
                               ^^ // notice the opening and closing parentheses

---

You could go about this a number of ways depending on what is easier to process (say, to display in front-end).

1. Return data records.

   public IEnumerable<IDataRecord> SelectDataRecord()
   {
       ....
   
       using (var reader = cmd.ExecuteReader())
           foreach (IDataRecord record in reader as IEnumerable)
               yield return record; //yield return to keep the reader open
   }
   

2. Return ExpandoObjects. Perhaps this is what you wanted?

   public IEnumerable<dynamic> SelectDynamic()
   {
       ....
   
       using (var reader = cmd.ExecuteReader())
       {
           var names = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
           foreach (IDataRecord record in reader as IEnumerable)
           {
               var expando = new ExpandoObject() as IDictionary<string, object>;
               foreach (var name in names)
                   expando[name] = record[name];
   
               yield return expando;
           }
       }
   }
   

3. Return sequence of property bag

   public IEnumerable<Dictionary<string, object>> SelectDictionary()
   {
       ....
   
       using (var reader = cmd.ExecuteReader())
       {
           var names = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
           foreach (IDataRecord record in reader as IEnumerable)
               yield return names.ToDictionary(n => n, n => record[n]);
       }
   }
   

4. Return sequence of plain object array

   public IEnumerable<List<object>> SelectObjectArray()
   {
       ....
   
       using (var reader = cmd.ExecuteReader())
       {
           var indices = Enumerable.Range(0, reader.FieldCount).ToList();
           foreach (IDataRecord record in reader as IEnumerable)
               yield return indices.Select(i => record[i]).ToList();
       }
   }
   

5. Return data rows

   public IEnumerable<DataRow> SelectDataRow()
   {
       ....
   
       using (var reader = cmd.ExecuteReader())
       {
           var table = new DataTable();
           table.BeginLoadData();
           table.Load(reader);
           table.EndLoadData();
           return table.AsEnumerable(); // in assembly: System.Data.DataSetExtensions
       }
   }
   

6. Last but not least, if it helps, you can return a strongly-typed sequence without any manual plumbing. You can use expression trees to compile code at run-time. See this for e.g.