Esempio n. 1
0
 static void SQL_ins_qry(tect_CodeFirstLingContext ctx, string sqlstr)
 {
     try
     {
         var qry = ctx.Database.ExecuteSqlCommand(sqlstr);
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.Message);
     }
 }
Esempio n. 2
0
 static void lecturers_print()
 {
     using (var ctx = new tect_CodeFirstLingContext())
     {
         List <lecturer> mylist = ctx.lecturers.ToList <lecturer>();
         Console.WriteLine("Lecturers list:");
         foreach (lecturer u in mylist)
         {
             Console.WriteLine("{0}: {1} {2} ", u.lc_id, u.lc_fname, u.lc_lname);
         }
     }
 }
Esempio n. 3
0
 static void emails_print()
 {
     using (var ctx = new tect_CodeFirstLingContext())
     {
         List <email> mylist = ctx.emails.ToList <email>();
         Console.WriteLine("email list:");
         foreach (email u in mylist)
         {
             Console.WriteLine("{0}: {1} {2} ", u.em_Id, u.em_value, u.lc_id);
         }
     }
 }
Esempio n. 4
0
 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();
             }
         }
     }
 }
Esempio n. 5
0
 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();
             }
         }
     }
 }
Esempio n. 6
0
        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();
                    }
                }
            }
        }
Esempio n. 7
0
        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());
                    }
                }
            }
        }
Esempio n. 8
0
        static void Main(string[] args)
        {
            //new Transaction_test().StartOwnTransactionWithinContext();

            using (var ctx = new tect_CodeFirstLingContext())
            {
                IEnumerable <string> script = File.ReadLines(@"D:\Khrolenko\C#\MainAcademy\Module_05\Lesson_06\Hello_CodeFirst_Linq\Hello_CodeFirst\insrt_ling.sql");
                try
                {
                    try
                    {
                        DB_init(ctx);
                    }
                    catch (Exception e)
                    {
                        Console.Write("Initialization error ?\r\n");
                    }
                    int a;

                    do
                    {
                        Console.WriteLine(@"Please,  type the number:                       
                        1.  Clear and insert to the DB by script
                        2.  Insert lecturer with email (one-to-many)
                        3.  Delete lecturer with email (one-to-many)
                        4.  Add new email to lecturer
                        5.  Update lecturer

                        ");
                        try
                        {
                            Console.SetBufferSize(Console.LargestWindowWidth, Console.LargestWindowHeight);
                            a = int.Parse(Console.ReadLine());
                            switch (a)
                            {
                            case 1:
                                Console.WriteLine("Clear and insert to the DB by script  ");

                                break;

                            case 2:
                                Console.WriteLine("Insert lecturer with email (one-to-many) ");

                                break;

                            case 3:
                                Console.WriteLine("Delete lecturer with email (one-to-many) ");

                                break;

                            case 4:
                                Console.WriteLine("Add new email to lecturer ");


                                break;

                            case 5:
                                Console.WriteLine("Update lecturer name ");


                                break;

                            default:
                                Console.WriteLine("Exit");
                                break;
                            }
                        }
                        catch (System.Exception e)
                        {
                            Console.WriteLine("Error: " + e.Message);
                        }
                        finally
                        {
                            Console.ReadLine();
                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine("Press Spacebar to exit; press any key to continue");
                            Console.ForegroundColor = ConsoleColor.White;
                        }
                    }while (Console.ReadKey().Key != ConsoleKey.Spacebar);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }

                Console.ReadKey();
            }
        }
Esempio n. 9
0
 static void DB_init(tect_CodeFirstLingContext ctx)
 {
     // Initialize database at this moment
     ctx.Database.Initialize(false);
 }
Esempio n. 10
0
 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();
             }
         }
     }
 }
Esempio n. 11
0
        static void Main(string[] args)
        {
            using (var ctx = new tect_CodeFirstLingContext())
            {
                List <string> script = null;
                try
                {
                    try
                    {
                        DB_init(ctx);
                    }
                    catch (Exception)
                    {
                        Console.Write("Initialization error ?\r\n");
                    }
                    int a;

                    do
                    {
                        Console.WriteLine(@"Please,  type the number:                       
                        1.  Clear and insert to the DB by script
                        2.  Insert lecturer with email (one-to-many)
                        3.  Delete lecturer with email (one-to-many)
                        4.  Add new email to lecturer
                        5.  Update lecturer
                       
                        ");
                        try
                        {
                            Console.SetBufferSize(Console.LargestWindowWidth, Console.LargestWindowHeight);
                            a = int.Parse(Console.ReadLine());
                            switch (a)
                            {
                            case 1:
                                DB_bulk_read(ref script, "insrt_ling.sql");
                                //Be aware that the email table has the identity key
                                foreach (var item in script)
                                {
                                    SQL_ins_qry(ctx, item.ToString());
                                }
                                lecturers_print();
                                break;

                            case 2:
                                Console.WriteLine("Insert lecturer with email (one-to-many) ");
                                Lect_email_ins_qry();
                                lecturers_print();
                                emails_print();
                                break;

                            case 3:
                                Console.WriteLine("Delete lecturer with email (one-to-many) ");
                                Lect_email_del_qry();
                                lecturers_print();
                                emails_print();
                                break;

                            case 4:
                                Console.WriteLine("Add new email to lecturer ");
                                Lect_add_email_qry();
                                lecturers_print();
                                emails_print();
                                break;

                            case 5:
                                Console.WriteLine("Update lecturer name ");
                                Lect_upd_qry();
                                lecturers_print();
                                break;

                            default:
                                Console.WriteLine("Exit");
                                break;
                            }
                        }
                        catch (System.Exception e)
                        {
                            Console.WriteLine("Error: " + e.Message);
                        }
                        finally
                        {
                            Console.ReadLine();
                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine("Press Spacebar to exit; press any key to continue");
                            Console.ForegroundColor = ConsoleColor.White;
                        }
                    }while (Console.ReadKey().Key != ConsoleKey.Spacebar);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }

                Console.ReadKey();
            }
        }
Esempio n. 12
0
 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();
             }
         }
     }
 }