コード例 #1
0
 /// <summary>
 /// LINQ method to insert a new lecturer object
 /// </summary>
 /// <returns>A bool to confirm/deny insert</returns>
 public bool InsertLecturer(Lecturer l)
 {
     try
     {
         using (LinqDBDataContext db = new LinqDBDataContext())
         {
             db.Lecturers.InsertOnSubmit(l);
             db.SubmitChanges();
         }
     }
     catch (SqlException sqlException)
     {
         this.ErrorMessage = "SQL Error: " + sqlException.Message;
         return(false);
     }
     catch (InvalidOperationException invalidOperationException)
     {
         this.ErrorMessage = "SQL Operation Error: " + invalidOperationException.Message;
         return(false);
     }
     catch (Exception e)
     {
         this.ErrorMessage = "Error: " + e.Message;
         return(false);
     }
     return(true);
 }
コード例 #2
0
        /// <summary>
        /// LINQ method to find the last LecturerID
        /// </summary>
        /// <returns>LecturerID as string</returns>
        public string GetLatestLecturerId()
        {
            string lecturerid;

            try
            {
                using (LinqDBDataContext db = new LinqDBDataContext())
                {
                    lecturerid = db.Lecturers
                                 .OrderByDescending(l => l.LecturerID)
                                 .Select(l => l.LecturerID)
                                 .Take(1).FirstOrDefault();
                }
            }
            catch (SqlException sqlException)
            {
                this.ErrorMessage = "SQL Error: " + sqlException.Message;
                return(null);
            }
            catch (InvalidOperationException invalidOperationException)
            {
                this.ErrorMessage = "SQL Operation Error: " + invalidOperationException.Message;
                return(null);
            }
            catch (Exception e)
            {
                this.ErrorMessage = "Error: " + e.Message;
                return(null);
            }
            return(lecturerid);
        }
コード例 #3
0
        /// <summary>
        /// Linq Method to find a lecturer id based on user inputted ID
        /// </summary>
        /// <returns>LecturerID as string</returns>
        public string FindLecturer(string lecturerID)
        {
            string lecturerid;

            try
            {
                using (LinqDBDataContext db = new LinqDBDataContext())
                {
                    lecturerid = db.Lecturers
                                 .Where(l => l.LecturerID.Equals(lecturerID))
                                 .Select(l => l.LecturerID).FirstOrDefault();
                }
            }
            catch (SqlException sqlException)
            {
                this.ErrorMessage = "SQL Error: " + sqlException.Message;
                return(null);
            }
            catch (InvalidOperationException invalidOperationException)
            {
                this.ErrorMessage = "SQL Operation Error: " + invalidOperationException.Message;
                return(null);
            }
            catch (Exception e)
            {
                this.ErrorMessage = "Error: " + e.Message;
                return(null);
            }
            return(lecturerid);
        }
コード例 #4
0
        /// <summary>
        /// LINQ method to insert a new subject object
        /// </summary>
        /// <returns>int count to confirm/deny insert</returns>
        public int InsertNewSubject(Subject s)
        {
            int count;

            try
            {
                using (LinqDBDataContext db = new LinqDBDataContext())
                {
                    count = db.Subjects.Count(n => n.SubjectName.Equals(s.SubjectName));

                    if (count == 0)
                    {
                        db.Subjects.InsertOnSubmit(s);
                        db.SubmitChanges();
                    }
                }
            }
            catch (SqlException sqlException)
            {
                this.ErrorMessage = "SQL Error: " + sqlException.Message;
                return(-1);
            }
            catch (InvalidOperationException invalidOperationException)
            {
                this.ErrorMessage = "SQL Operation Error: " + invalidOperationException.Message;
                return(-1);
            }
            catch (Exception e)
            {
                this.ErrorMessage = "Error: " + e.Message;
                return(-1);
            }
            return(count);
        }
コード例 #5
0
        /// <summary>
        /// LINQ method using multiple joins to fill a list which is converted to a datatable with student information
        /// </summary>
        /// <returns>A datatable filled with Student details</returns>
        public DataTable StudentDetails(string firstName, string lastName)
        {
            DataTable dt = new DataTable();

            try
            {
                using (LinqDBDataContext db = new LinqDBDataContext())
                {
                    var details = db.Grades.Join(db.Students, g => g.StudentID, s => s.StudentID, (g, s) => new { g = g, s = s })
                                  .Join(db.Subjects, x => x.g.SubjectID, su => su.SubjectID, (x, su) => new { x = x, su = su })
                                  .Join(db.Lecturers, y => y.su.LecturerID, l => l.LecturerID, (y, l) => new { y = y, l = l })
                                  .Where(z => (z.y.x.s.FirstName.Equals(firstName) && z.y.x.s.LastName.Equals(lastName)))
                                  .Select(z => new
                    {
                        StudentID    = z.y.x.s.StudentID,
                        FirstName    = z.y.x.s.FirstName,
                        LastName     = z.y.x.s.LastName,
                        DateOfBirth  = z.y.x.s.DateOfBirth,
                        Address      = z.y.x.s.Address,
                        SubjectID    = z.y.x.g.SubjectID,
                        SubjectName  = z.y.su.SubjectName,
                        Grade        = z.y.x.g.Grade1,
                        LecturerName = ((z.l.FirstName + " ") + z.l.LastName),
                        DateEntered  = z.y.x.g.DateEntered
                    });

                    dt = LINQtoDataTable(details);
                }
            }
            catch (SqlException sqlException)
            {
                this.ErrorMessage = "SQL Error: " + sqlException.Message;
                return(null);
            }
            catch (InvalidOperationException invalidOperationException)
            {
                this.ErrorMessage = "SQL Operation Error: " + invalidOperationException.Message;
                return(null);
            }
            catch (Exception e)
            {
                this.ErrorMessage = "Error: " + e.Message;
                return(null);
            }
            return(dt);
        }
コード例 #6
0
        /// <summary>
        /// LINQ method to either add a new subject and grade to a student or update the grade of
        /// and existing subject
        /// </summary>
        /// <returns>A count in int form</returns>
        public int UpdateSubjectDetails(Grade g)
        {
            int count;

            try
            {
                using (LinqDBDataContext db = new LinqDBDataContext())
                {
                    count = db.Grades.Count(n => n.SubjectID.Equals(g.SubjectID) && n.StudentID.Equals(g.StudentID));

                    if (count == 0)
                    {
                        db.Grades.InsertOnSubmit(g);
                        db.SubmitChanges();
                    }
                    else
                    {
                        Grade grade = db.Grades.FirstOrDefault(e => e.SubjectID.Equals(g.SubjectID) && e.StudentID.Equals(g.StudentID));

                        grade.Grade1 = g.Grade1;
                        db.SubmitChanges();
                    }
                }
            }
            catch (SqlException sqlException)
            {
                this.ErrorMessage = "SQL Error: " + sqlException.Message;
                return(-1);
            }
            catch (InvalidOperationException invalidOperationException)
            {
                this.ErrorMessage = "SQL Operation Error: " + invalidOperationException.Message;
                return(-1);
            }
            catch (Exception e)
            {
                this.ErrorMessage = "Error: " + e.Message;
                return(-1);
            }
            return(count);
        }
コード例 #7
0
        /// <summary>
        /// LINQ method to pull subject info and load to a list
        /// </summary>
        /// <returns>A list of subjects(ID and Name)</returns>
        public List <Subject> GetSubjects()
        {
            List <Subject> subjectList = new List <Subject>();

            try
            {
                using (LinqDBDataContext db = new LinqDBDataContext())
                {
                    var subjects = db.Subjects.Select(s => new { SubjectName = s.SubjectName, SubjectID = s.SubjectID });

                    foreach (var subject in subjects)
                    {
                        subjectList.Add(
                            new Subject()
                        {
                            SubjectID   = subject.SubjectID,
                            SubjectName = subject.SubjectName
                        }
                            );
                    }
                }
            }
            catch (SqlException sqlException)
            {
                this.ErrorMessage = "SQL Error: " + sqlException.Message;
                return(null);
            }
            catch (InvalidOperationException invalidOperationException)
            {
                this.ErrorMessage = "SQL Operation Error: " + invalidOperationException.Message;
                return(null);
            }
            catch (Exception e)
            {
                this.ErrorMessage = "Error: " + e.Message;
                return(null);
            }
            return(subjectList);
        }
コード例 #8
0
        /// <summary>
        /// LINQ method to insert a list of subjects for the new student above
        /// </summary>
        /// <returns>A bool to confirm/deny insert</returns>
        public bool InsertGrades(List <Grade> gList)
        {
            try
            {
                foreach (var item in gList)
                {
                    using (LinqDBDataContext db = new LinqDBDataContext())
                    {
                        Grade g = new Grade();

                        g.StudentID   = item.StudentID;
                        g.SubjectID   = item.SubjectID;
                        g.DateEntered = DateTime.Now;


                        db.Grades.InsertOnSubmit(g);
                        db.SubmitChanges();
                    }
                }
            }
            catch (SqlException sqlException)
            {
                this.ErrorMessage = "SQL Error: " + sqlException.Message;
                return(false);
            }
            catch (InvalidOperationException invalidOperationException)
            {
                this.ErrorMessage = "SQL Operation Error: " + invalidOperationException.Message;
                return(false);
            }
            catch (Exception e)
            {
                this.ErrorMessage = "Error: " + e.Message;
                return(false);
            }
            return(true);
        }
コード例 #9
0
        /// <summary>
        /// LINQ method using multiple joins to fill a list which is converted to a datatable with student information
        /// </summary>
        /// <returns>A datatable filled with Student details</returns>
        public DataTable StudentDetails(string studentId)
        {
            DataTable dt = new DataTable();

            try
            {
                using (LinqDBDataContext db = new LinqDBDataContext())
                {
                    var details2 = db.Grades.Join(db.Students, g => g.StudentID, s => s.StudentID, (g, s) => new { g = g, s = s })
                                   .Join(db.Subjects, x => x.g.SubjectID, su => su.SubjectID, (x, su) => new { x = x, su = su })
                                   .Join(db.Lecturers, y => y.su.LecturerID, l => l.LecturerID, (y, l) => new { y = y, l = l })
                                   .Where(z => z.y.x.s.StudentID.Equals(studentId))
                                   .Select(z => new
                    {
                        StudentID    = z.y.x.s.StudentID,
                        FirstName    = z.y.x.s.FirstName,
                        LastName     = z.y.x.s.LastName,
                        DateOfBirth  = z.y.x.s.DateOfBirth,
                        Address      = z.y.x.s.Address,
                        SubjectID    = z.y.x.g.SubjectID,
                        SubjectName  = z.y.su.SubjectName,
                        Grade        = z.y.x.g.Grade1,
                        LecturerName = ((z.l.FirstName + " ") + z.l.LastName),
                        DateEntered  = z.y.x.g.DateEntered
                    });

                    dt = LINQtoDataTable(details2);
                }
            }
            catch (SqlException sqlException)
            {
                this.ErrorMessage = "SQL Error: " + sqlException.Message;
                return(null);
            }
            catch (InvalidOperationException invalidOperationException)
            {
                this.ErrorMessage = "SQL Operation Error: " + invalidOperationException.Message;
                return(null);
            }
            catch (Exception e)
            {
                this.ErrorMessage = "Error: " + e.Message;
                return(null);
            }
            return(dt);

            // Query To Datatable
            //IEnumerable<DataRow> details = (from g in db.Grades.AsEnumerable()
            //                                join s in db.Students on g.StudentID equals s.StudentID
            //                                join su in db.Subjects on g.SubjectID equals su.SubjectID
            //                                join l in db.Lecturers on su.LecturerID equals l.LecturerID
            //                                where s.StudentID.Equals(studentId)
            //                                select new { s.StudentID, s.FirstName, s.LastName, s.DateOfBirth, s.Address, g.SubjectID, su.SubjectName, g.Grade1, LecturerName = l.FirstName + " " + l.LastName, g.DateEntered })
            //                                as IEnumerable<DataRow>;


            //return details.CopyToDataTable<DataRow>();

            // Queury
            //var details = from g in db.Grades
            //              join s in db.Students on g.StudentID equals s.StudentID
            //              join su in db.Subjects on g.SubjectID equals su.SubjectID
            //              join l in db.Lecturers on su.LecturerID equals l.LecturerID
            //              where s.StudentID.Equals(studentId)
            //              select (new { s.StudentID, s.FirstName, s.LastName, s.DateOfBirth, s.Address, g.SubjectID, su.SubjectName, g.Grade1, LecturerName = l.FirstName + " " + l.LastName, g.DateEntered });

            //DataTable dt = LINQtoDataTable(details);


            //return dt;
        }