Click here to Skip to main content
14,838,477 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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?
Posted
Updated 1-Sep-20 0:54am
v2

   
Comments
Bullgill Coder 27-Aug-20 3:56am
   
I had been looking down that route. The microsoft example just gives me what I've already achieved. How do I cope with an array within an array like I have?
Gerry Schmitz 28-Aug-20 19:46pm
   
You got 2 items; that's not an "array within an array; more like a List<object>.
Bullgill Coder 1-Sep-20 6:57am
   
Thanks, I had assumed array as I had a set of orders and then within each order a set of products. As you can see, I've used a list and managed to resolve this. I've not however needed to serialize.
Perhaps a little clunky, but I've now got something that works.

public HttpResponseMessage Get(string customer)
        {
            _con = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString);
            
            string queryString = "Select * from ORDERHEADER where customer = " + customer;

            DataSet ordetails = new DataSet();
            DataSet ordetailssub = new DataSet();
            var q_Head = "Select * from ORDERHEADER where customer = " + customer;
            _Header_adapt = new SqlDataAdapter
            {
                SelectCommand = new SqlCommand(q_Head, _con)
            };
            _Header_adapt.Fill(ordetails,"ORDERHEADER");
            
            var q_Detail = "Select * from ORDERDETAIL where customer = " + customer;
            SqlDataAdapter _Det_adapt = new SqlDataAdapter
            {
                SelectCommand = new SqlCommand(q_Detail, _con)
            };
            _Det_adapt.Fill(ordetails, "ORDERDETAIL");

            DataRelation ordRel = ordetails.Relations.Add("x",
                ordetails.Tables["ORDERHEADER"].Columns["Order"],
                ordetails.Tables["ORDERDETAIL"].Columns["Order"]);

            SqlDataAdapter custAdapter = 
                new SqlDataAdapter("Select * from ORDERHEADER where customer = " + customer, _con);
            SqlDataAdapter ordAdapter = new SqlDataAdapter("Select * from ORDERDETAIL where customer_urn = " + customer, _con);

            DataSet customerOrders = new DataSet();

            var jsonorderlist = new List<jsonOrder>();
            var oh3 = new jsonOrder() { };
            foreach (DataRow pRow in ordetails.Tables["ORDERHEADER"].Rows)
            {
                var ord = new jsonOrder()
                {
                    order = pRow["order"].ToString(),
                    customer = pRow["Customer"].ToString(),
                    total = Convert.ToDecimal(pRow["total"]),
                };
                int key1 = 0;
                foreach (DataRow cRow in pRow.GetChildRows(ordRel))
                {
                    key1 = key1 + 1;
                    ord.itemsordered.Add(key1.ToString(), new jsonOrder.orderitem
                    {
                        product= cRow["product"].ToString(),
                        qty = Convert.ToInt32(cRow["qty"]),
                        total_amount = Convert.ToDecimal(cRow["total_amount"])
                    });
                }
                //ord.grand_total = Convert.ToDecimal(pRow["total"]);
                jsonorderlist.Add(ord);
 
            }

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

    }
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900