public async Task UpdateAsync(FiosStation obj)
{
using (var connection = await DapperFactory.GetOpenConnectionAsync())
{
await connection.UpdateAsync <FiosStation>(obj);
}
}
public async Task <FiosStationGenre> GetByGenreId(int id)
{
using (var connection = await DapperFactory.GetOpenConnectionAsync())
{
return(await connection.GetAsync <FiosStationGenre>(id));
}
}
public async Task <IEnumerable <FiosRegion> > GetAllAsync()
{
using (var connection = await DapperFactory.GetOpenConnectionAsync())
{
return(await connection.GetAllAsync <FiosRegion>());
}
}
public async Task <FiosStation> FindByIDAsync(string id)
{
using (var connection = await DapperFactory.GetOpenConnectionAsync())
{
return(await connection.GetAsync <FiosStation>(id));
}
}
public async Task <long> AddAsync(FiosStation obj)
{
using (var connection = await DapperFactory.GetOpenConnectionAsync())
{
return(await connection.InsertAsync <FiosStation>(obj));
}
}
public async Task <int> UpdateBitmap(string FiosServiceId, int newBitmapId)
{
using (var connection = await DapperFactory.GetOpenConnectionAsync())
{
string query = "SELECT * FROM tFIOSBitmapStationMap WHERE strFIOSServiceId = @id AND strFIOSVersionAliasId = @version";
var bmDTO = await connection.QueryFirstOrDefaultAsync <BitmapStationMapDTO>(query, new { id = FiosServiceId, version = this._version });
if (bmDTO == null || bmDTO.intBitmapId == 0)
{
bmDTO = new BitmapStationMapDTO();
bmDTO.intBitmapId = newBitmapId;
bmDTO.strFIOSServiceId = FiosServiceId;
bmDTO.strFIOSVersionAliasId = this._version;
bmDTO.dtCreateDate = DateTime.Now;
bmDTO.dtLastUpdateDate = DateTime.Now;
return(await connection.InsertAsync <BitmapStationMapDTO>(bmDTO));
}
else
{
query = @"
UPDATE tFIOSBitmapStationMap SET dtCreateDate = @date, dtLastUpdateDate = @date, intBitmapId = @bmid
WHERE strFIOSServiceId = @id";
return(await connection.ExecuteAsync(query, new { date = DateTime.Now, bmid = newBitmapId, id = FiosServiceId }));
}
}
}
public async Task <IEnumerable <string> > GetAllIdsAsync()
{
var query = getQuery(this._version).Replace("*", "a.strFIOSServiceId");
using (var connection = await DapperFactory.GetOpenConnectionAsync())
{
return(await connection.QueryAsync <string>(query.ToString()));
}
}
public async Task <IEnumerable <string> > GetActiveRegions()
{
var query = string.Format("SELECT DISTINCT strFIOSRegionName FROM {0} WHERE strVHOId IN ({1})", TABLENAME, string.Join(',', this._vhos.Select(x => '\'' + x + '\'')));
using (var connection = await DapperFactory.GetOpenConnectionAsync())
{
return(await connection.QueryAsync <string>(query, new { vhos = string.Join(',', this._vhos) }));
}
}
public async Task <IEnumerable <ChannelDTO> > GetByVHOId(string id)
{
var query = getQuery(this._version);
query.AppendFormat("AND a.strVHOId = 'VHO{0}'", id);
using (var connection = await DapperFactory.GetOpenConnectionAsync())
{
return(await connection.QueryAsync <ChannelDTO>(query.ToString()));
}
}
public async Task <IEnumerable <ChannelDTO> > FindAllByIDAsync(string id)
{
var query = getQuery(this._version);
query.AppendFormat("AND a.strFIOSServiceId = {0}", id);
using (var connection = await DapperFactory.GetOpenConnectionAsync())
{
return(await connection.QueryAsync <ChannelDTO>(query.ToString()));
}
}
public async Task <IEnumerable <ChannelDTO> > GetByCallSign(string name)
{
var query = getQuery(this._version);
query.AppendFormat("AND a.strStationCallSign LIKE '%{0}%'", name);
using (var connection = await DapperFactory.GetOpenConnectionAsync())
{
return(await connection.QueryAsync <ChannelDTO>(query.ToString()));
}
}
public async Task <IEnumerable <ChannelDTO> > GetLikeColumn(string value, string columnName)
{
var query = getQuery(this._version);
query.AppendFormat("AND a.{0} LIKE '%{1}%'", columnName, value);
using (var connection = await DapperFactory.GetOpenConnectionAsync())
{
return(await connection.QueryAsync <ChannelDTO>(query.ToString()));
}
}
/// <summary>
/// Returns all stations that are mapped to the Bitmap ID
/// </summary>
/// <param name="bitmapId">Bitmap ID</param>
/// <returns>Fios stations mapped to the provided Bitmap ID</returns>
public async Task <IEnumerable <dynamic> > GetStationsByBitmapId(int bitmapId)
{
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT * FROM vChannels");
query.AppendLine("WHERE intBitMapId = @id AND strFIOSVersionAliasId = @version");
using (var connection = await DapperFactory.GetOpenConnectionAsync())
{
var result = await connection.QueryAsync <dynamic>(query.ToString(), new { id = bitmapId, version = this._version });
return(result.Select(x => new {
x.strFIOSServiceId,
x.strStationName,
x.strStationCallSign,
x.strVHOId,
x.strFIOSRegionName,
x.intChannelPosition
}).Distinct());
}
}
/// <summary>
/// Updates the station's assigned bitmap ID and version
/// </summary>
/// <param name="newBitmapId"></param>
/// <param name="logo"></param>
/// <returns></returns>
public async Task <int> UpdateChannelBitmap(int newBitmapId, Image logo)
{
int retVal = 0;
using (var connection = await DapperFactory.GetOpenConnectionAsync())
using (var trans = connection.BeginTransaction())
{
try
{
retVal += await updateBitmapDTO(connection, trans, newBitmapId, logo);
retVal += await updateBitmapVersionDTO(connection, trans, newBitmapId, logo);
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
trans.Commit();
}
return(retVal);
}