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