public static List<XmlDataRow> GetPlaylistItemData(PlaylistItem playlistItem) { List<XmlDataRow> xmlDataRows = new List<XmlDataRow>(); OracleConnection cnO = null; OracleCommand cmdO = null; OracleDataAdapter adpO = null; OracleCommandBuilder bldrO = null; OracleDataReader rdrO = null; MySqlConnection cnM = null; MySqlCommand cmdM = null; MySqlDataReader rdrM = null; DataTable tbl = null; DataSet ds = null; string teamLogo = " "; string teamSwatch = " "; string teamAbbrev = ""; try { tbl = new DataTable(); List<Int32> teamIds = new List<Int32>(); Int32 teamId = 0; FileInfo queryFile = null; if (playlistItem.Query != null && playlistItem.Query.ToString().Trim() != "") { queryFile = new FileInfo(ConfigurationManager.AppSettings["QueryDirectory"].ToString() + "\\" + playlistItem.Query); } string query = ""; if (queryFile != null) { if (queryFile.Exists) { query = File.ReadAllText(queryFile.FullName); } else { query = playlistItem.Query; } Object[] parms = null; switch (playlistItem.Datasource.ToUpper()) { case "SDR": cnO = createConnectionSDR(); cmdO = new OracleCommand(); cmdO.BindByName = true; //add the parms to the query/stored proc if there are any if (playlistItem.QueryParameters.ToString() != "") { parms = playlistItem.QueryParameters.Split('|'); for (int i = 0; i < parms.Length; i++) { string[] parmval = parms[i].ToString().Split('='); string parm = parmval[0].ToString(); string val = parmval[1].ToString(); if (val.Substring(0, 1) == "'" && val.Substring(val.Length - 1, 1) == "'") { string parmStr = val.Substring(1, val.Length - 2); if (parmStr.Length > 0 && parmStr.Substring(0, 1) == "#") { if (ConfigurationManager.AppSettings[parmStr.Substring(1)] != null) //pull the value from the app.config file { parmStr = ConfigurationManager.AppSettings[parmStr.Substring(1)].ToString(); } } if (parmStr.IndexOf(",") > -1 || (parm.IndexOf('(') > -1 && parm.IndexOf(')') > -1)) //this is an array { cmdO.Parameters.Add(new OracleParameter(parm, OracleDbType.Varchar2)); string[] arrVals = parmStr.Split(','); Int32[] arrSize = new Int32[arrVals.Length - 1]; for (int p = 0; p < arrVals.Length - 1; p++) { arrSize[p] = arrVals[p].Length; } cmdO.Parameters[parm].CollectionType = OracleCollectionType.PLSQLAssociativeArray; cmdO.Parameters[parm].Value = arrVals; cmdO.Parameters[parm].Size = arrVals.Length; cmdO.Parameters[parm].ArrayBindSize = arrSize; OracleParameterStatus[] arrStatus = new OracleParameterStatus[1]; arrStatus[0] = 0; cmdO.Parameters[parm].ArrayBindStatus = arrStatus; cmdO.Parameters[parm].Direction = ParameterDirection.Input; } else { cmdO.Parameters.Add(new OracleParameter(parm, OracleDbType.Varchar2, parmStr, ParameterDirection.Input)); } } else if (val.ToUpper() == "NULL") { cmdO.Parameters.Add(new OracleParameter(parm, OracleDbType.NVarchar2, DBNull.Value, ParameterDirection.Input)); } else { if (val.ToString().ToUpper() != "OTC") { if (val.Substring(0, 1) == "#") { if (ConfigurationManager.AppSettings[val.Substring(1)] != null) { val = ConfigurationManager.AppSettings[val.Substring(1)]; } } cmdO.Parameters.Add(new OracleParameter(parm, OracleDbType.Int32, val, ParameterDirection.Input)); } } if (parm.ToUpper() == "INTEAMID") { //adding a special case for the On The Clock (connected) items, to add the team swatch to the dataset if (val.ToString().ToUpper() == "OTC") { teamId = GlobalCollections.Instance.OnTheClock.Team.ID; cmdO.Parameters.Add(new OracleParameter(parm, OracleDbType.Int32, teamId, ParameterDirection.Input)); } else { if (val.Substring(0, 1) == "#") { if (ConfigurationManager.AppSettings[val.Substring(1)] != null) { val = ConfigurationManager.AppSettings[val.Substring(1)]; } } Int32.TryParse(val, out teamId); } teamIds.Add(teamId); } } } if (playlistItem.QueryType.ToUpper() == "SP") { cmdO.Connection = cnO; cmdO.CommandText = playlistItem.Query; cmdO.CommandType = System.Data.CommandType.StoredProcedure; cmdO.BindByName = true; cmdO.Parameters.Add(new OracleParameter(playlistItem.OutputParameter, OracleDbType.RefCursor, ParameterDirection.Output)); adpO = new OracleDataAdapter(cmdO); ds = new DataSet(); adpO.Fill(ds); tbl = ds.Tables[0]; } else { if (query != "") { cmdO.CommandText = query.ToString(); cmdO.Connection = cnO; rdrO = cmdO.ExecuteReader(); tbl.Load(rdrO); rdrO.Close(); rdrO.Dispose(); } } break; case "MYSQL": { cnM = createConnectionMySql(); cmdM = new MySqlCommand(query.ToString(), cnM); if (playlistItem.QueryType.ToUpper() == "SP") { cmdM.CommandType = System.Data.CommandType.StoredProcedure; if (playlistItem.QueryParameters.ToString() != "") { parms = playlistItem.QueryParameters.Split('|'); for (int i = 0; i < parms.Length; i++) { string[] parmval = parms[i].ToString().Split('='); string parm = parmval[0].ToString(); string val = parmval[1].ToString(); if (parm.ToUpper() == "INTEAMID") { //adding a special case for the On The Clock (connected) items, to add the team swatch to the dataset if (val.ToString().ToUpper() == "OTC") { teamId = GlobalCollections.Instance.OnTheClock.Team.ID; val = teamId.ToString(); } else { if (val.Substring(0, 1) == "#") { if (ConfigurationManager.AppSettings[val.Substring(1)] != null) { val = ConfigurationManager.AppSettings[val.Substring(1)]; } } Int32.TryParse(val, out teamId); } teamIds.Add(teamId); } cmdM.Parameters.AddWithValue(parm, val); } } } rdrM = cmdM.ExecuteReader(); tbl.Load(rdrM); rdrM.Close(); rdrM.Dispose(); } break; case "WS": parms = playlistItem.QueryParameters.Split('|'); Object[] wsParms = new Object[parms.Length]; for (int i = 0; i < parms.Length; i++) { string[] parmval = parms[i].ToString().Split('='); string parm = parmval[0].ToString(); string val = parmval[1].ToString(); val = val.Replace("'", ""); if (val.Length > 0 && val.Substring(0, 1) == "#") { if (ConfigurationManager.AppSettings[val.Substring(1)] != null) { val = ConfigurationManager.AppSettings[val.Substring(1)]; } } wsParms[i] = val; } DataSet dsTemp = null; try { dsTemp = WebService.CallFunctionByName(playlistItem.Query, wsParms); } catch { Debug.Print("Web Service call failed"); dsTemp = null; } if (dsTemp != null) { tbl = dsTemp.Tables[0]; } break; } } if (playlistItem.AdditionalDataFields != null) { foreach (KeyValuePair<string, string> pair in playlistItem.AdditionalDataFields) { if (pair.Key.ToString().ToUpper().IndexOf("INTEAMID") > -1) { string val = pair.Value; if (val.Substring(0, 1) == "#") { if (ConfigurationManager.AppSettings[val.Substring(1)] != null) { val = ConfigurationManager.AppSettings[val.Substring(1)]; } } Int32.TryParse(val, out teamId); teamIds.Add(teamId); } } } if (tbl.Rows.Count == 0 && playlistItem.Query != null) { Debug.Print("No data returned by " + playlistItem.Query); } XmlDataRow xmlDataRow; switch (playlistItem.MaxRows) { case 0: //just add a blank data row xmlDataRow = new XmlDataRow(); xmlDataRows.Add(xmlDataRow); break; case 1: foreach (DataRow row in tbl.Rows) { //DataRow row = tbl.Rows[playlistItem.RowIndex]; xmlDataRow = new XmlDataRow(); foreach (DataColumn col in tbl.Columns) { xmlDataRow.Add(col.ColumnName, row[col.ColumnName].ToString()); } if (playlistItem.Description.ToUpper() != "PROMPTER") { xmlDataRow.Add("PANEL_TYPE", playlistItem.PanelType); xmlDataRow.Add("PAGE_TYPE", playlistItem.PageType); } if (playlistItem.AdditionalDataFields != null) { foreach (KeyValuePair<string, string> pair in playlistItem.AdditionalDataFields) { if (pair.Key.ToString().ToUpper().IndexOf("INTEAMID") == -1) //non-teamid items { //possibly put a db field to determine if additional fields should be loaded only on the first item if (playlistItem.CurrentRow == 0) { xmlDataRow.Add(pair.Key, pair.Value); } } } } xmlDataRows.Add(xmlDataRow); } break; default: xmlDataRow = new XmlDataRow(); int count = 1; if (tbl.Rows.Count > 0) { foreach (DataRow row in tbl.Rows) { if (count > playlistItem.MaxRows) { xmlDataRows.Add(xmlDataRow); xmlDataRow = new XmlDataRow(); count = 1; } foreach (DataColumn col in tbl.Columns) { xmlDataRow.Add(col.ColumnName + "_" + count.ToString(), row[col.ColumnName].ToString()); } xmlDataRow.Add("PANEL_TYPE", playlistItem.PanelType); xmlDataRow.Add("PAGE_TYPE", playlistItem.PageType); if (playlistItem.AdditionalDataFields != null) { foreach (KeyValuePair<string, string> pair in playlistItem.AdditionalDataFields) { //possibly put a db field to determine if additional fields should be loaded only on the first item if (playlistItem.CurrentRow == 0) { xmlDataRow.Add(pair.Key, pair.Value); } } } count++; } ////blank out any extra fields that aren't filled... for (int i = count; i <= playlistItem.MaxRows; i++) { foreach (DataColumn col in tbl.Columns) { xmlDataRow.Add(col.ColumnName + "_" + i.ToString(), ""); } } xmlDataRows.Add(xmlDataRow); //adds in the last row } break; } //add all the team data to each xmlDataRow (so the team info is included with each data row) if (teamIds.Count > 0) { for (var i = 0; i < teamIds.Count; i++) { Team team = null; if (GlobalCollections.Instance.Teams != null) { team = (Team)GlobalCollections.Instance.Teams.SingleOrDefault(t => t.ID == teamIds[i]); if (team == null) { team = (Team)GlobalCollections.Instance.Schools.SingleOrDefault(t => t.ID == teamIds[i]); } } else { team = (Team)GlobalCollections.Instance.Schools.SingleOrDefault(t => t.ID == teamIds[i]); } if (team != null) { teamLogo = team.LogoTgaNoKey.LocalPath; FileInfo file = new FileInfo(teamLogo); if (file.Exists == false) { teamLogo = " "; } teamSwatch = team.SwatchTga.LocalPath; file = new FileInfo(teamSwatch); if (file.Exists == false) { teamSwatch = " "; } teamAbbrev = team.Tricode; foreach (XmlDataRow xmlRow in xmlDataRows) { xmlRow.Add("LOGO_" + (i + 1).ToString(), teamLogo); xmlRow.Add("ABBREV_4_" + (i + 1).ToString(), teamAbbrev); xmlRow.Add("SWATCH_" + (i + 1).ToString(), teamSwatch); xmlRow.Add("VENT_SWATCH_" + (i + 1).ToString(), teamSwatch); } } } } } catch (Exception ex) { SetStatusBarMsg("Error getting playlist item data (item " + playlistItem.PlaylistOrder.ToString() + "): " + ex.Message, "Red"); xmlDataRows.Clear(); } finally { if (cmdO != null) cmdO.Dispose(); if (cmdM != null) cmdM.Dispose(); if (tbl != null) tbl.Dispose(); if (cnO != null) { cnO.Close(); cnO.Dispose(); } if (cnM != null) { cnM.Close(); cnM.Dispose(); } } return xmlDataRows; }
public static ObservableCollection<PlaylistItem> GetPlaylistItems(int playlistId) { ObservableCollection<PlaylistItem> playlistItems = new ObservableCollection<PlaylistItem>(); MySqlConnection cn = null; MySqlCommand cmd = null; MySqlDataReader rdr = null; DataTable tbl = null; try { cn = createConnectionMySql(); String sql = "select * from playlistitems where playlistid = " + playlistId + " order by playlistorder asc"; cmd = new MySqlCommand(sql, cn); rdr = cmd.ExecuteReader(); tbl = new DataTable(); tbl.Load(rdr); rdr.Close(); rdr.Dispose(); PlaylistItem playlistItem; foreach (DataRow row in tbl.Rows) { playlistItem = new PlaylistItem(); playlistItem.Template = row["template"].ToString(); playlistItem.PanelType = row["paneltype"].ToString(); playlistItem.PageType = row["pagetype"].ToString(); playlistItem.Description = row["desc"].ToString(); playlistItem.Datasource = row["datasource"].ToString(); playlistItem.QueryType = row["querytype"].ToString(); playlistItem.QueryParameters = row["queryparameters"].ToString(); playlistItem.OutputParameter = row["outputparameter"].ToString(); if (row["enabled"] != DBNull.Value) { playlistItem.Enabled = Convert.ToBoolean(row["enabled"]); } else { playlistItem.Enabled = false; } if (row["mergedatanotransitions"] != DBNull.Value) { playlistItem.MergeDataNoTransitions = Convert.ToBoolean(row["mergedatanotransitions"]); } else { playlistItem.MergeDataNoTransitions = false; } if (row["playlistorder"] != DBNull.Value) { playlistItem.PlaylistOrder = Convert.ToInt16(row["playlistorder"]); } else { playlistItem.PlaylistOrder = 9999; } if (row["duration"] != DBNull.Value) { playlistItem.Duration = Convert.ToDecimal(row["duration"]); } else { playlistItem.Duration = 5; } playlistItem.Query = row["query"].ToString(); if (row["maxrows"] != DBNull.Value) { playlistItem.MaxRows = Convert.ToInt16(row["maxrows"]); } else { playlistItem.MaxRows = 1; } //if (row["rowindex"] != DBNull.Value) //{ // playlistItem.RowIndex = Convert.ToInt16(row["rowindex"]); //} //else //{ // playlistItem.RowIndex = 0; //} if (row["additionaldatafields"].ToString().Trim() != "") { string[] additionalDataFields = row["additionaldatafields"].ToString().Split('|'); if (additionalDataFields.Length > 0) { playlistItem.AdditionalDataFields = new Dictionary<string, string>(); for (int i = 0; i < additionalDataFields.Length; i++) { string[] keyVal = additionalDataFields[i].ToString().Split('='); playlistItem.AdditionalDataFields.Add(keyVal[0], keyVal[1].Trim()); } } } playlistItems.Add(playlistItem); } } finally { if (cmd != null) cmd.Dispose(); if (tbl != null) tbl.Dispose(); if (cn != null) cn.Close(); cn.Dispose(); } return playlistItems; }