1

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?
        }
    }
}
Improve this question
0

1 Answer 1

Reset to default
1

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;
using System.Text.Json;
using System.Text.Json.Serialization;
                
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 = 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);         
        }
        
        Console.WriteLine("\n");
        Console.WriteLine("Example using Dynamic: \n");
        using (JsonDocument document = JsonDocument.Parse(myJsonString))
        {
           foreach (JsonElement element in document.RootElement.EnumerateArray())
           {
               Console.WriteLine(element.GetProperty("DOC-ID"));
               Console.WriteLine(element.GetProperty("DOCUMENT_FULL_PATH"));
            }
        }
    }
}


public class MainReviewDocuments
{
    [JsonPropertyName("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

Working example: https://dotnetfiddle.net/nEjPIK

You can read more on the comparison between the two libraries in this article

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Thank you for this! I am actually still drawing an error for both methods. I know my query returns a JSON string but using the class method and using List<MainReviewDocuments> it will not compile due to cannot implicitly convert type 'System.Collections.Generic.List<Project.Models.MainReviewDocuments>' to 'Project.Models.MainReviewDocuments' . Using the <dynamic> method it indicates Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'Cannot implicitly convert type 'System.Text.Json.JsonElement' to 'System.Collections.IEnumerable''. My JSON array when returned does not have a @. That ok?
I also do not use JsonConvert which I hope is okay. I use JsonSerializer which is a default MS library. Could that be the issue?
@Bradyboyy88 Can you please post your MainReviewDocuments model class please? Have you tried using Newtonsoft package for parsing your JSON? You can parse with dynamic using the default MS library: var mainreviewdocuments = JsonNode.Parse(json);
I have used newtonsoft and your code does work. I was just trying to stick with MS libraries rather than third party but I cannot for the life of me make it work ha. My model is very similar to what you put but the code I did above is just a dumbed down version due to confidentiality. Does your example work using MS serializer()?.
@Bradyboyy88 I have updated my answer to include System.Text library also for your scenario. Hope it helps you out.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.