public List<Group> Read(string query, SQLiteConnection conn)
{
List<Group> results = new List<Group>();
SQLiteCommand command = conn.CreateCommand();
log.Debug("Performing query: " + query);
command.CommandText = query;
try
{
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Group g = new Group();
g.GroupId = Convert.ToInt32(reader["group_id"]);
g.GroupName = Convert.ToString(reader["group_name"]);
results.Add(g);
}
log.Debug("Query returned " + results.Count + " result(s)");
}
catch (Exception e)
{
log.Error("Error reading from database", e);
throw e;
}
return results;
}
public bool Delete(Group group, SQLiteConnection conn)
{
string deleteDML = config.getValue("DeleteGroupDML");
try
{
SQLiteHelper.ExecuteDML(conn,
deleteDML,
group.GroupId);
}
catch (Exception e)
{
log.Error("Exception caught when Deleting Group " + group.GroupId, e);
throw e;
}
log.Debug("Delete completed sucessfully");
return true;
}
public bool Create(Group group, SQLiteConnection conn)
{
string insertDML = config.getValue("InsertGroupDML");
try
{
SQLiteHelper.ExecuteDML(conn,
insertDML,
group.GroupName);
}
catch (Exception e)
{
log.Error("Exception caught when Creating new Group: " + group.GroupName, e);
throw e;
}
log.Debug("Create completed sucessfully");
return true;
}
private void button_save_Click(object sender, EventArgs e)
{
log.Debug("Save button clicked");
SQLiteConnection conn = SQLiteConnectionFactory.GetPrimaryDBConnection();
GroupDAO dao = new GroupDAO();
Group group = new Group();
try
{
label_message.ForeColor = Color.Blue;
group.GroupName = textBox_groupName.Text;
if (isUpdate)
{
group.GroupId = Convert.ToInt32(label_groupId.Text);
log.Debug("Attempting to update group " + group.GroupId + ": " + group.GroupName);
dao.Update(group, conn);
label_message.Text = "Updated group: " + group.GroupName;
log.Debug("Updated group successful");
}
else
{
log.Debug("Attempting to save new group: " + group.GroupName);
dao.Create(group, conn);
label_message.Text = "Created New group: " + group.GroupName;
log.Debug("Create new group successful");
this.Close();
}
}
catch (Exception ex)
{
log.Error("Error saving/updating group ", ex);
label_message.ForeColor = Color.Red;
label_message.Text = ex.Message;
}
}
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 Group InsertTestGroup(SQLiteConnection conn, int i)
{
GroupDAO groupDAO = new GroupDAO();
string name = lastNames[rand.Next(0, lastNames.Length - 1)] + " Group" + i;
Group g = new Group() { GroupName = name };
groupDAO.Create(g, conn);
g.GroupId = groupDAO.Read("SELECT * FROM groups WHERE group_id = (SELECT max(group_id) FROM groups)", conn)[0].GroupId;
return g;
}
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;
}
}
private Group InsertTestGroup(SQLiteConnection conn)
{
GroupDAO groupDAO = new GroupDAO();
Group g = new Group() { GroupName = "HuntTestGroup" };
groupDAO.Create(g, conn);
g.GroupId = groupDAO.Read("SELECT * FROM groups WHERE group_name = 'HuntTestGroup'", conn)[0].GroupId;
return g;
}