/* 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); }
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()); }
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(); } } }