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

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

7 comments :

ocando said...

also can be used easier way:

Public Function TableToJson(ByVal tbl As DataTable) As String
Dim ret As String = ""
For Each r As DataRow In tbl.Rows
If ret.Length > 0 Then
ret += vbLf
End If
For i As Integer = 0 To tbl.Columns.Count - 1
If i > 0 Then
ret += vbTab
End If
ret += If(r(i) Is Nothing, "", r(i).ToString())
Next
Next
Return ret
End Function

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

KsheeraSagar Rao said...

Worked for me!

Anonymous said...

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

Dhruv Vaishnav said...

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

Give your Valuable Comments

Other Related Posts

© 2010-2012 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.