Click here to Skip to main content
15,867,835 members
Articles / Programming Languages / C#
Alternative
Tip/Trick

Convert DataTable to String by Extension Method

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
30 Sep 2011CPOL 43.7K   6   3
Following might be another way to do the job:public static string ConvertDataTableToString(this DataTable dt){ StringBuilder stringBuilder = new StringBuilder(); dt.Rows.Cast().ToList().ForEach(dataRow => { ...
Following might be another way to do the job:

C#
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:

C#
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:
C#
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[^]


:)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Australia Australia

Comments and Discussions

 
QuestionConvert DataTable to String by Extension Method Pin
Mrkraju1-Aug-14 2:01
Mrkraju1-Aug-14 2:01 
GeneralYes, This is the way to convert data table to string but if ... Pin
Ram Balak Sharma28-Sep-11 22:29
Ram Balak Sharma28-Sep-11 22:29 
GeneralRe: Ram, yes that's why I left comment as "..formatting stuff ha... Pin
Mohammad A Rahman29-Sep-11 0:57
Mohammad A Rahman29-Sep-11 0:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.