public void Set(TaxonomicClassification taxonomy, WolframCommonNameData commonNameData)
{
using (var sqlConnection = new SqlConnection(WhatIsThatDbConnString))
{
sqlConnection.Open();
var cmd = new SqlCommand("UPDATE TaxonomyToName " +
"SET CommonName=@CommonName " +
"WHERE Kingdom=@Kingdom AND Phylum=@Phylum AND Class=@Class AND [Order]=@Order " +
"AND Family=@Family AND Genus=@Genus AND Species=@Species " +
"IF @@ROWCOUNT=0 INSERT INTO TaxonomyToName (CommonName, Kingdom, Phylum, Class, [Order], Family, Genus, Species) " +
"VALUES (@CommonName, @Kingdom, @Phylum, @Class, @Order, @Family, @Genus, @Species)")
{
CommandType = CommandType.Text,
Connection = sqlConnection
};
cmd.Parameters.AddWithValue("@CommonName", commonNameData.Name.Trim().ToLower());
cmd.Parameters.AddWithValue("@Kingdom", taxonomy.GetKingdom().Trim().ToLower());
cmd.Parameters.AddWithValue("@Phylum", taxonomy.GetPhylum().Trim().ToLower());
cmd.Parameters.AddWithValue("@Class", taxonomy.GetClass().Trim().ToLower());
cmd.Parameters.AddWithValue("@Order", taxonomy.GetOrder().Trim().ToLower());
cmd.Parameters.AddWithValue("@Family", taxonomy.GetFamily().Trim().ToLower());
cmd.Parameters.AddWithValue("@Genus", taxonomy.GetGenus().Trim().ToLower());
cmd.Parameters.AddWithValue("@Species", taxonomy.GetSpecies().Trim().ToLower());
cmd.ExecuteNonQuery();
}
var cacheKey = GenerateCommonNameCacheKey(taxonomy);
CommonNameCache.Set(cacheKey, commonNameData);
}
private WolframCommonNameData GetCommonNameDataFromDb(TaxonomicClassification taxonomy)
{
using (var sqlConnection = new SqlConnection(WhatIsThatDbConnString))
{
sqlConnection.Open();
var cmd = new SqlCommand("SELECT TOP 1 * FROM TaxonomyToName WHERE Kingdom=@Kingdom AND Phylum=@Phylum AND " +
"Class=@Class AND [Order]=@Order AND Family=@Family AND Genus=@Genus AND Species=@Species ")
{
CommandType = CommandType.Text,
Connection = sqlConnection
};
cmd.Parameters.AddWithValue("@Kingdom", taxonomy.GetKingdom().Trim().ToLower());
cmd.Parameters.AddWithValue("@Phylum", taxonomy.GetPhylum().Trim().ToLower());
cmd.Parameters.AddWithValue("@Class", taxonomy.GetClass().Trim().ToLower());
cmd.Parameters.AddWithValue("@Order", taxonomy.GetOrder().Trim().ToLower());
cmd.Parameters.AddWithValue("@Family", taxonomy.GetFamily().Trim().ToLower());
cmd.Parameters.AddWithValue("@Genus", taxonomy.GetGenus().Trim().ToLower());
cmd.Parameters.AddWithValue("@Species", taxonomy.GetSpecies().Trim().ToLower());
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var commonName = reader["CommonName"].ToString();
return(WolframCommonNameData.GetInstance(commonName));
}
}
}
}
return(null);
}