internal static ItemCommentEntity ItemComment(ItemComment ent)
{
ItemCommentEntity data = new ItemCommentEntity();
data.itemCommentId = ent.Id;
data.itemFK = ent.ItemFK;
data.commentorFK = ent.CommentorFK;
data.commentText = ent.CommentText;
data.isHiddenFromOwner = ent.IsHiddenFromOwner;
data.updateTimestamp = ent.UpdateTimestamp;
data.updatePersonFK = ent.UpdatePersonFK;
return data;
}
internal static ItemComment ItemComment(ItemCommentEntity data)
{
ItemComment ent = new ItemComment();
ent.Id = data.itemCommentId;
ent.ItemFK = data.itemFK;
ent.CommentorFK = data.commentorFK;
ent.CommentText = data.commentText;
ent.IsHiddenFromOwner = data.isHiddenFromOwner;
ent.UpdateTimestamp = data.updateTimestamp;
ent.UpdatePersonFK = data.updatePersonFK;
return ent;
}
public IList<ItemCommentEntity> GetAllItemComments(IConnection conn)
{
List<ItemCommentEntity> itemCommentList = new List<ItemCommentEntity>();
string sql = "SELECT itemCommentId, itemFK, commentorFK, commentText, isHiddenFromOwner, updateTimestamp, updatePersonFK FROM dbo.itemComment;";
var rdr = conn.ExecuteReader(sql);
while (rdr.Read())
{
var itemComment = new ItemCommentEntity()
{
itemCommentId = rdr.IsDBNull(rdr.GetOrdinal("itemCommentId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemCommentId")),
itemFK = rdr.IsDBNull(rdr.GetOrdinal("itemFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemFK")),
commentorFK = rdr.IsDBNull(rdr.GetOrdinal("commentorFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("commentorFK")),
commentText = rdr.IsDBNull(rdr.GetOrdinal("commentText")) ? null : rdr.GetString(rdr.GetOrdinal("commentText")),
isHiddenFromOwner = rdr.IsDBNull(rdr.GetOrdinal("isHiddenFromOwner")) ? true : (rdr.GetString(rdr.GetOrdinal("isHiddenFromOwner")) == "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"))
};
itemCommentList.Add(itemComment);
}
return itemCommentList;
}
private void CheckItemCommentForRequiredValues(ItemCommentEntity ic, 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, ItemCommentEntity itemComment, IConnection conn)
{
CheckItemCommentForRequiredValues(itemComment, RepositoryUtils.RepositoryAction.Update);
var contactToUpdate = GetItemCommentById(id);
if (contactToUpdate == null)
{
throw new Exception("ItemComment does not exist in database");
}
string sql = @"UPDATE itemComment SET [itemFK]=@itemFK,
[commentorFK]=@commentorFK,
[commentText]=@commentText,
[isHiddenFromOwner]=@getdate(),
[updatePersonFK]=@updatePersonFK
WHERE itemCommentId=@Id";
List<SqlParameter> prms = new List<SqlParameter>();
prms.Add(new SqlParameter { ParameterName = "@Id", Value = id });
prms.Add(new SqlParameter { ParameterName = "@itemFK", Value = itemComment.itemFK });
prms.Add(new SqlParameter { ParameterName = "@commentorFK", Value = itemComment.commentorFK });
prms.Add(new SqlParameter { ParameterName = "@commentText", Value = itemComment.commentText });
prms.Add(new SqlParameter { ParameterName = "@isHiddenFromOwner", Value = itemComment.isHiddenFromOwner ? "Y" : "N" });
prms.Add(new SqlParameter { ParameterName = "@updatePersonFK", Value = itemComment.updatePersonFK });
var number = conn.ExecuteNonQuery(sql,prms);
if (number != 1)
{
throw new Exception($"No ItemComments were updated with Id: {id}");
}
}
public void Update(int id, ItemCommentEntity itemComment)
{
using (Connection conn = new Connection())
{
Update(id, itemComment, conn);
}
}
public long Insert(ItemCommentEntity itemComment, IConnection conn)
{
CheckItemCommentForRequiredValues(itemComment, RepositoryUtils.RepositoryAction.Insert);
string sql =
@"INSERT INTO [dbo].[itemComment] (itemFK, commentorFK, commentText, isHiddenFromOwner, updateTimestamp, updatePersonFK)
VALUES(@itemFK, @commentorFK, @commentText, @isHiddenFromOwner, getdate(), @updatePersonFK);SELECT CAST(scope_identity() AS int)";
List<SqlParameter> prms = new List<SqlParameter>();
prms.Add(new SqlParameter { ParameterName = "@itemFK", Value = itemComment.itemFK });
prms.Add(new SqlParameter { ParameterName = "@commentorFK", Value = itemComment.commentorFK });
prms.Add(new SqlParameter { ParameterName = "@commentText", Value = itemComment.commentText });
prms.Add(new SqlParameter { ParameterName = "@isHiddenFromOwner", Value = itemComment.isHiddenFromOwner ? "Y" : "N" });
prms.Add(new SqlParameter { ParameterName = "@updatePersonFK", Value = itemComment.updatePersonFK });
try
{
return int.Parse(conn.ExecuteScalar(sql,prms).ToString());
}
catch (Exception)
{
throw new Exception($"ItemComment not inserted in database!");
}
}
public long Insert(ItemCommentEntity itemComment)
{
using (Connection conn = new Connection())
{
return Insert(itemComment, conn);
}
}
public ItemCommentEntity GetItemCommentByItem(int itemId, IConnection conn)
{
List<ItemCommentEntity> itemCommentList = new List<ItemCommentEntity>();
string sql = "SELECT itemCommentId, itemFK, commentorFK, commentText, isHiddenFromOwner, updateTimestamp, updatePersonFK FROM dbo.itemComment WHERE itemId = @itemId;";
List<SqlParameter> prms = new List<SqlParameter>();
var paramQuery = new SqlParameter
{
ParameterName = "@itemId",
Value = itemId
};
prms.Add(paramQuery);
var rdr = conn.ExecuteReader(sql,prms);
while (rdr.Read())
{
var itemComment = new ItemCommentEntity()
{
itemCommentId = rdr.IsDBNull(rdr.GetOrdinal("itemCommentId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemCommentId")),
itemFK = rdr.IsDBNull(rdr.GetOrdinal("itemFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemFK")),
commentorFK = rdr.IsDBNull(rdr.GetOrdinal("commentorFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("commentorFK")),
commentText = rdr.IsDBNull(rdr.GetOrdinal("commentText")) ? null : rdr.GetString(rdr.GetOrdinal("commentText")),
isHiddenFromOwner = rdr.IsDBNull(rdr.GetOrdinal("isHiddenFromOwner")) ? true : (rdr.GetString(rdr.GetOrdinal("isHiddenFromOwner")) == "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"))
};
itemCommentList.Add(itemComment);
}
return itemCommentList.FirstOrDefault();
}
