Serialize DataTable to JSON with just few lines of code

Generate JSON string from DataTable in C#

Although JSON is widely used as a format for sharing the data, there are still existing solutions or modules that still rely on ADO and DataTables. Even today it very common to see some piece of code which deals with DataTable or DataReader for the performance reasons.

In the end if any of the ADO objects needs to be exposed as a JSON string you have to write your own serialization code.

The thing is you can simply use the existing serializers such as

• Json.net
• JavaScriptSerializer class
• fastJson
• jayRock

The only requirements for these serializers is that their serialization methods accept objects which properties hod the values instead of DataTables or DataReaders. In this case you can can just simply convert the table to a simple POCO objects.

Some time ago I wrote the simple class at https://github.com/dejanstojanovic/DataBind where you can simply get the POCO object instance from the ADO objects. The condition is that you need to have a model class to which you want to bind to.

Since you only need JSON string in the end, you do not really care for the model class. It is only one additional step which you would use only for transforming the DataTable to model as an input for serializer. If this is your case, dynamics would work well.

The only thing is that dynamics initially o not have any property, so you would have to generate properties and fill the values on the runtime while reading the ADO object. Although it sounds like nuclear science, it is actually pretty simple by using ExpandoObject class.

The following is a method that transforms DataRow single instance to a dynamic object instance:

public static dynamic BindDynamic(DataRow dataRow)
        {
            dynamic result = null;
            if (dataRow != null)
            {
                result = new ExpandoObject();
                var resultDictionary = (IDictionary<string, object>)result;
                foreach (DataColumn column in dataRow.Table.Columns)
                {
                    var dataValue = dataRow[column.ColumnName];
                    resultDictionary.Add(column.ColumnName, DBNull.Value.Equals(dataValue) ? null : dataValue);
                }
            }
            return result;
        }
    

Since DataTable is a collection of multiple DataRow objects and DataSet is a collection of DataTable objects, it really simple to reuse the DataRow binding methods to DataTable and DataSet to get multiple dynamic object models from them.

        public static IEnumerable<dynamic> BindDynamics(DataTable dataTable)
        {
            foreach (DataRow row in dataTable.Rows)
            {
                yield return BindDynamic(row);
            }
        }

        public static IEnumerable<dynamic> BindDynamics(DataSet dataSet, int tableIndex = 0)
        {
            return BindDynamics(dataSet.Tables[tableIndex]);
        }

        public static IEnumerable<dynamic> BindDynamics(DataSet dataSet, string tableName)
        {
            return BindDynamics(dataSet.Tables[tableName]);
        }
    

When it comes to DataReader class which is still commonly used for the performance critical code, reusing of the methods above is nit possible, but we can simply write one which is based on the same logic, with the difference of iterating through the reader instead of the DataTable

        public static IEnumerable<dynamic> BindDynamics(SqlDataReader dataReader)
        {
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    dynamic item = new ExpandoObject();
                    for (int columnIndex = 0; columnIndex < dataReader.FieldCount; columnIndex  )
                    {
                        var resultDictionary = (IDictionary<string, object>)item;

                        var dataValue = dataReader.GetValue(columnIndex);
                        resultDictionary.Add(dataReader.GetName(columnIndex), DBNull.Value.Equals(dataValue) ? null : dataValue);
                    }
                  yield return item;
                }
            }
        }
    

To make a simple test I used the following code which generates collection of dynamics from a DataTable

 public void TestDynamic()
        {
            DataTable table = new DataTable();
            table.Columns.Add("Guid", typeof(string));
            table.Columns.Add("Time", typeof(DateTime));

            var row = table.NewRow();
            row["Guid"] = Guid.NewGuid().ToString();
            row["Time"] = DateTime.Now;

            var obj = DataBinding.DataBinder.BindDynamic(row);

            string json = JsonConvert.SerializeObject(obj, Formatting.Indented);
        }
    

References

• https://github.com/dejanstojanovic/DataBind

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.

About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN