// fill the door array for the specified floor // Door table is a child of floor table and the foreign key is Floor group ID private void fill_door_arr(Flr_Dr cur_flr, int IN_door_id) { String select_door = "SELECT * FROM " + dr_table + ";"; using (SqlCommand cmd1 = new SqlCommand(select_door, DBcnn)) { using (SqlDataReader reader1 = cmd1.ExecuteReader()) { if (reader1 != null) { while (reader1.Read()) { // Door table // Door ID | Door Name | Floor order (Foreign key) | Margin Left | Margin Top | Margin Right | Margin Bottom | Door IP ;; if same floor 1.0, 1.1, 1.2 etc if ((int)reader1.GetValue(2) == IN_door_id) { double[] door_margin_curr = new double[4]; // Must create new instance of this, so during the list.add it does not overwrite the previous value // append margins door_margin_curr[0] = (double)reader1.GetDecimal(3); door_margin_curr[1] = (double)reader1.GetDecimal(4); door_margin_curr[2] = (double)reader1.GetDecimal(5); door_margin_curr[3] = (double)reader1.GetDecimal(6); string dr_name = (string)reader1.GetValue(1); string dr_IP = (string)reader1.GetValue(7); // create and add door to door list for this floor cur_flr.addDoor(dr_name, door_margin_curr, dr_IP); } } } } // reader closed and disposed up here } // command disposed here } //connection closed and disposed here if connection was set up
// fill arr_floor ordered from lowest floor to highest and also filling the jagged_door array with the doors on the specific floor. private void DB_pull_floors() { // cannot parameterize table name String select_floor = "SELECT * FROM " + flr_table + ";"; // May have DB floors ordered based on column "floor order", but still check // count number of rows int floor_rows = 0; using (SqlCommand cmd = new SqlCommand(select_floor, DBcnn)) { cmd.Parameters.Add("@floor_table", System.Data.SqlDbType.VarChar).Value = flr_table; using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader != null) { while (reader.Read()) { //MessageBox.Show("row: " + (dataReader.GetDecimal(2)).ToString()); floor_rows++; } } } // reader closed and disposed up here } // command disposed here //MessageBox.Show("1. floors looped: " + floor_rows.ToString()); int floor_count = 0; // Get the table values while (floor_count < floor_rows) // while true { // need new instance of reader to start at first row using (SqlCommand cmd = new SqlCommand(select_floor, DBcnn)) { using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader != null) { while (reader.Read()) { //MessageBox.Show("looking: " + (double)reader.GetDecimal(2)); // Parent Table columns, Floor Table // Floor ID | Name | Floor order | Image Path | DoorID // add in order, just in case out of order // Admin app will sort DB after finalizing, so this is just to make sure if ((int)reader.GetValue(2) == floor_count) { // Create class object Flr_Dr new_flr = new Flr_Dr(); string floor_name = (reader.GetValue(1)).ToString(); string flr_numbered = floor_count + ". " + floor_name; // display name string img_dir = folder + floor_name + ".jpg"; int doorID = ((int)reader.GetValue(4)); // create floor struct in new_flr new_flr.setFloor_st(floor_name, floor_count, flr_numbered, img_dir, doorID); // Add new_flr to class list flr_dr_class_lst.Add(new_flr); // Don't break, may be more floorplans for the same floor. Floors with the same FloorOrder value } } // floor_count++; } } // reader closed and disposed up here } // command disposed here //MessageBox.Show("in loop: " + floor_count.ToString()); } //MessageBox.Show("Last loop " +floor_number.ToString()); foreach (Flr_Dr flr in flr_dr_class_lst) { // Fill doorLists based on DoorID from floor_struct int pull_dr_ID = flr.getDoorID(); fill_door_arr(flr, pull_dr_ID); } }