internal static ItemEntity Item(Item ent)
{
ItemEntity data = new ItemEntity();
data.itemId = ent.Id;
data.itemStatusFK = ent.ItemStatusFK;
data.giftListFK = ent.GiftListFK;
data.itemName = ent.ItemName;
data.description = ent.Description;
data.updateTimestamp = ent.UpdateTimestamp;
data.updatePersonFK = ent.UpdatePersonFK;
return data;
}
internal static Item Item(ItemEntity data)
{
Item ent = new Item();
ent.Id = data.itemId;
ent.ItemStatusFK = data.itemStatusFK;
ent.GiftListFK = data.giftListFK;
ent.ItemName = data.itemName;
ent.Description = data.description;
ent.UpdateTimestamp = data.updateTimestamp;
ent.UpdatePersonFK = data.updatePersonFK;
return ent;
}
public IList<ItemEntity> GetAllItems(IConnection conn)
{
List<ItemEntity> itemList = new List<ItemEntity>();
string sql = "SELECT itemId, itemStatusFK, giftListFK, itemName, description, updateTimestamp, updatePersonFK FROM dbo.item;";
var rdr = conn.ExecuteReader(sql);
while (rdr.Read())
{
var item = new ItemEntity()
{
itemId = rdr.IsDBNull(rdr.GetOrdinal("itemId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemId")),
itemStatusFK = rdr.IsDBNull(rdr.GetOrdinal("itemStatusFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemStatusFK")),
giftListFK = rdr.IsDBNull(rdr.GetOrdinal("giftListFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("giftListFK")),
itemName = rdr.IsDBNull(rdr.GetOrdinal("itemName")) ? null : rdr.GetString(rdr.GetOrdinal("itemName")),
description = rdr.IsDBNull(rdr.GetOrdinal("description")) ? null : rdr.GetString(rdr.GetOrdinal("description")),
updateTimestamp = rdr.IsDBNull(rdr.GetOrdinal("updateTimestamp")) ? new DateTime() : rdr.GetDateTime(rdr.GetOrdinal("updateTimestamp")),
updatePersonFK = rdr.IsDBNull(rdr.GetOrdinal("updatePersonFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("updatePersonFK"))
};
itemList.Add(item);
}
return itemList;
}
private void CheckItemForRequiredValues(ItemEntity i, RepositoryUtils.RepositoryAction action)
{
List<string> missingFields = new List<string>();
//if (String.IsNullOrWhiteSpace(p.userName)) missingFields.Add("User Name");
//if (String.IsNullOrWhiteSpace(p.emailAddress)) missingFields.Add("Email Address");
//if (String.IsNullOrWhiteSpace(p.firstName)) missingFields.Add("First Name");
//if (String.IsNullOrWhiteSpace(p.lastName)) missingFields.Add("Last Name");
//if (String.IsNullOrWhiteSpace(p.passwordHash)) missingFields.Add("Password");
if (missingFields.Count > 0)
{
throw new Exception(String.Format("Cannot {0} Person: Missing Fields {1}", action.ToString(), String.Join(", ", missingFields.ToArray())));
}
}
public void Update(int id, ItemEntity item, IConnection conn)
{
CheckItemForRequiredValues(item, RepositoryUtils.RepositoryAction.Update);
var contactToUpdate = GetItemById(id);
if (contactToUpdate == null)
{
throw new Exception("Contact does not exist in database");
}
string sql = @"UPDATE person SET [itemStatusFK]=@itemStatusFK,
[giftListFK]=@giftListFK,
[itemName]=@itemName,
[description]=@description,
[updateTimestamp]=getdate(),
[updatePersonFK]=@updatePersonFK,
WHERE personId=@Id";
List<SqlParameter> prms = new List<SqlParameter>();
prms.Add(new SqlParameter { ParameterName = "@Id", Value = id });
prms.Add(new SqlParameter { ParameterName = "@itemStatusFK", Value = item.itemStatusFK });
prms.Add(new SqlParameter { ParameterName = "@giftListFK", Value = item.giftListFK });
prms.Add(new SqlParameter { ParameterName = "@itemName", Value = item.itemName });
prms.Add(new SqlParameter { ParameterName = "@description", Value = item.description });
prms.Add(new SqlParameter { ParameterName = "@updatePersonFK", Value = item.updatePersonFK });
var number = conn.ExecuteNonQuery(sql,prms);
if (number != 1)
{
throw new Exception($"No Contacts were updated with Id: {id}");
}
}
public void Update(int id, ItemEntity item)
{
using (Connection conn = new Connection())
{
Update(id, item, conn);
}
}
public long Insert(ItemEntity item, IConnection conn)
{
CheckItemForRequiredValues(item, RepositoryUtils.RepositoryAction.Insert);
var itemExists = ItemExists(item.giftListFK, item.itemName);
if (!itemExists)
{
throw new Exception($"Entity {item.giftListFK} {item.itemName} already exists in database!");
}
string sql =
@"INSERT INTO[dbo].[person] (itemStatusFK, giftListFK, itemName, description, updateTimestamp, updatePersonFK)
VALUES(@itemStatusFK, @giftListFK, @itemName, @description, getdate(), @updatePersonFK);SELECT CAST(scope_identity() AS int)";
List<SqlParameter> prms = new List<SqlParameter>();
prms.Add(new SqlParameter { ParameterName = "@itemStatusFK", Value = item.itemStatusFK });
prms.Add(new SqlParameter { ParameterName = "@giftListFK", Value = item.giftListFK });
prms.Add(new SqlParameter { ParameterName = "@itemName", Value = item.itemName });
prms.Add(new SqlParameter { ParameterName = "@description", Value = item.description });
prms.Add(new SqlParameter { ParameterName = "@updatePersonFK", Value = item.updatePersonFK });
try
{
return int.Parse(conn.ExecuteScalar(sql, prms).ToString());
}
catch (Exception)
{
throw new Exception($"Entity {item.giftListFK} {item.itemName} not inserted in database!");
}
}
public long Insert(ItemEntity item)
{
using (Connection conn = new Connection())
{
return Insert(item, conn);
}
}
public ItemEntity GetItemById(int id, IConnection conn)
{
List<ItemEntity> itemList = new List<ItemEntity>();
string sql = "SELECT itemId, itemStatusFK, giftListFK, itemName, description, updateTimestamp, updatePersonFK FROM dbo.item FROM dbo.item WHERE itemId = @id;";
List<SqlParameter> prms = new List<SqlParameter>();
prms.Add(new SqlParameter { ParameterName = "@id", Value = id });
var rdr = conn.ExecuteReader(sql,prms);
while (rdr.Read())
{
var item = new ItemEntity()
{
itemId = rdr.IsDBNull(rdr.GetOrdinal("itemId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemId")),
itemStatusFK = rdr.IsDBNull(rdr.GetOrdinal("itemStatusFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemStatusFK")),
giftListFK = rdr.IsDBNull(rdr.GetOrdinal("giftListFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("giftListFK")),
itemName = rdr.IsDBNull(rdr.GetOrdinal("itemName")) ? null : rdr.GetString(rdr.GetOrdinal("itemName")),
description = rdr.IsDBNull(rdr.GetOrdinal("description")) ? null : rdr.GetString(rdr.GetOrdinal("description")),
updateTimestamp = rdr.IsDBNull(rdr.GetOrdinal("updateTimestamp")) ? new DateTime() : rdr.GetDateTime(rdr.GetOrdinal("updateTimestamp")),
updatePersonFK = rdr.IsDBNull(rdr.GetOrdinal("updatePersonFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("updatePersonFK"))
};
itemList.Add(item);
}
return itemList.FirstOrDefault();
}
