/// <summary> /// Converts a CSV file to a list of objects /// </summary> /// <param name="path">the full path to the csv file</param> /// <returns></returns> public static List <ItemPriceDTO> FromCSV(string path) { List <ItemPriceDTO> items = new List <ItemPriceDTO>(); using (StreamReader reader = new StreamReader(path)) { ItemPriceDTO item; string[] headers = reader.ReadLine().Split(';'); string line; while ((line = reader.ReadLine()) != null) { string[] row = line.Split(';'); item = new ItemPriceDTO(); for (int i = 0; i < headers.Length; i++) { try { string newValue = row[i]; item.GetType().GetProperty(headers[i]).SetValue(item, newValue, null); } catch (Exception ex) { _log.Error("Converting CSV cell to object resulted in an error", ex); throw ex; } } items.Add(item); } } return(items); }
public int InsertItemPrice(ItemPriceDTO item) { if (String.IsNullOrEmpty(item.ItemID)) { throw new ArgumentNullException("ItemID for item \"" + item.ItemName + "\" is missing while inserting new item price"); } SqlConnection connection = new SqlConnection(connectionString); int rowsUpdated = 0; try { connection.Open(); string query = Query.InsertItemPrice(item); using (SqlCommand cmd = new SqlCommand(query, connection)) { rowsUpdated = cmd.ExecuteNonQuery(); } } catch (Exception ex) { _log.Error("Inserting an item price into the DB failed", ex); } finally { connection.Close(); } return(rowsUpdated); }
public static string InsertItemPrice(ItemPriceDTO item) { var props = item.GetType().GetProperties(); string query = "Insert INTO ItemPrice ("; //query column headers foreach (var prop in props) { if (prop.Name != nameof(item.ItemName)) { query += $"{prop.Name}, "; } } query = query.Substring(0, query.Length - 2); //remove comma at end of query query += ") VALUES ("; //query values foreach (var prop in props) { if (prop.Name == nameof(item.ItemID)) { query += $"'{prop.GetValue(item, null)}', "; } else if (prop.Name != nameof(item.ItemName)) { query += $"{prop.GetValue(item, null)}, "; } } query = query.Substring(0, query.Length - 2); //remove comma at end of query query += ")"; return(query); }
/// <summary> /// updates an items price when the ItemPrice already exists /// </summary> /// <param name="item"></param> /// <returns></returns> public static string UpdateExistingItemPrice(ItemPriceDTO newItem, ItemPriceDTO existingItem) { if ((newItem.ScheduleIndex != existingItem.ScheduleIndex) || (newItem.ItemName != existingItem.ItemName)) { throw new ArgumentNullException(nameof(UpdateExistingItemPrice), "Item name and scheduled index must match!"); } var props = newItem.GetType().GetProperties(); string query = "UPDATE ItemPrice SET "; foreach (var prop in props) { if (prop.Name != nameof(newItem.ItemName) && prop.Name != nameof(newItem.ItemID)) { query += $"{prop.Name} = {prop.GetValue(newItem, null)}, "; } } query = query.Substring(0, query.Length - 2) + " "; //remove comma at end of query query += $"Where {nameof(existingItem.ItemID)} = '{existingItem.ItemID}' AND " + $"{nameof(existingItem.ScheduleIndex)} = {existingItem.ScheduleIndex}"; return(query); }
/// <summary> /// Get All item prices from the DB /// </summary> /// <returns></returns> public List <ItemPriceDTO> GetAllItemPrices() { SqlCommand command = null; SqlDataReader dataReader = null; SqlConnection connection = new SqlConnection(connectionString); List <ItemPriceDTO> items = new List <ItemPriceDTO>(); try { connection.Open(); string sql = Query.SelectAllItemPrices(); command = new SqlCommand(sql, connection); dataReader = command.ExecuteReader(); ItemPriceDTO item; while (dataReader.Read()) { item = new ItemPriceDTO(); foreach (var prop in item.GetType().GetProperties()) { string newValue = dataReader[prop.Name].ToString().ToUpper(); prop.SetValue(item, newValue, null); } items.Add(item); } } catch (Exception ex) { _log.Error("Retrieving all item prices from db to list failed", ex); } finally { if (dataReader != null) { dataReader.Close(); } if (command != null) { command.Dispose(); } connection.Close(); } return(items); }
public int UpdateItemPrice(ItemPriceDTO newItem, ItemPriceDTO existingItem) { SqlConnection connection = new SqlConnection(connectionString); int rowsUpdated = 0; try { connection.Open(); string query = Query.UpdateExistingItemPrice(newItem, existingItem); using (SqlCommand cmd = new SqlCommand(query, connection)) { rowsUpdated = cmd.ExecuteNonQuery(); } } catch (Exception ex) { _log.Error("Updating an item price in the DB failed", ex); } finally { connection.Close(); } return(rowsUpdated); }