/// <summary>
/// Funkcja pozwalająca dodać nowy rekord do bazy danych.
/// </summary>
public void InsertIntoDB()
{
try
{
using (var database = new CollectorsManagementSystemEntities())
{
database.Readings.Add(this);
database.SaveChanges();
}
}
catch (DbEntityValidationException ex)
{
var errorMessages = String.Empty;
foreach (var validationResult in ex.EntityValidationErrors)
{
string entityName = validationResult.Entry.Entity.GetType().Name;
foreach (DbValidationError error in validationResult.ValidationErrors)
{
errorMessages += entityName + "." + error.PropertyName + ": " + error.ErrorMessage + "\n";
}
}
MessageBox.Show(errorMessages);
}
catch (DbUpdateException ex)
{
ExceptionHandling.ShowException(ex);
}
}
public void ModifyRecord(string id)
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
dataBase.Database.ExecuteSqlCommand
(
"UPDATE Area SET Street={0}, CollectorId={1} WHERE AreaId={2}",
this.Street,
this.CollectorId,
id
);
dataBase.SaveChanges();
}
}
public void InsertIntoDB()
{
try
{
using (var database = new CollectorsManagementSystemEntities())
{
this.PostalCode = Regex.Replace(this.PostalCode, "-", "");
database.Customers.Add(this);
database.SaveChanges();
}
}
catch (DbUpdateException Ex)
{
ExceptionHandling.ShowException(Ex);
}
}
public void InsertIntoDB()
{
try
{
using (var database = new CollectorsManagementSystemEntities())
{
database.Areas.Add(this);
database.SaveChanges();
}
}
catch (DbUpdateException ex)
{
var innerEx = ex.InnerException;
while (innerEx.InnerException != null)
innerEx = innerEx.InnerException;
System.Windows.Forms.MessageBox.Show(innerEx.Message);
}
}
/// <summary>
/// Usuwanie odczytu z bazy danych.
/// </summary>
/// <param name="id">Id odczytu usuwanego.</param>
public static void DeleteReadFromDB(System.Guid id)
{
try
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
var items = from reading in dataBase.Readings
where reading.ReadingId == id
select reading;
foreach (var item in items)
{
dataBase.Readings.Remove(item);
}
dataBase.SaveChanges();
}
}
catch (DbUpdateException ex)
{
ExceptionHandling.ShowException(ex);
}
}
/// <summary>
/// Modyfikuje odpowiedni rekord.
/// </summary>
/// <param name="id">Id modyfikowanego rekordu.</param>
public void ModifyRecord(string id)
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
dataBase.Database.ExecuteSqlCommand
(
"UPDATE Customer SET Name={0}, LastName={1}, PostalCode={2}, City={3}, Address={4}, PhoneNumber={5} WHERE CustomerId={6}",
this.Name,
this.LastName,
Regex.Replace(this.PostalCode, "-", ""),
this.City,
this.Address,
this.PhoneNumber,
id
);
dataBase.SaveChanges();
}
}
/// <summary>
/// Modyfikuje odpowiedni rekord.
/// </summary>
/// <param name="id">Id modyfikowanego rekordu.</param>
public void ModifyRecord(string id)
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
dataBase.Database.ExecuteSqlCommand
(
"UPDATE Counter SET CircuitNo={0}, AddressId={1}, CustomerId={2} WHERE CounterNo={3}",
this.CircuitNo,
this.AddressId,
this.CustomerId,
id
);
dataBase.SaveChanges();
}
}
/// <summary>
/// Generuje losowo odczyty.
/// </summary>
static void GenerateReadings()
{
Random rnd = new Random();
Reading reading;
Guid g;
using (var dataBase = new CollectorsManagementSystemEntities())
{
for (int i = 0; i < numberOfReadings; i++)
{
reading = new Reading();
do
{
g = Guid.NewGuid();
} while (g == Guid.Empty);
reading.ReadingId = g;// Guid.NewGuid();
reading.CounterNo = Convert.ToInt32(ChooseRandomId(0));
//var counters = (from counter in dataBase.Counters
// select counter.CounterNo).ToList();
List<int> test = (from c in dataBase.Counters select c.CounterNo).ToList();
var tmp = (from c in dataBase.Counters where c.CounterNo == reading.CounterNo select c.AddressId).FirstOrDefault();
var tmp2 = (from a in dataBase.Addresses where a.AddressId == tmp select a.AreaId).FirstOrDefault();
reading.CollectorId = (from a in dataBase.Areas where a.AreaId == tmp2 select a.CollectorId).FirstOrDefault().ToString();
var friendsOfReading = from r in dataBase.Readings where r.CounterNo == reading.CounterNo orderby r.Date descending select r;
if (friendsOfReading.Count() == 0)
reading.Value = rnd.Next(0, 1000);
else
{
reading.Value = friendsOfReading.FirstOrDefault().Value + rnd.Next(0, 1000);
foreach (Reading friendOfReading in friendsOfReading)
friendOfReading.Date = friendOfReading.Date.AddDays(-30);
dataBase.SaveChanges();
}
reading.Date = DateTime.Now;
reading.InsertIntoDB();
}
}
}
/// <summary>
/// Modyfikuje odpowiedni rekord.
/// </summary>
/// <param name="id">Id modyfikowanego rekordu.</param>
public void ModifyRecord(string id)
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
dataBase.Database.ExecuteSqlCommand
(
"UPDATE Address SET HouseNo={0}, FlatNo={1}, AreaId={2} WHERE AddressId={3}",
this.HouseNo,
this.FlatNo,
this.AreaId,
id
);
dataBase.SaveChanges();
}
}
/// <summary>
/// Usuwa rekordy z tabeli Odczyt.
/// </summary>
/// <param name="IDs">Lista identyfikatorów rekordów tabeli Odczyt przeznaczonych do usunięcia.</param>
private static void DeleteFromReadings(List<string> IDs)
{
List<Guid> guidIDs = new List<Guid>(IDs.Count);
for (int i = 0; i < IDs.Count; i++)
guidIDs.Insert(i, new Guid(IDs[i]));
using (var database = new CollectorsManagementSystemEntities())
{
foreach (var id in guidIDs)
{
var result = from r in database.Readings where r.ReadingId == id select r;
if (result.Count() > 0)
{
foreach (Reading r in result)
database.Readings.Remove(r);
}
database.SaveChanges();
}
}
}
/// <summary>
/// Usuwa rekordy z tabeli Klient. Identyfikatory usuwanych rekordów z tej tabeli będące kluczami obcymi w tabeli Licznik zamieniane są na wartość null.
/// Zasady nałożone na bazę danych wymagają również ustawienia na wartość null odpowiednich kluczy obcych adresów w tabeli Licznik.
/// </summary>
/// <param name="IDs">Lista identyfikatorów rekordów przeznaczonych do usunięcia.</param>
/// <param name="idIsForeignKey">true - do kasowanych rekordów istnieją odniesienia w innych tabelach.</param>
private static void DeleteFromCustomers(List<string> IDs, bool idIsForeignKey)
{
using (var database = new CollectorsManagementSystemEntities())
{
foreach (var id in IDs)
{
var result = from r in database.Customers where r.CustomerId == id select r;
if (result.Count() > 0)
{
foreach (Customer c in result)
database.Customers.Remove(c);
}
if (idIsForeignKey)
{
var foreginResult = from f in database.Counters where f.CustomerId == id select f;
if (result.Count() > 0)
{
foreach (Counter c in foreginResult)
{
c.CustomerId = null;
c.AddressId = null;
}
}
}
database.SaveChanges();
}
}
}
/// <summary>
/// Usuwa wybrane rekordy z tabeli Licznik. Rekordy z tabeli Odczyt powiązane z usuwanymi rekordami tabeli Licznik również są usuwane.
/// </summary>
/// <param name="IDs">Lista identyfikatorów rekordów tabeli Licznik przeznaczonych do usunięcia.</param>
/// <param name="idIsForeignKey">true - do kasowanych rekordów istnieją odniesienia w innych tabelach.</param>
private static void DeleteFromCounters(List<string> IDs, bool idIsForeignKey)
{
List<Int32> guidIDs = new List<Int32>(IDs.Count);
for (int i = 0; i < IDs.Count; i++)
guidIDs.Insert(i, Convert.ToInt32(IDs[i]));
using (var database = new CollectorsManagementSystemEntities())
{
foreach (var Id in guidIDs)
{
var counters = from counter in database.Counters
where counter.CounterNo == Id
select counter;
if (counters.Count() > 0)
{
foreach (Counter c in counters)
database.Counters.Remove(c);
}
if (idIsForeignKey)
{
var foreignResult = from f in database.Readings where f.CounterNo == Id select f;
if (foreignResult.Count() > 0)
{
List<string> foreignList = new List<string>();
foreach (Reading r in foreignResult)
foreignList.Add(r.ReadingId.ToString());
DeleteFromReadings(foreignList);
}
}
database.SaveChanges();
}
}
}
/// <summary>
/// Usuwa rekordy z tabeli Inkasent. Zamienia identyfikatory tej tabeli będące kluczami obcymi w tabeli Teren na wartość null.
/// Usuwa rekordy z tabeli Odczyt powiązane z usuwanymi rekordami tabeli Inkasent.
/// </summary>
/// <param name="IDs">Lista identyfikatorów inkasentów do skasowania.</param>
/// <param name="idIsForeignKey">true - do kasowanych rekordów istnieją odniesienia w innych tabelach.</param>
private static void DeleteFromCollectors(List<string> IDs, bool idIsForeignKey)
{
using (var database = new CollectorsManagementSystemEntities())
{
foreach (var id in IDs)
{
var result = from r in database.Collectors where r.CollectorId == id select r;
if (result.Count() > 0)
{
foreach (Collector c in result)
database.Collectors.Remove(c);
}
if (idIsForeignKey)
{
var foreignResult = from f in database.Areas where f.CollectorId == id select f;
if (foreignResult.Count() > 0)
{
foreach (Area a in foreignResult)
a.CollectorId = null;
}
var foreignResult2 = from f in database.Readings where f.CollectorId == id select f;
if (foreignResult2.Count() > 0)
{
foreach (Reading r in foreignResult2)
r.CollectorId = "00000000000";
}
}
database.SaveChanges();
}
}
}
/// <summary>
/// Usuwa rekordy z tabeli Teren.
/// Jeśli z usuwanymi rekordami są powiązanie rekordy w tabeli Adres, również są usuwane z wszystkim konsekwencjami związanymi z usuwaniem rekordów tabeli Adres.
/// </summary>
/// <param name="IDs">Lista identyfikatorów rekordów, które mają zostać skasowane.</param>
/// <param name="idIsForeignKey">true - do kasowanych rekordów istnieją odniesienia w innych tabelach.</param>
private static void DeleteFromAreas(List<string> IDs, bool idIsForeignKey)
{
List<Guid> guidIDs = new List<Guid>(IDs.Count);
for (int i = 0; i < IDs.Count; i++)
guidIDs.Insert(i, new Guid(IDs[i]));
using (var database = new CollectorsManagementSystemEntities())
{
foreach (var id in guidIDs)
{
var result = from r in database.Areas where r.AreaId == id select r;
if (result.Count() > 0)
{
foreach (Area a in result)
database.Areas.Remove(a);
}
if (idIsForeignKey)
{
var foreignResult = from f in database.Addresses where f.AreaId == id select f;
if (foreignResult.Count() > 0)
{
List<string> foreignList = new List<string>();
foreach (Address a in foreignResult)
foreignList.Add(a.AddressId.ToString());
DeleteFromAddresses(foreignList, true);
}
}
database.SaveChanges();
}
}
}
/// <summary>
/// Usuwa rekordy z tabeli Adres. Jeśli w tabeli Licznik istniały odniesienia do usuwanych rekordów, zostają one zastąpione wartościami null.
/// Zgodnie z zasadami nałożonymi na bazę danych, również odpowiednie identyfikatory klienta w tabeli Licznik są ustawiane na wartość null.
/// </summary>
/// <param name="IDs">Lista identyfikatorów rekordów z tabeli Adres przeznaczonych do usunięcia.</param>
/// <param name="idIsForeignKey">true - do kasowanych rekordów istnieją odniesienia w innych tabelach.</param>
private static void DeleteFromAddresses(List<string> IDs, bool idIsForeignKey)
{
List<Guid> guidIDs = new List<Guid>(IDs.Count);
for (int i = 0; i < IDs.Count; i++)
guidIDs.Insert(i, new Guid(IDs[i]));
using (var database = new CollectorsManagementSystemEntities())
{
foreach (var Id in guidIDs)
{
var result = from r in database.Addresses where r.AddressId == Id select r;
if (result.Count() > 0)
{
foreach (Address a in result)
database.Addresses.Remove(a);
}
if (idIsForeignKey)
{
var foreignResult = from f in database.Counters where f.AddressId == Id select f;
if (foreignResult.Count() > 0)
{
foreach (Counter c in foreignResult)
{
c.AddressId = null;
c.CustomerId = null;
}
}
}
database.SaveChanges();
}
}
}
/// <summary>
/// Funkcja pozwalająca na aktualizację rekordu w bazie danych.
/// </summary>
/// <returns>Informacja o poprawnoťci dodania rekordu.</returns>
public bool UpdateDB()
{
try
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
var items = from reading in dataBase.Readings
where reading.ReadingId == this.ReadingId
select reading;
foreach (var item in items)
{
item.Value = this.Value;
item.CollectorId = this.CollectorId;
}
dataBase.SaveChanges();
}
return true;
}
catch (DbUpdateException ex)
{
ExceptionHandling.ShowException(ex);
return false;
}
}