How to: Populate object Collections from multiple sources

Question

How to: Populate object Collections from multiple sources

Navigation

#1 by (1 votes)

2

I would like an aid in passing values to my property with private IEnumerable<OrderDetail> orderDetail; .

I would like to fill it out, but I have already looked for some examples and did not find anything with this situation. I ask your colleagues for help if you can show some way to do this without having to fill in two DataTable to do inner join with Linq .

I will present the structure of the Classes below and form that I am conducting 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 using LINQ but this structure did not work under the error:

Error 1 Can not implicitly convert type 'System.Collections.Generic.List' to 'System.Collections.Generic.IEnumerable'. An explicit conversion exists (are you missing a cast?) D: \ Samples Projects \ Pizzeria \ TestLinq \ 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 an example, hint, or any structure to accomplish this procedure to populate the collection type property of my class?

mysql c# linq winforms

asked by anonymous 19.06.2015 / 18:35

1 answer

Decrease physical size when displaying image Delayed client side image rendering

score 1 · Answer 1

See an example below of how you can do 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]