public static void BuchungAnlegen() { Console.WriteLine("BuchungAnlegen"); using (WWWingsContext ctx = new WWWingsContext()) { var flight = ctx.FlightSet.FirstOrDefault(); var pas = ctx.PassengerSet.FirstOrDefault(); Console.WriteLine($"Passenger {pas.PersonID} -> Flight {flight.FlightNo}"); #region Vorbereitung // nur für die idempotente Demo: Booking vorher löschen, wenn schon da :-) var b_alt = ctx.BookingSet.FirstOrDefault(x => x.FlightNo == flight.FlightNo && x.PassengerID == pas.PersonID); if (b_alt != null) { ctx.BookingSet.Remove(b_alt); ctx.SaveChanges(); } #endregion // so würde man das in EF 6.x machen: //flight.PassengerSet.Add(pas); // in EF Core braucht man Zwischenobjekt Booking mit den PKs als FKs :-( var b = new Booking(); b.FlightNo = flight.FlightNo; b.PassengerID = pas.PersonID; ctx.BookingSet.Add(b); var count = ctx.SaveChanges(); Console.WriteLine("Saved changes: " + count); } }
/// <summary> /// Create random flights /// </summary> private static Random Init_FlightSet(WWWingsContext ctx) { Console.WriteLine("Create Flights..."); var flightSet = from f in ctx.FlightSet select f; int Start = 100; Pilot[] PilotArray = ctx.PilotSet.ToArray(); for (int i = Start; i < (Start + NO_OF_FLIGHTS); i++) { if (i % 100 == 0) { Console.WriteLine("Flight #" + i); try { ctx.SaveChanges(); } catch (Exception ex) { Console.WriteLine("ERROR: " + ex.Message.ToString()); } } Flight flightNew = new Flight(); flightNew.FlightNo = i; flightNew.Departure = Flighthaefen[rnd.Next(0, Flighthaefen.Length - 1)]; flightNew.Destination = Flighthaefen[rnd.Next(0, Flighthaefen.Length - 1)]; ; if (flightNew.Departure == flightNew.Destination) { i--; continue; } // Keine Rundflüge! flightNew.FreeSeats = Convert.ToInt16(new Random(i).Next(250)); flightNew.Seats = 250; flightNew.AircraftTypeID = 1; flightNew.Date = DateTime.Now.AddDays((double)flightNew.FreeSeats).AddMinutes((double)flightNew.FreeSeats * 7); flightNew.PilotId = PilotArray[rnd.Next(PilotArray.Count() - 1)].PersonID; flightNew.CopilotId = PilotArray[rnd.Next(PilotArray.Count() - 1)].PersonID; ctx.FlightSet.Add(flightNew); } ctx.SaveChanges(); CUI.PrintSuccess("Number of flights: " + ctx.FlightSet.Count()); var f101 = ctx.FlightSet.Find(101); f101.Departure = "Berlin"; f101.Destination = "Seattle"; ctx.SaveChanges(); return(rnd); }
public static void Demo_AddGraph() { CUI.Headline("Demo_AddGraph"); using (var ctx = new WWWingsContext()) { ctx.Log(); ctx.Database.ExecuteSqlCommand("Delete from Booking where FlightNo = 101"); ctx.Database.ExecuteSqlCommand("Delete from Flight where FlightNo = 101"); var pilot = new Pilot(); pilot.Surname = "testpilot"; //ctx.PilotSet.Add(pilot); var fneu = new Flight(); fneu.FlightNo = 101; fneu.Departure = "Essen"; fneu.Destination = "Darmstadt"; fneu.Pilot = pilot; fneu.Seats = 100; fneu.Copilot = pilot; fneu.FreeSeats = 100; ctx.ChangeTracker.TrackGraph(fneu, (obj) => obj.NodeState = obj.Entry.State = EntityState.Added); //ctx.ChangeTracker.TrackGraph(fneu, TrackGraph_CallbackAdded); ctx.FlightSet.Add(fneu); EFC_Util.PrintChangeInfo(ctx); var anz = ctx.SaveChanges(); Console.WriteLine("Saved changes: " + anz); } }
public static void AddBatch(short Anz = 400) { Console.WriteLine(nameof(AddBatch)); using (var ctx = new WWWingsContext()) { CUI.MainHeadline(Anz + " Add Batch"); var pilot1 = ctx.PilotSet.FirstOrDefault(); var pilot2 = ctx.PilotSet.Skip(1).FirstOrDefault(); ctx.Log(); ctx.Database.ExecuteSqlCommand("Delete from Booking where FlightNo >= 10000"); ctx.Database.ExecuteSqlCommand("Delete from Flight where FlightNo >= 10000"); Stopwatch sw2 = new Stopwatch(); sw2.Start(); for (int i = 0; i < Anz; i++) { var newFlight = new Flight(); newFlight.FlightNo = 20000 + i; newFlight.Pilot = pilot1; newFlight.Seats = 100; newFlight.Copilot = pilot2; newFlight.FreeSeats = 100; ctx.FlightSet.Add(newFlight); } ctx.SaveChanges(); sw2.Stop(); Console.WriteLine(Anz + " Add Batch: " + sw2.ElapsedMilliseconds + "ms"); ctx.Database.ExecuteSqlCommand("Delete from Booking where FlightNo >= 10000"); ctx.Database.ExecuteSqlCommand("Delete from Flight where FlightNo >= 10000"); } }
public static void ChangeFlightManyProperties() { CUI.MainHeadline(nameof(ChangeFlightManyProperties)); int flightNo = 101; using (WWWingsContext ctx = new WWWingsContext()) { var f = ctx.FlightSet.SingleOrDefault(x => x.FlightNo == flightNo); Console.WriteLine($"After loading: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the Flight object: " + ctx.Entry(f).State); f.FreeSeats -= 2; //f.Departure = "sdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"; f.Memo = $"Last change durch Benutzer {System.Environment.UserName} on {DateTime.Now}.";; // Änderung 2 Console.WriteLine($"After changes: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State); try { var anz = ctx.SaveChanges(); if (anz == 0) { Console.WriteLine("Problem: No changes saved!"); } else { Console.WriteLine("Number of saved changes: " + anz); } Console.WriteLine($"After saving: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the Flight object: " + ctx.Entry(f).State); } catch (Exception ex) { Console.WriteLine("Error: " + ex.ToString()); } } }
/// <summary> /// Attach()/EntityState.Modified --> All Spalten zurückschreiben /// </summary> public static void Attached_Flight() { CUI.MainHeadline(nameof(Attached_Flight)); Flight f; CUI.Print("Lade Objekt in Kontextinstanz #1"); using (WWWingsContext ctx1 = new WWWingsContext()) { ctx1.Log(); f = ctx1.FlightSet.Find(101); CUI.Print("Objekt ändern"); CUI.Print(f.ToString()); f.Memo = "last changed at " + DateTime.Now; f.FreeSeats--; CUI.Print(f.ToString()); var count = ctx1.SaveChanges(); Console.WriteLine("Saved changes: " + count.ToString()); if (count != 1) { Debugger.Break(); } } }
public static void TrackingMode_AsNoTracking() { CUI.MainHeadline(nameof(TrackingMode_AsNoTracking)); CUI.Headline("Tracking mode"); using (WWWingsContext ctx = new WWWingsContext()) { var flightSet = ctx.FlightSet.ToList(); var flight = flightSet[0]; Console.WriteLine(flight + " object state: " + ctx.Entry(flight).State); // Unchanged flight.FreeSeats--; Console.WriteLine(flight + " object state: " + ctx.Entry(flight).State); // Modified int count = ctx.SaveChanges(); Console.WriteLine($"Saved changes: {count}"); // 1 } CUI.Headline("No-Tracking mode"); using (WWWingsContext ctx = new WWWingsContext()) { var flightSet = ctx.FlightSet.AsNoTracking().ToList(); var flight = flightSet[0]; Console.WriteLine(flight + " object state: " + ctx.Entry(flight).State); // Detached flight.FreeSeats--; Console.WriteLine(flight + " object state: " + ctx.Entry(flight).State); // Detached int count = ctx.SaveChanges(); Console.WriteLine($"Saved changes: {count}"); // 0 } }
public static void TimestampQuery() { CUI.Headline("Vor Änderung"); Print(); using (var ctx = new WWWingsContext()) { ctx.Log(); var flightSet = ctx.FlightSet.Where(f => f.Departure == "Paris").OrderBy(x => x.Timestamp).Take(1); foreach (var f in flightSet) { f.FreeSeats--; } ctx.SaveChanges(); CUI.Headline("Nach Änderung"); Print(); CUI.Headline("Abfrage: TS > " + ts.ByteArrayToString()); var geänderteflightSet = ctx.FlightSet.Where(x => x.Timestamp.Compare(ts) > 0).ToList(); foreach (var f in flightSet) { Console.WriteLine(f.FlightNo + ": " + f.FreeSeats + "/" + f.Timestamp.ByteArrayToString()); } } }
public static void Run() { Console.WriteLine("Start..."); using (var ctx = new WWWingsContext()) { // Datenbank anlegen, wenn nicht vorhanden! ctx.Database.EnsureCreated(); // Passagierobjekt erzeugen var newPassenger = new Passenger(); newPassenger.GivenName = "Holger"; newPassenger.Surname = "Schwichtenberg"; // Passenger an EF-Kontext anfügen ctx.PassengerSet.Add(newPassenger); // Objekt speichern var anz = ctx.SaveChanges(); Console.WriteLine("Number of changes: " + anz); // All Passagiere einlesen aus Datenbank var passengerSet = ctx.PassengerSet.ToList(); Console.WriteLine("Number of passengers: " + passengerSet.Count); // Filtern mit LINQ-to-Objects foreach (var p in passengerSet.Where(x => x.Surname == "Schwichtenberg").ToList()) { Console.WriteLine(p.PersonID + ": " + p.GivenName + " " + p.Surname); } } Console.WriteLine("Ende!"); Console.ReadLine(); }
public static void BulkDeleteEFCAPIusingPseudoObject() { CUI.Headline(nameof(BulkDeleteEFCAPIusingPseudoObject)); int total = 0; var sw = new Stopwatch(); sw.Start(); using (var ctx = new WWWingsContext()) { for (int i = 20001; i < 21000; i++) { var f = new Flight() { FlightNo = i }; ctx.FlightSet.Attach(f); ctx.FlightSet.Remove(f); } total = ctx.SaveChanges(); } sw.Stop(); Console.WriteLine("Number of DELETE statements: " + total); Console.WriteLine("Duration: " + sw.ElapsedMilliseconds); Timer_BulkDeleteEFCAPIusingPseudoObject += sw.ElapsedMilliseconds; }
private static void Init_PassengerSet(WWWingsContext ctx) { Console.WriteLine("Create Passengers..."); Random rnd2 = new Random(); for (int PNummer = 1; PNummer <= NO_OF_PASSENGERS; PNummer++) { string Vorname = Vornamen[rnd2.Next(0, Vornamen.Length - 1)]; ; string Nachname = Nachnamen[rnd2.Next(0, Nachnamen.Length - 1)]; ; Passenger p = new Passenger(); // p.PersonID = 1; p.Surname = Nachname; p.GivenName = Vorname; p.Birthday = new DateTime(1940, 1, 1).AddDays(Convert.ToInt32(new Random(DateTime.Now.Millisecond).Next(20000))); //p.PersonID = PNummer; p.Status = PassengerStatus.PassengerStatusSet.ElementAt(rnd.Next(3)); ctx.PassengerSet.Add(p); ctx.SaveChanges(); if (PNummer % 100 == 0) { Console.WriteLine("Passenger #" + PNummer + ": " + Vorname + " " + Nachname); } } CUI.PrintSuccess("Number of passengers: " + ctx.PassengerSet.Count()); }
private static void Init_PilotSet(WWWingsContext ctx) { Console.WriteLine("Create pilots..."); for (int PNummer = 1; PNummer <= NO_OF_PILOTS; PNummer++) { string Vorname = PilotsVornamen[rnd.Next(0, PilotsVornamen.Length - 1)]; ; string Nachname = PilotsNachnamen[rnd.Next(0, PilotsNachnamen.Length - 1)]; ; Pilot p = new Pilot(); //p.PersonID = 1000 + PNummer; p.Surname = Nachname; p.GivenName = Vorname; p.Birthday = new DateTime(1940, 1, 1).AddDays(Convert.ToInt32(new Random(DateTime.Now.Millisecond).Next(20000))); //p.HireDate = DateTime.Now.AddDays(-rnd.Next(365)); ctx.PilotSet.Add(p); ctx.SaveChanges(); Console.WriteLine("Pilot #" + PNummer + ": " + Vorname + " " + Nachname); } CUI.PrintSuccess("Number of pilots: " + ctx.PilotSet.Count()); }
private static void Attempt2() { using (var ctx = new WWWingsContext()) { CUI.MainHeadline("Attempt 2: First assignment by foreign key property, then navigation property"); CUI.PrintStep("Load a flight..."); var flight101 = ctx.FlightSet.Include(f => f.Pilot).SingleOrDefault(x => x.FlightNo == 101); Console.WriteLine($"Flight Nr {flight101.FlightNo} from {flight101.Departure} to {flight101.Destination} has {flight101.FreeSeats} free seats!"); CUI.Print("Pilot object: " + flight101.Pilot.PersonID + " PilotId: " + flight101.PilotId); var neuePilotID2 = GetPilotIdEinesFreienPilots(); CUI.PrintStep($"Assign a new pilot #{neuePilotID2} via foreign key property..."); flight101.PilotId = neuePilotID2; CUI.Print($"PilotId: {flight101.PilotId} Pilot object: {flight101.Pilot?.PersonID}"); CUI.PrintStep("Load another pilot..."); var newPilot1 = ctx.PilotSet.Find(GetPilotIdEinesFreienPilots()); // nächster Pilot CUI.PrintStep($"Assign a new pilot #{newPilot1.PersonID} via navigation property..."); flight101.Pilot = newPilot1; CUI.Print($"PilotId: {flight101.PilotId} Pilot object: {flight101.Pilot?.PersonID}"); CUI.PrintStep("SaveChanges()"); var anz2 = ctx.SaveChanges(); CUI.PrintSuccess("Number of saved changes: " + anz2); CUI.PrintStep("Control output after saving: "); CUI.Print($"PilotId: {flight101.PilotId} Pilot object: {flight101.Pilot?.PersonID}"); } }
public static void ComputedColumnWithFormula() { CUI.MainHeadline(nameof(ComputedColumnWithFormula)); int flightNo = 101; using (WWWingsContext ctx = new WWWingsContext()) { ctx.Log(); var flight = ctx.FlightSet.Find(flightNo); Console.WriteLine($"BEFORE: {flight}: Utilization={flight.Utilization:##0.00}%"); flight.FreeSeats -= 10; //not possible: flight.Utilization = 100; // The change is not yet visible in the Utilization, since the Utilization is calculated in the DBMS Console.WriteLine($"After changes: {flight}: Utilization={flight.Utilization:##0.00}%"); ctx.SaveChanges(); // The change in Utilization is now visible Console.WriteLine($"After saving: {flight}: Utilization={flight.Utilization:##0.00}%"); CUI.Headline("Metadata of Flight properties"); foreach (PropertyEntry p in ctx.Entry(flight).Properties) { Console.WriteLine(p.Metadata.Name + ": " + p.Metadata.ValueGenerated); } } }
public static void Demo_Projection_OneFlight() { using (var ctx = new WWWingsContext()) { CUI.MainHeadline("Projection"); var q = from x in ctx.FlightSet where x.FlightNo == 101 orderby x.FlightNo select new Flight() { FlightNo = x.FlightNo, Date = x.Date, Departure = x.Departure, Destination = x.Destination, Seats = x.Seats, FreeSeats = x.FreeSeats, }; var f = q.FirstOrDefault(); Console.WriteLine(f + " State: " + ctx.Entry(f).State + " Timestamp: " + ByteArrayToString(f.Timestamp)); ctx.Attach(f); Console.WriteLine(f + " State: " + ctx.Entry(f).State + " Timestamp: " + ByteArrayToString(f.Timestamp)); f.FreeSeats++; Console.WriteLine(f + " State: " + ctx.Entry(f).State + " Timestamp: " + ByteArrayToString(f.Timestamp)); var anz = ctx.SaveChanges(); Console.WriteLine("Number of saved changes: " + anz); } }
public static void Run() { Console.WriteLine("Start..."); using (var ctx = new WWWingsContext()) { // Create database at runtime, if not available! var e = ctx.Database.EnsureCreated(); if (e) { Console.WriteLine("Database has been created!"); } // Create passenger object var newPassenger = new Passenger(); newPassenger.GivenName = "Holger"; newPassenger.Surname = "Schwichtenberg"; // Append Passenger to EFC context ctx.PassengerSet.Add(newPassenger); // Save object var anz = ctx.SaveChanges(); Console.WriteLine("Number of changes: " + anz); // Read all passengers from the database var passengerSet = ctx.PassengerSet.ToList(); Console.WriteLine("Number of passengers: " + passengerSet.Count); // Filter with LINQ-to-Objects foreach (var p in passengerSet.Where(x => x.Surname == "Schwichtenberg").ToList()) { Console.WriteLine(p.PersonID + ": " + p.GivenName + " " + p.Surname); } } Console.WriteLine("Done!"); Console.ReadLine(); }
public static void BulkDelete_Prepare() { int countNew = 1000; CUI.Headline($"Create records {countNew}..."); using (var ctx = new WWWingsContext()) { for (int i = 0; i < countNew; i++) { // Create flight in RAM var f = new Flight(); f.FlightNo = 20000 + i; f.Departure = "Berlin"; f.Destination = "Sydney"; f.AirlineCode = "WWW"; f.PilotId = ctx.PilotSet.FirstOrDefault().PersonID; f.Seats = 100; f.FreeSeats = 100; ctx.FlightSet.Add(f); // or: ctx.Add(f); } var count = ctx.SaveChanges(); Console.WriteLine("Number of saved changes: " + count); } }
public static void Demo_CreateRelatedObjects() { CUI.MainHeadline(nameof(Demo_CreateRelatedObjects)); using (var ctx = new WWWingsContext()) { ctx.Database.ExecuteSqlCommand("Delete from Booking where FlightNo = 456789"); ctx.Database.ExecuteSqlCommand("Delete from Flight where FlightNo = 456789"); var p = new Pilot(); p.GivenName = "Holger"; p.Surname = "Schwichtenberg"; p.HireDate = DateTime.Now; p.LicenseDate = DateTime.Now; var pd = new Persondetail(); pd.City = "Essen"; pd.Country = "DE"; p.Detail = pd; var act = new AircraftType(); act.TypeID = (byte)(ctx.AircraftTypeSet.Max(x => x.TypeID) + 1); act.Manufacturer = "Airbus"; act.Name = "A380-800"; ctx.AircraftTypeSet.Add(act); ctx.SaveChanges(); var actd = new AircraftTypeDetail(); actd.TurbineCount = 4; actd.Length = 72.30f; actd.Tare = 275; act.Detail = actd; var f = new Flight(); f.FlightNo = 456789; f.Pilot = p; f.Copilot = null; f.Seats = 850; f.FreeSeats = 850; f.AircraftType = act; // One Add() is enough for all related objects! ctx.FlightSet.Add(f); ctx.SaveChanges(); CUI.Print("Total number of flights: " + ctx.FlightSet.Count()); CUI.Print("Total number of pilots: " + ctx.PilotSet.Count()); } }
public static void ExplicitTransactionTwoSaveChanges() { CUI.MainHeadline(nameof(ExplicitTransactionTwoSaveChanges)); using (var ctx = new WWWingsContext()) { // Start transaction. Default is System.Data.IsolationLevel.ReadCommitted using (var t = ctx.Database.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)) { // Print isolation level RelationalTransaction rt = t as RelationalTransaction; DbTransaction dbt = rt.GetDbTransaction(); Console.WriteLine("Transaction with Level: " + dbt.IsolationLevel); // Read data int flightNo = ctx.FlightSet.OrderBy(x => x.FlightNo).FirstOrDefault().FlightNo; var f = ctx.FlightSet.Where(x => x.FlightNo == flightNo).SingleOrDefault(); Console.WriteLine("Before: " + f.ToString()); // Change data and save f.FreeSeats--; var count1 = ctx.SaveChanges(); Console.WriteLine("Number of saved changes: " + count1); // Change data again and save f.Memo = "last changed at " + DateTime.Now.ToString(); var count2 = ctx.SaveChanges(); Console.WriteLine("Number of saved changes: " + count2); Console.WriteLine("Commit or Rollback? 1 = Commit, other = Rollback"); var eingabe = Console.ReadKey().Key; if (eingabe == ConsoleKey.D1) { t.Commit(); Console.WriteLine("Commit done!"); } else { t.Rollback(); Console.WriteLine("Rollback done!"); } Console.WriteLine("After in RAM: " + f.ToString()); ctx.Entry(f).Reload(); Console.WriteLine("After in DB: " + f.ToString()); } } }
// Nun prüfen, ob Timestamp noch stimmt (sonst müsste ich mein altes Objekt noch haben, um All Werte zu vergleichen! // Achtung: Vergleich mit == geht hier nicht, weil Byte-Array! //if (!flightOrginal.Timestamp.SequenceEqual(flight.Timestamp)) //{ // throw new System.Data.DBConcurrencyException("Flight " + flight.FlightNo + " wurde from jemand anderem geändert!"); //} /// <summary> /// Variante 3b fehlerhaft (Richtig: Original-Objekt noch haben) /// </summary> public static void Detached_Flight_ConcurrencyCheck_CloneOrg() { CUI.MainHeadline(nameof(Detached_Flight_ConcurrencyCheck_CloneOrg)); Flight flight; Flight flightOrginal; CUI.Print("Lade Objekt in Kontextinstanz #1"); using (WWWingsContext ctx1 = new WWWingsContext()) { ctx1.Log(); //ctx1.Configuration.LazyLoadingEnabled = false; flight = ctx1.FlightSet.Find(110); // Kopie anlegen flightOrginal = Cloner.Clone <Flight>(flight); } CUI.Print("Objekt ändern"); CUI.Print(flight.ToString()); flight.FreeSeats--; flight.Date = flight.Date.AddHours(2); CUI.Print(flight.ToString()); CUI.Print("Objekt nun speichern mit Kontextinstanz #2"); using (WWWingsContext ctx2 = new WWWingsContext()) { ctx2.Log(); ctx2.FlightSet.Attach(flight); var entry = ctx2.Entry(flight); CUI.Print(ctx2.Entry(flight).State.ToString()); // Nun Originalwerte im Kontext ablegen. Besser als die aktuellen Werte aus DB laden! // Variante 1: ctx2.Entry(flight).OriginalValues.SetValues(flightOrginal); // Variante 2: //var flightOrgDic = ObjectToDictionaryHelper.ToDictionary(flightOrginal); //foreach (string propertyName in entry.OriginalValues.PropertyNames) //{ // entry.Property(propertyName).OriginalValue = flightOrgDic[propertyName]; //} CUI.Print(ctx2.Entry(flight).State.ToString()); var count = ctx2.SaveChanges(); Console.WriteLine("Saved changes: " + count.ToString()); if (count != 1) { Debugger.Break(); } } }
public static void ConvertStringToBoolean() { CUI.MainHeadline(nameof(ConvertStringToBoolean)); using (WWWingsContext ctx = new WWWingsContext()) { ctx.Log(); // Add new Passenger CUI.Headline("Add new Passenger"); var p = new Passenger(); p.GivenName = "Max"; p.Surname = "Müller"; p.Birthday = new DateTime(2070, 2, 3); p.Status = 'A'; p.FrequentFlyer = "Yes"; ctx.PassengerSet.Add(p); var count = ctx.SaveChanges(); Console.WriteLine("Saved Changes: " + count); Console.WriteLine("Added new Passenger #" + p.PersonID); // Get raw data from Database as DataReader CUI.Headline("Raw Data"); var r = ctx.Database.ExecuteSqlQuery("Select p.PersonID, p.Surname, p.Birthday, p.FrequentFlyer from Passenger as p where p.personID= " + p.PersonID); DbDataReader dr = r.DbDataReader; while (dr.Read()) { Console.WriteLine("{0}\t{1}\t{2}\t{3} \n", dr[0], dr[1], dr[2], dr[3]); } dr.Dispose(); // Get all Frequent Travellers CUI.Headline("All Frequent Travellers:"); var ft = ctx.PassengerSet.Where(x => x.FrequentFlyer == "Yes").ToList(); foreach (var pas in ft) { Console.WriteLine(pas); } // Get all Frequent Travellers CUI.Headline("All Frequent Travellers: -> StartsWith() does not work!"); var ft2 = ctx.PassengerSet.Where(x => x.FrequentFlyer.StartsWith("Y")).ToList(); if (ft2.Count == 0) { CUI.PrintError("No passengers :-("); } foreach (var pas in ft2) { Console.WriteLine(pas); } } }
public static void ChangeTracking_OneObject() { CUI.MainHeadline(nameof(ChangeTracking_OneObject)); Flight flight; EntityEntry <BO.Flight> entryObj; PropertyEntry propObj; using (var ctx = new WWWingsContext()) { CUI.Headline("Loading Object..."); flight = (from y in ctx.FlightSet select y).FirstOrDefault(); // Access Change Tracker entryObj = ctx.Entry(flight); propObj = entryObj.Property(f => f.FreeSeats); Console.WriteLine(" Object state: " + entryObj.State); Console.WriteLine(" Is FreeSeats modified?: " + propObj.IsModified); CUI.Headline("Chanhing Object..."); flight.FreeSeats--; // Access Change Tracker again entryObj = ctx.Entry(flight); propObj = entryObj.Property(f => f.FreeSeats); Console.WriteLine(" Object state: " + entryObj.State); Console.WriteLine(" Is FreeSeats modified?: " + propObj.IsModified); // Print old and new values if (entryObj.State == EntityState.Modified) { foreach (PropertyEntry p in entryObj.Properties) { if (p.IsModified) { Console.WriteLine(" " + p.Metadata.Name + ": " + p.OriginalValue + "->" + p.CurrentValue + " / State in database: " + entryObj.GetDatabaseValues()[p.Metadata.Name]); } } } CUI.Headline("Save..."); int anz = ctx.SaveChanges(); Console.WriteLine(" Number of changes: " + anz); // aktualisieren der Objecte des Change Trackers entryObj = ctx.Entry(flight); propObj = entryObj.Property(f => f.FreeSeats); Console.WriteLine(" Object state: " + entryObj.State); Console.WriteLine(" Is FreeSeats modified?: " + propObj.IsModified); } }
public static void ShowUpdatedTimeStamp() { CUI.MainHeadline(nameof(ShowUpdatedTimeStamp)); using (WWWingsContext ctx = new WWWingsContext()) { var f = ctx.FlightSet.Take(1).SingleOrDefault(); Console.WriteLine("Before: " + f.ToString() + " Timestamp: " + f.Timestamp.ByteArrayToString()); f.FreeSeats--; // Change #1 Console.WriteLine("After change: " + f.ToString() + " Timestamp: " + f.Timestamp.ByteArrayToString()); var anz1 = ctx.SaveChanges(); Console.WriteLine("After saving: " + f.ToString() + " Timestamp: " + f.Timestamp.ByteArrayToString()); CUI.PrintSuccess("Number of saved changes: " + anz1); Console.WriteLine("Before: " + f.ToString() + " Timestamp: " + f.Timestamp.ByteArrayToString()); f.FreeSeats--; // Change #2 Console.WriteLine("After change: " + f.ToString() + " Timestamp: " + f.Timestamp.ByteArrayToString()); var anz2 = ctx.SaveChanges(); Console.WriteLine("After saving: " + f.ToString() + " Timestamp: " + f.Timestamp.ByteArrayToString()); CUI.PrintSuccess("Number of saved changes: " + anz2); } }
public static void UpdateWithReadLock() { CUI.MainHeadline(nameof(UpdateWithReadLock)); Console.WriteLine("--- Change flight"); int flightNo = 101; using (WWWingsContext ctx = new WWWingsContext()) { try { ctx.Database.SetCommandTimeout(10); // 10 seconds // Start transaction IDbContextTransaction t = ctx.Database.BeginTransaction(IsolationLevel.ReadUncommitted); // default is System.Data.IsolationLevel.ReadCommitted Console.WriteLine("Transaction with Level: " + t.GetDbTransaction().IsolationLevel); // Load flight with read lock using WITH (UPDLOCK) Console.WriteLine("Load flight using SQL..."); Flight f = ctx.FlightSet.FromSql("SELECT * FROM dbo.Flight WITH (UPDLOCK) WHERE FlightNo = {0}", flightNo).SingleOrDefault(); Console.WriteLine($"Before changes: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State); Console.WriteLine("Waiting for ENTER key..."); Console.ReadLine(); // Change object in RAM Console.WriteLine("Change flight..."); f.FreeSeats -= 2; Console.WriteLine($"After changes: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State); // Send changes to DBMS Console.WriteLine("Save changes..."); var c = ctx.SaveChanges(); t.Commit(); if (c == 0) { Console.WriteLine("Problem: No changes saved!"); } else { Console.WriteLine("Number of saved changes: " + c); Console.WriteLine($"After saving: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State); } } catch (Exception ex) { CUI.PrintError("Error: " + ex.ToString()); } } }
public static void AddFlightWithDetails() { Console.WriteLine(nameof(AddFlightWithDetails)); using (WWWingsContext ctx = new WWWingsContext()) { //var p = ctx.PilotSet.SingleOrDefault(x => x.PersonID == 234); // Flight im RAM anlegen var f = new Flight(); f.FlightNo = 123456; //f.Pilot = p; f.Departure = "Essen"; f.Destination = "Sydney"; f.AirlineCode = "WWW"; f.PilotId = 23; f.Copilot = null; // kein Copilot :-( f.Seats = 100; f.FreeSeats = 100; Console.WriteLine($"Vor dem Anfügen: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State); // Flight dem Kontext "hinzufügen" ctx.FlightSet.Add(f); ctx.FlightSet.Add(f); ctx.Add(f); Console.WriteLine($"Nach dem Anfügen: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State); try { var anz = ctx.SaveChanges(); if (anz == 0) { Console.WriteLine("Problem: No changes saved!"); } else { Console.WriteLine("Number of saved changes: " + anz); } Console.WriteLine($"After saving: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State); } catch (Exception ex) { Console.WriteLine("Error: " + ex.ToString()); } } }
internal static void UsePrivateField() { Console.WriteLine(nameof(UsePrivateField)); using (WWWingsContext ctx = new WWWingsContext()) { ctx.Log(); var m = ctx.PilotSet.Where(x => x.PassportNumber == null).FirstOrDefault(); Console.WriteLine("Pilot: " + m.ToString()); m.SetPassportNumber("WW123"); var anz = ctx.SaveChanges(); Console.WriteLine("Saved changes: " + anz); var m2 = ctx.PilotSet.Find(m.PersonID); Console.WriteLine("PassportNumber: " + m2.PassportNumber); } }
public static void DefaultValues() { CUI.MainHeadline(nameof(DefaultValues)); using (WWWingsContext ctx = new WWWingsContext()) { var pilot = ctx.PilotSet.FirstOrDefault(); ctx.Log(); var f = new Flight(); f.FlightNo = ctx.FlightSet.Max(x => x.FlightNo) + 1; f.Departure = "Berlin"; f.Destination = null; f.Pilot = pilot; f.Copilot = null; f.FreeSeats = 100; f.Seats = 100; CUI.Headline("Object has been created in RAM"); Console.WriteLine($"{f} Price: {f.Price:###0.00} Euro."); ctx.FlightSet.Add(f); CUI.Headline("Object has been connected to the ORM"); Console.WriteLine($"{f} Price: {f.Price:###0.00} Euro."); ctx.SaveChanges(); CUI.Headline("Object has been saved"); Console.WriteLine($"{f} Price: {f.Price:###0.00} Euro."); f.FreeSeats--; CUI.Headline("Object has been changed in RAM"); Console.WriteLine($"{f} Price: {f.Price:###0.00} Euro."); ctx.SaveChanges(); CUI.Headline("Object has been saved");; Console.WriteLine($"{f} Price: {f.Price:###0.00} Euro."); //if (f.Destination != "(not set)") Debugger.Break(); //if (f.Price != 123.45m) Debugger.Break(); } }
public static void ChangePilotUsingFKShadowProperty() { CUI.Headline(nameof(ChangePilotUsingFKShadowProperty)); var flightNo = 102; var neuerPilotNr = 123; using (var ctx = new WWWingsContext()) { ctx.Log(); Flight flight = ctx.FlightSet.Find(flightNo); ctx.Entry(flight).Property("PilotId").CurrentValue = neuerPilotNr; var count = ctx.SaveChanges(); Console.WriteLine("Number of saved changes: " + count); } }
public static void ChangePilotUsingFK() { CUI.MainHeadline(nameof(ChangePilotUsingFK)); var flightNo = 102; var newPilotID = 55; using (var ctx = new WWWingsContext()) { ctx.Log(); Flight flight = ctx.FlightSet.Find(flightNo); flight.PilotId = newPilotID; var count = ctx.SaveChanges(); Console.WriteLine("Number of saved changes: " + count); } }
public static void Attached() { CUI.Headline("Attached"); using (WWWingsContext ctx = new WWWingsContext()) { // Objekt laden var flight = ctx.FlightSet.Find(150); // Objekt ändern flight.FreeSeats--; flight.Date = flight.Date.AddHours(2); // Speichern var count = ctx.SaveChanges(); Console.WriteLine("Saved changes: " + count.ToString()); } }