public List <Object> GetJoinedTableData()
{
ContosoUniversityEntities contextObj = new ContosoUniversityEntities();
List <Object> newTab = new List <object>();
var joinedTab = contextObj.Students.Join(contextObj.Enrollments, stud => stud.StudentID, enr => enr.StudentID, (stud, enr) => enr); //Joining two tables by StudentID
var groupedTab = from tab in joinedTab
group tab by new { tab.Date, tab.Student.LastName, tab.Student.FirstName, tab.Student.Email, tab.Student.StudentID } into grpTab
select new { ID = grpTab.Key.StudentID, Date = grpTab.Key.Date, FirstName = grpTab.Key.FirstName, LastName = grpTab.Key.LastName, Email = grpTab.Key.Email, Count = grpTab.Select(cache => cache).Count() };
foreach (var entry in groupedTab)
{
newTab.Add(new //Creating new anonymous object in order to change the date for ShortDateString
{
ID = entry.ID,
Date = entry.Date.ToShortDateString(),
FullName = string.Format("{0} {1}", entry.FirstName, entry.LastName),
Email = entry.Email,
Count = entry.Count,
});
}
return(newTab);
}
public void DeleteStudent(int id)
{
ContosoUniversityEntities contextObj = new ContosoUniversityEntities();
contextObj.Students.Remove(contextObj.Students.First(stud => stud.StudentID == id));
contextObj.SaveChanges();
}
public ActionResult List()
{
var db = new ContosoUniversityEntities();
var data = db.Course.ToList();
return(View(data));
}
private static void AddNewRecord(ContosoUniversityEntities db)
{
db.Course.Add(new Course()
{
Title = "Hello 1",
Department = db.Department.Find(5)
});
}
private static void DeleteData(ContosoUniversityEntities db)
{
foreach (var item in db.Course.Where(p => p.CourseID >= 11 && p.CourseID <= 18).ToList())
{
//db.Course.Remove(new Course() { CourseID = item.CourseID });
db.Course.Remove(item);
}
db.Database.ExecuteSqlCommand("DELETE FROM dbo.Course WHERE CourseID >= @p0 AND CourseID <= @p1", 11, 18);
}
private static void 多對多關聯新增(ContosoUniversityEntities db)
{
var c = db.Course.Find(1);
//c.Person.Add(db.Person.Find(3));
c.Person.Add(new Person()
{
FirstName = "AA",
LastName = "BB",
Discriminator = "123"
});
}
private static void UpdateData(ContosoUniversityEntities db)
{
foreach (var item in db.Course.ToList())
{
item.Credits += 1;
}
var course = db.Course.Find(5);
course.Credits = 5;
course.Department = db.Department.Find(2);
}
private void AddingNewEnrollment(int studentId, int courseId)
{
Enrollment newEnrollment = new Enrollment();
ContosoUniversityEntities contextObj = new ContosoUniversityEntities();
//Adding new entry to Enrollment
newEnrollment.CourseID = courseId;
newEnrollment.Date = DateTime.Now;
newEnrollment.StudentID = studentId;
contextObj.Enrollments.Add(newEnrollment);
contextObj.SaveChanges();
}
private void AddingNewStudent(string firstName, string lastName, DateTime birthDate, string email = "Has not specified")
{
Student newStudent = new Student();
ContosoUniversityEntities contextObj = new ContosoUniversityEntities();
//Adding new Student
newStudent.FirstName = firstName;
newStudent.LastName = lastName;
newStudent.BirthDate = birthDate;
newStudent.Email = email;
contextObj.Students.Add(newStudent);
contextObj.SaveChanges();
}
private static void QueryData(ContosoUniversityEntities db)
{
db.Configuration.ProxyCreationEnabled = false;
var depts = db.Department.Include("Course").ToList();
foreach (var dept in depts)
{
Console.WriteLine("部門: " + dept.Name);
foreach (var course in dept.Course)
{
Console.WriteLine("\t" + course.Title);
}
}
}
public override void OnActionExecuting(ActionExecutingContext filterContext)
{
using (var db = new ContosoUniversityEntities())
{
filterContext.Controller.ViewBag.DepartmentList
= (from p in db.Department
select new DepartmentCreationVM()
{
DepartmentId = p.DepartmentID,
Name = p.Name,
Budget = p.Budget,
StartDate = p.StartDate
}).ToList();
}
base.OnActionExecuting(filterContext);
}
public void InsertNewEntry(string firstName, string lastName, DateTime birthDate, string course, string email = "Has not specified")
{
int courseId, studentId = 0;
Student newStudent = new Student();
Enrollment newEnrollment = new Enrollment();
ContosoUniversityEntities contextObj = new ContosoUniversityEntities();
var student = (from stud in contextObj.Students
where stud.FirstName == firstName && stud.LastName == lastName && stud.BirthDate == birthDate && stud.Email == email
select stud).FirstOrDefault();
//In case student doesn't exist
if (student as Student == null)
{
AddingNewStudent(firstName, lastName, birthDate, email);
//Getting the lastly added student ID
foreach (var stud in contextObj.Students)
{
studentId = stud.StudentID;
}
//Finding Course ID
courseId = (from crs in contextObj.Courses
where crs.CourseName == course
select crs).FirstOrDefault().CourseID;
AddingNewEnrollment(studentId, courseId);
}
//In case Student allready exist
else
{
studentId = (student as Student).StudentID;
//Finding Course ID
courseId = (from crs in contextObj.Courses
where crs.CourseName == course
select crs).FirstOrDefault().CourseID;
AddingNewEnrollment(studentId, courseId);
}
}
public void UpdateStudentData(int id, string name, string email = null)
{
ContosoUniversityEntities contextObj = new ContosoUniversityEntities();
Student student = contextObj.Students.First(stud => stud.StudentID == id);
if (name != null)
{
string[] arr = name.Split(' ');
if (arr.Length > 1)
{
student.FirstName = arr[0];
student.LastName = arr[1];
student.Email = email;
contextObj.SaveChanges();
}
}
}
public List <object> GetStudents(string name)
{
ContosoUniversityEntities contextObj = new ContosoUniversityEntities();
string[] arr;
List <object> studentsInfo = new List <object>();
if (!String.IsNullOrEmpty(name))
{
arr = name.Split(' ');
if (arr.Length > 1)
{
var firstName = arr[0];
var lastName = arr[1];
var students = (from stud in contextObj.Students
where stud.FirstName == firstName && stud.LastName == lastName
select stud).ToList <Student>();
if (students != null)
{
foreach (Student stud in students)
{
var student = new //Creating new anonymous object in order to change the date for ShortDateString
{
FirstName = stud.FirstName,
LastName = stud.LastName,
Email = stud.Email,
BirthDate = stud.BirthDate.ToShortDateString(),
StudentID = stud.StudentID
};
studentsInfo.Add(student);
}
}
}
}
return(studentsInfo);
}
public ActionResult List(IList <CourseBatchViewModel> data)
{
var db = new ContosoUniversityEntities();
if (ModelState.IsValid)
{
foreach (var item in data)
{
var dbitem = db.Course.Find(item.CourseID);
dbitem.Title = item.Title;
dbitem.Credits = item.Credits;
}
db.SaveChanges();
//return Json(data);
return(RedirectToAction("List"));
}
else
{
return(View(db.Course.ToList()));
}
}
public List<ObjectPageProperty> FindByObjectPage(string objectName, int pageType)
{
var _entities = new ContosoUniversityEntities();
return _entities.Set<ObjectPageProperty>().Where(obj => obj.ObjectPage.ObjectName == objectName && obj.ObjectPage.PageType == pageType).ToList();
}
static void Main(string[] args)
{
Console.WriteLine(DateTime.Now + "\t" + "Started.");
using (var db = new ContosoUniversityEntities())
{
Console.WriteLine(DateTime.Now + "\t" + "Query Started.");
db.Database.Log = (log) => { Console.WriteLine(log); };
//QueryData(db);
db.Database.Log = null;
//AddNewRecord(db);
//UpdateData(db);
//DeleteData(db);
//多對多關聯新增(db);
var c = db.Course.Find(1);
Console.WriteLine(db.Entry(c).State);
c.Credits++;
Console.WriteLine(db.Entry(c).State);
var ce = db.Entry(c);
Console.WriteLine("修改前: " + ce.OriginalValues.GetValue <int>("Credits"));
Console.WriteLine("修改後: " + ce.CurrentValues.GetValue <int>("Credits"));
var cc = new Course()
{
Title = "Hello 2",
Department = db.Department.Find(5)
};
db.Course.Add(cc);
var ce2 = db.Entry(cc);
if (ce2.State == System.Data.Entity.EntityState.Added)
{
ce2.Entity.CreatedOn = DateTime.Now;
}
db.SaveChanges();
Console.WriteLine(db.Entry(c).State);
//Console.WriteLine(DateTime.Now + "\t" + "SaveChanges Started.");
//try
//{
// db.SaveChanges();
//}
//catch (DbEntityValidationException ex)
//{
// throw ex;
//}
//Console.WriteLine(DateTime.Now + "\t" + "Query Started.");
//QueryData(db);
}
Console.WriteLine(DateTime.Now + "\t" + "Ended.");
}