public void TestReadHabitats()
{
Habitat h1 = new Habitat();
Habitat h2 = new Habitat();
Habitat h3 = new Habitat();
h1.HabitatName = "Habitat 1";
h2.HabitatName = "Habitat 2";
h3.HabitatName = "Habitat 3";
h1.ProviderId = 1;
h2.ProviderId = 2;
h3.ProviderId = 2;
Assert.IsTrue(dao.Create(h1, conn));
Assert.IsTrue(dao.Create(h2, conn));
Assert.IsTrue(dao.Create(h3, conn));
List<Habitat> list1 = dao.Read("SELECT * FROM habitats", conn);
Assert.IsTrue(list1.Count == 3);
List<Habitat> list2 = dao.Read("SELECT * FROM habitats WHERE habitat_name = 'Habitat 2'", conn);
Assert.IsTrue(list2.Count == 1);
List<Habitat> list3 = dao.Read("SELECT * FROM habitats WHERE provider_id = 2", conn);
Assert.IsTrue(list3.Count == 2);
}
public List<Habitat> Read(string query, SQLiteConnection conn)
{
List<Habitat> results = new List<Habitat>();
SQLiteCommand command = conn.CreateCommand();
log.Debug("Performing query: " + query);
command.CommandText = query;
try
{
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Habitat h = new Habitat();
h.HabitatId = Convert.ToInt32(reader["habitat_id"]);
h.HabitatName = Convert.ToString(reader["habitat_name"]);
h.ProviderId = Convert.ToInt32(reader["provider_id"]);
results.Add(h);
}
log.Debug("Query returned " + results.Count + " result(s)");
}
catch (Exception e)
{
log.Error("Error reading from database", e);
throw e;
}
return results;
}
public void TestCreateHabitat()
{
Habitat h = new Habitat();
h.HabitatName = "Habitat Create";
h.ProviderId = 1;
Assert.IsTrue(dao.Create(h, conn));
Habitat hOut = dao.Read("SELECT * FROM habitats WHERE habitat_name = 'Habitat Create'", conn)[0];
Assert.IsTrue(hOut.HabitatName == h.HabitatName);
Assert.IsTrue(hOut.ProviderId == h.ProviderId);
}
public void TestDeleteHabitat()
{
Habitat h = new Habitat();
h.HabitatName = "Habitat Delete";
h.ProviderId = 5;
Assert.IsTrue(dao.Create(h, conn));
List<Habitat> hList = dao.Read("SELECT * FROM habitats", conn);
int afterCreate = hList.Count;
Assert.IsTrue(afterCreate == 1);
Assert.IsTrue(dao.Delete(hList[0], conn));
int afterDelete = dao.Read("SELECT * FROM habitats", conn).Count;
Assert.IsTrue(afterDelete == 0);
}
public bool Delete(Habitat habitat, SQLiteConnection conn)
{
string deleteDML = config.getValue("DeleteHabitatDML");
try
{
SQLiteHelper.ExecuteDML(conn,
deleteDML,
habitat.HabitatId);
}
catch (Exception e)
{
log.Error("Exception caught when Deleting Habitat " + habitat.HabitatId, e);
throw e;
}
log.Debug("Delete completed sucessfully");
return true;
}
public bool Create(Habitat habitat, SQLiteConnection conn)
{
string insertDML = config.getValue("InsertHabitatDML");
try
{
SQLiteHelper.ExecuteDML(conn,
insertDML,
habitat.ProviderId,
habitat.HabitatName);
}
catch (Exception e)
{
log.Error("Exception caught when Creating new Habitat: " + habitat.HabitatName, e);
throw e;
}
log.Debug("Create completed sucessfully");
return true;
}
private Hunt getHunt(Habitat habitat, Group group, List<Guide> guides, SQLiteConnection conn)
{
Harvest harvest = new Harvest()
{
Pheasant = rand.Next(3, 20),
Grouse = rand.Next(0, 6),
Partridge = rand.Next(0, 3),
BirdsSeen = rand.Next(0,100)*10,
BirdsMissed = rand.Next(0,15),
Crop = crops[rand.Next(0,crops.Length)],
CropHarvested = "Y"
};
if (rand.Next(0, 1) == 1)
{
harvest.CropHarvested = "N";
}
Hunt h = new Hunt()
{
Habitat = habitat,
Group = group,
Guides = guides,
NumberOfGuns = rand.Next(2,15),
Harvest = harvest,
HuntDate = new DateTime(2015, rand.Next(10,13), rand.Next(1,31)),
Comments = "Comment here"
};
return h;
}
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 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;
}
private void button_save_Click(object sender, EventArgs e)
{
log.Debug("Save button Clicked");
SQLiteConnection conn = SQLiteConnectionFactory.GetPrimaryDBConnection();
HuntDAO dao = new HuntDAO();
DataRow selectedDataRow;
Hunt hunt = new Hunt();
Habitat habitat = new Habitat();
Group group = new Group();
Harvest harvest = new Harvest();
selectedDataRow = ((DataRowView)comboBox_habitat.SelectedItem).Row;
int habitatId = Convert.ToInt32(selectedDataRow["habitat_id"]);
habitat.HabitatId = habitatId;
selectedDataRow = ((DataRowView)comboBox_group.SelectedItem).Row;
int groupId = Convert.ToInt32(selectedDataRow["group_id"]);
group.GroupId = groupId;
DateTime huntDate = datePicker_huntDate.Value.Date;
hunt.NumberOfGuns = (int)numericUpDown_numberOfGuns.Value;
harvest.Pheasant = (int)numericUpDown_pheasant.Value;
harvest.Grouse = (int)numericUpDown_grouse.Value;
harvest.Partridge = (int)numericUpDown_partridge.Value;
harvest.BirdsSeen = (int)numericUpDown_birdsSeen.Value;
harvest.BirdsMissed = (int)numericUpDown_birdsMissed.Value;
harvest.Crop = textBox_crop.Text;
harvest.CropHarvested = "";
if (radioButton_harvestYes.Checked == true)
{
harvest.CropHarvested = "Y";
}
else if (radioButton_harvestNo.Checked == true)
{
harvest.CropHarvested = "N";
}
string comments = textBox_comments.Text;
hunt.Habitat = habitat;
hunt.Group = group;
hunt.Guides = guides.ToList();
hunt.HuntDate = huntDate;
hunt.Harvest = harvest;
hunt.Comments = comments;
try
{
label_message.ForeColor = Color.Blue;
if (isUpdate)
{
hunt.HuntId = Convert.ToInt32(label_huntId.Text);
log.Debug("Attempting to update hunt " + hunt.HuntId);
dao.Update(hunt, conn);
label_message.Text = "Updated Hunt: " + hunt.HuntId;
}
else
{
log.Debug("Attempting to save new hunt");
dao.Create(hunt, conn);
label_message.Text = "Saved new Hunt";
this.Close();
}
}
catch (Exception ex)
{
log.Error("Error saving/updating hunt", ex);
label_message.ForeColor = Color.Red;
label_message.Text = ex.Message;
}
}
public void TestUpdateHabitat()
{
Habitat hIn = new Habitat();
Habitat hOut;
Habitat hUpdated;
hIn.HabitatName = "Habitat Update";
hIn.ProviderId = 1;
Assert.IsTrue(dao.Create(hIn, conn));
hOut = dao.Read("SELECT * FROM habitats WHERE habitat_name = 'Habitat Update'", conn)[0];
hOut.HabitatName = "Habitat Updated";
hOut.ProviderId = 5;
Assert.IsTrue(dao.Update(hOut, conn));
hUpdated = dao.Read("SELECT * FROM habitats WHERE habitat_name = 'Habitat Updated'", conn)[0];
Assert.IsTrue(hOut.HabitatName == hUpdated.HabitatName);
Assert.IsTrue(hOut.ProviderId == hUpdated.ProviderId);
}
public void TestReadHabitatsById()
{
Habitat h1 = new Habitat();
h1.HabitatName = "Habitat 1";
h1.ProviderId = 1;
Assert.IsTrue(dao.Create(h1, conn));
Habitat hOut = dao.Read("SELECT * FROM habitats", conn)[0];
Habitat hById = dao.Read(hOut.HabitatId, conn);
Assert.IsTrue(hById.HabitatId == hOut.HabitatId);
Assert.IsTrue(hById.HabitatName == h1.HabitatName);
Assert.IsTrue(hById.ProviderId == h1.ProviderId);
}
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;
}