How to efficiently load API results into a DataTable?

Question

API Response :

{
    "success": true,
    "code": 0,
    "msg": "성공하였습니다.",
    "list": [
        {
            "code": "ANT",
            "code_name": "소둔구분",
            "p_code": "",
            "p_code_name": "",
            "code_type": 0,
            "code_level": 0,
            "max_level": 1,
            "description": "GROP : 99  DETL : ANT  SUBS : 00",
            "create_user": "SYSTEM",
            "create_time": "2019-04-24T17:58:58.000+0000",
            "disable_yn": "Y"
        },
        {...}
        ]
}

C# API Call :

// CodeInfo 초기 데이터 로드
public static DataTable selectAllCodeInfo()
{
    DataTable dt = new DataTable();
    try
    {
        var httpWebRequest = (HttpWebRequest)WebRequest.Create(API_ADDRESS + "/api/codeInfo");
        httpWebRequest.ContentType = "application/json";
        httpWebRequest.Method = "GET";
        var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();

        using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
        {
            JObject applyObj = JObject.Parse(streamReader.ReadToEnd());
            var applyObj2 = streamReader.ReadToEnd();

            string success = applyObj["success"].ToString();

            if (success.Equals("True"))
            {
                if (applyObj["list"].ToString() != null)
                {
                    ??????????
                    return dt;
                }  
            } else
            {
                //API 응답 데이터 수신 실패
            }

        }
    } catch(WebException)
    {
        //API 서버 닫혀있을때, 연결이 안될때
        Console.Write("예외");
        return null;
    }
    catch (Exception)
    {
        //그 외의 Exception
        Console.Write("예외");
        return null;
    }
    return dt;
}

I want to efficiently load the above API call return data values into a DataTable.

I've thought of several ways, but haven't found an efficient way yet.

I have tried to use json data as property object. But it's too cumbersome and inefficient.

Of the Json data above, I want to load only the data contained in the list array as columns and rows in the DataTable.

Is there a good alternative?

This was answered here. https://stackoverflow.com/questions/11981282/convert-json-to-datatable — Atk, Jan 31 '20 at 04:46

dbc · Answer 1 · 2020-01-31T04:59:23.433

As explained in this answer to Convert JSON to DataTable by Kyle, Json.NET (which you are already using) currently supports deserializing a list of objects to a DataTable. In the specific JSON provided the list is embedded in a root object property named "list", so you will need to extract it in order to deserialize it.

But since you also ask how to do this in an efficient way, you will want to avoid intermediate representations such as streamReader.ReadToEnd(), applyObj and applyObj["list"].ToString(). To do this, first introduce the following extension method, modeled on JsonConvert.DeserializeAnonymousType<T>(string value, T anonymousTypeObject):

public static class JsonExtensions
{
    public static T DeserializeAnonymousType<T>(TextReader textReader, T anonymousTypeObject, JsonSerializerSettings settings = null)
    {
        return (T)JsonSerializer.CreateDefault(settings).Deserialize(textReader, typeof(T));
    }
}

And now you can do:

var root = JsonExtensions.DeserializeAnonymousType(streamReader, 
                                                   new { success = default(bool), list = default(DataTable) });
if (root.success)
{
    var dt = root.list;
    // return the table
}
else
{
    // Handle failure
}

Demo fiddle here.

Bibin · Answer 2 · 2020-01-31T11:51:22.223

This code is convert json to datatable in your case.Json.NET (which you are already using) currently supports deserializing a list of objects to a DataTable.

public static DataTable selectAllCodeInfo()
{
    DataTable dt = new DataTable();
    try
    {
         var httpWebRequest = (HttpWebRequest)WebRequest.Create(API_ADDRESS + "/api/codeInfo");
            httpWebRequest.ContentType = "application/json";
            httpWebRequest.Method = "GET";

            if (!string.IsNullOrEmpty(RequestParameters))
                using (var streamWriter = new StreamWriter(httpWebRequest.GetRequestStream()))
                {
                    streamWriter.Write(RequestParameters);
                }
            var result = string.Empty;
            var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
            using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
            {
                result = streamReader.ReadToEnd();
            }
        dt = JsonConvert.DeserializeObject<DataTable>(result.list) 

    } catch(WebException)
    {
        //API 서버 닫혀있을때, 연결이 안될때
        Console.Write("예외");
        return null;
    }
    catch (Exception)
    {
        //그 외의 Exception
        Console.Write("예외");
        return null;
    }
    return dt;
}

How to efficiently load API results into a DataTable?

2 Answers2