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;
}
}
}
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);
}
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;
}
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;
}