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);
}
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 String GenerateCommonNameCacheKey(TaxonomicClassification taxonomy)
{
var keyBuffer = new StringBuilder();
keyBuffer.Append(taxonomy.GetGenus());
keyBuffer.Append("_");
keyBuffer.Append(taxonomy.GetSpecies());
return(keyBuffer.ToString());
}
public String GetCommonNameFromScientific(TaxonomicClassification taxonomy)
{
//Both are required
if (String.IsNullOrEmpty(taxonomy.GetGenus()) || String.IsNullOrWhiteSpace(taxonomy.GetSpecies()))
{
return("");
}
var cachedNameInfo = CommonNameCache.Get(taxonomy);
if (cachedNameInfo != null)
{
return(cachedNameInfo.Name);
}
var parameters = new List <Parameter>();
var genusParam = new Parameter {
Name = "genus", Value = taxonomy.GetGenus(), Type = ParameterType.QueryString
};
parameters.Add(genusParam);
var speciesParam = new Parameter {
Name = "species", Value = taxonomy.GetSpecies(), Type = ParameterType.QueryString
};
parameters.Add(speciesParam);
var nameDataDto = ExecuteGetRequest <WolframResponseDto>(CommonNameDataPath, parameters);
if (nameDataDto != null)
{
var nameData = WolframCommonNameData.GetInstance(nameDataDto);
CommonNameCache.Set(taxonomy, nameData);
return(nameData.Name);
}
return("");
}