Convert Datatable to JSON String in C#, VB.NET with Column Names (Dictionary Object)


public string ConverDataTableToJSON()
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)){

SqlDataAdapter da = new SqlDataAdapter(cmd);

//convert the filled DT into JSON
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();

List<Dictionary> rows = new List<Dictionary>();
Dictionary row;
foreach (DataRow dr in dt.Rows){
row = new Dictionary();
foreach (DataColumn col in dt.Columns){
row.Add(col.ColumnName, dr[col]);
return serializer.Serialize(rows);

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)
Dim da As New SqlDataAdapter(cmd)

'convert the filled DT into JSON
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))
Return serializer.Serialize(rows)
End Using
End Using
End Function


One thought on “Convert Datatable to JSON String in C#, VB.NET with Column Names (Dictionary Object)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s