Full working Console App code pasted below. But the 2 main methods you need are as follows.
For this code to work, you will have to do the following in your Project.
- Add the
JSON.Net Nuget Package to the Project.
Add a reference to System.Web.Extensions (if you get a compile error in the line where System.Web.Script.Serialization.JavaScriptSerializer is being referenced in the GetJson method.
/// <summary>
/// Returns Json representation of Generic class with only matching properties from the DataTable (passed as parameter)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <returns></returns>
public static string GetJsonFromDataTable<T>(DataTable dt) where T : new()
{
string json = GetJson(dt);
return JsonConvert.SerializeObject(JsonConvert.DeserializeObject<List<T>>(json));
}
/// <summary>
/// Returns a JSON string for entire DataTable (passed as parameter)
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static string GetJson(DataTable dt)
{
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = (from DataRow dr in dt.Rows select dt.Columns.Cast<DataColumn>().ToDictionary(col => col.ColumnName.Trim(), col => dr[col])).ToList();
return serializer.Serialize(rows);
}
Fully working Console App code.
Create a new console App, and replace everything in the Program.cs with this code. Also add JSON.Net to the Console App Project and add the references to System.Web.Extensions.
namespace DataTable2Json
{
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
public class Patient
{
public string FullName { get; set; }
public string PatientID { get; set; }
public int NumberOfIllnesses { get; set; }
public DateTime DateAdmitted { get; set; }
}
public class PatientDrug
{
public string Patient { get; set; }
public string Drug { get; set; }
}
internal class Program
{
private static void Main(string[] args)
{
DataTable patientDrugDataTable = GetPatientDrugTable();
DataTable patientDataTable = GetPatientTable();
string patientDrugJson = GetJsonFromDataTable<PatientDrug>(patientDrugDataTable);
Console.WriteLine("Json for PatientDrug:\n{0}",patientDrugJson);
string patientJson = GetJsonFromDataTable<Patient>(patientDataTable);
Console.WriteLine("\nJson for Patient:\n{0}", patientJson);
Console.WriteLine("\n\nPress a key to Exit...");
Console.ReadKey();
}
private static DataTable GetPatientDrugTable()
{
//
// Here we create a DataTable with four columns.
//
DataTable table = new DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Columns.Add("Drug", typeof(string));
table.Columns.Add("Patient", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
//
// Here we add five DataRows.
//
table.Rows.Add(25, "Indocin", "David", DateTime.Now);
table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
return table;
}
private static DataTable GetPatientTable()
{
//
// Here we create a DataTable with four columns.
//
DataTable table = new DataTable();
table.Columns.Add("NumberOfIllnesses", typeof(int));
table.Columns.Add("PatientID", typeof(string));
table.Columns.Add("FullName", typeof(string));
table.Columns.Add("DateAdmitted", typeof(DateTime));
table.Columns.Add("StreetAddress1", typeof(string));
table.Columns.Add("City", typeof(string));
table.Columns.Add("State", typeof(string));
//
// Here we add five DataRows.
//
table.Rows.Add(2, "PAT-00001", "David", DateTime.Now, "1 Mill Ln", "Schenectady", "NY");
table.Rows.Add(1, "PAT-00002", "Sam", DateTime.Now, "1915 Boylston Steet", "Boston", "MA");
table.Rows.Add(3, "PAT-00003", "Christoff", DateTime.Now, "15 Polk Steet", "San Francisco", "CA");
table.Rows.Add(4, "PAT-00004", "Janet", DateTime.Now, "10 Waverly St", "Los Angeles", "CA");
table.Rows.Add(5, "PAT-00005", "Melanie", DateTime.Now, "50 Kapaa St", "Kailua", "HI");
return table;
}
/// <summary>
/// Returns Json representation of Generic class with only matching properties from the DataTable (passed as parameter)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <returns></returns>
public static string GetJsonFromDataTable<T>(DataTable dt) where T : new()
{
string json = GetJson(dt);
return JsonConvert.SerializeObject(JsonConvert.DeserializeObject<List<T>>(json));
}
/// <summary>
/// Returns a JSON string for entire DataTable (passed as parameter)
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static string GetJson(DataTable dt)
{
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = (from DataRow dr in dt.Rows select dt.Columns.Cast<DataColumn>().ToDictionary(col => col.ColumnName.Trim(), col => dr[col])).ToList();
return serializer.Serialize(rows);
}
}
}
Explanation of Code:
Notice I have 2 classes, Patient and PatientDrug.
I wrote helper methods to return data tables for both classes, that have additional columns.
Then the following 2 lines get the JSON for the class representation for Patient and PatientDrug respectively, while ignoring the additional data columns in DataTable that don't match names.
string patientDrugJson = GetJsonFromDataTable<PatientDrug>(patientDrugDataTable);
string patientJson = GetJsonFromDataTable<Patient>(patientDataTable);
Output in Console Windows (the json strings)
