private async Task <IEnumerable <Channel> > GetAsync(string command)
{
List <Channel> channels = new List <Channel>();
await DBFactory.SQL_ExecuteReaderAsync(WebApiConfig.ConnectionString, command, System.Data.CommandType.Text, null, dr =>
{
var clCtrl = new ChannelLogoController();
while (dr.Read())
{
var chan = new Channel();
chan.ServiceID = int.Parse(dr.GetString(0));
chan.ChannelNumber = dr.GetInt32(1);
chan.CallSign = dr.GetString(2);
chan.StationName = dr.GetString(3);
chan.Logo = clCtrl.getLogoInfo(dr.GetInt32(4));
chan.Version = dr.IsDBNull(5) ? null : dr.GetString(5);
chan.VHO = dr.IsDBNull(6) ? null : dr.GetString(6);
chan.LastUpdate = dr.IsDBNull(7) ? (DateTime?)null : dr.GetDateTime(7);
chan.RegionID = int.Parse(dr.GetString(8));
chan.RegionName = dr.IsDBNull(9) ? null : dr.GetString(9);
chan.StationDescription = dr.IsDBNull(10) ? null : dr.GetString(10);
chan.UniqueString = dr.IsDBNull(11) ? null : dr.GetString(11);
chan.IsVirtual = dr.GetBoolean(12);
channels.Add(chan);
}
});
return(channels);
}
private async Task <IEnumerable <Channel> > getChannelWithInactiveLogo(double version = 1.9, int?vhoId = null, int?regionId = null, string regionName = "")
{
StringBuilder command = new StringBuilder();
StringBuilder conditions = new StringBuilder();
command.AppendFormat("SELECT intBitmapId FROM vChannelMap WHERE strFIOSVersionAliasId = '{0}'", version);
if (null != vhoId)
{
conditions.AppendFormat(" AND strVHOId = 'VHO{0}'", vhoId);
}
if (null != regionId)
{
conditions.AppendFormat(" AND strFIOSRegionId = '{0}'", regionId);
}
if (!string.IsNullOrEmpty(regionName))
{
conditions.AppendFormat(" AND strFIOSRegionName LIKE '{0}'", regionName);
}
command.Append(conditions.ToString());
var clCtrl = new ChannelLogoController();
var missingIdsFromDir = await clCtrl.getMissingBitmapIds();
List <int> existingIdsFromDB = new List <int>();
await DBFactory.SQL_ExecuteReaderAsync(WebApiConfig.ConnectionString, command.ToString(), System.Data.CommandType.Text, null, dr =>
{
while (dr.Read())
{
existingIdsFromDB.Add(dr.GetInt32(0));
}
});
List <int> assignedMissingIds = existingIdsFromDB.Intersect(missingIdsFromDir).ToList();
var strJoined = String.Join(",", assignedMissingIds);
command.Clear();
command.AppendFormat("SELECT * FROM vChannelMap WHERE strFIOSVersionAliasId = '{0}' AND intBitmapId IN ({1})", version, strJoined);
command.Append(conditions.ToString());
return(await GetAsync(command.ToString()));
}
private IEnumerable <Channel> Get(string command)
{
foreach (var record in DBFactory.SQL_ExecuteReader(WebApiConfig.ConnectionString, command, System.Data.CommandType.Text))
{
var chan = new Channel();
var clCtrl = new ChannelLogoController();
chan.ServiceID = int.Parse(record.GetString(0));
chan.ChannelNumber = record.GetInt32(1);
chan.CallSign = record.GetString(2);
chan.StationName = record.GetString(3);
chan.Logo = clCtrl.getLogoInfoAsync(record.GetInt32(4)).Result ?? clCtrl.getLogoInfoAsync(record.GetInt32(4), false).Result;
chan.Version = record.GetString(5);
chan.VHO = record.GetString(6);
chan.LastUpdate = record.IsDBNull(7) ? (DateTime?)null : record.GetDateTime(7);
chan.RegionID = int.Parse(record.GetString(8));
chan.RegionName = record.IsDBNull(9) ? null : record.GetString(9);
chan.StationDescription = record.IsDBNull(10) ? null : record.GetString(10);
chan.UniqueString = record.IsDBNull(11) ? null : record.GetString(11);
yield return(chan);
}
}