public static async Task <IEnumerable <ITaxon> > GetSubtaxaAsync(this SQLiteDatabase database, ITaxon taxon)
{
List <ITaxon> result = new List <ITaxon>();
string tableName = GetTableNameForRank(taxon.GetChildRank());
string parentColumnName = GetFieldNameForRank(taxon.GetRank());
if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(parentColumnName) || !taxon.Id.HasValue)
{
return(Enumerable.Empty <ITaxon>());
}
string query = "SELECT * FROM {0} WHERE {1} = $parent_id";
using (SQLiteCommand cmd = new SQLiteCommand(string.Format(query, tableName, parentColumnName))) {
cmd.Parameters.AddWithValue("$parent_id", taxon.Id);
foreach (DataRow row in await database.GetRowsAsync(cmd))
{
result.Add(await database.CreateTaxonFromDataRowAsync(row, taxon.GetChildRank()));
}
}
// Sort taxa alphabetically by name.
result.Sort((lhs, rhs) => lhs.Name.CompareTo(rhs.Name));
return(result);
}
public static async Task <IEnumerable <ITaxon> > GetTaxaAsync(this SQLiteDatabase database, string name, TaxonRankType rank)
{
if (rank == TaxonRankType.Any)
{
return(await database.GetTaxaAsync(name));
}
else
{
List <ITaxon> taxa = new List <ITaxon>();
string tableName = GetTableNameForRank(rank);
if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(name))
{
return(Enumerable.Empty <ITaxon>());
}
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("SELECT * FROM {0} WHERE name = $name OR common_name = $name COLLATE NOCASE", tableName))) {
cmd.Parameters.AddWithValue("$name", name.ToLowerInvariant());
foreach (DataRow row in await database.GetRowsAsync(cmd))
{
taxa.Add(await database.CreateTaxonFromDataRowAsync(row, rank));
}
}
return(taxa);
}
}
public static async Task <IEnumerable <ITaxon> > GetTaxaAsync(this SQLiteDatabase database, TaxonRankType rank)
{
List <ITaxon> taxa = new List <ITaxon>();
string tableName = GetTableNameForRank(rank);
if (string.IsNullOrEmpty(tableName))
{
return(Enumerable.Empty <ITaxon>());
}
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("SELECT * FROM {0}", tableName)))
foreach (DataRow row in await database.GetRowsAsync(cmd))
{
taxa.Add(await database.CreateTaxonFromDataRowAsync(row, rank));
}
return(taxa);
}
public static async Task <ITaxon> GetTaxonAsync(this SQLiteDatabase database, long?id, TaxonRankType rank)
{
string tableName = GetTableNameForRank(rank);
if (string.IsNullOrEmpty(tableName) || !id.HasValue)
{
return(null);
}
ITaxon taxon = null;
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("SELECT * FROM {0} WHERE id = $id", tableName))) {
cmd.Parameters.AddWithValue("$id", id);
DataRow row = await database.GetRowAsync(cmd);
if (row != null)
{
taxon = await database.CreateTaxonFromDataRowAsync(row, rank);
}
}
return(taxon);
}
