Convert DataTable to String by Extension Method

Following might be another way to do the job:

public static string ConvertDataTableToString(this DataTable dt)
{
    StringBuilder stringBuilder = new StringBuilder();
    dt.Rows.Cast<DataRow>().ToList().ForEach(dataRow =>
    {
        dt.Columns.Cast<DataColumn>().ToList().ForEach(column =>
        {
            stringBuilder.AppendFormat("{0}:{1} ", column.ColumnName, dataRow[column]);
        });
        stringBuilder.Append(Environment.NewLine);
    });
    return stringBuilder.ToString();
}

Note: For simplicity, all the formatting stuff has not been included.

Though the above method does not format as JSon, I created another extension to the job. So the following method will serialize DataTable to JSon formatted string using .NET framework class named JavaScriptSerializer which will do the job for us.

We have the following data stored into a DataTable:

AppID | AppName
--------------------------------------------
01 | Microsoft Word
02 | Microsoft Excel
03 | Microsoft Access
04 | Microsoft OneNote
05 | Microsoft Visual Studio 2003
06 | Microsoft Visual Studio 2005
07 | Microsoft Visual Studio 2008
08 | Microsoft Visual Studio 2010
09 | Microsoft Visual Studio 2010 Sp1
10 | .Net Reflector
11 | VS DocMan
12 | Etc
13 | Etc Etc
14 | Etc Etc Etc
--------------------------------------------

We would like to serialize it using JavaScriptSerializer class, the code block is:

public static class Extensions
{
    public static string SerializeToJSon(this DataTable dt)
    {
        JavaScriptSerializer ser = new JavaScriptSerializer();
        List<Dictionary<string, object>> dataRows = new List<Dictionary<string, object>>();
        dt.Rows.Cast<DataRow>().ToList().ForEach(dataRow =>
        {
            var row = new Dictionary<string, object>();
            dt.Columns.Cast<DataColumn>().ToList().ForEach(column =>
            {
                row.Add(column.ColumnName, dataRow[column]);
            });
            dataRows.Add(row);
        });
        return ser.Serialize(dataRows);
    }
}

Usage:

protected void btnTest_Click(object sender, EventArgs e)
{
    DataSet dataSet = new DataSet();
    using (SqlConnection connection = new SqlConnection(@"CONNECTION STRING"))
    {
        using (SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT * FROM Applications", connection))
        {
            adapter.Fill(dataSet);
        }
        var result = dataSet.Tables[0].SerializeToJSon();
    }
}

So after running the code, the output will be as below:

[{"AppID":1,"AppName":"Microsoft Word"},
{"AppID":2,"AppName":"Microsoft Excel"},
{"AppID":3,"AppName":"Microsoft Access"},
{"AppID":4,"AppName":"Microsoft OneNote"},
{"AppID":5,"AppName":"Microsoft Visual Studio 2003"},
{"AppID":6,"AppName":"Microsoft Visual Studio 2005"},
{"AppID":7,"AppName":"Microsoft Visual Studio 2008"},
{"AppID":8,"AppName":"Microsoft Visual Studio 2010"},
{"AppID":9,"AppName":"Microsoft Visual Studio 2010 Sp1"},
{"AppID":10,"AppName":".Net Reflector"},
{"AppID":11,"AppName":"VS DocMan"},
{"AppID":12,"AppName":"Etc"},
{"AppID":13,"AppName":"Etc Etc"},
{"AppID":14,"AppName":"Etc Etc Etc"}]

In addition, it is also possible to Serialize by Custom JavaScriptConverter.

References:

1. http://msdn.microsoft.com/en-us/library/system.web.script.serialization.javascriptserializer.aspx[^]
   
2. http://msdn.microsoft.com/en-us/library/system.web.script.serialization.javascriptconverter.aspx[^]
   

:)