C# dynamic and ExpandoObject to fetch data from Sql Server

A little while ago I was working on a simple application that would let the user define or upload an HTML template and then upload the data that would map to the template. The data could be csv or an excel file. The user could define multiple HTML templates and correspondingly upload data files for each template. The HTML templates and the data files were to be stored in the application for anytime use.

Obviously I had to figure out a mechanism to read the Excel sheet’s columns and corresponding data and get that into an SQL table. That was simple enough. Using the LinqToExcel library  I could read the excel data as simply as:

var excelFactory = new ExcelQueryFactory(fileName);                
var worksheet = excelFactory.GetWorksheetNames();
var columnNames = excelFactory.GetColumnNames(worksheet.FirstOrDefault());
                
var data = (from row in excelFactory.Worksheet(0)
            select row).ToList();

And then just simply store that list into the database.
But the issue was when I was reading that data back from the SQL database, I didn’t have any POCO classes to map that data to for my application to understand. So, this is what ended up doing in the end:

A simple function to read data from the database provided the table name

public IEnumerable<dynamic> GetData(string tableName)
{
    using (var connection = new SqlConnection(this.GetConnectionString()))
    {
        var commandText = "select * from " + tableName;
        using (var command = new SqlCommand(commandText, connection))
        {
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return GetDynamicData(reader);
                }
            }
            connection.Close();
        }
    }
}

private dynamic GetDynamicData(SqlDataReader reader)
{
    var expandoObject = new ExpandoObject() as IDictionary<string, object>;
    for (int i = 0; i < reader.FieldCount; i++)
    {
        expandoObject.Add(reader.GetName(i), reader[i]);
    }
    return expandoObject;
}

And then just cast each item in the returned

IEnumerable<dynamic>

to an

IDictionary<string, object>

and bind that to my view or do any processing with it.