/// <summary> /// Get all decoders information in an instance of <see cref="DataTable"/>. /// </summary> /// <returns>An instance of <see cref="DataTable"/> filled with the requested data.</returns> public static DataSet FindBySwitchboard(int switchboardId) { string sql = string.Empty; DataSet ds = new DataSet(); Logger.LogDebug("Rwm.Otc.Layout.Device.FindBySwitchboard(" + switchboardId + ")"); try { Connect(); sql = @"SELECT s.id as ""SwitchboardID"", s.name as ""Switchboard"", d.name as ""Name"", d.manufacturer || ' ' || d.model as ""Decoder"", dc.name as ""DecoderInput"", dc.address as ""Address"", CASE WHEN (e.name <> '') THEN e.name ELSE ('X:' || (e.x + 1) || ' Y:' || (e.y + 1)) END as ""ConnectTo"" FROM " + Switchboard.TableName + @" s Inner Join " + Element.TableName + @" e On (e.switchboardid = s.id) Inner Join " + AccessoryDecoderConnection.TableName + @" dc On (e.id = dc.elementid) Inner Join " + AccessoryDecoder.TableName + @" d On (d.id = dc.deviceid) WHERE s.id = @sbid ORDER BY s.name Asc, d.name Asc, dc.name Asc"; AccessoryDecoder.SetParameter("sbid", switchboardId); ds.Tables.Add(AccessoryDecoder.ExecuteDataTable(sql)); ds.Tables[0].TableName = "AccessoryConnections"; return(ds); } catch (Exception ex) { Logger.LogError(ex); throw; } finally { Disconnect(); } }
/// <summary> /// Get all decoders information in an instance of <see cref="DataTable"/>. /// </summary> /// <returns>An instance of <see cref="DataTable"/> filled with the requested data.</returns> public static DataSet FindByConnection() { string sql = string.Empty; DataSet ds = new DataSet(); Logger.LogDebug("Rwm.Otc.Layout.Device.FindByConnection()"); try { Connect(); sql = @"SELECT s.id as ""SwitchboardID"", s.name as ""Switchboard"" FROM " + Switchboard.TableName + @" s ORDER BY s.name Asc"; ds.Tables.Add(AccessoryDecoder.ExecuteDataTable(sql)); ds.Tables[0].TableName = "Switchboards"; sql = @"SELECT s.id as ""SwitchboardID"", s.name as ""Switchboard"", d.name as ""Name"", d.manufacturer || ' ' || d.model as ""Decoder"", dc.name as ""DecoderInput"", dc.address as ""Address"", e.name as ""ConnectTo"" FROM " + Switchboard.TableName + @" s Inner Join " + Element.TableName + @" e On (e.switchboardid = s.id) Inner Join " + AccessoryDecoderConnection.TableName + @" dc On (e.id = dc.elementid) Inner Join " + AccessoryDecoder.TableName + @" d On (d.id = dc.deviceid) ORDER BY s.name Asc, d.name Asc, dc.name Asc"; ds.Tables.Add(AccessoryDecoder.ExecuteDataTable(sql)); ds.Tables[1].TableName = "AccessoryConnections"; // Create a relation to be used in reports ds.Relations.Add(new DataRelation("SwitchboardConnection", ds.Tables["Switchboards"].Columns["SwitchboardID"], ds.Tables["AccessoryConnections"].Columns["SwitchboardID"])); return(ds); } catch (Exception ex) { Logger.LogError(ex); throw; } finally { Disconnect(); } }