public System.Data.DataTable listItems(ITEMFILTER filter, String param) { try { string query = "SELECT dataitems.* FROM dataitems"; if (filter == ITEMFILTER.CATEGORY) { query += " WHERE category='" + param + "';"; } else if (filter == ITEMFILTER.DATAITEMID) { query += " WHERE iddataitems='" + param + "';"; } else if (filter == ITEMFILTER.CANVASSLICE) { query = "SELECT * FROM canvasitem"; if (param != null) { query += " WHERE idcanvasslice='" + param + "' ORDER By clockposition"; } } else if (filter == ITEMFILTER.CANVASPERIOD) { query = "SELECT * FROM canvasslice, canvasperiod_items"; if (param != null) { query += " WHERE idcanvasslice=idslice AND idperiod = '" + param + "' ORDER by position asc"; } } //prepare adapter to run query adapter = new MySqlDataAdapter(query, connection); DataSet DS = new DataSet(); //get query results in dataset adapter.Fill(DS); return DS.Tables[0]; } catch (Exception ex) { Console.WriteLine(ex.Message); } return null; }
public void loadDataGrid(ITEMFILTER filter=ITEMFILTER.NONE, String param="") { items = new DataItems(this); items.loadDataFromDB(filter, param); if (filter == ITEMFILTER.CANVASPERIOD) { this.currentDisplay = ITEMFILTER.CANVASPERIOD; this.grid.Columns.Clear(); this.grid.Columns.Add(newTextCol("id", "id")); this.grid.Columns.Add(newTextCol("label", "label")); this.UIData = items.getUIDataTableCanvasPeriod(); } else if (filter == ITEMFILTER.CANVASSLICE) { this.currentDisplay = ITEMFILTER.CANVASSLICE; this.grid.Columns.Clear(); this.grid.Columns.Add(newTextCol("id", "id")); this.grid.Columns.Add(newTextCol("type", "type")); this.grid.Columns.Add(newTextCol("sliceposition", "sliceposition")); this.grid.Columns.Add(newTextCol("label", "label")); this.grid.Columns.Add(newTextCol("param1", "param1")); this.grid.Columns.Add(newTextCol("param2", "param2")); this.grid.Columns.Add(newTextCol("param3", "param3")); this.UIData = items.getUIDataTableCanvasSlice(); } //CanvasPeriod else { this.currentDisplay = ITEMFILTER.CATEGORY; this.grid.Columns.Clear(); this.grid.Columns.Add(newTextCol("data.ID", "id")); this.grid.Columns.Add(newTextCol("data.label", "label")); this.grid.Columns.Add(newTextCol("data.runtime", "runtime")); this.grid.Columns.Add(newTextCol("data.category", "category")); this.UIData = items.getUIDataTable(); } }
public void loadDataFromDB(ITEMFILTER filter = ITEMFILTER.NONE, String param=null) { try { dbengine = DBengineMySql.GetInstance(); //TODO: CHECK CHANGES??? dataitems.Clear(); dbengine.connect("127.0.0.1", "root", "1234", "ebuplayout-dev2"); _datatable = dbengine.listItems(filter, param); if (filter == ITEMFILTER.CATEGORY) { for (int i = 0; i < _datatable.Rows.Count; i++) { if (_datatable.Rows[i]["type"].ToString() == "AUDIOFILE") { DataRow data = _datatable.Rows[i]; int id = (int)data["iddataitems"]; String artist = data["artist"].ToString(); String title = data["title"].ToString(); String filename = data["file"].ToString(); String radiovis1 = data["radiovis1"].ToString(); String radiovis2 = data["radiovis2"].ToString(); String radiovis3 = data["radiovis3"].ToString(); String radiovis4 = data["radiovis4"].ToString(); String radiovistxt = data["radiovistxt"].ToString(); String category = data["category"].ToString(); TimeSpan tmcue = TimeSpan.FromMilliseconds((int)data["tmcue"]); TimeSpan tmnext = TimeSpan.FromMilliseconds((int)data["tmnext"]); if (tmnext == TimeSpan.Zero) { tmnext = TimeSpan.FromMilliseconds((int)data["runtime"]); } DataSongItem song = new DataSongItem(id, artist, title, filename, new TimeMarker(tmcue, tmnext)); song.category = category; song.setField("radiovis1", radiovis1); song.setField("radiovis2", radiovis2); song.setField("radiovis3", radiovis3); song.setField("radiovis4", radiovis4); song.setField("radiovistxt", radiovistxt); dataitems.Add(id, song); } } } else if(filter == ITEMFILTER.CANVASPERIOD){ slicecanvas.Clear(); for (int i = 0; i < _datatable.Rows.Count; i++) { DataRow data = _datatable.Rows[i]; SliceCanvasItem c = new SliceCanvasItem(data["idcanvasperiod_items"].ToString(), (int)data["position"], data["idslice"].ToString(), data["name"].ToString(), TimeSpan.FromMilliseconds(0)); slicecanvas.Add(Int32.Parse(c.idperioditem), c); } } else if (filter == ITEMFILTER.CANVASSLICE) { sliceitems.Clear(); for (int i = 0; i < _datatable.Rows.Count; i++) { DataRow data = _datatable.Rows[i]; SlotCanvas slot = new SlotCanvas(Int32.Parse(data["idcanvasitem"].ToString()), Int32.Parse(data["idcanvasslice"].ToString()), Int32.Parse(data["clockposition"].ToString()), getSlotCanvasType(data["type"]), data["param1"].ToString(), data["param2"].ToString(), data["param3"].ToString(), data["label"].ToString()); sliceitems.Add(slot.id, slot); } } } catch (Exception e) { MessageBox.Show(e.Message); } }