internal static PurchaseEntity Purchase(Purchase ent) { PurchaseEntity data = new PurchaseEntity(); data.purchaseId = ent.Id; data.itemFK = ent.ItemFK; data.purchaserFK = ent.PurchaserFK; data.purchaseDate = ent.PurchaseDate; data.updateTimestamp = ent.UpdateTimestamp; data.updatePersonFK = ent.UpdatePersonFK; return data; }
internal static Purchase Purchase(PurchaseEntity data) { Purchase ent = new Purchase(); ent.Id = data.purchaseId; ent.ItemFK = data.itemFK; ent.PurchaserFK = data.purchaserFK; ent.PurchaseDate = data.purchaseDate; ent.UpdateTimestamp = data.updateTimestamp; ent.UpdatePersonFK = data.updatePersonFK; return ent; }
public IList<PurchaseEntity> GetAllPurchases(IConnection conn) { List<PurchaseEntity> purchaseList = new List<PurchaseEntity>(); string sql = "SELECT purchaseId, itemFK, purchaserFK, purchaseDate, updateTimestamp, updatePersonKey FROM DBO.PURCHASE;"; var rdr = conn.ExecuteReader(sql); while (rdr.Read()) { var purchase = new PurchaseEntity() { purchaseId = rdr.IsDBNull(rdr.GetOrdinal("purchaseId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("purchaseId")), itemFK = rdr.IsDBNull(rdr.GetOrdinal("itemFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemFK")), purchaserFK = rdr.IsDBNull(rdr.GetOrdinal("purchaserFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("purchaserFK")), purchaseDate = rdr.IsDBNull(rdr.GetOrdinal("purchaseDate")) ? null : (DateTime?)rdr.GetDateTime(rdr.GetOrdinal("purchaseDate")), 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")) }; purchaseList.Add(purchase); } return purchaseList; }
public IList<PurchaseEntity> GetAllPurchases(int purchaser, IConnection conn) { List<PurchaseEntity> purchaseList = new List<PurchaseEntity>(); string sql = "SELECT purchaseId, itemFK, purchaserFK, purchaseDate, updateTimestamp, updatePersonKey FROM DBO.PURCHASE WHERE purchaserFK = @purchaserFK"; List<SqlParameter> prms = new List<SqlParameter>(); prms.Add( new SqlParameter { ParameterName = "@purchaserFK", Value = purchaser }); var rdr = conn.ExecuteReader(sql,prms); while (rdr.Read()) { var purchase = new PurchaseEntity() { purchaseId = rdr.IsDBNull(rdr.GetOrdinal("purchaseId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("purchaseId")), itemFK = rdr.IsDBNull(rdr.GetOrdinal("itemFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemFK")), purchaserFK = rdr.IsDBNull(rdr.GetOrdinal("purchaserFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("purchaserFK")), purchaseDate = rdr.IsDBNull(rdr.GetOrdinal("purchaseDate")) ? null : (DateTime?)rdr.GetDateTime(rdr.GetOrdinal("purchaseDate")), 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")) }; purchaseList.Add(purchase); } return purchaseList; }
private void CheckPurchaseForRequiredValues(PurchaseEntity p, 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, PurchaseEntity purchase, IConnection conn) { CheckPurchaseForRequiredValues(purchase, RepositoryUtils.RepositoryAction.Update); var purchaseToUpdate = GetPurchase(purchase.itemFK, purchase.purchaserFK); if (purchaseToUpdate == null) { throw new Exception("Contact does not exist in database"); } string sql = @"UPDATE person SET [itemFK]=@itemFK, [purchaserFK]=@purchaserFK, [purchaseDate]=@purchaseDate, [updateTimestamp]=@getdate(), [updatePersonFK]=@updatePersonFK WHERE purchaseId=@Id"; List<SqlParameter> prms = new List<SqlParameter>(); prms.Add(new SqlParameter { ParameterName = "@Id", Value = id }); prms.Add(new SqlParameter { ParameterName = "@itemFK", Value = purchase.itemFK }); prms.Add(new SqlParameter { ParameterName = "@purchaserFK", Value = purchase.purchaserFK }); prms.Add(new SqlParameter { ParameterName = "@purchaseDate", Value = purchase.purchaseDate }); prms.Add(new SqlParameter { ParameterName = "@updatePersonFK", Value = purchase.updatePersonFK }); var number = conn.ExecuteNonQuery(sql,prms); if (number != 1) { throw new Exception($"No Purchases were updated with Id: {id}"); } }
public void Update(int id, PurchaseEntity purchase) { using (Connection conn = new Connection()) { Update(id, purchase, conn); } }
public long Insert(PurchaseEntity purchase, IConnection conn) { CheckPurchaseForRequiredValues(purchase, RepositoryUtils.RepositoryAction.Insert); var purchaseExists = GetPurchase(purchase.itemFK, purchase.purchaserFK); if (purchaseExists != null) { throw new Exception($"Entity {purchase.itemFK}, {purchase.purchaserFK} already exists in database!"); } string sql = @"INSERT INTO[dbo].[purchase] (itemFK, purchaserFK, purchaseDate, updateTimestamp, updatePersonFK) VALUES(@itemFk, @purchaserFK, @purchaseDate, getdate(), @updatePersonKey);SELECT CAST(scope_identity() AS int)"; List<SqlParameter> prms = new List<SqlParameter>(); prms.Add(new SqlParameter { ParameterName = "@itemFK", Value = purchase.itemFK }); prms.Add(new SqlParameter { ParameterName = "@purchaserFK", Value = purchase.purchaserFK }); prms.Add(new SqlParameter { ParameterName = "@purchaseDate", Value = purchase.purchaseDate }); prms.Add(new SqlParameter { ParameterName = "@updatePersonFK", Value = purchase.updatePersonFK }); try { return int.Parse(conn.ExecuteScalar(sql,prms).ToString()); } catch (Exception) { throw new Exception($"Entity {purchase.itemFK} {purchase.purchaserFK} not inserted in database!"); } }
public long Insert(PurchaseEntity purchase) { using (Connection conn = new Connection()) { return Insert(purchase, conn); } }