Converting Data Table / Dataset Into JSON String

JSON (Java Script Object Notation), is a light weight, easily understandable to read and write string. It is also easily parse-able by machine.

JSON is introduced on two structues

A collection (key/value pair)

And ordered list of values.

I have not covered this topic in detail. Detailed analysis is stated on http://www.json.org/.

I am presenting a helper function (in C#) for developers for fast parsing on datatable / dataset into JSON String, and access it on client-side.

public static string GetJSONString(DataTable Dt)

{

string[] StrDc = new string[Dt.Columns.Count];

string HeadStr = string.Empty;

for (int i = 0; i < Dt.Columns.Count; i++)

{

StrDc[i] = Dt.Columns[i].Caption;

HeadStr +=
"\"" + StrDc[i] + "\" : \"" + StrDc[i] + i.ToString() + "¾" + "\",";

}

HeadStr = HeadStr.Substring(0, HeadStr.Length - 1);

StringBuilder Sb = new StringBuilder();

Sb.Append("{\"" + Dt.TableName + "\" : [");

for (int i = 0; i < Dt.Rows.Count; i++)

{

string TempStr = HeadStr;

Sb.Append("{");

for (int j = 0; j < Dt.Columns.Count; j++)

{

TempStr = TempStr.Replace(Dt.Columns[j] + j.ToString() + "¾", Dt.Rows[i][j].ToString());

}

Sb.Append(TempStr +
"},");

}

Sb =
new StringBuilder(Sb.ToString().Substring(0, Sb.ToString().Length - 1));

Sb.Append("]}");

return Sb.ToString();

}

Here, Dt is the datatable, and it returns JSON formatted string.

For detailed porcedure on how to access this string on client side, please refer to this link Exposing Webservices to Client-Side because I don't like to do task repetition.

Please donot forget to convert to JSON string to JSON Object using

var JObject = eval('(' + JSONString + ');');

in Javascript JObject have all characteristics of JSON object, through which you can Use JObject by iterrating or what so ever.

e.g., you can use your JObject as

for(var i = 0; i < JObject .Employees.length; i++)

{

var val1 = JObject.Employees[i].EmployeeID;

var val2 = JObject.Employees[i].NationalIDNumber;

var val3 = JObject.Employees[i].Title;

var val4 = JObject.Employees[i].BirthDate;

var val5 = JObject .Employees[i].HireDate ;

}

Please note that I am querieng data from AdventurWorksDB SQL Sample Database (Table: Employee).

I hope this article will be helpful for you.

Any Questions / Queries ??

Regards,

Naveed Akhtar

kick it on DotNetKicks.com

Published Tuesday, July 8, 2008 7:04 PM by NavaidAkhtar

Comments

# re: Converting Data Table / Dataset Into JSON String

Wednesday, July 9, 2008 4:10 PM by RichardD

This looks like a very fragile method. If you're using .NET 3.5 (or the MS AJAX 1.0 library), you already have the JavaScriptSerializer which does most of the work.

Unfortunately, the DataTable and DataSet objects aren't suitable for JSON serialization directly; you'll need to convert them first. To duplicate your results, you need to convert the DataSet to an IDictionary<string, IEnumerable<IDictionary<string, object>>>.

With LINQ, the code is fairly simple:

static object RowsToDictionary(this DataTable table)

{

   var columns = table.Columns.Cast<DataColumn>().ToArray();

   return table.Rows.Cast<DataRow>().Select(r => columns.ToDictionary(c => c.ColumnName, c => r[c]));

}

static Dictionary<string, object> ToDictionary(this DataTable table)

{

   return new Dictionary<string, object>

   {

       { table.TableName, table.RowsToDictionary() }

   };

}

static Dictionary<string, object> ToDictionary(this DataSet data)

{

   return data.Tables.Cast<DataTable>().ToDictionary(t => t.TableName, t => t.RowsToDictionary());

}

public static string GetJSONString(DataTable table)

{

   JavaScriptSerializer serializer = new JavaScriptSerializer();

   return serializer.Serialize(table.ToDictionary());

}

public static string GetJSONString(DataSet data)

{

   JavaScriptSerializer serializer = new JavaScriptSerializer();

   return serializer.Serialize(data.ToDictionary());

}

# re: Converting Data Table / Dataset Into JSON String

Thursday, July 10, 2008 6:56 AM by NavaidAkhtar

Hi RichardD

Thanks for your feedback and a wonderful suggestions, but your answer also contradicts your answer beceause, you stated that your solution works on .Net 3.5 or above then .Net 2.0. But My solution is for developers having .Net 2.0 or may be prior to that.

LINQ, JSonSerializer, are not supporting in Net 2.0 or prior versions.

# re: Converting Data Table / Dataset Into JSON String

Tuesday, November 25, 2008 10:58 PM by Ron

>> This looks like a very fragile method

RichardD, what I think is fragile is whatever the latest Microsoft .NET fad is. Apparently that's LINQ now, so I guess you think that everyone should keep up with the latest thing. I personally prefer timeless and simple solutions like the one Navid posted here. I think it's rude for you to use a term like "fragile" to describe someone else's code. Who do you think you are, Bill Gates, Jr.?

# re: Converting Data Table / Dataset Into JSON String

Sunday, January 11, 2009 9:44 PM by Danny117

Just what I was looking for. Bookmarked! Simple expanation of how to deserialize table.

# re: Converting Data Table / Dataset Into JSON String

Saturday, April 4, 2009 6:08 PM by NavaidAkhtar

CRajkumar.ece, Please see your table data, as it contains some illegle chracters like " ' ", try these by using the technique of javascript error free chracter replacement. I will work fine.

If you still have an issue, just shoot me a mail including the code, the resuly set data. I will try to solve your issue.

# re: Converting Data Table / Dataset Into JSON String

Saturday, April 4, 2009 6:12 PM by NavaidAkhtar

Danny117, Basically, I am using just a simple technique to convert the dataset to JSON string.

Actually on the code behind i am serializing the data into a format, that is known as JSON format, and with the single line on client side (Javascript)

var JObject = eval('(' + JSONString + ')');

I am deserializing to a JSon Object and then Iterating over the object.

# re: Converting Data Table / Dataset Into JSON String

Wednesday, July 1, 2009 9:01 AM by Ashfaq

Thanks for your excellent post, I was looking for something like that too.

Thanks for your dt to json conversion function. It saved lot of time and will help me to make better web form. Since I use codeSmith tool. This function is gonna really help me a lot.

Thank you again:)

Ashfaq

ziasif@hotmail.com

MCP

# re: Converting Data Table / Dataset Into JSON String

Sunday, July 12, 2009 6:22 AM by Guss

   Public Shared Function GetJSONString(ByVal Dt As DataTable) As String

       Dim StrDc As String() = New String(Dt.Columns.Count - 1) {}

       Dim HeadStr As String = String.Empty

       For i As Integer = 0 To Dt.Columns.Count - 1

           StrDc(i) = Dt.Columns(i).Caption

           HeadStr += ("""" & StrDc(i) & """ : """) + StrDc(i) + i.ToString() & "¾" & ""","

       Next

       HeadStr = HeadStr.Substring(0, HeadStr.Length - 1)

       Dim Sb As New StringBuilder()

       Sb.Append("{""" & Dt.TableName & """ : [")

       For i As Integer = 0 To Dt.Rows.Count - 1

           Dim TempStr As String = HeadStr

           Sb.Append("{")

           For j As Integer = 0 To Dt.Columns.Count - 1

               TempStr = TempStr.Replace(Dt.Columns(j) + j.ToString() & "¾", Dt.Rows(i)(j).ToString())

           Next

           Sb.Append(TempStr & "},")

       Next

       Sb = New StringBuilder(Sb.ToString().Substring(0, Sb.ToString().Length - 1))

       Sb.Append("]};")

       Return Sb.ToString()

   End Function

THIS PART IS NOT VALID IN VB

---Dt.Columns(j) + j.ToString() ----

TempStr = TempStr.Replace(Dt.Columns(j) + j.ToString() & "¾", Dt.Rows(i)(j).ToString())

Can you help

# re: Converting Data Table / Dataset Into JSON String

Wednesday, July 15, 2009 10:34 AM by Fabio

It's very good.

Tank's.

# re: Converting Data Table / Dataset Into JSON String

Friday, May 7, 2010 1:02 AM by Rupesh Kumar Tiwari

Hi Naveed,

Thanks for writing such a great article. It cleared the concept of json object. Now i can make json object by my ownself. I am also working with vs2005.

# re: Converting Data Table / Dataset Into JSON String

Tuesday, July 27, 2010 3:49 AM by NavaidAkhtar

Dear Guss,

TempStr = TempStr.Replace(Dt.Columns(j) + j.ToString() & "¾", Dt.Rows(i)(j).ToString())

for VB.Net you have to write like that

TempStr = TempStr.Replace(Dt.Columns(j) & j.ToString() & "¾", Dt.Rows(i)(j).ToString())

# re: Converting Data Table / Dataset Into JSON String

Sunday, September 5, 2010 3:47 AM by Abidali

Thank Naveed,

Keep it up, Good Article

# re: Converting Data Table / Dataset Into JSON String

Tuesday, January 18, 2011 1:53 PM by Greg

Thanks for the code Naveed. I recently moved to PHP after 5 years of .NET development...now I know why :s

PHP: json_encode($any_object) <-- Nice and easy

.NET 3.5+ <-- complicated, poorly documents and even more poorly supported by their own objects like DataSets and DataTables.

Thanks again!

# re: Converting Data Table / Dataset Into JSON String

Wednesday, April 20, 2011 2:56 AM by Mohamed Riswan

Great article.

# re: Converting Data Table / Dataset Into JSON String

Saturday, July 16, 2011 7:38 AM by Andy

method worked great for me. saved me a lot of time. thanks Naveed !

# re: Converting Data Table / Dataset Into JSON String

Friday, July 29, 2011 4:01 PM by Abbas

Hi Naveed awesome article, i have a datatable which is getting filled from stored procedure. can you tell me how shall i get the records in Javascript. you have used JSObject.Employee.length.. in my case what shall i use. as i dont have any table.

# re: Converting Data Table / Dataset Into JSON String

Monday, September 26, 2011 1:20 AM by jai

Hi,

I am new to asp .net.

what is "3/4" represents??

# re: Converting Data Table / Dataset Into JSON String

Friday, April 13, 2012 6:59 AM by NavaidAkhtar

@jai, '3/4' means any character you can put there. that might not come in the string as this is used for separation of strings.

# re: Converting Data Table / Dataset Into JSON String

Friday, April 13, 2012 7:06 AM by NavaidAkhtar

@M. Salah, I am assuming there is some data in the table and you can call this function after checking whether the table is not null and has row count > 0.

# re: Converting Data Table / Dataset Into JSON String

Friday, April 13, 2012 12:29 PM by prysson

I just wanted to say that I love this article and I'm actually using .net 4.0  There are a lot of converters out there that do a lot of fancy things like indent formatting for easy readability etc. But frankly I found those alternatives annoying. They may be nice if you are passing more complex data structures like DataSets forward... but for a simple datatable I find the results they produce to be bloated with unnecessary garbage. Not to mention that the documentation is usually shoddy with poor examples that often times wont even build. What I really like about this is that its very clean, elegant and simple.  The best solutions usually are. Very nicely done in my opinion

# re: Converting Data Table / Dataset Into JSON String

Tuesday, July 31, 2012 8:55 AM by sanjaya verma

Its a great article......

thanks

# re: Converting Data Table / Dataset Into JSON String

Tuesday, January 8, 2013 12:43 PM by Baines

I will right away grab your rss feed because I can’t to get

your email subscription hyperlink or e-newsletter service.

Do you have any other? Please permit me understand

in order that I could subscribe. Thanks.

# re: Converting Data Table / Dataset Into JSON String

Friday, January 11, 2013 7:26 AM by Schenk

It was something of great contentment getting to your site

last night. I got here right now hoping to

uncover something new. And I was not disappointed.

Your ideas upon new techniques on this topic were informative and a first-rate help to us.

Thank you for having time to create these things plus for sharing your thoughts.

# re: Converting Data Table / Dataset Into JSON String

Sunday, April 21, 2013 3:20 AM by paletipati_msr

Great ..! Naveed Akhtar, such a helpful discussion on web making programmer cool.......

# re: Converting Data Table / Dataset Into JSON String

Thursday, July 11, 2013 9:20 AM by Anchal

What I have to change in above code if I pass DataSet to function?

Thanks in advance..

# re: Converting Data Table / Dataset Into JSON String

Monday, November 11, 2013 2:33 AM by Rian

thank you!

Leave a Comment

(required) 
(required) 
(optional)
(required)