public Form1() { InitializeComponent(); using (var db = new MusicClassesContext()) { //inserting tutor names in tutor dropdown box in Lesson Records tab var tutors = (from p in db.Person join t in db.Tutor on p.PersonId equals t.PersonPersonId select (p.FirstName + " " + p.LastName)); foreach (string tutor in tutors) { lessonTutorDropdown.Items.Add(tutor); } //inserting lessons in lesson dropdown box in Student and Tutor Records tab var lessons = from l in db.Lesson select l.LessonName; foreach (string l in lessons) { lessonDropdownBox.Items.Add(l.ToString()); lessonsToTeachListbox.Items.Add(l); } //inserting instruments in instrument dropdown box in Student Records tab var instruments = from i in db.Instrument select i.InstrumentName; foreach (string i in instruments) { instrumentDropdownBox.Items.Add(i); lessonInstrumentDropdown.Items.Add(i); } //inserting ensembles in ensembles dropdown box in Student and Tutor Records tab var ensembles = from e in db.Ensemble select e.EnsembleName; foreach (string e in ensembles) { ensembleDropdownBox.Items.Add(e); tutorEnsemblesListBox.Items.Add(e); } //inserting sheetmusic items in sheetmusic listbox in Tutor and Student Records tab var sheetMusicItems = from sm in db.SheetMusic select sm.Title; foreach (string sm in sheetMusicItems) { tutorSheetMusicListbox.Items.Add(sm); sheetMusicListBox.Items.Add(sm); } } }
public void InsertEnsembleRecord() { using (var db = new MusicClassesContext()) { IQueryable<Person> persons = from p in db.Person where p.PersonId != null select p; var ensemble = new Ensemble { EnsembleName = "Beginners Ensemble", EnsembleLevel = 1, Person = persons.ToList() }; db.Ensemble.Add(ensemble); db.SaveChanges(); db.Database.Connection.Close(); } }
public void InsertAddressRecord() { using (var db = new MusicClassesContext()) { string streetAddress = "101 Tramway Road"; string suburb = "Chesapeake Bay"; string townOrCity = "Dunedin"; int postcode = 9012; string landlinePhone = "034567890"; var address = new Address() { StreetAddress = streetAddress, Suburb = suburb, TownOrCity = townOrCity, PostCode = postcode, LandlinePhone = landlinePhone }; string streetAddress2 = "25 Mystery Street"; string suburb2 = "Enigma"; string townOrCity2 = "Dunedin"; int postcode2 = 9012; string landlinePhone2 = "034789012"; var address2 = new Address() { StreetAddress = streetAddress2, Suburb = suburb2, TownOrCity = townOrCity2, PostCode = postcode2, LandlinePhone = landlinePhone2 }; db.Address.Add(address); db.Address.Add(address2); db.SaveChanges(); db.Database.Connection.Close(); } }
public void InsertStudentSheetMusicRecord() { using (var db = new MusicClassesContext()) { int studentID = (from p in db.Person join s in db.Student on p.PersonId equals s.PersonPersonId select s.StudentId).First(); int sheetMusicID = (from sm in db.SheetMusic where sm.Title == "Bohemian Rhapsody" select sm.SheetMusicId).First(); var studentSheetMusic = new StudentSheetMusic() { StudentStudentId = studentID, SheetMusicSheetMusicId = sheetMusicID }; db.StudentSheetMusic.Add(studentSheetMusic); db.SaveChanges(); db.Database.Connection.Close(); } }
public void InsertStudentRecord() { using (var db = new MusicClassesContext()) { short age = 12; decimal lessonFeesOwed = 50.00m; decimal instrumentFeesOwed = 50.00m; int instrumentId = (from i in db.Instrument where i.InstrumentName == "Violin" select i.InstrumentId).First(); int personId = (from p in db.Person where p.FirstName == "Bob" && p.LastName == "Brown" select p.PersonId).First(); var student = new Student() { Age = age, LessonFeesOwed = lessonFeesOwed, InstrumentFeesOwed = instrumentFeesOwed, InstrumentInstrumentId = instrumentId, PersonPersonId = personId, Instrument = (from i in db.Instrument where i.InstrumentId == instrumentId select i).First(), Person = (from p in db.Person where p.PersonId == personId select p).First() }; db.Student.Add(student); db.SaveChanges(); db.Database.Connection.Close(); } }
public void InsertInstrumentRecord() { using (var db = new MusicClassesContext()) { var violin = new Instrument() { InstrumentName = "Violin", HireFee = 40.00m, ConditionStatus = "Ok to use", RepairStatus = "No need for repair" }; db.Instrument.Add(violin); var recorder = new Instrument() { InstrumentName = "Recorder", HireFee = 0.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(recorder); var viola = new Instrument() { InstrumentName = "Viola", HireFee = 40.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(viola); var cello = new Instrument() { InstrumentName = "Cello", HireFee = 80.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(cello); var doubleBass = new Instrument() { InstrumentName = "Double Bass", HireFee = 80.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(doubleBass); var flute = new Instrument() { InstrumentName = "Flute", HireFee = 40.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(flute); var clarinet = new Instrument() { InstrumentName = "Clarinet", HireFee = 40.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(clarinet); var oboe = new Instrument() { InstrumentName = "Oboe", HireFee = 80.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(oboe); var bassoon = new Instrument() { InstrumentName = "Bassoon", HireFee = 80.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(bassoon); var otherWoodwind = new Instrument() { InstrumentName = "Other Woodwind", HireFee = 80.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(otherWoodwind); var trumpet = new Instrument() { InstrumentName = "Trumpet", HireFee = 40.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(trumpet); var trombone = new Instrument() { InstrumentName = "Trombone", HireFee = 40.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(trombone); var frenchHorn = new Instrument() { InstrumentName = "French Horn", HireFee = 80.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(frenchHorn); var tuba = new Instrument() { InstrumentName = "Tuba", HireFee = 80.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(tuba); var otherBrass = new Instrument() { InstrumentName = "Other Brass", HireFee = 80.00m, ConditionStatus = "Ok for use", RepairStatus = "No need for repair" }; db.Instrument.Add(otherBrass); var percussion = new Instrument() { InstrumentName = "Percussion", HireFee = 0.00m, ConditionStatus = "N/A", RepairStatus = "N/A" }; db.Instrument.Add(percussion); var vocalTechnique = new Instrument() { InstrumentName = "Vocal Technique", HireFee = 0.00m, ConditionStatus = "N/A", RepairStatus = "N/A" }; db.Instrument.Add(vocalTechnique); var musicTheory = new Instrument() { InstrumentName = "Music Theory", HireFee = 0.00m, ConditionStatus = "N/A", RepairStatus = "N/A" }; db.Instrument.Add(vocalTechnique); db.SaveChanges(); db.Database.Connection.Close(); } }
public void InsertSheetMusicRecord() { using (var db = new MusicClassesContext()) { var sheetMusic = new SheetMusic() { Title = "Bohemian Rhapsody", ComposerName = "Freddy Mercury", DifficultyLevel = 2, NumberOfAuthCopies = 100, ScoreType = "Orchestral" }; db.SheetMusic.Add(sheetMusic); db.SaveChanges(); db.Database.Connection.Close(); } }
public void InsertPositionsHeldRecord() { using (var db = new MusicClassesContext()) { string positionName = "Violin Tutor"; var positionHeld = new PositionsHeld() { PositionName = positionName }; db.PositionsHeld.Add(positionHeld); db.SaveChanges(); db.Database.Connection.Close(); } }
public void InsertPersonRecord() { using (var db = new MusicClassesContext()) { var ensembleID = (from e in db.Ensemble where e.EnsembleName == "Beginners Ensemble" select e.EnsembleId).FirstOrDefault(); var addressId = (from a in db.Address where a.StreetAddress == "101 Tramway Road" && a.Suburb == "Chesapeake Bay" && a.TownOrCity == "Dunedin" && a.PostCode == 9012 && a.LandlinePhone == "034567890" select a.AddressId).First(); var addressId2 = (from a in db.Address where a.StreetAddress == "25 Mystery Street" && a.Suburb == "Enigma" && a.TownOrCity == "Dunedin" && a.PostCode == 9012 && a.LandlinePhone == "034789012" select a.AddressId).First(); var person = new Person { FirstName = "Bob", LastName = "Brown", EmailAddress = "*****@*****.**", MobileNumber = "0271234567", AddressAddressId = addressId, EnsembleEnsembleId = ensembleID }; //parent (father) of Bob Brown var person2 = new Person { FirstName = "Bob", LastName = "Brown Sr", EmailAddress = "*****@*****.**", MobileNumber = "0271234578", AddressAddressId = addressId, EnsembleEnsembleId = ensembleID }; //parent (mother) of Bob Brown var person3 = new Person { FirstName = "Christina", LastName = "Brown", EmailAddress = "*****@*****.**", MobileNumber = "0271029384", AddressAddressId = addressId, EnsembleEnsembleId = ensembleID }; var person4 = new Person { FirstName = "John", LastName = "Doe", EmailAddress = "*****@*****.**", MobileNumber = "0275647382", AddressAddressId = addressId2, EnsembleEnsembleId = ensembleID }; db.Person.Add(person); db.Person.Add(person2); db.Person.Add(person3); db.Person.Add(person4); db.SaveChanges(); db.Database.Connection.Close(); } }
public void InsertPerformanceRecord() { using (var db = new MusicClassesContext()) { string performanceName = "Orchestra of Wonder - One Night Only"; DateTime performanceDate = new DateTime(2013, 11, 23); TimeSpan startTime = new DateTime(2013, 11, 23, 18, 0, 0).TimeOfDay; TimeSpan finishTime = new DateTime(2013, 11, 23, 19, 30, 0).TimeOfDay; int locationId = (from l in db.Location where l.LocationName == "Symfony Orchestra Hall" select l.LocationId).First(); var performance = new Performance() { PerformanceName = performanceName, PerformanceDate = performanceDate, StartTime = startTime, FinishTime = finishTime, LocationLocationId = locationId, Location = (from l in db.Location where l.LocationId == locationId select l).First() }; db.Performance.Add(performance); db.SaveChanges(); db.Database.Connection.Close(); } }
public void InsertLocationRecord() { using (var db = new MusicClassesContext()) { string locationName = "Symfony Orchestra Hall"; var location = new Location() { LocationName = locationName }; db.Location.Add(location); db.SaveChanges(); db.Database.Connection.Close(); } }
public void InsertLessonRecord() { using (var db = new MusicClassesContext()) { int tutorID = (from t in db.Tutor where t.PersonPersonId == ((from p in db.Person where p.FirstName == "John" && p.LastName == "Doe" select p.PersonId)).FirstOrDefault() select t.TutorId).FirstOrDefault(); int personID = (from p in db.Person where p.FirstName == "John" && p.LastName == "Doe" select p.PersonId).FirstOrDefault(); var tutor = (from t in db.Tutor where t.TutorId == tutorID select t).FirstOrDefault(); var violinLesson = new Lesson() { LessonName = "Percussion", Location = "Symfony Orchestra Hall", Subject = "Percussion", Instrument = (from i in db.Instrument where i.InstrumentName == "Percussion" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 280.00m, LessonDate = new DateTime (2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var recorderLesson = new Lesson() { LessonName = "Recorder", Location = "Symfony Orchestra Hall", Subject = "Recorder", Instrument = (from i in db.Instrument where i.InstrumentName == "Recorder" select i.InstrumentName).First(), StudentFee = 40.00m, OpenFee = 0.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var violaLesson = new Lesson() { LessonName = "Viola", Location = "Symfony Orchestra Hall", Subject = "Viola", Instrument = (from i in db.Instrument where i.InstrumentName == "Viola" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 210.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var celloLesson = new Lesson() { LessonName = "Cello", Location = "Symfony Orchestra Hall", Subject = "Cello", Instrument = (from i in db.Instrument where i.InstrumentName == "Cello" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 240.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var doubleBassLesson = new Lesson() { LessonName = "Double Bass", Location = "Symfony Orchestra Hall", Subject = "Double Bass", Instrument = (from i in db.Instrument where i.InstrumentName == "Double Bass" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 240.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var fluteLesson = new Lesson() { LessonName = "Flute", Location = "Symfony Orchestra Hall", Subject = "Flute", Instrument = (from i in db.Instrument where i.InstrumentName == "Flute" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 240.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var clarinetLesson = new Lesson() { LessonName = "Clarinet", Location = "Symfony Orchestra Hall", Subject = "Clarinet", Instrument = (from i in db.Instrument where i.InstrumentName == "Clarinet" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 210.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var oboeLesson = new Lesson() { LessonName = "Oboe", Location = "Symfony Orchestra Hall", Subject = "Oboe", Instrument = (from i in db.Instrument where i.InstrumentName == "Oboe" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 300.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var bassoonLesson = new Lesson() { LessonName = "Bassoon", Location = "Symfony Orchestra Hall", Subject = "Bassoon", Instrument = (from i in db.Instrument where i.InstrumentName == "Bassoon" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 300.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var otherWoodwindLesson = new Lesson() { LessonName = "Other Woodwind", Location = "Symfony Orchestra Hall", Subject = "Other Woodwind", Instrument = (from i in db.Instrument where i.InstrumentName == "Other Woodwind" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 300.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var trumpetLesson = new Lesson() { LessonName = "Trumpet", Location = "Symfony Orchestra Hall", Subject = "Trumpet", Instrument = (from i in db.Instrument where i.InstrumentName == "Trumpet" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 210.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var tromboneLesson = new Lesson() { LessonName = "Trombone", Location = "Symfony Orchestra Hall", Subject = "Trombone", Instrument = (from i in db.Instrument where i.InstrumentName == "Trombone" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 240.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var frenchHornLesson = new Lesson() { LessonName = "French Horn", Location = "Symfony Orchestra Hall", Subject = "Trombone", Instrument = (from i in db.Instrument where i.InstrumentName == "French Horn" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 240.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var tubaLesson = new Lesson() { LessonName = "Tuba", Location = "Symfony Orchestra Hall", Subject = "Tuba", Instrument = (from i in db.Instrument where i.InstrumentName == "Tuba" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 280.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var otherBrassLesson = new Lesson() { LessonName = "Other Brass", Location = "Symfony Orchestra Hall", Subject = "Other Brass", Instrument = (from i in db.Instrument where i.InstrumentName == "Other Brass" select i.InstrumentName).First(), StudentFee = 50.00m, OpenFee = 240.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var vocalTechniqueLesson = new Lesson() { LessonName = "Vocal Technique", Location = "Symfony Orchestra Hall", Subject = "Vocal Technique", Instrument = (from i in db.Instrument where i.InstrumentName == "Vocal Technique" select i.InstrumentName).First(), StudentFee = 80.00m, OpenFee = 300.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; var musicTheoryLesson = new Lesson() { LessonName = "Music Theory", Location = "Symfony Orchestra Hall", Subject = "Music Theory", Instrument = null, StudentFee = 80.00m, OpenFee = 300.00m, LessonDate = new DateTime(2013, 10, 19), StartTime = new DateTime(2013, 10, 19, 08, 30, 0, DateTimeKind.Local), FinishTime = new DateTime(2013, 10, 19, 09, 0, 0, DateTimeKind.Local), TutorTutorId = tutorID, Tutor = tutor }; db.Lesson.Add(violinLesson); db.Lesson.Add(recorderLesson); db.Lesson.Add(violaLesson); db.Lesson.Add(celloLesson); db.Lesson.Add(doubleBassLesson); db.Lesson.Add(fluteLesson); db.Lesson.Add(oboeLesson); db.Lesson.Add(bassoonLesson); db.Lesson.Add(otherWoodwindLesson); db.Lesson.Add(trumpetLesson); db.Lesson.Add(tromboneLesson); db.Lesson.Add(frenchHornLesson); db.Lesson.Add(tubaLesson); db.Lesson.Add(otherBrassLesson); db.Lesson.Add(vocalTechniqueLesson); db.Lesson.Add(musicTheoryLesson); db.SaveChanges(); db.Database.Connection.Close(); } }
private void showStudentsBtn_Click(object sender, EventArgs e) { using (var db = new MusicClassesContext()) { //////////////////////////////////////////////////////////////////// // The query which retrieves appropriate student records, namely: // // // // - Student's first name // // - Student's last name // // - The student's age // // - The student's suburb and town/city details // // - The name of the lesson the student is taking // // - The name of the instrument the student is learning // // - The amount of lesson fees the student owes // // - The amount of instrument fees the student owes // // - The total amount of fees the student owes // //////////////////////////////////////////////////////////////////// var studentRecordsQuery = from p in db.Person join s in db.Student on p.PersonId equals s.PersonPersonId join sl in db.StudentLesson on s.StudentId equals sl.StudentStudentId join l in db.Lesson on sl.LessonLessonId equals l.LessonId join a in db.Address on p.AddressAddressId equals a.AddressId join i in db.Instrument on s.InstrumentInstrumentId equals i.InstrumentId select new { s.StudentId, p.FirstName, p.LastName, s.Age, a.Suburb, a.TownOrCity, l.LessonName, i.InstrumentName, s.LessonFeesOwed, s.InstrumentFeesOwed, TotalFeesOwed = s.LessonFeesOwed + s.InstrumentFeesOwed }; DataGridViewRow rw = new DataGridViewRow(); studentRecordsView.Rows.Clear(); rw.CreateCells(studentRecordsView); foreach (var s in studentRecordsQuery) { rw.Cells[0].Value = s.StudentId; rw.Cells[1].Value = s.FirstName; rw.Cells[2].Value = s.LastName; rw.Cells[3].Value = s.Age; rw.Cells[4].Value = s.Suburb; rw.Cells[5].Value = s.TownOrCity; rw.Cells[6].Value = s.LessonName; rw.Cells[7].Value = s.InstrumentName; rw.Cells[8].Value = s.LessonFeesOwed; rw.Cells[9].Value = s.InstrumentFeesOwed; rw.Cells[10].Value = s.TotalFeesOwed; } studentRecordsView.Rows.Add(rw); } }
public void InsertTutorPositionsHeldRecord() { using (var db = new MusicClassesContext()) { int tutorId = (from t in db.Tutor where t.PersonPersonId == (from p in db.Person where p.FirstName == "John" && p.LastName == "Doe" select p.PersonId).FirstOrDefault() select t.TutorId).FirstOrDefault(); int positionId = (from p in db.PositionsHeld where p.PositionName == "Violin Tutor" select p.PositionId).First(); var tutorPositionsHeld = new TutorPositionsHeld() { PositionsHeldPositionId = positionId, TutorTutorId = tutorId, PositionsHeld = (from p in db.PositionsHeld where p.PositionId == positionId select p).First(), Tutor = (from t in db.Tutor where t.TutorId == tutorId select t).First() }; db.TutorPositionsHeld.Add(tutorPositionsHeld); db.SaveChanges(); db.Database.Connection.Close(); } }
public void InsertPerformanceSheetMusicRecord() { using (var db = new MusicClassesContext()) { int performanceId = (from p in db.Performance where p.PerformanceName == "Orchestra of Wonder - One Night Only" select p.PerformanceId).First(); int sheetMusicId = (from s in db.SheetMusic where s.Title == "Bohemian Rhapsody" select s.SheetMusicId).First(); var performanceSheetMusic = new PerformanceSheetMusic() { PerformancePerformanceId = performanceId, SheetMusicSheetMusicId = sheetMusicId }; db.PerformanceSheetMusic.Add(performanceSheetMusic); db.SaveChanges(); db.Database.Connection.Close(); } }
public void InsertTutorRecord() { using (var db = new MusicClassesContext()) { int personID = (from p in db.Person where p.FirstName == "John" && p.LastName == "Doe" select p.PersonId).First(); var tutor = new Tutor() { PersonPersonId = personID, Person = (from p in db.Person where p.PersonId == personID select p).First() }; db.Tutor.Add(tutor); db.SaveChanges(); db.Database.Connection.Close(); } }
/** * <summary> * This click handler is responsible for adding and updating Student records * - and also creating records for associated entities * </summary> */ private void submitUpdateBtn_Click(object sender, EventArgs e) { formUtilities.checkFieldNullLengths(firstNameField, formErrors, "First Name"); formUtilities.checkFieldNullLengths(lastNameField, formErrors, "Last Name"); formUtilities.ageFieldCheck(ageField, formErrors); formUtilities.checkFieldNullLengths(streetAddressField, formErrors, "Street Address"); formUtilities.checkFieldNullLengths(suburbField, formErrors, "Suburb"); formUtilities.postCodeFieldCheck(postCodeField, formErrors); formUtilities.checkFieldNullLengths(studentTownOrCityField, formErrors, "Town or City"); formUtilities.landlineNumberValidation(landlinePhoneField, formErrors); formUtilities.mobileNumberValidation(mobilePhoneField, formErrors); formUtilities.validateEmailField(emailAddressField, formErrors); formUtilities.checkDropdownBox(lessonDropdownBox, formErrors, "Lesson"); formUtilities.checkDropdownBox(instrumentDropdownBox, formErrors, "Instrument"); formUtilities.checkListBox(sheetMusicListBox, formErrors, "Sheet Music"); formUtilities.checkDropdownBox(ensembleDropdownBox, formErrors, "Ensemble"); formUtilities.lessonFeesFieldCheck(lessonFeesOwedField, formErrors); formUtilities.instrumentFeesFieldCheck(instrumentFeesOwedField, formErrors); formUtilities.totalFeesFieldCheck(totalFeesOwedField, formErrors); if (formErrors.ToString().Length != 0) { MessageBox.Show(formErrors.ToString()); //Setting the error message string back to zero length, //so continuous submit attempts don't keep appending //subsequent error messages. formErrors = new StringBuilder(); } else { using (var db = new MusicClassesContext()) { //Creating new Address object - using the appropriate form inputs //from the Student Records pane. var address = new Address() { StreetAddress = streetAddressField.Text.ToString(), Suburb = suburbField.Text.ToString(), TownOrCity = studentTownOrCityField.Text.ToString(), PostCode = int.Parse(postCodeField.Text.ToString()), LandlinePhone = landlinePhoneField.Text.ToString() }; //Add the new Address object to the database db.Address.Add(address); //Save the changes that have just been made - after the //new Address object has been created and added db.SaveChanges(); //Close the current conenction to the database, so //other functions and record additions can be executed. db.Database.Connection.Close(); //Creating new Person object - using the appropriate form inputs //from the Student Records pane. var person = new Person() { FirstName = firstNameField.Text, LastName = lastNameField.Text, EmailAddress = emailAddressField.Text, MobileNumber = mobilePhoneField.Text, //A LINQ query, which obtains the Id for the Ensemble chosen by the user - //determined by the Ensemble name selected form the Student records pane. EnsembleEnsembleId = (from ensemble in db.Ensemble where ensemble.EnsembleName == ensembleDropdownBox.Text select ensemble.EnsembleId).FirstOrDefault(), //A LINQ query to obtain the Id of the Address record (of the current Student //being added) that was just added. AddressAddressId = (from a in db.Address where a.StreetAddress == address.StreetAddress && a.Suburb == address.Suburb && a.TownOrCity == address.TownOrCity && a.PostCode == address.PostCode && a.LandlinePhone == address.LandlinePhone select a.AddressId).FirstOrDefault(), //A LINQ query to obtain the Ensemble object required for reverse navigation //- using the Ensemble selected by the user. Ensemble = (from en in db.Ensemble where en.EnsembleId == (from ensemble in db.Ensemble where ensemble.EnsembleName == ensembleDropdownBox.Text select ensemble.EnsembleId).FirstOrDefault() select en).First(), //A LINQ query to create the Address object required for reverse navigation //- using the Address record created by the user earlier. Address = (from ad in db.Address where ad.AddressId == (from a in db.Address where a.StreetAddress == address.StreetAddress && a.Suburb == address.Suburb && a.TownOrCity == address.TownOrCity && a.PostCode == address.PostCode && a.LandlinePhone == address.LandlinePhone select a.AddressId).FirstOrDefault() select ad).FirstOrDefault() }; //Add the new Person object to the database db.Person.Add(person); //Save the changes that have been made - by the new record being added. db.SaveChanges(); //Close the connection to the database, so other methods and functions can //use the connection. db.Database.Connection.Close(); //Creating a new Student record - with the necessary Person record //created earlier. string instrumentName = instrumentDropdownBox.Text.ToString(); var student = new Student() { //Converting the input for Age into a short Age = short.Parse(ageField.Text.ToString()), //Converting the inout for Lesson Fees Owed into decimal format LessonFeesOwed = Convert.ToDecimal(lessonFeesOwedField.Text.ToString()), //Converting the inout for Instrument Fees Owed into decimal format InstrumentFeesOwed = Convert.ToDecimal(instrumentFeesOwedField.Text.ToString()), //A LINQ query to obtain the associated InstrumentId with the Instrument //selected by the user from the interface. InstrumentInstrumentId = (from i in db.Instrument where i.InstrumentName == instrumentName select i.InstrumentId).FirstOrDefault(), //Associating the Person record, created for the new Student, with the current Student object //being constructed. PersonPersonId = person.PersonId, Instrument = (from i in db.Instrument where i.InstrumentId == (from inst in db.Instrument where inst.InstrumentName == instrumentDropdownBox.Text select inst.InstrumentId).FirstOrDefault() select i).FirstOrDefault(), //A LINQ query to obtain the Person object required for reverse navigation //- using the Person object created for the Student earlier. Person = (from p in db.Person where p.PersonId == person.PersonId select p).FirstOrDefault() }; //Add the student to the database db.Student.Add(student); //Save the changes made to the database - made by the addition of the Student record. db.SaveChanges(); //Close the current connection to the database, so other record creations and method/function/ //handler calls can use the connection. db.Database.Connection.Close(); //Creating the many-many record that relates Students with Lessons - //(Yes, I only found out on 17/10/2013 I could have done this better //with the Fluent API. var studentLesson = new StudentLesson() { //Obtaining the StudentId from the Student object created earlier. StudentStudentId = student.StudentId, //Obtaining the LessonId from the Lesson the user selected form the //interface. LessonLessonId = (from l in db.Lesson where l.LessonName == lessonDropdownBox.Text select l.LessonId).FirstOrDefault(), //Obtaining the Lesson object required for reverse navigation. Lesson = (from l in db.Lesson where l.LessonId == (from le in db.Lesson where le.LessonName == lessonDropdownBox.Text select le.LessonId).FirstOrDefault() select l).FirstOrDefault(), //Obtaining the Student object required for reverse navigation. Student = student }; //Add the student record to the database. db.StudentLesson.Add(studentLesson); //Save the changes that have just been made to the database. db.SaveChanges(); //Close the connection so other method/function/handler calls //can use the connection. db.Database.Connection.Close(); string[] sheetMusicRecords = new string[sheetMusicListBox.SelectedItems.Count]; sheetMusicListBox.SelectedItems.CopyTo(sheetMusicRecords, 0); //Creating the many-many object that links Students //with SheetMusic records. foreach (string smc in sheetMusicRecords) { var studentSheetMusic = new StudentSheetMusic() { //Obtaining the StudentId from the Student object created earlier. StudentStudentId = student.StudentId, //Obtaining the SheetMusicId from the SheetMusic item object //selected by the user. SheetMusicSheetMusicId = (from s in db.SheetMusic where s.Title == smc select s.SheetMusicId).FirstOrDefault(), //Obtaining the Student object required for reverse navigation. Student = student, //Obtaining the SheetMusic object required for reverse navigation, //obtained from the SheetMusic item selected by the user, SheetMusic = (from sm in db.SheetMusic where sm.SheetMusicId == (from s in db.SheetMusic where s.Title == smc select s.SheetMusicId).FirstOrDefault() select sm).FirstOrDefault() }; //Add the StudentSheetMusic record to the database. db.StudentSheetMusic.Add(studentSheetMusic); //Save the changes made to the database. db.SaveChanges(); } //Close the connection db.Database.Connection.Close(); } } }