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