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.

Advertisements

About Jinish Bhardwaj
Jinish works as a Contractor @ Microsoft, Hyderabad and has more than 8 years of experience in building Web, Windows and Smart Client application for clients across the globe. Apart from that Jinish is also a Microsoft Certified Professional and a Certified SCRUM Master

5 Responses to C# dynamic and ExpandoObject to fetch data from Sql Server

  1. Raj Kumar says:

    Thanks Jinish !!!
    But i hv problem while binding asp .net gridview. My code is below:

    IEnumerable result = GetData();

    I am getting dynamic list from my database. Then i am trying to bind grid view

    GridView1.DataSource = null;
    GridView1.DataSource = result.ToList();
    GridView1.DataBind();

    but cannot bind grid view.
    My aspx code below:

    Please share your valuable feedback…..;

    • Raj Kumar says:

      Sorry below code is wrong:
      IEnumerable result = GetData();

      I am fetching records as below:
      IEnumerable result = GetData();

      Thanks !!!

  2. Hi Raj, did you try and see that your “result” variable is populated with data before you bind it? and what format is the data in? If so, and assuming you’re using the GetDynamicData function as it is without modification, it will return an enumerable of dynamic object that cast internally as a IDictionary

    So essentially, the GetData(string tablename) function returns a IEnumerable<IDictionary>. So casting it to a list and trying to set the datasource of your gridview to it wouldn’t work. It should not thrown any exception, but it just wouldn’t find the binding context properties to bind to.

    So, each item in your “result” is an IDictionary. You can easily set the datasource of your GridView to an IDictionary.

    • Raj Kumar says:

      Thanks Jinish for ur reply!!!
      Every thing working fine as you explain above and I am dynamic list and stored in “result” variable. Like below

      IEnumerable result = GetData();

      I am using your GetDynamicData() and getting full records from my database. The rows in collection of System.Dynamic.ExpendoObject. After get the records into IEnumerable result variable. I used below code:

      var expandoDic = (IDictionary)result;

      But getting Below error while casting :

      Unable to cast object of type ‘d__0’ to type ‘System.Collections.Generic.IDictionary`2[System.String,System.Object]’

      Thanks !!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: