public List<Provider> Read(string query, SQLiteConnection conn)
{
List<Provider> results = new List<Provider>();
SQLiteCommand command = conn.CreateCommand();
log.Debug("Performing query: " + query);
command.CommandText = query;
try
{
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Provider p = new Provider();
p.ProviderId = Convert.ToInt32(reader["provider_id"]);
p.ProviderName = Convert.ToString(reader["provider_name"]);
results.Add(p);
}
log.Debug("Query returned " + results.Count + " result(s)");
}
catch (Exception e)
{
log.Error("Error reading from database", e);
throw e;
}
return results;
}
public void TestCreateProvider()
{
Provider p = new Provider();
p.ProviderName = "Erik Hage Create";
Assert.IsTrue(dao.Create(p, conn));
Provider pOut = dao.Read("SELECT * FROM providers WHERE provider_name = 'Erik Hage Create'", conn)[0];
Assert.IsTrue(pOut.ProviderName == p.ProviderName);
}
public void TestDeleteProvider()
{
Provider p = new Provider();
p.ProviderName = "Erik Hage Delete";
Assert.IsTrue(dao.Create(p, conn));
List<Provider> pList = dao.Read("SELECT * FROM providers", conn);
int afterCreate = pList.Count;
Assert.IsTrue(afterCreate == 1);
Assert.IsTrue(dao.Delete(pList[0], conn));
int afterDelete = dao.Read("SELECT * FROM providers", conn).Count;
Assert.IsTrue(afterDelete == 0);
}
public bool Delete(Provider provider, SQLiteConnection conn)
{
string deleteDML = config.getValue("DeleteProviderDML");
try
{
SQLiteHelper.ExecuteDML(conn,
deleteDML,
provider.ProviderId);
}
catch (Exception e)
{
log.Error("Exception caught when Deleting Provider " + provider.ProviderId, e);
throw e;
}
log.Debug("Delete completed sucessfully");
return true;
}
public bool Create(Provider provider, SQLiteConnection conn)
{
string insertDML = config.getValue("InsertProviderDML");
try
{
SQLiteHelper.ExecuteDML(conn,
insertDML,
provider.ProviderName);
}
catch (Exception e)
{
log.Error("Exception caught when Creating new Provider: " + provider.ProviderName, e);
throw e;
}
log.Debug("Create completed sucessfully");
return true;
}
public void TestReadProviders()
{
Provider p1 = new Provider();
Provider p2 = new Provider();
Provider p3 = new Provider();
p1.ProviderName = "Erik Hage 1";
p2.ProviderName = "Erik Hage 2";
p3.ProviderName = "Erik Hage 3";
Assert.IsTrue(dao.Create(p1, conn));
Assert.IsTrue(dao.Create(p2, conn));
Assert.IsTrue(dao.Create(p3, conn));
List<Provider> list1 = dao.Read("SELECT * FROM providers", conn);
Assert.IsTrue(list1.Count == 3);
List<Provider> list2 = dao.Read("SELECT * FROM providers WHERE provider_name = 'Erik Hage 2'", conn);
Assert.IsTrue(list2.Count == 1);
}
private void button_save_Click(object sender, EventArgs e)
{
log.Debug("Save button clicked");
SQLiteConnection conn = SQLiteConnectionFactory.GetPrimaryDBConnection();
ProviderDAO dao = new ProviderDAO();
Provider provider = new Provider();
try
{
label_message.ForeColor = Color.Blue;
provider.ProviderName = textBox_providerName.Text;
if (isUpdate)
{
provider.ProviderId = Convert.ToInt32(label_providerId.Text);
log.Debug("Attempting to update provider " + provider.ProviderId + ": " + provider.ProviderName);
dao.Update(provider, conn);
label_message.Text = "Updated Provider " + provider.ProviderName;
log.Debug("Update provider successful");
}
else
{
log.Debug("Attempting to save new provider: " + provider.ProviderName);
dao.Create(provider, conn);
label_message.Text = "Saved new Provider " + provider.ProviderName;
log.Debug("Create new provider successful");
this.Close();
}
}
catch (Exception ex)
{
log.Error("Error saving/updating provider", ex);
label_message.ForeColor = Color.Red;
label_message.Text = ex.Message;
}
}
public void SaveDummyHunts(int num)
{
//backup current db
//File.Copy(Configuration.Instance.PrimaryDatabaseName, Configuration.Instance.PrimaryDatabaseName + ".tmp");
//Thread.Sleep(2000);
HuntDAO huntDao = new HuntDAO();
using (SQLiteConnection conn = SQLiteConnectionFactory.GetPrimaryDBConnection())
{
//delete all from all tables
SQLiteHelper.ExecuteDML(conn, "DELETE FROM hunts");
SQLiteHelper.ExecuteDML(conn, "DELETE FROM providers");
SQLiteHelper.ExecuteDML(conn, "DELETE FROM habitats");
SQLiteHelper.ExecuteDML(conn, "DELETE FROM guides");
SQLiteHelper.ExecuteDML(conn, "DELETE FROM groups");
SQLiteHelper.ExecuteDML(conn, "DELETE FROM hunts_guides");
Provider[] providers = new Provider[70];
Habitat[] habitats = new Habitat[210];
Group[] groups = new Group[250];
Guide[] guides = new Guide[25];
for (int i = 0; i < providers.Length; i++)
{
providers[i] = InsertTestProvider(conn, i);
}
for (int i = 0; i < habitats.Length; i++)
{
habitats[i] = InsertTestHabitat(conn, providers[rand.Next(0, providers.Length)], i);
}
for (int i = 0; i < groups.Length; i++)
{
groups[i] = InsertTestGroup(conn, i);
}
for (int i = 0; i < guides.Length; i++)
{
guides[i] = InsertTestGuide(conn, i);
}
for (int i = 0; i < num; i++)
{
List<Guide> guidesL = new List<Guide>();
guidesL.Add(guides[rand.Next(0,guides.Length)]);
if (rand.Next(0, 20) == 10)
{
guidesL.Add(guides[rand.Next(0, guides.Length)]);
}
Hunt hunt = getHunt(
habitats[rand.Next(0,habitats.Length)],
groups[rand.Next(0,groups.Length)],
guidesL,
conn);
huntDao.Create(hunt, conn);
}
}
}
private Provider InsertTestProvider(SQLiteConnection conn, int i)
{
ProviderDAO providerDAO = new ProviderDAO();
string name = GetRandomName() + i;
Provider p = new Provider() { ProviderName = name };
providerDAO.Create(p, conn);
p.ProviderId = providerDAO.Read("SELECT * FROM providers WHERE provider_id = (SELECT max(provider_id) FROM providers)", conn)[0].ProviderId;
return p;
}
private Habitat InsertTestHabitat(SQLiteConnection conn, Provider p, int i)
{
HabitatDAO habitatDAO = new HabitatDAO();
string name = habitatName1[rand.Next(0, habitatName1.Length)] + " " + habitatName2[rand.Next(0, habitatName2.Length)] + i;
Habitat h = new Habitat() { HabitatName = name, ProviderId = p.ProviderId };
habitatDAO.Create(h, conn);
h.HabitatId = habitatDAO.Read("SELECT * FROM habitats WHERE habitat_id = (SELECT max(habitat_id) FROM habitats)", conn)[0].HabitatId;
return h;
}
public void TestUpdateProvider()
{
Provider pIn = new Provider();
Provider pOut;
Provider pUpdated;
pIn.ProviderName = "Erik Hage Update";
Assert.IsTrue(dao.Create(pIn, conn));
pOut = dao.Read("SELECT * FROM providers WHERE provider_name = 'Erik Hage Update'", conn)[0];
pOut.ProviderName = "Erik Hage Updated";
Assert.IsTrue(dao.Update(pOut, conn));
pUpdated = dao.Read("SELECT * FROM providers WHERE provider_name = 'Erik Hage Updated'", conn)[0];
Assert.IsTrue(pOut.ProviderName == pUpdated.ProviderName);
}
private Provider InsertTestProvider(SQLiteConnection conn)
{
ProviderDAO providerDAO = new ProviderDAO();
Provider p = new Provider() { ProviderName = "HuntTestProvider" };
providerDAO.Create(p, conn);
p.ProviderId = providerDAO.Read("SELECT * FROM providers WHERE provider_name = 'HuntTestProvider'", conn)[0].ProviderId;
return p;
}
private Habitat InsertTestHabitat(SQLiteConnection conn, Provider p)
{
HabitatDAO habitatDAO = new HabitatDAO();
Habitat h = new Habitat() { HabitatName = "HuntTestHabitat", ProviderId = p.ProviderId };
habitatDAO.Create(h, conn);
h.HabitatId = habitatDAO.Read("SELECT * FROM habitats WHERE habitat_name = 'HuntTestHabitat'", conn)[0].HabitatId;
return h;
}