public void StartOwnTransactionWithinContext() { using (var ctx = new tect_CodeFirstLingContext()) { using (var dbContextTransaction = ctx.Database.BeginTransaction()) { try { var lecturerList = ctx.lecturers.ToList <lecturer>(); //Perform create operation Console.WriteLine("Perform create operation"); ctx.lecturers.Add(new lecturer() { lc_id = "L_2", lc_fname = "Second lecturer" }); //Execute Inser, Update & Delete queries in the database ctx.SaveChanges(); var lects1 = ctx.lecturers.ToList <lecturer>(); //Perform Update operation Console.WriteLine("Perform Update operation"); var lecturerList1 = ctx.lecturers.ToList <lecturer>(); lecturer lecturerToUpdate = lecturerList1.Where(s => s.lc_fname == "Second lecturer").FirstOrDefault <lecturer>(); lecturerToUpdate.lc_fname = "Edited second lecturer"; //Execute Inser, Update & Delete queries in the database ctx.SaveChanges(); dbContextTransaction.Commit(); } catch (Exception) { dbContextTransaction.Rollback(); } } } }
static void Lect_email_del_qry() { using (var ctx = new tect_CodeFirstLingContext()) { using (var dbContextTransaction = ctx.Database.BeginTransaction()) { try { Console.WriteLine("Type delete key"); string new_lc_id = Console.ReadLine().ToString(); List <lecturer> lecturers_to_del = ctx.lecturers .Where(u => u.lc_id == new_lc_id).ToList(); List <email> emails_to_del = ctx.lecturers .Where(u => u.lc_id == new_lc_id) .SelectMany(u => u.emails) .OrderBy(p => p.em_Id) .ToList(); Console.WriteLine("Are you sure ? Type Y "); if (Console.ReadKey().Key == ConsoleKey.Y) { foreach (var item in emails_to_del) { ctx.emails.Remove(item); } foreach (var item in lecturers_to_del) { ctx.lecturers.Remove(item); } ctx.SaveChanges(); dbContextTransaction.Commit(); } else { dbContextTransaction.Rollback(); } } catch (Exception ex) { Console.WriteLine(ex.Message); dbContextTransaction.Rollback(); } } } }
static void Lect_upd_qry() { using (var ctx = new tect_CodeFirstLingContext()) { using (var dbContextTransaction = ctx.Database.BeginTransaction()) { try { Console.WriteLine("Type lecturer key"); string new_lc_id = Console.ReadLine().ToString(); Console.WriteLine("Type first name"); string new_lc_fname = Console.ReadLine().ToString(); Console.WriteLine("Type last name"); string new_lc_lname = Console.ReadLine().ToString(); List <lecturer> lecturers_to_upd = ctx.lecturers .Where(u => u.lc_id == new_lc_id).ToList(); Console.WriteLine("Are you sure ? Type Y "); if (Console.ReadKey().Key == ConsoleKey.Y) { foreach (var item in lecturers_to_upd) { item.lc_fname = new_lc_fname; item.lc_lname = new_lc_lname; } ctx.SaveChanges(); dbContextTransaction.Commit(); } else { dbContextTransaction.Rollback(); } } catch (Exception ex) { Console.WriteLine(ex.Message); dbContextTransaction.Rollback(); } } } }
public void StartOwnTransactionWithinContext() { using (var ctx = new tect_CodeFirstLingContext()) { using (var dbContextTransaction = ctx.Database.BeginTransaction()) { try { var lecturerList = ctx.lecturers.ToList <lecturer>(); foreach (var item in ctx.lecturers) { Console.WriteLine(item.lc_lname + " " + item.lc_fname); } Console.WriteLine("---------------------------------"); //Perform create operation Console.WriteLine("Perform create operation"); ctx.lecturers.Add(new lecturer() { lc_id = "L_8", lc_fname = "Second lecturer", lc_lname = "sdfs" }); //Execute Inser, Update & Delete queries in the database ctx.SaveChanges(); var lects1 = ctx.lecturers.ToList <lecturer>(); foreach (var item in ctx.lecturers) { Console.WriteLine(item.lc_lname + " " + item.lc_fname); } Console.WriteLine("---------------------------------"); //Perform Update operation Console.WriteLine("Perform Update operation"); var lecturerList1 = ctx.lecturers.ToList <lecturer>(); lecturer lecturerToUpdate = ctx.lecturers.Where(s => s.lc_fname == "Second lecturer").FirstOrDefault <lecturer>(); lecturerToUpdate.lc_fname = "Edited second"; //Execute Inser, Update & Delete queries in the database ctx.SaveChanges(); Console.WriteLine("Update is succesfully!"); dbContextTransaction.Commit(); foreach (var item in ctx.lecturers) { Console.WriteLine(item.lc_lname + " " + item.lc_fname); } } catch (DbEntityValidationException e) { foreach (var eve in e.EntityValidationErrors) { Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:", eve.Entry.Entity.GetType().Name, eve.Entry.State); foreach (var ve in eve.ValidationErrors) { Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage); } } throw; } catch (System.Data.Entity.Infrastructure.DbUpdateException e) { Console.WriteLine(e.GetBaseException()); } } } }
static void Lect_add_email_qry() { using (var ctx = new tect_CodeFirstLingContext()) { using (var dbContextTransaction = ctx.Database.BeginTransaction()) { try { Console.WriteLine("Type update lector key"); string new_lc_id = Console.ReadLine().ToString(); Console.WriteLine("Type email to add"); string new_email = Console.ReadLine().ToString(); var itemNamesByCategory = from i in ctx.emails where i.em_value == new_email group i by i.lc_id into g select new { Lecturer_inst = g.Key, Items = g }; StringBuilder res = new StringBuilder(); foreach (var item in itemNamesByCategory) { res.AppendLine("Lecturer: " + item.Lecturer_inst); foreach (var group in item.Items) { res.AppendLine(group.lc_id + " " + group.em_value); res.AppendLine(); } } Console.WriteLine(); Console.WriteLine("All lecturers with such email: \r\n" + res); Console.WriteLine(); var itemNamesByCategory1 = itemNamesByCategory .Where(i => i.Lecturer_inst == new_lc_id);; StringBuilder res1 = new StringBuilder(); foreach (var item in itemNamesByCategory1) { res1.AppendLine("Lecturer: " + item.Lecturer_inst); foreach (var group in item.Items) { res1.AppendLine(group.lc_id + " " + group.em_value); res1.AppendLine(); } } if (itemNamesByCategory1.Count() == 0) { Console.WriteLine(); Console.WriteLine("Are you sure ? Type Y "); if (Console.ReadKey().Key == ConsoleKey.Y) { ctx.emails.Add(new email() { lc_id = new_lc_id, em_value = new_email }); ctx.SaveChanges(); dbContextTransaction.Commit(); } else { dbContextTransaction.Rollback(); } } else { Console.WriteLine("Duplicate email :" + res1); dbContextTransaction.Rollback(); } } catch (Exception ex) { Console.WriteLine(ex.Message); dbContextTransaction.Rollback(); } } } }
static void Lect_email_ins_qry() { using (var ctx = new tect_CodeFirstLingContext()) { using (var dbContextTransaction = ctx.Database.BeginTransaction()) { try { var lecturerList = ctx.lecturers.ToList <lecturer>(); var lecturerItems = lecturerList.OrderByDescending(i => i.lc_id).Take <lecturer>(1); Console.WriteLine("Find lecturer max key "); Console.WriteLine("Max: {0}", lecturerItems.ElementAt(0).lc_id); Console.WriteLine("Insert new key"); string new_lc_id = Console.ReadLine().ToString(); Console.WriteLine("Insert new first name"); string new_lc_fname = Console.ReadLine().ToString(); Console.WriteLine("Insert new last name"); string new_lc_lname = Console.ReadLine().ToString(); Console.WriteLine("Insert new email"); string new_email = Console.ReadLine().ToString(); List <lecturer> chlect = ctx.lecturers.ToList(); lecturer lectToUpd = chlect.Where(i => i.lc_id == new_lc_id).FirstOrDefault <lecturer>(); if (lectToUpd == null) { List <email> chmail = ctx.emails.ToList(); email emailToUpdate = chmail.Where(s => s.lc_id == new_lc_id).FirstOrDefault <email>(); if (emailToUpdate == null) { ctx.lecturers.Add(new lecturer() { lc_id = new_lc_id, lc_fname = new_lc_fname, lc_lname = new_lc_lname }); ctx.emails.Add(new email { em_value = new_email, lc_id = new_lc_id }); Console.WriteLine("Are you sure ? Type Y "); if (Console.ReadKey().Key == ConsoleKey.Y) { ctx.SaveChanges(); dbContextTransaction.Commit(); } else { dbContextTransaction.Rollback(); } } else { Console.WriteLine("Foreign key problem, email for this lc_id exists"); dbContextTransaction.Rollback(); } } else { Console.WriteLine("Duplicate key problem, lc_id exists"); dbContextTransaction.Rollback(); } } catch (Exception ex) { Console.WriteLine(ex.Message); dbContextTransaction.Rollback(); } } } }