Exemple #1
0
        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);
        }
Exemple #2
0
        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);
        }