public List<CommentDTO> GetAllCommentsByDiagramID(int diagramId)
        {
            DbCommand cmd = db.GetStoredProcCommand("GetCommentsByDiagramID");
            db.AddInParameter(cmd, "@DiagramID", System.Data.DbType.Int32,
                diagramId);
            IDataReader reader = db.ExecuteReader(cmd);

            List<CommentDTO> comments = new List<CommentDTO>();
            while (reader.Read())
            {
                CommentDTO comment = new CommentDTO();
                comment.CommentID = reader.GetInt32(0);
                comment.DiagramID = reader.GetInt32(1);
                comment.Username = reader.GetString(2);
                comment.Comment = reader.GetString(3);
                comment.CommentOrder = reader.GetInt32(4);
                comment.CommentDate = reader.GetDateTime(5);

                comment.Attachments = GetAllAttachmentByCommentIDAndDiagramID(
                                            comment.CommentID,
                                            comment.DiagramID);

                comments.Add(comment);
            }

            reader.Close();
            reader.Dispose();
            cmd.Dispose();

            return comments;
        }
        public CommentDTO GetCommentByCommentID(int commentId)
        {
            DbCommand cmd = db.GetStoredProcCommand("GetByCommentId");
            db.AddInParameter(cmd, "@CommentID", DbType.Int32, commentId);

            CommentDTO dto = null;
            IDataReader reader = db.ExecuteReader(cmd);
            using (reader)
            {
                while (reader.Read())
                {
                    dto = new CommentDTO();
                    dto.CommentID = reader.GetInt32(0);
                    dto.DiagramID = reader.GetInt32(1);
                    dto.Username = reader.GetString(2);
                    dto.Comment = reader.GetString(3);
                    dto.CommentOrder = reader.GetInt32(4);
                    dto.CommentDate = reader.GetDateTime(5);
                    dto.Attachments = GetAllAttachmentByCommentIDAndDiagramID(dto.CommentID, dto.DiagramID);
                }
            }
            cmd.Dispose();
            return dto;
        }
        public bool SaveComment(CommentDTO comment, out int commentId)
        {
            DbCommand cmd = db.GetStoredProcCommand("SaveCommentWithCommentID");
            db.AddInParameter(cmd, "@DiagramID", System.Data.DbType.Int32, comment.DiagramID);
            db.AddInParameter(cmd, "@Username", System.Data.DbType.String, comment.Username);
            db.AddInParameter(cmd, "@Comment", System.Data.DbType.String, comment.Comment);
            db.AddInParameter(cmd, "@CommentOrder", System.Data.DbType.Int32, comment.CommentOrder);
            db.AddInParameter(cmd, "@CommentDate", System.Data.DbType.DateTime, comment.CommentDate);
            db.AddOutParameter(cmd, "@CommentID", DbType.Int32, int.MaxValue);
            //db.AddInParameter(cmd, "@Department", System.Data.DbType.String, comment.Department);

            int result = db.ExecuteNonQuery(cmd);

            int id = Convert.ToInt32(db.GetParameterValue(cmd, "@CommentID"));
            commentId = id;

            cmd.Dispose();

            return result > 0;
        }
        public bool SaveComment(CommentDTO comment)
        {
            DbCommand cmd = db.GetStoredProcCommand("SaveComment");
            db.AddInParameter(cmd, "@DiagramID", System.Data.DbType.Int32, comment.DiagramID);
            db.AddInParameter(cmd, "@Username", System.Data.DbType.String, comment.Username);
            db.AddInParameter(cmd, "@Comment", System.Data.DbType.String, comment.Comment);
            db.AddInParameter(cmd, "@CommentOrder", System.Data.DbType.Int32, comment.CommentOrder);
            db.AddInParameter(cmd, "@CommentDate", System.Data.DbType.DateTime, comment.CommentDate);
            //db.AddInParameter(cmd, "@Department", System.Data.DbType.String, comment.Department);

            int result = db.ExecuteNonQuery(cmd);

            cmd.Dispose();

            return result > 0;
        }