Populate an object from multiple sql queries

Question

I'm just looking for a couple of pointers to get me on the right path. I have 2 SQL queries, one returning a list of customers and one returning the list of orders that customer has made. Working in c# how am I able to populate a class that then can be serialized into json.

I've tried multiple different way and this is now the closest I've got for my class...

public class jsonOrder
    {     
        public string order_no { get; set; }
        public string customer { get; set; }
        public List<orderitem> items { get; set; }
        public decimal grandtotal { get; set; }
        public jsonOrder()
        {
            this.items = new List<orderitem>();
        }        
    }

    public class orderitem
    {
        public string itemcode{ get; set; }
        public int quantity { get; set; }
        public decimal amount { get; set; }
    }

Using this I can get my json to look like this...

[
    {
        "order_no": "12345",
        "customer": "12",
        "items": [],
        "grand_total": 6.0000,
    }
    {
        ...another order...
    }
]

How can I get the items to list the times in the order?

For example

{
        "order_no": "12345",
        "customer": "12",
        "items": [
                      {"itemcode":"a","quantity":1,"amount":12.34}
                      {"itemcode":"b","quantity":2,"amount":6.12}
                 ],
        "grand_total": 24.5800
}

at the moment my code is

List<readOrder> orderhistory = new List<Models.readOrder>(ordHead.Rows.Count);
            if (ordHead.Rows.Count > 0)
            {
                foreach (DataRow orders in ordHead.Rows)
                {
                    orderhistory.Add(new readOrder(orders));
                }
            }

but this is only bringing back header details.

I currently get my SQL from the following, but this I am flexible on...

_con = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString);
            
// Get all orders
   DataTable ordHead = new DataTable();
   var queryHead = "Select * from ORDERHEADER where customer = " + customer;
   _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 = " + customer;
            _adapt = new SqlDataAdapter
            {
                SelectCommand = new SqlCommand(queryDetail, _con)
            };
            _adapt.Fill(ordDetail);
    ```

if our post is got help full to you, then you need to give us a vote at least. — Shakir Ahamed, Oct 05 '20 at 11:09

score 0 · Answer 1 · answered Aug 27 '20 at 12:49

You could try to "fake it" this way:

internal class Program
{
    private static void Main(string[] args)
    {
        Console.WriteLine("Hello World!");
        var jo = new jsonOrder()
        {
            customer = "cust",
            order_no = "123"
        };

        jo.itemsOrdered.Add("cdf", new jsonOrder.orderitem
        {
            amount = 1,
            itemcode = "cdf",
            quantity = 10
        });
        jo.itemsOrdered.Add("abc", new jsonOrder.orderitem
        {
            amount = 1,
            itemcode = "abc",
            quantity = 10
        });

        Console.WriteLine(JsonConvert.SerializeObject(jo));
        Console.ReadKey();
    }
}

internal class jsonOrder
{
    public jsonOrder()
    {
        this.itemsOrdered = new SortedList<string, orderitem>();
    }

    public string customer { get; set; }

    public decimal grandtotal { get; set; }


    [JsonIgnore]
    public SortedList<string, orderitem> itemsOrdered 
    {
        get;set;
    }

    public List<orderitem> items
    {
        get { return itemsOrdered.Values.ToList(); }
    }        

    public string order_no { get; set; }

    public class orderitem
    {
        public decimal amount { get; set; }

        public string itemcode { get; set; }

        public int quantity { get; set; }
    }
}

Thanks, I need to however populate "jo" from SQL queries. Is there an easy ( or complex ) way to acheive this? — Kevin Robinson, Aug 27 '20 at 13:25
You did not say how you are reading your data from a SQL db. Do you use any ORM? If not, you can use i.e. SqlDataReader and map/populate the fields manually (https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader?view=dotnet-plat-ext-3.1) or use DataSets (https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/populating-a-dataset-from-a-dataadapter). — vhr, Aug 27 '20 at 13:34
I'm flexibly on how I populate the data, the end result it the important bit. I've added how I get my SQL to the original question. — Kevin Robinson, Aug 27 '20 at 13:50
@KevinRobinson Can you also post the readOrder's class code and how you're mapping that readOrder to jsonOrder — vhr, Aug 28 '20 at 09:31

score 0 · Answer 2 · answered Aug 27 '20 at 12:52

try like below and also check this will help you.

public class jsonOrder
{     
    public string order_no { get; set; }
    public string customer { get; set; }
    public List<orderitem> items { get; set; }
    public decimal grandtotal { get; set; }
    public jsonOrder()
    {
        this.items = new List<orderitem>();
    }        
}

public class orderitem
{
    public string itemcode{ get; set; }
    public int quantity { get; set; }
    public decimal amount { get; set; }
}

public class Program
{
      static public void Main()
      {
             using (StreamReader r = new StreamReader(Server.MapPath("pass_your_json")))
             {
                  string json = r.ReadToEnd();
                   List<jsonOrder> jsonObject = JsonConvert.DeserializeObject<List<jsonOrder>>(json);
             }

         Console.WriteLine(jsonObject[0].orderitem[0].itemcode);                 
      }  
}

Thanks, where you've put "pass_you_json" what do you mean? I'm trying to populate the data from two SQL queries, and then I assume I'll have to use JsonConvert.SerializeObject to get my output. — Kevin Robinson, Aug 27 '20 at 13:22
@KevinRobinson you have to assign your json to string variable and pass it to there — Shakir Ahamed, Aug 27 '20 at 13:29

score 0 · Accepted Answer · answered Sep 01 '20 at 10:49

I've managed to resolve it... my corrected code is below. Thanks to those who pointed me in directions I needed to look at...

public HttpResponseMessage Get(string customer_urn)
        {
            _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 = " + 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_id = pRow["order"].ToString(),
                    customer_urn = 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
                    {
                        prod = cRow["prod"].ToString(),
                        qty = Convert.ToInt32(cRow["qty"]),
                        total_amount = Convert.ToDecimal(cRow["total_amount"])
                    });
                }
                jsonorderlist.Add(ord);
 
            }

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

    }

Populate an object from multiple sql queries

3 Answers3