private List<PatchPanel> getPatchPanelList() { List<PatchPanel> patchPanels = new List<PatchPanel>(); string patchPanelsQuery = "SELECT PatchPanel.PatchPanelID, PatchPanel.PatchPanelName, IDF.IDFID, IDF.IDFName, VenueSpace.VenueSpaceID, VenueSpace.VenueSpaceName, Venue.VenueID, Venue.VenueName FROM [PatchPanel], [IDF], [VenueSpace], [Venue] WHERE PatchPanel.IDFID = IDF.IDFID AND IDF.VenueSpaceID = VenueSpace.VenueSpaceID AND VenueSpace.VenueID = Venue.VenueID;"; DataSet patchPanelsDS = getDataSet(patchPanelsQuery); foreach (DataRow dr in patchPanelsDS.Tables[0].Rows) { PatchPanel pp = new PatchPanel(); pp.patchPanelID = (int)dr.ItemArray.GetValue(0); pp.patchPanelName = dr.ItemArray.GetValue(1).ToString(); pp.idfID = (int)dr.ItemArray.GetValue(2); pp.idfName = dr.ItemArray.GetValue(3).ToString(); pp.venueSpaceID = (int)dr.ItemArray.GetValue(4); pp.venueSpaceName = dr.ItemArray.GetValue(5).ToString(); pp.venueID = (int)dr.ItemArray.GetValue(6); pp.venueName = dr.ItemArray.GetValue(7).ToString(); pp.patchPanelRecord = pp.patchPanelName + ", " + pp.idfName + ", " + pp.venueSpaceName + ", " + pp.venueName; string portCountQuery = "SELECT COUNT(PatchPanelPort.PatchPanelID) FROM [PatchPanelPort] WHERE PatchPanelPort.PatchPanelID = " + pp.patchPanelID + ";"; DataSet portCountDS = getDataSet(portCountQuery); pp.portTotal = portCountDS.Tables[0].Rows[0].ItemArray.GetValue(0).ToString(); patchPanels.Add(pp); } patchPanels.OrderBy(p => p.venueName); return patchPanels; }
//load venues into combobox private void Form2_Load(object sender, EventArgs e) { string venueQuery = "SELECT Venue.VenueID, Venue.VenueName FROM [Venue];"; string venueSpaceQuery = "SELECT VenueSpace.VenueSpaceID, VenueSpace.VenueSpaceName, VenueSpace.VenueID FROM [VenueSpace];"; string idfQuery = "SELECT IDF.IDFID, IDF.IDFName, IDF.VenueSpaceID FROM [IDF];"; string ppQuery = "SELECT PatchPanel.PatchPanelID, PatchPanel.PatchPanelName, PatchPanel.IDFID FROM [PatchPanel];"; string switchQuery = "SELECT Switch.SwitchID, Switch.DNSName, Switch.IDFID FROM [Switch];"; string ppPortQuery = "SELECT PatchPanelPort.PatchPanelPortID, PatchPanelPort.PatchPanelPortNum, PatchPanelPort.PatchPanelID FROM [PatchPanelPort] WHERE PatchPanelPort.SwitchPortID IS NULL;"; string switchPortQuery = "SELECT SwitchPort.SwitchPortID, SwitchPort.SwitchPortNum, SwitchPort.SwitchID FROM [SwitchPort] WHERE SwitchPort.PatchPanelPortID IS NULL;"; DataSet venDS = getDataSet(venueQuery); DataSet vsDS = getDataSet(venueSpaceQuery); DataSet idfDS = getDataSet(idfQuery); DataSet swDS = getDataSet(switchQuery); DataSet ppDS = getDataSet(ppQuery); DataSet swpDS = getDataSet(switchPortQuery); DataSet pppDS = getDataSet(ppPortQuery); try { foreach (DataRow dr in venDS.Tables[0].Rows) { Venue venue = new Venue(); venue.venueID = (int)dr.ItemArray.GetValue(0); venue.venueName = dr.ItemArray.GetValue(1).ToString(); venueList.Add(venue); } foreach (DataRow dr in vsDS.Tables[0].Rows) { VenueSpace venueSpace = new VenueSpace(); venueSpace.venueSpaceID = (int)dr.ItemArray.GetValue(0); venueSpace.venueSpaceName = dr.ItemArray.GetValue(1).ToString(); venueSpace.venueID = (int)dr.ItemArray.GetValue(2); venueSpaceList.Add(venueSpace); } foreach (DataRow dr in idfDS.Tables[0].Rows) { IDF idf = new IDF(); idf.idfID = (int)dr.ItemArray.GetValue(0); idf.idfName = dr.ItemArray.GetValue(1).ToString(); idf.venueSpaceID = (int)dr.ItemArray.GetValue(2); idfList.Add(idf); } foreach (DataRow dr in swDS.Tables[0].Rows) { Switch sw = new Switch(); sw.switchID = (int)dr.ItemArray.GetValue(0); sw.switchNameDNS = dr.ItemArray.GetValue(1).ToString(); sw.idfID = (int)dr.ItemArray.GetValue(2); switchList.Add(sw); } foreach (DataRow dr in ppDS.Tables[0].Rows) { PatchPanel pp = new PatchPanel(); pp.patchPanelID = (int)dr.ItemArray.GetValue(0); pp.patchPanelName = dr.ItemArray.GetValue(1).ToString(); pp.idfID = (int)dr.ItemArray.GetValue(2); PPList.Add(pp); } foreach (DataRow dr in swpDS.Tables[0].Rows) { SwitchPort swp = new SwitchPort(); swp.switchPortID = (int)dr.ItemArray.GetValue(0); swp.switchPortNum = (int)dr.ItemArray.GetValue(1); swp.switchID = (int)dr.ItemArray.GetValue(2); switchPortList.Add(swp); } foreach (DataRow dr in pppDS.Tables[0].Rows) { PatchPanelPort ppp = new PatchPanelPort(); ppp.patchPanelPortID = (int)dr.ItemArray.GetValue(0); ppp.patchPanelPortNum = (int)dr.ItemArray.GetValue(1); ppp.patchPanelID = (int)dr.ItemArray.GetValue(2); PPPList.Add(ppp); } } catch (OleDbException exp) { MessageBox.Show("Database Error:" + exp.Message.ToString()); } updateVenueBoxList(venueList); updateDataGridView(); }