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