public static void BuildTabels()
{
IDbConnection conn = ConnectionCreater.CreateConnection();
conn.Open();
conn.Execute(
"CREATE TABLE Owner (" +
"owner_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, " +
"owner_first_name TEXT NOT NULL, " +
"owner_last_name TEXT NOT NULL," +
"owner_address TEXT NOT NULL, " +
"owner_email TEXT NOT NULL," +
"owner_phone_no TEXT NOT NULL)"
);
conn.Execute("CREATE TABLE Pet (" +
"pet_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, " +
"pet_name TEXT NOT NULL," +
"pet_type INTEGER NOT NULL," +
"pet_birthdate TEXT NOT NULL, " +
"pet_sold_date TEXT, " +
"pet_previous_owner_id INTEGER, " +
"pet_color TEXT NOT NULL, " +
"pet_price REAL NOT NULL, " +
"FOREIGN KEY(pet_previous_owner_id) REFERENCES Owner(owner_id))"
);
conn.Close();
}
public Owner Delete(Owner entity)
{
using (SQLiteConnection conn = ConnectionCreater.CreateConnection())
{
conn.Open();
conn.Execute("DELETE FROM Owner WHERE owner_id = @Id", entity);
conn.Close();
}
return(entity);
}
public Pet Delete(Pet entity)
{
using (SQLiteConnection conn = ConnectionCreater.CreateConnection())
{
conn.Open();
conn.Execute("DELETE FROM Pet WHERE pet_id = @Id", entity);
conn.Close();
}
return(entity);
}
public Pet Create(Pet entity)
{
using (SQLiteConnection conn = ConnectionCreater.CreateConnection())
{
conn.Open();
conn.Execute("INSERT INTO Pet " +
"(pet_name, pet_type, pet_birthdate, pet_sold_date, pet_previous_owner_id, pet_color, pet_price) " +
"VALUES (@Name, " + (int)entity.PType.Value + ", @BirthDate, @SoldDate, " + (entity.PriviousOwner == null ? "NULL" : "" + entity.PriviousOwner.Id) + ", @Color, @Price)", entity);
entity.Id = conn.LastInsertRowId;
conn.Close();
}
return(entity);
}
public Pet Update(Pet entity)
{
using (SQLiteConnection conn = ConnectionCreater.CreateConnection())
{
conn.Open();
conn.Execute("UPDATE Pet " +
"SET pet_name = @Name, pet_type = " + (int)entity.PType.Value + " , pet_birthdate = @BirthDate, pet_sold_date = @SoldDate, " +
"pet_previous_owner_id = " + (entity.PriviousOwner == null ? "NULL" : "" + entity.PriviousOwner.Id) + ", pet_color = @Color, " +
"pet_price = @Price WHERE pet_id = @Id", entity);
conn.Close();
}
return(entity);
}
public Owner Create(Owner entity)
{
using (SQLiteConnection conn = ConnectionCreater.CreateConnection())
{
conn.Open();
conn.Execute("INSERT INTO Owner " +
"(owner_first_name, owner_last_name, owner_address, owner_email, owner_phone_no) " +
"VALUES (@FirstName, @LastName, @Address, @Email, @PhoneNumber)", entity);
entity.Id = conn.LastInsertRowId;
conn.Close();
}
return(entity);
}
public Owner Update(Owner entity)
{
using (SQLiteConnection conn = ConnectionCreater.CreateConnection())
{
conn.Open();
conn.Execute("UPDATE Owner SET " +
"owner_first_name = @FirstName, " +
"owner_last_name = @LastName, " +
"owner_address = @Address, " +
"owner_email = @Email, " +
"owner_phone_no = @PhoneNumber " +
"WHERE owner_id = @Id", entity);
conn.Close();
}
return(entity);
}
public static void BuildData()
{
SQLiteConnection conn = ConnectionCreater.CreateConnection();
conn.Open();
conn.Execute("DELETE FROM Pet");
foreach (var item in TestDB.GetPetsInDB())
{
conn.Execute("INSERT INTO Pet(pet_name, pet_type, pet_birthdate, pet_sold_date, pet_previous_owner_id, pet_color, pet_price) " +
"VALUES (@Name, " + (int)item.PType.Value + ", @BirthDate, @SoldDate, " + (item.PriviousOwner == null ? "NULL" : "" + item.PriviousOwner.Id) + ", @Color, @Price)", item);
Console.WriteLine(conn.LastInsertRowId);
}
conn.Execute("DELETE FROM Owner");
foreach (var item in TestDB.GetOwnersInDB())
{
conn.Execute("INSERT INTO Owner (owner_first_name, owner_last_name, owner_address, owner_email, owner_phone_no) " +
"VALUES (@FirstName, @LastName, @Address, @Email, @PhoneNumber)", item);
}
conn.Close();
}
public IEnumerable <Pet> ReadAll()
{
List <Pet> pets = new List <Pet>();
using (SQLiteConnection conn = ConnectionCreater.CreateConnection())
{
conn.Open();
IDataReader reader = conn.ExecuteReader("Select * FROM Pet as p LEFT JOIN Owner as o ON p.pet_previous_owner_id = o.owner_id");
while (reader.Read())
{
Pet pet = new Pet();
pet.Id = reader.GetInt64(0);
pet.Name = reader.GetString(1);
pet.PType = (PetType.PType)Enum.ToObject(typeof(PetType.PType), reader.GetInt32(2));
pet.BirthDate = reader.GetDateTime(3);
if (reader[4].GetType() != typeof(DBNull))
{
pet.SoldDate = reader.GetDateTime(4);
}
pet.Color = reader.GetString(6);
pet.Price = reader.GetDouble(7);
if (reader[5].GetType() != typeof(DBNull))
{
pet.PriviousOwner = new Owner()
{
Id = reader.GetInt64(8),
FirstName = reader.GetString(9),
LastName = reader.GetString(10),
Address = reader.GetString(11),
Email = reader.GetString(12),
PhoneNumber = reader.GetString(13),
};
}
pets.Add(pet);
}
conn.Close();
}
return(pets);
}
public IEnumerable <Owner> ReadAll()
{
List <Owner> owners = new List <Owner>();
using (SQLiteConnection conn = ConnectionCreater.CreateConnection())
{
conn.Open();
IDataReader reader = conn.ExecuteReader("Select * FROM Owner");
while (reader.Read())
{
Owner owner = new Owner();
owner.Id = reader.GetInt64(0);
owner.FirstName = reader.GetString(1);
owner.LastName = reader.GetString(2);
owner.Address = reader.GetString(3);
owner.Email = reader.GetString(4);
owner.PhoneNumber = reader.GetString(5);
owners.Add(owner);
}
conn.Close();
}
return(owners);
}
public Owner Read(long id)
{
Owner owner = null;
using (SQLiteConnection conn = ConnectionCreater.CreateConnection())
{
conn.Open();
IDataReader reader = conn.ExecuteReader("Select * FROM Owner WHERE owner_id = " + id);
while (reader.Read())
{
owner = new Owner();
owner.Id = reader.GetInt64(0);
owner.FirstName = reader.GetString(1);
owner.LastName = reader.GetString(2);
owner.Address = reader.GetString(3);
owner.Email = reader.GetString(4);
owner.PhoneNumber = reader.GetString(5);
}
conn.Close();
}
return(owner);
}
