Converting a DataTable to JSON in Asp.net Core involves transforming tabular data into a JSON format that is commonly used for data interchange. This process is essential when you need to serialize DataTable data into a JSON string for various purposes like API responses, data transfer, or storage.
Here we learn How to Convert DataTable to JSON with step to step.
Suppose we have to create an API's which is used to show the data of student .
that time we need to create an class for student_Fetch_Data with property like studentName , stduentId ,Age etc , But When you have less column that time it is easy to handle , but suppose you have more than 10 column's (it may any number of column's) that time creating each property with appropriate datatype would be major challenge . In that case may be very helpful . Let's understand.
first understand with class wise :
I have created an class student_Fetch_Data :
namespace WebApplication1.Model
{
public class StudentRequest
{
public int studentId { get; set; }
public string studentName { get; set; }
public string emailId { get; set; }
public string gender { get; set; }
public string dob { get; set; }
}
public class student_Fetch_Data
{
public int studentId { get; set; }
public string studentName { get; set; }
public string emailId { get; set; }
public string gender { get; set; }
public string dob { get; set; }
}
}
this class is used to bind the data from database :
public async Task<List<GetStudentData>> GetStudnetData(string studentId)
{
List<GetStudentData> res = new List<GetStudentData>();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@StudentId", studentId);
DataTable dt = await ExecuteProcedure(cmd, "USP_ShowStudentDetails");
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow rw in dt.Rows)
{
student_Fetch_Data obj = new student_Fetch_Data ();
obj.studentId = Convert.ToInt32(rw["StudentID"]);
obj.studentName = Convert.ToString(rw["StudentName"]);
obj.emailId = Convert.ToString(rw["Email"]);
obj.dob = Convert.ToString(rw["DOB"]);
obj.gender = Convert.ToString(rw["Gender"]);
res.Add(obj);
}
}
}
catch (Exception ex)
{
throw new Exception();
}
return res;
}
In above snippet :
Key Concepts
- Data Retrieval: The code snippet retrieves data from a DataTable dt containing student records.
- Object Mapping: It maps the retrieved data to a custom object student_Fetch_Data that encapsulates student details.
- Conversion: The code converts data types from the DataTable to appropriate types for the custom object properties.
Code Structure
The code snippet uses a foreach loop to iterate through each row in the dt.Rows collection. For each row:
- A new instance of the student_Fetch_Data class is created.
- Data from the current row is extracted and assigned to the properties of the student_Fetch_Data object.
- The object is then added to a collection res.
In this case API's Response :
{
"response": [
{
"StudentID": 6,
"StudentName": "Nitya Thakur",
"Email": "Nitya@gmail.com",
"DOB": "12/02/2000",
"Gender": "female",
"CreatedDate": "05/22/2024"
},
{
"StudentID": 9,
"StudentName": "Surya Prakash",
"Email": "Surya@gmail.com",
"DOB": "12/15/1999",
"Gender": "male",
"CreatedDate": "06/30/2024"
},
{
"StudentID": 10,
"StudentName": "Suraj",
"Email": "Suraj@gmail.com",
"DOB": "12/15/1999",
"Gender": "male",
"CreatedDate": "06/30/2024"
},
{
"StudentID": 11,
"StudentName": "surya",
"Email": "surya@gmail.com",
"DOB": "01/15/1999",
"Gender": "male",
"CreatedDate": "06/30/2024"
},
{
"StudentID": 14,
"StudentName": "hi",
"Email": "hi@gmail.com",
"DOB": "07/05/2024",
"Gender": "male",
"CreatedDate": "07/05/2024"
},
{
"StudentID": 15,
"StudentName": "sharvan",
"Email": "sharvan#gmail.com",
"DOB": "12/15/1999",
"Gender": "male",
"CreatedDate": "07/12/2024"
}
]
}
Now understand without class wise :
Install-package : Newtonsoft.Json;
public async Task<IActionResult> GetStudentData_JSON(string studentId = "0")
{
try
{
DataTable dt = await this.ServiceLayer.GetStudnetData_JSON(studentId);
if (dt != null && dt.Rows.Count > 0)
{
string json = JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.Indented); // Converting DataTable to JSON
return Ok(new
{
response = json
});
}
else
{
return NotFound(new
{
response = "No data found"
});
}
}
catch (Exception ex)
{
return StatusCode(500, new
{
statusCode = 500,
error = ex.Message
});
}
}
Explanation of string json = JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.Indented);
In this line of code, the JsonConvert.SerializeObject method is used to serialize the DataTable object dt into a JSON string. The second parameter Newtonsoft.Json.Formatting.Indented specifies that the JSON output should be formatted with indentation for better readability. This formatting option adds spaces and new lines to the JSON string, making it easier to understand when viewed directly.
In this case API's Response :
{
"response": "[\r\n {\r\n \"StudentID\": 6,\r\n \"StudentName\": \"Nitya Thakur\",
\r\n \"Email\": \"Nitya@gmail.com\",\r\n \"DOB\": \"12/02/2000\",
\r\n \"Gender\": \"female\",\r\n \"CreatedDate\": \"05/22/2024\"\r\n },
\r\n {\r\n \"StudentID\": 9,\r\n \"StudentName\": \"Surya Prakash\",
\r\n \"Email\": \"Surya@gmail.com\",\r\n \"DOB\": \"12/15/1999\",
\r\n \"Gender\": \"male\",\r\n \"CreatedDate\": \"06/30/2024\"\r\n },
\r\n {\r\n \"StudentID\": 10,\r\n \"StudentName\": \"Suraj\",
\r\n \"Email\": \"Suraj@gmail.com\",\r\n \"DOB\": \"12/15/1999\",
\r\n \"Gender\": \"male\",\r\n \"CreatedDate\": \"06/30/2024\"\r\n },
\r\n {\r\n \"StudentID\": 11,\r\n \"StudentName\": \"surya\",
\r\n \"Email\": \"surya@gmail.com\",\r\n \"DOB\": \"01/15/1999\",
\r\n \"Gender\": \"male\",\r\n \"CreatedDate\": \"06/30/2024\"\r\n },
\r\n {\r\n \"StudentID\": 14,\r\n \"StudentName\": \"hi\",\r\n \"Email\": \"hi@gmail.com\",
\r\n \"DOB\": \"07/05/2024\",\r\n \"Gender\": \"male\",\r\n \"CreatedDate\": \"07/05/2024\"\r\n },
\r\n {\r\n \"StudentID\": 15,\r\n \"StudentName\": \"sharvan\",\r\n \"Email\": \"sharvan#gmail.com\",
\r\n \"DOB\": \"12/15/1999\",\r\n \"Gender\": \"male\",\r\n \"CreatedDate\": \"07/12/2024\"\r\n }\r\n]"
}
In above API's response you can see their are \r\n present :
Let's make it better as List format :
Install-package : System.Text.Json;
Use as dynamic res = JsonDocument.Parse(json);
public async Task<IActionResult> GetStudentData_JSON(string studentId = "0")
{
try
{
DataTable dt = await this.ServiceLayer.GetStudnetData_JSON(studentId);
if (dt != null && dt.Rows.Count > 0)
{
dt.TableName = "studentList";
string json = JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.Indented);
dynamic res = JsonDocument.Parse(json); // Use it for better result .
return Ok(new
{
response = res
});
}
else
{
return NotFound(new
{
response = "No data found"
});
}
}
catch (Exception ex)
{
return StatusCode(500, new
{
statusCode = 500,
error = ex.Message
});
}
}
Explanation of dynamic res = JsonDocument.Parse(json);
The line dynamic res = JsonDocument.Parse(json); parses the JSON string json into a dynamic object res using the JsonDocument class. By using dynamic, the type of res is determined at runtime, allowing flexibility in handling the parsed JSON data. This approach is useful when dealing with JSON structures of unknown or varying shapes, as it avoids the need to define specific classes for deserialization.
In this case API's Response :
{
"response": [
{
"StudentID": 6,
"StudentName": "Nitya Thakur",
"Email": "Nitya@gmail.com",
"DOB": "12/02/2000",
"Gender": "female",
"CreatedDate": "05/22/2024"
},
{
"StudentID": 9,
"StudentName": "Surya Prakash",
"Email": "Surya@gmail.com",
"DOB": "12/15/1999",
"Gender": "male",
"CreatedDate": "06/30/2024"
},
{
"StudentID": 10,
"StudentName": "Suraj",
"Email": "Suraj@gmail.com",
"DOB": "12/15/1999",
"Gender": "male",
"CreatedDate": "06/30/2024"
},
{
"StudentID": 11,
"StudentName": "surya",
"Email": "surya@gmail.com",
"DOB": "01/15/1999",
"Gender": "male",
"CreatedDate": "06/30/2024"
},
{
"StudentID": 14,
"StudentName": "hi",
"Email": "hi@gmail.com",
"DOB": "07/05/2024",
"Gender": "male",
"CreatedDate": "07/05/2024"
},
{
"StudentID": 15,
"StudentName": "sharvan",
"Email": "sharvan#gmail.com",
"DOB": "12/15/1999",
"Gender": "male",
"CreatedDate": "07/12/2024"
}
]
}
You can also use this approach to convert DataTable to JSON :
public async Task<IActionResult> GetStudentData_JSON(string studentId = "0")
{
try
{
DataTable dt = await this.ServiceLayer.GetStudnetData_JSON(studentId);
if (dt != null && dt.Rows.Count > 0)
{
dt.TableName = "studentList";
var rows = dt.AsEnumerable().Select(r => dt.Columns.Cast<DataColumn>()
.ToDictionary(c => c.ColumnName, c => r[c].ToString())).ToList(); // convert DataTable to JSON
return Ok(new
{
response = rows
});
}
else
{
return NotFound(new
{
response = "No data found"
});
}
}
catch (Exception ex)
{
return StatusCode(500, new
{
statusCode = 500,
error = ex.Message
});
}
}
Explaination :
var rows = dt.AsEnumerable().Select(r => dt.Columns.Cast<DataColumn>()
.ToDictionary(c => c.ColumnName, c => r[c].ToString())).ToList();
This line uses LINQ to convert the DataTable rows into a list of dictionaries. Here's a breakdown:
- dt.AsEnumerable(): Converts the DataTable into an enumerable collection of rows.
- Select(r => ...): Projects each row r into a new form.
- dt.Columns.Cast<DataColumn>(): Retrieves all columns of the DataTable as DataColumn objects.
- ToDictionary(c => c.ColumnName, c => r[c].ToString()): Converts each row r into a dictionary where the column name is the key and the cell value is the value.
- ToList(): Converts the result into a list.
This line efficiently transforms the DataTable rows into a list of dictionaries, making it easier to work with structured data in a more flexible format.