internal static GiftList GiftList(GiftListEntity data) { GiftList ent = new GiftList(); ent.Id = data.giftListId; ent.PersonFK = data.personFK; ent.ListName = data.listName; ent.IsPrivate = data.isPrivate; ent.UpdateTimestamp = data.updateTimestamp; ent.UpdatePersonFK = data.updatePersonFK; return ent; }
internal static GiftListEntity GiftList(GiftList ent) { GiftListEntity data = new GiftListEntity(); data.giftListId = ent.Id; data.personFK = ent.PersonFK; data.listName = ent.ListName; data.isPrivate = ent.IsPrivate; data.updateTimestamp = ent.UpdateTimestamp; data.updatePersonFK = ent.UpdatePersonFK; return data; }
public IList<GiftListEntity> GetAllGiftLists(IConnection conn) { List<GiftListEntity> giftListlist = new List<GiftListEntity>(); string sql = "SELECT giftListId, personFK, listName, isPrivate, updateTimestamp, updatePersonFK FROM DBO.GIFTLIST;"; List<SqlParameter> prms = new List<SqlParameter>(); var rdr = conn.ExecuteReader(sql,prms); while (rdr.Read()) { var giftList = new GiftListEntity() { giftListId = rdr.IsDBNull(rdr.GetOrdinal("giftListId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("giftListId")), personFK = rdr.IsDBNull(rdr.GetOrdinal("personFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("personFK")), listName = rdr.IsDBNull(rdr.GetOrdinal("listName")) ? null : rdr.GetString(rdr.GetOrdinal("listName")), isPrivate = rdr.IsDBNull(rdr.GetOrdinal("isPrivate")) ? false : (rdr.GetString(rdr.GetOrdinal("isPrivate")) == "Y"), 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")) }; giftListlist.Add(giftList); } return giftListlist; }
private void CheckGiftListForRequiredValues(GiftListEntity gl, 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} Link: Missing Fields {1}", action.ToString(), String.Join(", ", missingFields.ToArray()))); } }
public void Update(int id, GiftListEntity giftList, IConnection conn) { CheckGiftListForRequiredValues(giftList, RepositoryUtils.RepositoryAction.Update); var linkToUpdate = GetListById(giftList.giftListId); if (linkToUpdate == null) { throw new Exception("Gift List does not exist in database"); } string sql = @"UPDATE person SET [personFK]=@personFK, [listName]=@listName, [isPrivate]=@isPrivate, [updateTimestamp]=getdate(), [updatePersonFK]=@updatePersonFK WHERE giftListId=@giftListId"; List<SqlParameter> prms = new List<SqlParameter>(); var param1 = new SqlParameter { ParameterName = "@personFK", Value = giftList.personFK }; prms.Add(param1); var param2 = new SqlParameter { ParameterName = "@listName", Value = giftList.listName }; prms.Add(param2); var param3 = new SqlParameter { ParameterName = "@isPrivate", Value = giftList.isPrivate ? 'Y' : 'N' }; prms.Add(param3); var param4 = new SqlParameter { ParameterName = "@updatePersonFK", Value = giftList.updatePersonFK }; prms.Add(param4); var number = conn.ExecuteNonQuery(sql,prms); if (number != 1) { throw new Exception($"No Gift Lsits were updated with Id: {id}"); } }
public void Update(int id, GiftListEntity giftList) { using (Connection conn = new Connection()) { Update(id, giftList, conn); } }
public long Insert(GiftListEntity giftList, IConnection conn) { CheckGiftListForRequiredValues(giftList, RepositoryUtils.RepositoryAction.Insert); var linkExists = GetList(giftList.personFK, giftList.listName); if (linkExists != null) { throw new Exception($"Gift List {giftList.personFK} {giftList.listName} already exists in database!"); } string sql = @"INSERT INTO[dbo].[giftList] (personFK, listName, isPrivate, updateTimestamp, updatePersonFK) VALUES(@personFK, @listName, @isPrivate, getdate(), @updatePersonFK );SELECT CAST(scope_identity() AS int)"; List<SqlParameter> prms = new List<SqlParameter>(); var param1 = new SqlParameter { ParameterName = "@personFK", Value = giftList.personFK }; prms.Add(param1); var param2 = new SqlParameter { ParameterName = "@listName", Value = giftList.listName }; prms.Add(param2); var param3 = new SqlParameter { ParameterName = "@isPrivate", Value = giftList.isPrivate ? 'Y' : 'N' }; prms.Add(param3); var param4 = new SqlParameter { ParameterName = "@updatePersonFK", Value = giftList.updatePersonFK }; prms.Add(param4); try { return int.Parse(conn.ExecuteScalar(sql,prms).ToString()); } catch (Exception) { throw new Exception($"Entity {giftList.personFK} {giftList.listName} not inserted in database!"); } }
public long Insert(GiftListEntity giftList) { using (Connection conn = new Connection()) { return Insert(giftList, conn); } }
public GiftListEntity GetListById(int id, IConnection conn) { List<GiftListEntity> giftListlist = new List<GiftListEntity>(); string sql = "SELECT giftListId, personFK, listName, isPrivate, updateTimestamp, updatePersonFK FROM DBO.GIFTLIST WHERE giftListId = @id;"; List<SqlParameter> prms = new List<SqlParameter>(); var paramId = new SqlParameter { ParameterName = "@id", Value = id }; prms.Add(paramId); var rdr = conn.ExecuteReader(sql,prms); while (rdr.Read()) { var giftList = new GiftListEntity() { giftListId = rdr.IsDBNull(rdr.GetOrdinal("giftListId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("giftListId")), personFK = rdr.IsDBNull(rdr.GetOrdinal("personFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("personFK")), listName = rdr.IsDBNull(rdr.GetOrdinal("listName")) ? null : rdr.GetString(rdr.GetOrdinal("listName")), isPrivate = rdr.IsDBNull(rdr.GetOrdinal("isPrivate")) ? false : (rdr.GetString(rdr.GetOrdinal("isPrivate")) == "Y"), 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")) }; giftListlist.Add(giftList); } return giftListlist.FirstOrDefault(); }