Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

C# - Convert Datatable to JSON String in C#, VB.NET

May 13, 2013
Introduction:

Here I will explain how to convert asp.net datatable to JSON string in C#, VB.NET or How to get JSON string from Datatable in in C#VB.NET

Description:


To convert datatable to JSON string we need to write the code like as shown below

C# Code


// This method is used to convert datatable to json string
public string ConvertDataTabletoString()
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Initial Catalog=master;Integrated Security=true"))
{
using (SqlCommand cmd = new SqlCommand("select title=City,lat=latitude,lng=longitude,description from LocationDetails", con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return serializer.Serialize(rows);
}
}
}
VB.NET Code


' This method is used to convert datatable to json string
Public Function ConvertDataTabletoString() As String
Dim dt As New DataTable()
Using con As New SqlConnection("Data Source=SureshDasari;Initial Catalog=master;Integrated Security=true")
Using cmd As New SqlCommand("select title=City,lat=latitude,lng=longitude,description from LocationDetails", con)
con.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
Dim serializer As New System.Web.Script.Serialization.JavaScriptSerializer()
Dim rows As New List(Of Dictionary(Of String, Object))()
Dim row As Dictionary(Of String, Object)
For Each dr As DataRow In dt.Rows
row = New Dictionary(Of String, Object)()
For Each col As DataColumn In dt.Columns
row.Add(col.ColumnName, dr(col))
Next
rows.Add(row)
Next
Return serializer.Serialize(rows)
End Using
End Using
End Function

If want to see above code in complete example check below link



If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

10 comments :

Anonymous said...

Hello, My name is Leo.
I am new in WCF. Can you give me Example :
For Service :
- Convert Data table to Json and publish in IIS using database.

For client :
- Consume JSON and put in the Gridview or Dropdown.
Can you write in VB cause i am not familiar in C#.
you can send the .zip examples to my email leowidodo037@gmail.com.
Thanks..

Anonymous said...

Hi,
Thanks for the great code. I am trying to insert a WHERE clause in my SQL query, but it throws an error:Data type mismatch in criteria expression.
Any simple fixes or work arounds?

Thanks again.

Anonymous said...

Hello,
Thank you very much for the code. I tried your code but I am getting the following error in this line

return serializer.Serialize(rows);

error says: "Cannot Implicitly convert type string to string[]"

Sagar Rao said...

Worked for me!

Anonymous said...

Gracias por la ayuda, serializacion rapida y transparente.
Atte pakirri - Peru

Unknown said...

My json string is not perfect. At every string value it insert \. Like \"CountryId\": 12.
How can i remove it?

Anonymous said...

Is it converting special characters also? Please clear my doubt.

Unknown said...

it will be very help full.

Unknown said...

I try but screen white. please, help me

Unknown said...

Thank you very much it worked for me

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.