I currently have an API which I use dapper to connect to a SQL database to find the file path for documents related to a certain ID. The result is returned in JSON format as follows:
[{"DOC-ID": 1, "DOCUMENT_FULL_PATH": "/PATH/FILENAME.DOC"},{"DOC-ID": 2, "DOCUMENT_FULL_PATH": "/PATH/FILENAME2.DOC"}]
I am trying to get my API to deserialize the JSON data then link that too a model (I prefer to not use models but the only solution I found was using JSON DOM which is briefly discussed on MS website but does not provide an example of how to loop through the JSON array so I could not move forward with this example). When I try to deserialize the dapper query result I get the error indicated below (shown at the line item in the code). I am not sure what is triggering this as I would think the QuerySingle could be deserialized with this method. Once this error is fixed I need to check the files last modified date and save that value to the model which I then again need to serialize to send to the front end! I have spent so much time on this so some help would be much appreciated!
[HttpPost]
public ActionResult MainReviewDocuments([FromForm] string ID)
{
//Using FormData on frontend
//checking ID exists on searching in dashboard
if (ID == null || ID.Length == 0)
{
return Ok(new { Result = "Failed" });
}
else
{
//We have parameters here just in case we want to use them
var UserID = HttpContext.User.FindFirst(ClaimTypes.Name).Value;
String query = "select dbo.A2Q_0132W_RR_IDDocumentation_JSON(@ID) as output";
using (var connection = new SqlConnection(connectionString))
{
var json = connection.QuerySingle<string>(query, new { ID = ID});
MainReviewDocuments? mainreviewdocuments = JsonSerializer.Deserialize<MainReviewDocuments>(json); // this line draws an error 'The JSON value could not be converted to Project.Models.MainReviewDocuments. Path: $ | LineNumber: 0 | BytePositionInLine: 1.'
var rootPath = Path.Combine(Directory.GetParent(env.ContentRootPath).ToString(), "Files");
foreach (var document in mainreviewdocuments)
{
filePath = Path.Combine(rootPath, document.DOCUMENT_FULL_PATH);
//Check file system for each file path and set last modified value to model object LAST_MODIFIED. Struggling with this as well
}
return Ok(mainreviewdocuments); // Can I use Ok() method to convert model back to JSON?
}
}
}
CodePudding user response:
In your original call, you need to de-serialize to a List
:
MainReviewDocuments? mainreviewdocuments = JsonSerializer.Deserialize<List<MainReviewDocuments>>(json);
and then access your properties are required.
Using Newtonsoft.Json
library:
You can de-serialize your JSON
string that you receive from your DB
into the following class:
public class MainReviewDocuments
{
[JsonProperty("DOC-ID")]
public int DOCID { get; set; }
public string DOCUMENT_FULL_PATH { get; set; }
}
Or you can use dynamic
to de-serialize your JSON
:
var mainreviewdocuments = JsonSerializer.Deserialize<dynamic>(json);
and then access the properties as shown in the example below.
You can refer to a working example below:
using System;
using Newtonsoft.Json;
using System.Collections.Generic;
public class Program
{
public static void Main()
{
var myJsonString=@"[{'DOC-ID': 1, 'DOCUMENT_FULL_PATH': '/PATH/FILENAME.DOC'},{'DOC-ID': 2, 'DOCUMENT_FULL_PATH': '/PATH/FILENAME2.DOC'}]";
var mainreviewdocuments =JsonConvert.DeserializeObject<List<MainReviewDocuments>>(myJsonString);
Console.WriteLine("Example using Model: \n");
foreach(var item in mainreviewdocuments)
{
Console.WriteLine(item.DOCID);
Console.WriteLine(item.DOCUMENT_FULL_PATH);
}
Console.WriteLine("\n");
Console.WriteLine("Example using Dynamic: \n");
//Example using dynamic
var mainreviewdocumentsDynamic=JsonConvert.DeserializeObject<dynamic>(myJsonString);
foreach(var item in mainreviewdocumentsDynamic)
{
Console.WriteLine(item["DOC-ID"]);
Console.WriteLine(item["DOCUMENT_FULL_PATH"]);
}
}
}
public class MainReviewDocuments
{
[JsonProperty("DOC-ID")]
public int DOCID { get; set; }
public string DOCUMENT_FULL_PATH { get; set; }
}
Output:
Example using Model:
1
/PATH/FILENAME.DOC
2
/PATH/FILENAME2.DOC
Example using Dynamic:
1
/PATH/FILENAME.DOC
2
/PATH/FILENAME2.DOC
Using System.Text.Json
library:
using System;
using System.Collections.Generic;
public class Program
{
public static void Main()
{
var myJsonString="[{\"DOC-ID\": 1, \"DOCUMENT_FULL_PATH\": \"/PATH/FILENAME.DOC\"},{\"DOC-ID\": 2, \"DOCUMENT_FULL_PATH\": \"/PATH/FILENAME2.DOC\"}]";
var mainreviewdocuments = System.Text.Json.JsonSerializer.Deserialize<List<MainReviewDocuments>>(myJsonString);
Console.WriteLine("Example using Model: \n");
foreach(var item in mainreviewdocuments)
{
Console.WriteLine(item.DOCID);
Console.WriteLine(item.DOCUMENT_FULL_PATH);
}
}
}
public class MainReviewDocuments
{
[System.Text.Json.Serialization.JsonPropertyName("DOC-ID")]
public int DOCID { get; set; }
public string DOCUMENT_FULL_PATH { get; set; }
}
Working example: https://dotnetfiddle.net/nEjPIK