public static async Task <List <VVideoHistory> > GetVideoHistoryView(this IDbControl control, int order) { var results = new List <VVideoHistory>(); var sql = new StringBuilder(); sql.AppendLine($"SELECT id, tick, count"); sql.AppendLine($"FROM ("); sql.AppendLine($" SELECT ifnull(id, '') id, ifnull(max(tick), 0) tick, count(id) count"); sql.AppendLine($" FROM video_history"); sql.AppendLine($" GROUP BY id"); sql.AppendLine($")"); sql.AppendLine($"ORDER BY {order} desc"); using (var reader = await control.ExecuteReaderAsync(sql.ToString())) { while (await reader.ReadAsync()) { results.Add(new VVideoHistory( reader.GetString(0), // id reader.GetInt64(1), // tick reader.GetInt64(2) // count )); } } return(results); }
public static async Task <List <TVideoHistory> > GetVideoHistory(this IDbControl control) { var results = new List <TVideoHistory>(); var sql = new StringBuilder(); sql.AppendLine($"SELECT id, tick"); sql.AppendLine($"FROM video_history"); using (var reader = await control.ExecuteReaderAsync(sql.ToString())) { while (await reader.ReadAsync()) { results.Add(new TVideoHistory( reader.GetString(0), // id reader.GetInt64(1) // tick )); } } return(results); }
public static async Task <TSetting[]> GetSetting(this IDbControl control) { var results = new List <TSetting>(); var sql = new StringBuilder(); sql.AppendLine($"SELECT cast(key as text), value"); sql.AppendLine($"FROM setting"); using (var reader = await control.ExecuteReaderAsync(sql.ToString())) { while (await reader.ReadAsync()) { results.Add(new TSetting( (SettingKeys)Enum.Parse(typeof(SettingKeys), reader.GetString(0)), // key reader.GetString(1) // value )); } } return(results.ToArray()); }
public static async Task <TFavorite[]> GetFavorite(this IDbControl control) { var results = new List <TFavorite>(); var sql = new StringBuilder(); sql.AppendLine($"SELECT id, tick"); sql.AppendLine($"FROM favorite"); using (var reader = await control.ExecuteReaderAsync(sql.ToString())) { while (await reader.ReadAsync()) { results.Add(new TFavorite( reader.GetString(0), // id reader.GetInt64(1) // tick )); } } return(results.ToArray()); }
public static async Task <TSetting> GetSetting(this IDbControl control, SettingKeys key) { var sql = new StringBuilder(); sql.AppendLine($"SELECT cast(key as text), value"); sql.AppendLine($"FROM setting"); sql.AppendLine($"WHERE key = ?"); using (var reader = await control.ExecuteReaderAsync(sql.ToString(), Sqlite3Util.CreateParameter(DbType.Int32, (int)key))) { if (await reader.ReadAsync()) { return(new TSetting( (SettingKeys)Enum.Parse(typeof(SettingKeys), reader.GetString(0)), // key reader.GetString(1) // value )); } else { return(null); } } }