Пример #1
0
        /* Build a SQL insert statement supporting multiple insert values, without duplicating any entries:
         *   MERGE INTO dbo.Tags AS Target
         *   USING(VALUES (@param1, @param2),(@param1, @param3)) AS Source (Name, Tag)
         *   ON Target.Name = Source.Name AND Target.Tag = Source.Tag
         *   WHEN NOT MATCHED BY Target THEN
         *   INSERT(Name, Tag) VALUES(Source.Name, Source.Tag);
         *
         *  After substitution:
         *  USING(VALUES ('AA Battery', 'aa'),('AA Battery', 'battery')) AS Source (Name, Tag)
         */
        private static int InsertTags(SqlConnection connection, string item, TagSet tagSet)
        {
            string insertTagsCommand = $@"
MERGE INTO dbo.Tags AS Target
USING(VALUES {string.Join(",", tagSet.Select((_, index) => $"(@param1, @param{index + 2})"))}) AS Source (NameKey, Tag)
ON Target.NameKey = Source.NameKey AND Target.Tag = Source.Tag
WHEN NOT MATCHED BY Target THEN
INSERT(NameKey, Tag) VALUES(Source.NameKey, Source.Tag);";

            int tagsAdded = 0;

            using (SqlCommand sqlCommand = new SqlCommand())
            {
                sqlCommand.Connection  = connection;
                sqlCommand.CommandText = insertTagsCommand;
                sqlCommand.Parameters.AddWithValue("@param1", QueryHelper.Instance.SingularizeAndLower(item));
                int i = 2;
                foreach (string tag in tagSet)
                {
                    sqlCommand.Parameters.AddWithValue($"@param{i++}", tag);
                }
                tagsAdded = sqlCommand.ExecuteNonQuery();
            }

            return(tagsAdded);
        }
Пример #2
0
        public static BundleWithResponse BundleWithTags(string text, int quantity, SqlConnection connection, ILogger log)
        {
            string[] itemAndTags = text.Split(" with tags ", StringSplitOptions.RemoveEmptyEntries);

            if (itemAndTags.Length != 2)
            {
                return(new BundleWithResponse());
            }

            string newItem          = itemAndTags[0].Trim();
            TagSet existingItemTags = new TagSet(itemAndTags[1]);

            // Take a string of words: "Green motor driver"
            // Extract a HashSet of tags: HashSet<string> = { "Green", "motor", "driver" }
            // Format as params for SQL query, to defend against SQL injection attacks:
            //     "@param2,@param3,@param4"
            string paramList = string.Join(",", existingItemTags.Select((tag, index) => $"@param{index+2}"));

            log.LogInformation(paramList);

            int maxResults = 3;

            var queryString = $@"
SELECT TOP (@param1) i.Name, i.Quantity, i.Row, i.Col, i.IsSmallBox, t.TagsMatched
FROM dbo.Items i JOIN
(
    SELECT NameKey, COUNT(NameKey) TagsMatched
    FROM dbo.Tags
    WHERE Tag IN({paramList})
    GROUP BY NameKey
) t ON i.NameKey = t.NameKey
ORDER BY t.TagsMatched DESC";

            List <TaggedItem> items = new List <TaggedItem>();

            using (SqlCommand command = new SqlCommand())
            {
                command.Connection  = connection;
                command.CommandText = queryString;
                command.Parameters.AddWithValue("@param1", maxResults);

                int index = 2;
                foreach (string tag in existingItemTags)
                {
                    command.Parameters.AddWithValue($"@param{index++}", tag);
                }

                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        items.Add(new TaggedItem
                        {
                            Name        = (string)reader["Name"],
                            Row         = (int)reader["Row"],
                            Col         = (int)reader["Col"],
                            TagsMatched = (int)reader["TagsMatched"],
                            IsSmallBox  = (bool)reader["IsSmallBox"]
                        });
                    }
                }
                catch (Exception ex)
                {
                    log.LogInformation(ex.Message);
                    return(new BundleWithResponse());
                }
                finally
                {
                    reader.Close();
                }
            }

            IEnumerable <TaggedItem> fullyMatchedItems = items.Where(a => a.TagsMatched.Value == existingItemTags.Count);

            if (fullyMatchedItems.Count() == 1)
            {
                TaggedItem existingItem = fullyMatchedItems.First();
                Item       insertItem   = new Item(
                    newItem,
                    quantity,
                    existingItem.Row.Value,
                    existingItem.Col.Value,
                    existingItem.IsSmallBox.Value);

                TagSet newItemTags = new TagSet(newItem);

                InsertItemResponse resp = InsertItemWithTags(insertItem, newItemTags, connection, log);

                if (resp.Success)
                {
                    return(new BundleWithResponse(true, newItem, quantity, existingItem.Name, insertItem.Row, insertItem.Col));
                }
                else
                {
                    return(new BundleWithResponse(false, newItem, quantity, existingItem.Name));
                }
            }

            return(new BundleWithResponse());
        }
Пример #3
0
        public static FindTagsResponse FindTags(TagSet tagSet, SqlConnection connection, ILogger log, int maxResults = 10)
        {
            if (tagSet.Count == 0)
            {
                return(new FindTagsResponse(-1, null));
            }

            // Take a string of words: "Green motor driver"
            // Extract a HashSet of tags: HashSet<string> = { "Green", "motor", "driver" }
            // Format as params for SQL query, to defend against SQL injection attacks:
            //     "@param2,@param3,@param4"
            string paramList = string.Join(",", tagSet.Select((_, index) => $"@param{index + 2}"));

            log.LogInformation(paramList);

            var queryString = $@"
SELECT i.NameKey, i.Name, i.Quantity, i.Row, i.Col, t.TagsMatched
FROM dbo.Items i JOIN
(
    SELECT NameKey, COUNT(NameKey) TagsMatched
    FROM dbo.Tags
    WHERE Tag IN({paramList})
    GROUP BY NameKey
) t ON i.NameKey = t.NameKey
ORDER BY t.TagsMatched DESC";

            using (SqlCommand command = new SqlCommand())
            {
                command.Connection  = connection;
                command.CommandText = queryString;
                command.Parameters.AddWithValue("@param1", maxResults);

                int index = 2;
                foreach (string tag in tagSet)
                {
                    command.Parameters.AddWithValue($"@param{index++}", tag);
                }

                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    int i = 0;

                    List <int[]> coordsAndMatches = new List <int[]>();
                    while (reader.Read() && i++ < maxResults)
                    {
                        coordsAndMatches.Add(new int[] { (int)reader["Row"], (int)reader["Col"], (int)reader["TagsMatched"] });
                    }

                    return(new FindTagsResponse(tagSet.Count, coordsAndMatches));
                }
                catch (Exception ex)
                {
                    log.LogInformation(ex.Message);
                    return(new FindTagsResponse(tagSet.Count, null));
                }
                finally
                {
                    reader.Close();
                }
            }
        }