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

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:

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