none
Using data from one SQL query to performa match on another and add data to a list RRS feed

  • Question

  • Hello, I have the following code in .net 

        public class master_historyController : Controller
        {
            private dbEntities db = new dbEntities();
    
            public IEnumerable<DataRow> DeptNameOutput { get; private set; }
    
    
            // GET: master_history
            public ActionResult Index()
            {
                db.Database.Connection.Open();
    
                //Get Department Names
                var departNameoutputmodel = new List<pr_lookup_departments_Result>();
                var deprtmntNames = db.Database.Connection.CreateCommand();
                deprtmntNames.CommandText = "dbo.pr_lookup_departments";
                deprtmntNames.CommandType = System.Data.CommandType.StoredProcedure;
                deprtmntNames.CommandTimeout = 2400;
    
                using (var SPOutputdeprtmntNames = deprtmntNames.ExecuteReader())
                {
                    foreach (var row in SPOutputdeprtmntNames)
                    {
                        departNameoutputmodel.Add(new pr_lookup_departments_Result()
                        {
                            department_id = Convert.ToDecimal(SPOutputdeprtmntNames["department_id"]),
                            department_name = Convert.ToString(SPOutputdeprtmntNames["department_name"]),
                            //unit_id = Convert.ToDecimal(SPOutputdeprtmntNames["unit_id"]),
                        });
    
                    }
    
                    ViewData["DeptNameOutput"] = departNameoutputmodel;
                    //db.Database.Connection.Close();
                    //return View();
                }
    
    
    
    
                //Get all non closed orders
                decimal count = 0;
                var resultData = new List<master_history>();
                var command = db.Database.Connection.CreateCommand();
                command.CommandText = "SELECT master_history.pos_id , master_history.department_id ,master_history.table_no , master_history.order_no ,master_history.status_id , master_history.pax , master_history.hotel_day , master_history.open_time , master_history.close_time , master_history.pos_reservation_id ,master_history.guest_id , master_history.operator_id FROM master_history";
    
                using (var retrevedData = command.ExecuteReader())
                {
                    foreach (var row in retrevedData)
                    {
                        resultData.Add(new pos_master_history()
                        {
                            id = Convert.ToString(retrevedData["id"]),
                            depart = Convert.ToString(retrevedData["department"]),
                            table = Convert.ToString(retrevedData["table"]),
                            order = Convert.ToString(retrevedData["order"]),
                  /// etc
                        });
                        count = count + 1;
                    }
    
                    var order = resultData.OrderBy(c => c.orderno);
                    ViewData["ListOutput"] = order;
                    db.Database.Connection.Close();
                    ViewBag.rowcount = count;
                    return View();
                }
                //return View();
            }
        }

    what I am trying to do now (and getting lost doing it) is something like

     });
                        count = count + 1;

    if (DepartName.Output.department_id = PosData.depart.id) { POSData.Add(new pos_master_history() { departName = DepartName.Output.department_id[0].department_name }

    }

                    var order = resultData.OrderBy(c => c.orderno);

    so afterwards in the index.html I can add 

    <td>@item.departName </td>

    I hope this makes sense.

    Can someone tell me how I can do what I am after?

    Thank you

    Monday, May 14, 2018 10:02 AM

Answers

  • That would mean you likely have POS data without departments or vice versa. The left table should be the master table for which you want all the records. The right table is adding extra data. In my opinion you'd be going from master_history to departments. So change JOIN to LEFT JOIN and then you'll get all master history records and the associated departments, if any.

    If that doesn't work then please post the updated query along with examples of what is still missing.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by G-Oker Monday, May 14, 2018 4:06 PM
    Monday, May 14, 2018 3:44 PM
    Moderator

All replies

  • This forum is for C# specific questions. Please post questions related to MVC in the ASP.NET forums.

    Michael Taylor http://www.michaeltaylorp3.net

    Monday, May 14, 2018 2:11 PM
    Moderator
  • As for your query, don't use a C# if. I don't understand where you're using this POSData variable that appeared in the last block. It appears in your first block that you're reading department information. Then you're running another query to pull POS data. You then want to combine the two together. 

    If that is what you want to do then do a JOIN in the SQL query instead. It is far more efficient and would return everything you need to build up your structure in 1 query.

    SELECT ..., departments.department_id, departments.department_name
    FROM master_history
        JOIN departments ON master_history.department_id = departments.department_id

    Now a single while loop would read everything in.

    using (var reader = command.ExecuteReader())
    {
       while (reader.Read())
       {
          new pos_masterHistory() {
              ...
              depart = reader.GetString("department_id"),
              depart_name = reader.GetString("department_name")
          }
       };
    };
    I made up the table and column names so you'll have to fix those but it lets the DB do the combination as it is far more efficient than C#. Then you can just read the data once. 


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, May 14, 2018 2:29 PM
    Moderator
  • Sorry, I've remember for next time
    Monday, May 14, 2018 3:28 PM
  • Although this works, if I use the JOIN command, I lose 1/2 of the data that is supposed to be displayed .... odd
    Monday, May 14, 2018 3:29 PM
  • That would mean you likely have POS data without departments or vice versa. The left table should be the master table for which you want all the records. The right table is adding extra data. In my opinion you'd be going from master_history to departments. So change JOIN to LEFT JOIN and then you'll get all master history records and the associated departments, if any.

    If that doesn't work then please post the updated query along with examples of what is still missing.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by G-Oker Monday, May 14, 2018 4:06 PM
    Monday, May 14, 2018 3:44 PM
    Moderator
  • You Sir, are a STAR. Left join is was. THANK YOU
    Monday, May 14, 2018 4:06 PM