从多个SQL查询填充对象

debugcn 发表于 Dev

凯文·罗宾逊

我只是在寻找一些指示，以使我走上正确的道路。我有2个SQL查询，一个返回客户列表，一个返回客户已完成的订单列表。在C＃中工作，我如何填充一个可以序列化为json的类。

我尝试了多种不同的方式，现在这是我上课时最接近的方式...

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; }
    }

使用这个我可以让我的json看起来像这样...

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

如何获得要在订单中列出时间的项目？

例如

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

目前我的代码是

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));
                }
            }

但这只会带回标头详细信息。

我目前可以从以下数据库中获取SQL，但是我可以灵活选择...

_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);
    ```

凯文·罗宾逊

我已经设法解决了...我的更正代码如下。感谢那些为我指明方向的人...

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);
        }

    }

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。