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