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[^]

:)