How: Fill Object Collections from Multiple Sources

Question

I would like a help in passing values to my property
private IEnumerable<OrderDetail> orderDetail;.

I would like to fill it in, but I have already looked for some examples and I have not found anything with this situation. I ask for the help of colleagues if I can show some way to carry out these procedures without having to fill two Datatable to make Inner Join with the Linq.

I will present the structure of the Classes below and how I am performing the search in the Database.

 public class Order
    {
        public string orderNumber { get; set; }
        public DateTime orderDate { get; set; }
        private DateTime requiredDate { get; set; }
        private DateTime shippedDate { get; set; }
        public int customerNumber { get; set; }
        public bool status { get; set; }
        public string comments { get; set; }
        private IEnumerable<OrderDetail> orderDetail;

        public virtual IEnumerable<OrderDetail> OrderDetails
        {
            get { return orderDetail; }
            set { orderDetail = value; }
        }

        public Order()
        {
            orderDetail = new List<OrderDetail>();
        }


//No formulário do Evento Load
     private void Form1_Load(object sender, EventArgs e)
        {

     myConnection = new MySqlConnection(myConnString);
            myConnection.Open();

            MySqlDataAdapter conexaoAdapter = 
                new MySqlDataAdapter("select O.*, D.* " + 
                                        "from Orders O " +
                                        "INNER JOIN OrderDetails D " + 
                                        "ON O.orderNumber = D.orderNumber ", myConnection);
            conexaoAdapter.Fill(DataTableDatbase);

.....
}

I tried to use with the LINQ but it did not work this structure below the error:

Error 1 Cannot implicitly Convert type 'System.Collections.Generic.List' to 'System.Collections.Generic.Ienumerable'. An Explicit Conversion exists (are you Missing a cast?) D: Samples Projects Pizzaria Testelinq LINQ_TESTE2 Form1.Cs 47 42 LINQ_TESTE2

   IEnumerable<Order> queryNamesScores =
    from Listorder in DataTableDatbase.Tables["Orders"].AsEnumerable()
    select new Order()
    {
        orderNumber = DataTableDatbase.Tables[0].Rows[0]["orderNumber"].ToString(),
         OrderDetails  = (from scoreAsText in DataTableDatbase.Tables["OrderDetails"].AsEnumerable()
                      select scoreAsText).ToList()
    };

Does anyone have any example, hint or structure to perform this procedure of filling the collection type property of my class?

Answer 1

See an example below of how you can be doing this.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        public string myConnString
        {
            get
            {
                return "Server=.\\SQLEXPRESS;Database=Teste;User ID=sa;Password=*****";
            }
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnString;
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT orderNumber,orderDate,requiredDate,shippedDate,customerNumber,status,comments FROM Teste.dbo.Order_";

                SqlDataAdapter da = new SqlDataAdapter();
                DataTable dtOrder_ = new DataTable();

                da.SelectCommand = cmd;
                conn.Open();
                da.Fill(dtOrder_);  

                var qrOrder = from row in dtOrder_.AsEnumerable()
                           select new Order
                           {
                               orderNumber = Convert.ToInt32(row[0].ToString()),
                               orderDate = Convert.ToDateTime(row[1]),
                               requiredDate = Convert.ToDateTime(row[2]),
                               shippedDate = Convert.ToDateTime(row[3]),
                               customerNumber = Convert.ToInt32(row[4]),
                               status = Convert.ToBoolean(row[5]),
                               comments = Convert.ToString(row[6]),
                           };


                List<Order> OrderList = new List<Order>();
                // aqui você varre toda sua order e busca os detalhes
                foreach (var Ord in qrOrder)
                {
                    SqlCommand cmd2 = new SqlCommand();
                    cmd2.Connection = conn;
                    cmd2.CommandType = CommandType.Text;
                    cmd2.CommandText = "SELECT idDetails,   Details,    IdOrder,    orderNumber FROM Teste.dbo.OrderDetails WHERE orderNumber = @orderNumber";
                    cmd2.Parameters.Add("@orderNumber", SqlDbType.Int).Value = Ord.orderNumber;
                    SqlDataAdapter daOrderDetails = new SqlDataAdapter();
                    DataTable dtOrderDetails = new DataTable();

                    daOrderDetails.SelectCommand = cmd2;
                    daOrderDetails.Fill(dtOrderDetails);

                    var qrOrderDetail = from row in dtOrderDetails.AsEnumerable()
                                  select new OrderDetail
                                  {
                                      idDetails = Convert.ToInt32(row[0]),
                                      Details = Convert.ToString(row[1]),
                                      IdOrder = Convert.ToInt32(row[2]),
                                      orderNumber = Convert.ToInt32(row[3]),
                                  };

                    Order order = new Order()
                    {
                        orderNumber = Ord.orderNumber,
                        orderDate = Ord.orderDate,
                        requiredDate = Ord.requiredDate,
                        shippedDate = Ord.shippedDate,
                        customerNumber = Ord.customerNumber,
                        status = Ord.status,
                        comments = Ord.comments,
                        // aqui você carregar o IEnumerable OrderDetail ou uma list 
                        OrderDetails = qrOrderDetail, /// ou  qrOrderDetail.ToList(),

                    };

                    OrderList.Add(order);
                }

                // fim 
                var sualista = OrderList;
            }
            catch (SqlException sqle)
            {
                // MessageBox.Show("Falha ao efetuar a conexão. Erro: ");
            }
            finally
            {
                conn.Close();
            }
        }
    }

    public class OrderDetail
    {

        public int idDetails { get; set; }
        public string Details { get; set; }
        public int IdOrder { get; set; }
        public int orderNumber { get; set; }
    }

    public class Order
    {
        public Int32 orderNumber { get; set; }
        public DateTime orderDate { get; set; }
        public DateTime requiredDate { get; set; }
        public DateTime shippedDate { get; set; }
        public int customerNumber { get; set; }
        public bool status { get; set; }
        public string comments { get; set; }
        private IEnumerable<OrderDetail> orderDetail;

        public virtual IEnumerable<OrderDetail> OrderDetails
        {
            get { return orderDetail; }
            set { orderDetail = value; }
        }

    }
}

Bank tables

SELECT TOP 1000 [id]
      ,[orderNumber]
      ,[orderDate]
      ,[requiredDate]
      ,[shippedDate]
      ,[customerNumber]
      ,[status]
      ,[comments]
  FROM [Teste].[dbo].[Order_]

SELECT TOP 1000 [idDetails]
      ,[Details]
      ,[IdOrder]
      ,[orderNumber]
  FROM [Teste].[dbo].[OrderDetails]