Send SQL data in WEB API in JSON array

See more:

C#

SQL

JSON

API

Hi, I'm looking for a few pointers for an API I'm writing. The purpose of the API is to send order details to an external company, so the formatting of the resulting JSON is fixed.

We'll start with that...

[
  {
    "order_id": 1,
    "customer_no": 1,
    "items": [
      {
        "itemid": "12345",
        "qty": 1,
        "discount_amount": 20,
        "original_amount": 100,
        "total_amount": 80
      }
      {
        "itemid": "67890",
        "qty": 1,
        "discount_amount": 20,
        "original_amount": 100,
        "total_amount": 80
      }
    ],
    "grandtotal": 160,
    "createddate": "2020-08-26"
  }
  {
    "order_id": 2,
    "customer_no": 1,
    "items": [
      {
        "itemid": "12345",
        "qty": 1,
        "discount_amount": 20,
        "original_amount": 100,
        "total_amount": 80
      }
    ],
    "grandtotal": 80,
    "createddate": "2020-08-26"
  }
]

So to get to this endpoint I can be quite flexible. I've managed to get the order_id, customer_no, grand_total and created date to come out in my json, but nothing in the items section.

What I have tried:

This is my model that I've used to get the header information for the order...

public class readOrder : jsonOrder
{
    public readOrder(DataRow row)
    {
        order_id = row["order_id"].ToString();
        customer_no = row["customer_no"].ToString();
        //items.itemid= row["itemid"].ToString();
        grandtotal = Convert.ToDecimal(row["grandtotal"]);
        createddate = row["createddate"].ToString();

    }
}

public class jsonOrder
{
    public string order_id { get; set; }
    public string customer_no { get; set; }
    public orderitem items { get; set; }
    public decimal grandtotal { get; set; }
    public string createddate { get; set; }
}

public class orderitem
{
    public string itemid{ get; set; }
    public int qty { get; set; }
    public decimal discount_amount { get; set; }
    public decimal original_amount { get; set; }
    public decimal total_amount { get; set; }
}

and my controller...

public HttpResponseMessage Get(string customer_no)
{
    _con = new SqlConnection(ConfigurationManager.ConnectionStrings["xxxx_Connection"].ConnectionString);

    // Get all orders
    DataTable ordHead = new DataTable();
    var queryHead = "Select * from ORDERHEADER where customer_no = " + customer_no;
    _Header_adapt = new SqlDataAdapter
    {
        SelectCommand = new SqlCommand(queryHead,_con)
    };
    _Header_adapt.Fill(ordHead);

    //Get items within orders
    DataTable ordDetail = new DataTable();
    var queryDetail = "Select * from ORDERHISTORY where customer_no = " + customer_no;
    _adapt = new SqlDataAdapter
    {
        SelectCommand = new SqlCommand(queryDetail, _con)
    };
    _adapt.Fill(ordDetail);

    List<readOrder> orderhistory = new List<Models.readOrder>(ordHead.Rows.Count);
    if (ordHead.Rows.Count > 0)
    {
        //var retd = new jsonOrder();
        //var jsonOrdHist = JsonConvert.SerializeObject(retd);
        foreach (DataRow orders in ordHead.Rows)
        {
            orderhistory.Add(new readOrder(orders));
        }
    }


    return Request.CreateResponse(HttpStatusCode.OK, orderhistory);
}

this gives me the results...

[
    {
        "order_id": "102535",
        "customer_no": "123",
        "items": null,
        "grand_total": 6.0000,
        "created_at": "08/07/2010 00:00:00"
    },
    {
        "order_id": "10929",
        "customer_12": "123",
        "items": null,
        "grand_total": 28.8000,
        "created_at": "28/03/2019 00:00:00"
    }
]

What do I need to do to file the "items" section with an array of items.

I can format the data in any way required, i.e. one view with all data, or as I have at the moment one view with the header information and one with the order details. It's the output I need to get right.

The code is a little clunky as I've been trying different things all day!

Thanks

--- UPDATE ---
I've spent another several hours trawling online for some hints for this, but still can't find a workable solution even though this would be what I would have thought a commonly used task?