public async Task InsertStudent()
        {
            //In order to create or insert new date we first instantiate a new type. We then update the values of the properties. Next we add it to the underlying set of the context and then save that back to the database.


            using (var context = new collegestudentsEntities())
            {
                var contact = new Student_Details();
                contact.Name       = Name;
                contact.Address    = Address;
                contact.Phone      = Phone;
                contact.CourseIDFK = CourseIDFK;
                //Add to entity set of context
                context.Student_Details.Add(contact);


                // add new marks to cascade up the student details.

                var marks = new Mark();
                marks.Marks1   = 0;
                marks.marks2   = 0;
                marks.Marks3   = 0;
                marks.Marks4   = 0;
                marks.StudIDFK = contact.StudID;
                context.Marks.Add(marks);

                await context.SaveChangesAsync();

                //      ClearTextBoxes();
            }
        }
        public void MarksBest()
        {
            //get the average of the marks using entity
            using (var context = new collegestudentsEntities())
            {
                var StudentMarks = (from m in context.Marks

                                    select new
                {
                    m.Student_Details.Name,
                    //get the total marks
                    totalmarks = m.Marks1 + m.marks2 + m.Marks3 +
                                 m.Marks4,
                });

                DGVMarks.DataSource = StudentMarks.OrderByDescending(s => s.totalmarks).ToList();

                StudentMarks.OrderByDescending(s => s.totalmarks).Select(s => s.Name).FirstOrDefault();


                BestWorstStudent = "Best Student " + StudentMarks.OrderByDescending(s => s.totalmarks).Select(s => s.Name).FirstOrDefault().ToString() + Environment.NewLine;

                BestWorstStudent += "Worst Student " + StudentMarks.OrderBy(s => s.totalmarks).Select(s => s.Name).FirstOrDefault().ToString();
            }
        }
        public void NoMarks()
        {
            using (var context = new collegestudentsEntities())
            {
                //    var allStudentLambda = context.Marks.Select(s => s.StudIDFK).ToList();
                //      var NoMatch = context.Student_Details.Select(s => s.Name).Where(s => allStudentLambda.Contains(s.StudID)).ToList();

                //var alldata = from s in context.Marks
                //              where s.Marks1 < 0

                //              select new
                //              {
                //                  s.Student_Details.Name
                //              };


                //into output  s.StudIDFK != s.Student_Details.StudID

                //  from o in output.DefaultIfEmpty()
                //to add the default to the right hand side


                //o.Student_Details.Name,
                //o.Student_Details.StudID,

                var alldata = from s in context.Student_Details
                              join m in context.Marks.DefaultIfEmpty()
                                                                //    where m.StudIDFK == null
                                                                //       join m in context.Marks
                                                                //this assumes there IS a student IDFK
                              on s.StudID equals m.StudIDFK into output
                                                                //  where s.Marks.Any()
                              from o in output.DefaultIfEmpty() //to add the default to the right hand side
                              select new
                {
                    o.Student_Details.Name,
                    o.Student_Details.StudID,
                    o.Marks1,
                    o.marks2,
                    o.Marks3,
                    o.Marks4
                };



                // foreach (var person in alldata)
                //{

                //          listBox1.Items.Add(person);


                //}

                //    dataGridView1.DataSource = alldata.ToList();

                //    dataGridView1.DataSource = NoMatch.ToList();   //alldata.ToList();
            }
        }
예제 #4
0
 /// <summary>
 ///     Navigation property should be of entity type of collection of related entities. Including some navigation property
 ///     means joining your current entity with some related entity or entities. That allows eager loading of data from
 ///     several tables in single query. LastName is not a navigation property - it is simple field, and it will be loaded
 ///     by default, you don't need to include it:
 ///     http://stackoverflow.com/questions/20637776/a-specified-include-path-is-not-valid-the-entitytype-does-not-declare-a-navigat
 /// </summary>
 /// <returns></returns>
 public IEnumerable EagerLoading()
 {
     using (var context = new collegestudentsEntities())
     {
         var eagerloadingCourse = context.Student_Details.Select(c => c.CourseIDFK);
         //   var eagerloadingCourse = context.Courses.Include("CourseID.CourseName");
         return(eagerloadingCourse.ToList());
     }
 }
예제 #5
0
 public IEnumerable FillTheCourseComboBox()
 {
     // this query fills the course combobox. collegestudentsEntities
     using (var context = new collegestudentsEntities())
     {
         //create a query to grab the course name and ID
         var courses = from c in context.Courses select new { c.CourseName, c.CourseID };
         return(courses.ToList());
     }
 }
        public IEnumerable AllStudentsDirect()
        {
            //Our standard Using statement passing all the data to context
            using (var context = new collegestudentsEntities())
            {
                var students = (from s in context.Student_Details
                                select new { s.StudID, s.Name, s.Phone, s.Address, s.Course.CourseName }).ToList();

                return(students);
            }
        }
        public async Task UpdateStudent()
        {
            using (var context = new collegestudentsEntities())
            {
                var query   = from s in context.Student_Details where s.StudID == StudID select s;
                var student = query.FirstOrDefault(); //gets the first one although we know there is only one
                student.Name       = Name;
                student.Address    = Address;
                student.Phone      = Phone;
                student.CourseIDFK = CourseIDFK;

                await context.SaveChangesAsync();
            }
        }
예제 #8
0
 public IEnumerable AllCourses()
 {
     //Our standard Using statement passing all the data to context
     using (var context = new collegestudentsEntities())
     {
         //What ever we want our code to do we do it in here
         //Pass our fields across to a variable
         var alldata = from c in context.Courses
                       select new
         {
             c.CourseID,
             c.CourseName,
             c.CourseTime
         };
         return(alldata.ToList());
     }
 }
        public async Task Delete()
        {
            //Our standard Using statement passing all the data to context

            using (var context = new collegestudentsEntities())
            {
                //select the row you want to delete

                var contact = (from s in context.Student_Details where s.StudID == StudID select s).SingleOrDefault();

                //run remove command
                context.Student_Details.Remove(contact);
                //save the changes
                await context.SaveChangesAsync();

                //ClearTextBoxes();
            }
        }
        //http://stackoverflow.com/questions/19126088/entity-cant-pass-short-int16-values-out
        public void MarksSummary(string StudentID)
        {
            int ID = Convert.ToInt32(StudentID);

            //get the average of the marks using entity
            using (var context = new collegestudentsEntities())
            {
                var StudentMarks = (from m in context.Marks
                                    where m.StudIDFK == ID
                                    select new
                {
                    m.Marks1,
                    m.marks2,
                    m.Marks3,
                    m.Marks4
                }).ToList();

                //need to get it out of the entity and into a list and work on the list

                var Marklist = new List <int>();
                //Marklist = StudentMarks.Select(s => new {s.Marks1, s.marks2, s.Marks3, s.Marks4}).ToList();

                //   Marklist = StudentMarks.ToList();

                // MarkList =  StudentMarks.ToList().ForEach(m => Marklist.Add(m));

                //  Marklist.AddRange(StudentMarks.ToList());

                foreach (var s in StudentMarks)
                {
                    Marklist.Add((int)s.Marks1);
                    Marklist.Add((int)s.marks2);
                    Marklist.Add((int)s.Marks3);
                    Marklist.Add((int)s.Marks4);
                }

                // double av = Convert.ToDouble(Marklist.Average());
                MarksAverage    = Marklist.Average().ToString();
                MarksOverFifty  = Marklist.Count(s => s > 50).ToString();
                MarksUnderFifty = Marklist.Count(s => s < 50).ToString();
            }
        }
        public ComboBox FillTheStudentCBX()
        {
            // this query fills the course combobox.
            var cbx = new ComboBox();

            using (var context = new collegestudentsEntities())
            {
                //create a query to grab the course name and ID
                var students = from s in context.Student_Details select new { s.Name, s.StudID };
                //pass all the data to a ComboBox
                cbx.DataSource = students.ToList();
                //set the display member - what it shows - to the course name
                cbx.DisplayMember = "Name";
                //set the value member - what data is returned - to the ID
                cbx.ValueMember = "StudID";
                //   cbx.SelectedIndex = 1;
            }

            return(cbx);
        }
        public void AllMarks()
        {
            //using navigation
            using (var context = new collegestudentsEntities())
            {
                var alldata = from m in context.Marks
                              where m.StudIDFK == m.Student_Details.StudID

                              select new
                {
                    m.Student_Details.Name,
                    m.Student_Details.StudID,
                    m.Marks1,
                    m.marks2,
                    m.Marks3,
                    m.Marks4
                };
                DGVMarks.DataSource = alldata.ToList();

                // context.SaveChanges();
            }
        }
        public void StudentsAndMarks3()
        {
            //using the ANY()
            using (var context = new collegestudentsEntities())
            {
                var alldata = from s in context.Student_Details
                              where s.Marks.Any()

                              select new
                {
                    s.Name,
                    s.StudID,
                };
                // dataGridView1.DataSource = alldata.ToList();
                //context.SaveChanges();

                //foreach (var item in alldata)
                //{
                //    listBox1.Items.Add(item.Name + " " + item.markOne);
                //}
            }
        }
예제 #14
0
 private void tsInsertMarks_Click(object sender, EventArgs e)
 {
     if (txtmarks1.Text != string.Empty && txtmarks2.Text != string.Empty && txtmarks3.Text != string.Empty &&
         txtmarks4.Text != string.Empty)
     {
         using (var context = new collegestudentsEntities())
         {
             var NewMarks = new Mark();
             NewMarks.Marks1   = short.Parse(txtmarks1.Text);
             NewMarks.marks2   = short.Parse(txtmarks2.Text);
             NewMarks.Marks3   = short.Parse(txtmarks3.Text);
             NewMarks.Marks4   = short.Parse(txtmarks4.Text);
             NewMarks.StudIDFK = Convert.ToInt32(cbxStudents.SelectedValue);
             //Add to entity set of context
             context.Marks.Add(NewMarks);
             context.SaveChanges();
             myMarks.AllMarks();
         }
     }
     else
     {
         MessageBox.Show("Please complete all the fields");
     }
 }
        //http://msdn.microsoft.com/en-us/library/bxt3k60s%28v=vs.110%29.aspx - important

        //not sure the async is doing anything
        public async Task AllStudents()
        {
            //Our standard Using statement passing all the data to context
            using (var context = new collegestudentsEntities())
            {
                //What ever we want our code to do we do it in here
                //Pass our fields across to a variable
                // var students = from s in context.Student_Details select s;

                var students = await(from s in context.Student_Details
                                     select new { s.StudID, s.Name, s.Phone, s.Address, s.Course.CourseName })
                               .ToArrayAsync();

                // var students2 = context.Student_Details.Select(s => new {s.StudID, s.Name, s.Phone});


                DGVStudent.AutoGenerateColumns = false;
                //      dataGridView1.AutoSize = true;
                DGVStudent.AutoSizeColumnsMode =
                    DataGridViewAutoSizeColumnsMode.AllCells;
                // Set the column header style.
                //DataGridViewCellStyle columnHeaderStyle = new DataGridViewCellStyle();
                //dataGridView1.ColumnHeadersDefaultCellStyle = columnHeaderStyle;
                //dataGridView1.Rows.Clear();
                //dataGridView1.Columns.Clear();
                //create the columns in the dgv
                DGVStudent.ColumnCount     = 7;
                DGVStudent.Columns[0].Name = "ID";
                DGVStudent.Columns[1].Name = "Name";
                DGVStudent.Columns[2].Name = "Phone";
                DGVStudent.Columns[3].Name = "Address";
                DGVStudent.Columns[4].Name = "Course Name";
                DGVStudent.Columns[5].Name = "Current";
                //   dataGridView1.Columns[5].Visible = false;
                //pass data across to the dgv

                //   students.ForEachAsync(s => dataGridView1.Rows.Add(s.StudID, s.Name, s.Phone, s.Address, s.Course));,
                //   List<bool> CurrentStudents = new List<bool>();


                foreach (var s in students.ToArray()) //add data to the dgv
                {
                    DGVStudent.Rows.Add(s.StudID, s.Name, s.Phone, s.Address, s.CourseName);
                }

                //        CurrentStudents.Add(Convert.ToBoolean(s.IsCurrentStudent));


                //http://stackoverflow.com/questions/19484551/c-sharp-entity-navigation-not-working-when-returning-all-data-to-a-manually-crea

                //-------------CheckBoxes----------------------

                var checkBox = new DataGridViewCheckBoxColumn();

                checkBox.ThreeState       = false;
                checkBox.Name             = "Enrolled";
                checkBox.DisplayIndex     = 6;
                checkBox.FalseValue       = "False";
                checkBox.TrueValue        = "True";
                checkBox.DataPropertyName = "Current";
                //  checkBox.CellTemplate.Style.BackColor = System.Drawing.Color.LightBlue;
                //   checkBox.CellTemplate = new DataGridViewCheckboxCellFilter();

                //loop through all data and change cells accordingly
                foreach (DataGridViewRow dr in DGVStudent.Rows)
                {
                    if (dr.Cells[5].Value != null)
                    {
                        if (dr.Cells[5].Value.ToString() == "True")
                        {
                            //   dr.Cells[6].Value = "True";
                            //    dr.DataGridView.Name.Where(d => d.Equals("Enrolled")).Select(d => d.);
                            dr.Cells[0].Style.BackColor = Color.Chartreuse;
                        }
                        else
                        {
                            dr.Cells[1].Value = dr.Cells[5].Value;
                            //   dr.Cells[6].Value = "False";

                            dr.Cells[0].Style.BackColor = Color.Black;
                        }
                    }
                }


                DGVStudent.Columns.Add(checkBox);


                //---------- combobox added ------------------------

                var coursename = await(from c in context.Courses select new { c.CourseName, c.CourseID }).ToListAsync();

                //http://msdn.microsoft.com/en-us/library/ms404353.aspx
                //http://nickstips.wordpress.com/2010/11/19/c-datagridviewcomboboxcolumn-displaying-different-values-in-drop-down-list/
                //http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.columns.aspx


                var comboBoxColumn = new DataGridViewComboBoxColumn();
                comboBoxColumn.DataSource    = coursename.ToList();
                comboBoxColumn.HeaderText    = "Course Name";
                comboBoxColumn.ValueMember   = "CourseID";
                comboBoxColumn.DisplayMember = "Coursename";
                //cell is blank unless you click on it
                comboBoxColumn.DisplayStyleForCurrentCellOnly = true;
                //   comboBoxColumn.DataPropertyName = "1";
                //set the column number that you want it to be in
                comboBoxColumn.DisplayIndex = 7;
                DGVStudent.Columns.Add(comboBoxColumn);

                //---------- Buttons added ------------------------

                //http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridviewbuttoncell.aspx
                //just an ordinary button
                var BtnDelete = new Button();
                BtnDelete.Text = "Delete?";
                //    BtnDelete.Click += new EventHandler(BtnDelete_click);
                BtnDelete.Dock = DockStyle.Top;
                //     Controls.Add(BtnDelete);


                // Add a button column.
                var buttonColumn = new DataGridViewButtonColumn();
                buttonColumn.DisplayIndex = 8;
                buttonColumn.HeaderText   = "Delete S";
                buttonColumn.Name         = "Delete Request";
                buttonColumn.Text         = "Delete?";
                buttonColumn.UseColumnTextForButtonValue = true;

                DGVStudent.Columns.Add(buttonColumn);
                //does this even work, we are not saving changes
                //  await context.SaveChangesAsync();
            }
            //------------Add a new Column ---------------------------
            //                var CourseNameForStudents = from s in context.Student_Details select s.Course.CourseName;
            //              DataGridViewTextBoxColumn CourseNameColumn = new DataGridViewTextBoxColumn();

            //              CourseNameColumn.HeaderText = "Course Name";

            //              foreach (var c in CourseNameForStudents.ToArray())
            //              {

            //              }
            //dataGridView1.Columns.Insert(4, CourseNameColumn);
            // Add a CellClick handler to handle clicks in the button column.
            //dataGridView1.CellClick +=
            //    new DataGridViewCellEventHandler(dataGridView1_CellContentClick);

            //   dataGridView1.DataSource = students.ToList();
            //save any changes
        }