예제 #1
0
        public void AddCmdParams(DbCommand dbCmd, SqlCommandData data)
        {
            var oraCmd = (OracleCommand)dbCmd;

            if (data.ArrayBindCount > 0)
            {
                oraCmd.ArrayBindCount = data.ArrayBindCount;
            }
            oraCmd.BindByName = data.BindByName;
            foreach (var prm in data.Params)
            {
                var opa = oraCmd.Parameters.Add(prm.name, ToOraDbType(prm.type));
                opa.Value = prm.value;
                var lstF = prm.value as float[];
                if (lstF != null)
                {
                    var sts = new OracleParameterStatus[lstF.Length];
                    for (int i = lstF.Length - 1; i >= 0; i--)
                    {
                        sts[i] = float.IsNaN(lstF[i]) ? OracleParameterStatus.NullInsert : OracleParameterStatus.Success;
                    }
                    opa.ArrayBindStatus = sts;
                }
                else if (Common.Utils.IsEmpty(prm.value))
                {
                    opa.Status = OracleParameterStatus.NullInsert;
                }
            }
        }
예제 #2
0
        public IEnumerable <DbCommand> GetSpecificCommands(DbConnection dbConn, SqlCommandData data)
        {
            var oraCmd = ((OracleConnection)dbConn).CreateCommand();

            oraCmd.CommandText = data.SqlText;

            if (data.ArrayBindCount > 0)
            {
                oraCmd.ArrayBindCount = data.ArrayBindCount;
            }
            oraCmd.BindByName = data.BindByName;
            foreach (var prm in data.Params)
            {
                var opa = oraCmd.Parameters.Add(prm.name, ToOraDbType(prm.type));
                opa.Value = prm.value;
                if (prm.value is float[] lstF)
                {
                    var sts = new OracleParameterStatus[lstF.Length];
                    for (int i = lstF.Length - 1; i >= 0; i--)
                    {
                        sts[i] = float.IsNaN(lstF[i]) ? OracleParameterStatus.NullInsert : OracleParameterStatus.Success;
                    }
                    opa.ArrayBindStatus = sts;
                }
                else if (prm.value is double[] lstD)
                {
                    var sts = new OracleParameterStatus[lstD.Length];
                    for (int i = lstD.Length - 1; i >= 0; i--)
                    {
                        sts[i] = double.IsNaN(lstD[i]) ? OracleParameterStatus.NullInsert : OracleParameterStatus.Success;
                    }
                    opa.ArrayBindStatus = sts;
                }
                else if (prm.value is IList lst)
                {
                    var sts = new OracleParameterStatus[lst.Count];
                    for (int i = lst.Count - 1; i >= 0; i--)
                    {
                        sts[i] = W.Common.Utils.IsEmpty(lst[i]) ? OracleParameterStatus.NullInsert : OracleParameterStatus.Success;
                    }
                    opa.ArrayBindStatus = sts;
                }
                else if (Common.Utils.IsEmpty(prm.value))
                {
                    opa.Status = OracleParameterStatus.NullInsert;
                }
            }
            yield return(oraCmd);
        }
예제 #3
0
        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;
        }
예제 #4
0
        private static DataSet getSchoolLogos(Int32[] schoolsArr)
        {
            OracleConnection cn = null;
            OracleCommand cmd = null;
            OracleDataAdapter adp = null;
            DataSet ds = null;
            int i = 0;

            try
            {
                cn = createConnectionSDR();

                Int32[] bindSize = new Int32[schoolsArr.Length - 1];
                OracleParameterStatus[] bindStatus = new OracleParameterStatus[schoolsArr.Length -1];

                for (i = 0; i < schoolsArr.Length - 1; i++)
                {
                    bindSize[i] = 10;
                    bindStatus[i] = OracleParameterStatus.Success;
                }

                cmd = cn.CreateCommand();
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "IMS.TV_GETS.getImagePath";
                cmd.BindByName = true;

                cmd.Parameters.Add(new OracleParameter("OCURSOR", OracleDbType.RefCursor, ParameterDirection.Output));

                cmd.Parameters.Add(new OracleParameter("INIDARRAY", OracleDbType.Int32));
                cmd.Parameters["INIDARRAY"].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                cmd.Parameters["INIDARRAY"].Value = schoolsArr;
                cmd.Parameters["INIDARRAY"].Size = schoolsArr.Length;
                cmd.Parameters["INIDARRAY"].ArrayBindSize = bindSize;
                cmd.Parameters["INIDARRAY"].ArrayBindStatus = bindStatus;
                cmd.Parameters["INIDARRAY"].Direction = ParameterDirection.Input;

                cmd.Parameters.Add(new OracleParameter("INIMAGETYPE", OracleDbType.Int32, 2, ParameterDirection.Input));
                cmd.Parameters.Add(new OracleParameter("INHIDEFIND", OracleDbType.Varchar2, "N", ParameterDirection.Input));
                cmd.Parameters.Add(new OracleParameter("INGETSWATCH", OracleDbType.Varchar2, "Y", ParameterDirection.Input));

                adp = new OracleDataAdapter(cmd);

                ds = new DataSet();

                DateTime start = DateTime.Now;

                adp.Fill(ds);

                TimeSpan elapsed = DateTime.Now - start;
                Debug.Print("Time to get IMS logos: " + elapsed.ToString());
            }
            catch (Exception ex)
            {
                Debug.Print(ex.Message);
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                if (ds != null) ds.Dispose();
                if (cn != null) cn.Close(); cn.Dispose();
            }

            return ds;
        }