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