I had a weird experience before few days, I definitely knew how to solve the problem and I am sure it is the right way to do it.
Thinking it would not be any different to map a datatable to POCO class(es) using Automapper. But it didn't turned out to be completely true. I have been playing with Automapper for couple of years now in my day-to-day work, but I stumbled a bit this time. Situationally, I was not able to conclude the problem with a working solution at that moment, thus I would take this opportunity and write about it so anyone out there who is facing same or similar problem can find their answers.
Problem
Data filled into a datatable was expected to be converted to Json object.
If the datatable contains more than one row the resultant Json should have list of Json objects for each row.
The data table will be filled from database and the columns will as below:
CREATE TABLE [dbo].[Demo]
(
[EmployeeId] INT NOT NULL PRIMARY KEY,
[EmployeeCode] NVARCHAR(50) NOT NULL,
[ManagerId] INT NOT NULL,
[FirstName] NVARCHAR(50) NOT NULL,
[LastName] NVARCHAR(50) NOT NULL,
[AverageSalePerDay] INT NOT NULL,
[TotalSales] INT NOT NULL
)
Some sample data to use:
INSERT INTO [dbo].[Demo] ([EmployeeId] ,[EmployeeCode] ,[ManagerId] ,[FirstName] ,[LastName] ,[AverageSalePerDay] ,[TotalSales])
VALUES (1 ,'ABC' ,10 ,'Alex' ,'Shrute' ,12 ,100);
INSERT INTO [dbo].[Demo] ([EmployeeId] ,[EmployeeCode] ,[ManagerId] ,[FirstName] ,[LastName] ,[AverageSalePerDay] ,[TotalSales])
VALUES (2 ,'COD' ,10 ,'Jasmine' ,'Roal' ,35 ,150);
INSERT INTO [dbo].[Demo] ([EmployeeId] ,[EmployeeCode] ,[ManagerId] ,[FirstName] ,[LastName] ,[AverageSalePerDay] ,[TotalSales])
VALUES (3 ,'JAS' ,10 ,'Jsinh' ,'Chauhan' ,25 ,500);
INSERT INTO [dbo].[Demo] ([EmployeeId] ,[EmployeeCode] ,[ManagerId] ,[FirstName] ,[LastName] ,[AverageSalePerDay] ,[TotalSales])
VALUES (4 ,'PAR' ,10 ,'Rex' ,'Maul' ,14 ,233);
INSERT INTO [dbo].[Demo] ([EmployeeId] ,[EmployeeCode] ,[ManagerId] ,[FirstName] ,[LastName] ,[AverageSalePerDay] ,[TotalSales])
VALUES (5 ,'SUR' ,10 ,'Sam' ,'Paulo' ,21 ,200);
INSERT INTO [dbo].[Demo] ([EmployeeId] ,[EmployeeCode] ,[ManagerId] ,[FirstName] ,[LastName] ,[AverageSalePerDay] ,[TotalSales])
VALUES (6 ,'RAM' ,10 ,'Remo' ,'Fernd' ,18 ,321);
Expected list of Json object is expected to be as below:
[
{
"EmployeeId": 1,
"EmployeeCode": "ABC",
"ManagerId": 10,
"EmpDetails": {
"FirstName": "Alex",
"LastName": "Shrute"
},
"Statistics": {
"AverageSalePerDay": 12,
"TotalSales": 100
}
},
{
"EmployeeId": 2,
"EmployeeCode": "COD",
"ManagerId": 10,
"EmpDetails": {
"FirstName": "Jasmine",
"LastName": "Roal"
},
"Statistics": {
"AverageSalePerDay": 35,
"TotalSales": 150
}
},
{
"EmployeeId": 3,
"EmployeeCode": "JAS",
"ManagerId": 12,
"EmpDetails": {
"FirstName": "Jsinh",
"LastName": "Chauhan"
},
"Statistics": {
"AverageSalePerDay": 25,
"TotalSales": 500
}
}
]
In above Json you can see the FirstName and LastName are part of EmpDetails child Json object. Same goes for the AverageSalePerDay and TotalSales - part of child Json object Statistics.
Question:
- How to convert datatable to Json without any manual string manipulation?
- How can I do datatable to Json convertion using Newtonsoft Json library?
- How can I create hierarchical Json object from datatable using Newtonsoft?
Solution
Step: Fill the datatable with some sample data. I am filling it via seed method for the same of this example, you can fetch and fill actual data from database using ADO.NET.
private static DataSet Seed()
{
var demoDataTable = new DataTable();
demoDataTable.TableName = "Demo";
demoDataTable.Columns.Add("EmployeeId", typeof(int));
demoDataTable.Columns.Add("EmployeeCode", typeof(string));
demoDataTable.Columns.Add("ManagerId", typeof(int));
demoDataTable.Columns.Add("FirstName", typeof(string));
demoDataTable.Columns.Add("LastName", typeof(string));
demoDataTable.Columns.Add("AverageSalePerDay", typeof(int));
demoDataTable.Columns.Add("TotalSales", typeof(int));
demoDataTable.Rows.Add(1, "ABC", 10, "Alex", "Shrute", 12, 100);
demoDataTable.Rows.Add(2, "COD", 10, "Jasmine", "Roal", 35, 150);
demoDataTable.Rows.Add(3, "JAS", 12, "Jsinh", "Chauhan", 25, 500);
demoDataTable.Rows.Add(4, "PAR", 10, "Rex", "Maul", 14, 233);
demoDataTable.Rows.Add(5, "SUR", 12, "Sam", "Paulo", 21, 200);
demoDataTable.Rows.Add(6, "RAM", 11, "Remo", "Fernd", 18, 321);
var dataSet = new DataSet();
dataSet.Tables.Add(demoDataTable);
return dataSet;
}
Step: Create POCO classes that resembles your desired Json object
Employee Class:
public class Employee
{
private string firstName;
private string lastName;
private int averageSalePerDay;
private int totalSales;
public Employee()
{
this.EmpDetails = new Details();
this.Statistics = new Aggregates();
}
public int EmployeeId { get; set; }
public string EmployeeCode { get; set; }
public int ManagerId { get; set; }
[JsonIgnore]
public string FirstName
{
get
{
return this.firstName;
}
set
{
this.firstName = value;
this.EmpDetails.FirstName = value;
}
}
[JsonIgnore]
public string LastName
{
get
{
return this.lastName;
}
set
{
this.lastName = value;
this.EmpDetails.LastName = value;
}
}
[JsonIgnore]
public int AverageSalePerDay
{
get
{
return this.averageSalePerDay;
}
set
{
this.averageSalePerDay = value;
this.Statistics.AverageSalePerDay = value;
}
}
[JsonIgnore]
public int TotalSales
{
get
{
return this.totalSales;
}
set
{
this.totalSales = value;
this.Statistics.TotalSales = value;
}
}
public Details EmpDetails { get; set; }
public Aggregates Statistics { get; set; }
}
Employee Details class:
public class Details
{
public string FirstName { get; set; }
public string LastName { get; set; }
}
Employee statistics class:
public class Aggregates
{
public int AverageSalePerDay { get; set; }
public int TotalSales { get; set; }
}
Step: Create mapping configuration for your datatable to list of employees and fill the list from datatable using AutoMapper
Mapper.CreateMap<IDataReader, List<Employee>>();
var result = Mapper.Map<IDataReader, List<Employee>>(dataSet.CreateDataReader());
Step: Serialize list of employees filled from datatable using Automapper to list of Json object using Newtonsoft Json library.
var resultJson = JsonConvert.SerializeObject(result, Formatting.Indented);
Conclusion
-
How to convert datatable to Json without any manual string manipulation?
Answer: Use Automapper and Newtonsoft for mapping the datatable row - POCO class - Json object.
-
How can I do datatable to Json convertion using Newtonsoft Json library?
Answer: Newtonsoft Json library is utility for managing and manipulation of Json object. You can serialize your object to Json using Newtonsoft.
-
How can I create hierarchical Json object from datatable using Newtonsoft?
Answer: Bring your data into desired format by mapping and filling POCO entities via Automapper or other object-to-object mapping library. Use Newtonsoft for your filled object to Json conversion.
Sample on github for How to: Convert datatable to Json object using Automapper and Newtonsoft
Happy Coding !!