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