public void Set(String tag, WolframTaxonomyData taxonomyData)
{
using (var sqlConnection = new SqlConnection(WhatIsThatDbConnString))
{
sqlConnection.Open();
var cmd = new SqlCommand("UPDATE TagToTaxonomy " +
"SET Kingdom=@Kingdom, Phylum=@Phylum, Class=@Class, [Order]=@Order, Family=@Family, " +
"Genus=@Genus, Species=@Species " +
"WHERE Tag=@Tag " +
"IF @@ROWCOUNT=0 INSERT INTO TagToTaxonomy (Tag, Kingdom, Phylum, Class, [Order], Family, Genus, Species) " +
"VALUES (@Tag, @Kingdom, @Phylum, @Class, @Order, @Family, @Genus, @Species)")
{
CommandType = CommandType.Text,
Connection = sqlConnection
};
cmd.Parameters.AddWithValue("@Tag", tag.Trim().ToLower());
cmd.Parameters.AddWithValue("@Kingdom", taxonomyData.Kingdom);
cmd.Parameters.AddWithValue("@Phylum", taxonomyData.Phylum);
cmd.Parameters.AddWithValue("@Class", taxonomyData.Class);
cmd.Parameters.AddWithValue("@Order", taxonomyData.Order);
cmd.Parameters.AddWithValue("@Family", taxonomyData.Family);
cmd.Parameters.AddWithValue("@Genus", taxonomyData.Genus);
cmd.Parameters.AddWithValue("@Species", taxonomyData.Species);
cmd.ExecuteNonQuery();
}
var cacheKey = GenerateTaxonomicDataCacheKey(tag);
TaxonomicDataCache.Set(cacheKey, taxonomyData);
}
private void UpdateDatabaseWithTagToTaxonomicInfo(String tagName, WolframTaxonomyData taxonomyData)
{
var whatIsThatDbConnString = ConfigurationManager.AppSettings["whatisthatdb_connection"];
using (var sqlConnection = new SqlConnection(whatIsThatDbConnString))
{
sqlConnection.Open();
var cmd = new SqlCommand("UPDATE TagToTaxonomy " +
"SET Kingdom=@Kingdom, Phylum=@Phylum, Class=@Class, [Order]=@Order, Family=@Family, " +
"Genus=@Genus, Species=@Species " +
"WHERE Tag=@Tag " +
"IF @@ROWCOUNT=0 INSERT INTO TagToTaxonomy (Tag, Kingdom, Phylum, Class, [Order], Family, Genus, Species) " +
"VALUES (@Tag, @Kingdom, @Phylum, @Class, @Order, @Family, @Genus, @Species)")
{
CommandType = CommandType.Text,
Connection = sqlConnection
};
cmd.Parameters.AddWithValue("@Tag", tagName.Trim().ToLower());
cmd.Parameters.AddWithValue("@Kingdom", taxonomyData.Kingdom);
cmd.Parameters.AddWithValue("@Phylum", taxonomyData.Phylum);
cmd.Parameters.AddWithValue("@Class", taxonomyData.Class);
cmd.Parameters.AddWithValue("@Order", taxonomyData.Order);
cmd.Parameters.AddWithValue("@Family", taxonomyData.Family);
cmd.Parameters.AddWithValue("@Genus", taxonomyData.Genus);
cmd.Parameters.AddWithValue("@Species", taxonomyData.Species);
cmd.ExecuteNonQuery();
}
}
private WolframTaxonomyData GetTaxonomyDataFromDB(String tag)
{
using (var sqlConnection = new SqlConnection(WhatIsThatDbConnString))
{
sqlConnection.Open();
var cmd = new SqlCommand("SELECT TOP 1 * FROM TagToTaxonomy WHERE Tag=@Tag ")
{
CommandType = CommandType.Text,
Connection = sqlConnection
};
cmd.Parameters.AddWithValue("@Tag", tag.Trim().ToLower());
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var kingdom = reader["Kingdom"].ToString();
var phylum = reader["Phylum"].ToString();
var tclass = reader["Class"].ToString();
var order = reader["Order"].ToString();
var family = reader["Family"].ToString();
var genus = reader["Genus"].ToString();
var species = reader["Species"].ToString();
return(WolframTaxonomyData.GetInstance(kingdom, phylum, tclass, order, family, genus, species));
}
}
}
}
return(null);
}
public void PopulateImageTagToTaxonomicDataDb()
{
var cacheDirectoryInfo = new DirectoryInfo(Directory.GetCurrentDirectory() + "/WolframCache/TaxonomicData");
var cacheFilesList = cacheDirectoryInfo.GetFiles("*.xml");
foreach (var fileInfo in cacheFilesList)
{
var responseDto = ReadFromXmlFile <WolframResponseDto>(fileInfo.FullName);
var taxonomyData = WolframTaxonomyData.GetInstance(responseDto);
var tagName = fileInfo.Name.Replace("WolframResponseDto_", "").Replace(".xml", "");
UpdateDatabaseWithTagToTaxonomicInfo(tagName, taxonomyData);
}
}
public TaxonomicClassification GetTaxonomyData(String tag)
{
var cachedTaxonomyData = TaxonomicDataCache.Get(tag);
if (cachedTaxonomyData != null)
{
return(TaxonomicClassification.GetInstance(cachedTaxonomyData.Kingdom, cachedTaxonomyData.Phylum,
cachedTaxonomyData.Class, cachedTaxonomyData.Order, cachedTaxonomyData.Family,
cachedTaxonomyData.Genus, cachedTaxonomyData.Species));
}
var parameters = new List <Parameter>();
var tagParameter = new Parameter {
Name = "tag", Value = tag, Type = ParameterType.QueryString
};
parameters.Add(tagParameter);
var taxonomicDataDto = ExecuteGetRequest <WolframResponseDto>(TaxonomicDataPath, parameters);
if (taxonomicDataDto != null)
{
var wolframTaxonomyData = WolframTaxonomyData.GetInstance(taxonomicDataDto);
TaxonomicDataCache.Set(tag, wolframTaxonomyData);
return(TaxonomicClassification.GetInstance(wolframTaxonomyData.Kingdom, wolframTaxonomyData.Phylum,
wolframTaxonomyData.Class, wolframTaxonomyData.Order, wolframTaxonomyData.Family,
wolframTaxonomyData.Genus, wolframTaxonomyData.Species));
}
var nullData = WolframTaxonomyData.NULL;
return(TaxonomicClassification.GetInstance(nullData.Kingdom, nullData.Phylum,
nullData.Class, nullData.Order, nullData.Family,
nullData.Genus, nullData.Species));
}
