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