jdescopingtool/OLD/DataModel/Process/LotFinderDB.Item.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using DataModel.Models;

namespace DataModel.Process
{
    /// <summary>
    /// Item tracking functions for LotFinderDB interface
    /// </summary>
    public partial class LotFinderDB
    {
        /// <summary>
        /// Query to find items with matching item number or description
        /// </summary>
        private const string SQL_SEARCH_ITEMS = @"
            SELECT TOP 25
                    i.ShortItemNumber, 
                    i.ItemNumber,
                    i.Description,
                    i.LastUpdateDT
            FROM    dbo.Item AS i
            WHERE   i.ItemNumber LIKE '%' + @filter + '%' OR
                    i.Description LIKE '%' + @filter + '%'
            ORDER BY i.ItemNumber";

        /// <summary>
        /// Finds items with matching item number or description
        /// </summary>
        /// <param name="filter">Search filter</param>
        /// <returns>Items with matching item number or description</returns>
        public static List<Item> SearchItems(string filter)
        {
            List<Item> results = new List<Item>();

            try
            {
                using (SqlConnection connection = GetConnection())
                {
                    results.AddRange(connection.Query<Item>(SQL_SEARCH_ITEMS, new {filter}));
                }
            }
            catch (Exception error)
            {
                //Log and forward exception
                logger.Error("SearchItems: failed to search for item '{0}': {1}.", filter, error.Message);
                throw new Exception($"Failed to get serach for item '{filter}' from LotFinderDB.", error);
            }

            return results;
        }

        /// <summary>
        /// Query to find items with matching item numbers
        /// </summary>
        private const string SQL_LOOKUP_ITEMS = @"
         SELECT i.ShortItemNumber,
                i.ItemNumber,
                i.Description,
                i.LastUpdateDT
         FROM   dbo.Item AS i INNER JOIN 
                @itemNumbers AS i2 ON (i.ItemNumber = i2.ItemNumber)
         ORDER BY i.ItemNumber";

        /// <summary>
        /// Finds items with matching item numbers
        /// </summary>
        /// <param name="itemNumbers">Item numbers to match</param>
        /// <returns></returns>
        public static List<Item> LookupItems(List<string> itemNumbers)
        {
            List<Item> results = new List<Item>();

            try
            {
                //Create search filter parameter
                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("ItemNumber", typeof(string));

                foreach (string itemNumber in itemNumbers)
                {
                    dataTable.Rows.Add(itemNumber);
                }

                using (SqlConnection connection = GetConnection())
                {
                    results = connection.Query<Item>(SQL_LOOKUP_ITEMS, new { itemNumbers = dataTable.AsTableValuedParameter("ItemNumberFilterParameter") }).ToList();
                }
            }
            catch (Exception error)
            {
                //Log and forward exception
                logger.Error("LookupItems: failed to find matching items: {0}.", error.Message);
                throw new Exception("LookupItems: failed to find matching items in LotFinderDB.", error);
            }

            return results;
        }
    }
}