// Load all information from the data base
private void LoadData()
{
// Get country information from the database.
DataTable countryTable = Database.GetDataTable(new MySqlCommand("SELECT * FROM country;"));
foreach (DataRow field in countryTable.Rows)
{
Country country = new Country(Convert.ToInt32(field[0]));
country.Name = Convert.ToString(field[1]);
country.CreateDate = Convert.ToDateTime(field[2]).ToLocalTime();
country.CreateBy = Users.GetUserBy(u => u.Name == Convert.ToString(field[3]));
country.UpdateTimestamp = Convert.ToDateTime(field[4]).ToLocalTime();
country.UpdateBy = Users.GetUserBy(u => u.Name == Convert.ToString(field[5]));
Countries.AllCountriesList.Add(country);
}
// Get city information from the database.
DataTable cityTable = Database.GetDataTable(new MySqlCommand("SELECT * FROM city;"));
foreach (DataRow field in cityTable.Rows)
{
City city = new City(Convert.ToInt32(field[0]));
city.Name = Convert.ToString(field[1]);
city.Country = Countries.GetCountryBy(c => c.ID == Convert.ToInt32(field[2]));
city.CreateDate = Convert.ToDateTime(field[3]).ToLocalTime();
city.CreateBy = Users.GetUserBy(u => u.Name == Convert.ToString(field[4]));
city.UpdateTimestamp = Convert.ToDateTime(field[5]).ToLocalTime();
city.UpdateBy = Users.GetUserBy(u => u.Name == Convert.ToString(field[6]));
Cities.AllCitiesList.Add(city);
}
// Get address information from the database.
DataTable addressTable = Database.GetDataTable(new MySqlCommand("SELECT * FROM address;"));
foreach (DataRow field in addressTable.Rows)
{
Address address = new Address(Convert.ToInt32(field[0]));
address.Address1 = Convert.ToString(field[1]);
address.Address2 = Convert.ToString(field[2]);
address.City = Cities.GetCityBy(c => c.ID == Convert.ToInt32(field[3]));
address.PostalCode = Convert.ToString(field[4]);
address.Phone = Convert.ToString(field[5]);
address.CreateDate = Convert.ToDateTime(field[6]).ToLocalTime();
address.CreateBy = Users.GetUserBy(u => u.Name == Convert.ToString(field[7]));
address.UpdateTimestamp = Convert.ToDateTime(field[8]).ToLocalTime();
address.UpdateBy = Users.GetUserBy(u => u.Name == Convert.ToString(field[9]));
Addresses.AllAddressList.Add(address);
}
// Get customer information from the database.
// NOTE: Whomever set up the database, has made it so that records
// deleted from the customer table do not delete thier rows. So as
// a result, the customer table will return rows that are empty.
// To protect against this, we will need to include a check in the
// SQL command.
DataTable CustomersTable = Database.GetDataTable(new MySqlCommand(("SELECT * FROM customer WHERE customerName IS NOT NULL;")));
foreach (DataRow field in CustomersTable.Rows)
{
Customer customer = new Customer(Convert.ToInt32(field[0]));
customer.Name = Convert.ToString(field[1]);
customer.Address = Addresses.GetAddressBy(a => a.ID == Convert.ToInt32(field[2]));
customer.CityName = customer.Address.City.Name;
customer.CountryName = customer.Address.City.Country.Name;
customer.Active = Convert.ToBoolean(field[3]);
customer.CreateDate = Convert.ToDateTime(field[4]).ToLocalTime();
customer.CreateBy = Users.GetUserBy(u => u.Name == Convert.ToString(field[5]));
customer.UpdateTimestamp = Convert.ToDateTime(field[6]).ToLocalTime();
customer.UpdateBy = Users.GetUserBy(u => u.Name == Convert.ToString(field[7]));
Customers.AllCustomersList.Add(customer);
}
// Get appointment information from the appointment table.
DataTable AppointmentsTable = Database.GetDataTable(new MySqlCommand("SELECT * FROM appointment;"));
foreach (DataRow field in AppointmentsTable.Rows)
{
Appointment appointment = new Appointment(Convert.ToInt32(field[0]));
int foo = Convert.ToInt32(field[1]);
appointment.Customer = Customers.GetCustomerBy(c => c.ID == Convert.ToInt32(field[1]));
appointment.User = Users.GetUserBy(u => u.ID == Convert.ToInt32(field[2]));
appointment.Title = Convert.ToString(field[3]);
appointment.Description = Convert.ToString(field[4]);
appointment.Location = Convert.ToString(field[5]);
appointment.Contact = appointment.Customer.Address.Phone;
appointment.Type = Convert.ToString(field[7]);
appointment.URL = Convert.ToString(field[8]);
appointment.StartDate = Convert.ToDateTime(field[9]).ToLocalTime();
appointment.EndDate = Convert.ToDateTime(field[10]).ToLocalTime();
appointment.CreateDate = Convert.ToDateTime(field[11]).ToLocalTime();
appointment.CreateBy = Users.GetUserBy(u => u.Name == Convert.ToString(field[12]));
appointment.UpdateTimestamp = Convert.ToDateTime(field[13]).ToLocalTime();
appointment.UpdateBy = Users.GetUserBy(u => u.Name == Convert.ToString(field[14]));
Appointments.AllAppointmentsList.Add(appointment);
}
}