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