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

//C#

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)){

con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);

//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]);
}
rows.Add(row);
}
return serializer.Serialize(rows);
}
}
}

‘VB.NET
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)

'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))
Next
rows.Add(row)
Next
Return serializer.Serialize(rows)
End Using
End Using
End Function

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s