Click here to Skip to main content
Click here to Skip to main content
Alternative Tip/Trick

Tagged as

Convert DataTable to String by Extension Method

, 30 Sep 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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:
 
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[^]
 
Smile | :)

License

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

Share

About the Author


Comments and Discussions

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

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 30 Sep 2011
Article Copyright 2011 by Mohammad A Rahman
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid