public List<Guide> Read(string query, SQLiteConnection conn)
{
List<Guide> results = new List<Guide>();
SQLiteCommand command = conn.CreateCommand();
log.Debug("Performing query: " + query);
command.CommandText = query;
try
{
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Guide g = new Guide();
g.GuideId = Convert.ToInt32(reader["guide_id"]);
g.GuideName = Convert.ToString(reader["guide_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 void TestCreateGuide()
{
Guide g = new Guide();
g.GuideName = "Guide Create";
Assert.IsTrue(dao.Create(g, conn));
Guide gOut = dao.Read("SELECT * FROM guides WHERE guide_name = 'Guide Create'", conn)[0];
Assert.IsTrue(gOut.GuideName == g.GuideName);
}
public void TestReadGuideById()
{
Guide g1 = new Guide();
g1.GuideName = "Guide 1";
Assert.IsTrue(dao.Create(g1, conn));
Guide gOut = dao.Read("SELECT * FROM guides", conn)[0];
Guide gById = dao.Read(gOut.GuideId, conn);
Assert.IsTrue(gById.GuideId == gOut.GuideId);
Assert.IsTrue(gById.GuideName == g1.GuideName);
}
public void TestDeleteGuide()
{
Guide g = new Guide();
g.GuideName = "Guide Delete";
Assert.IsTrue(dao.Create(g, conn));
List<Guide> gList = dao.Read("SELECT * FROM guides", conn);
int afterCreate = gList.Count;
Assert.IsTrue(afterCreate == 1);
Assert.IsTrue(dao.Delete(gList[0], conn));
int afterDelete = dao.Read("SELECT * FROM guides", conn).Count;
Assert.IsTrue(afterDelete == 0);
}
public bool Delete(Guide guide, SQLiteConnection conn)
{
string deleteDML = config.getValue("DeleteGuideDML");
try
{
SQLiteHelper.ExecuteDML(conn,
deleteDML,
guide.GuideId);
}
catch (Exception e)
{
log.Error("Exception caught when Deleting Guide " + guide.GuideId, e);
throw e;
}
log.Debug("Delete completed sucessfully");
return true;
}
public bool Create(Guide guide, SQLiteConnection conn)
{
string insertDML = config.getValue("InsertGuideDML");
try
{
SQLiteHelper.ExecuteDML(conn,
insertDML,
guide.GuideName);
}
catch (Exception e)
{
log.Error("Exception caught when Creating new Guide: " + guide.GuideName, 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();
GuideDAO dao = new GuideDAO();
Guide guide = new Guide();
try
{
label_message.ForeColor = Color.Blue;
guide.GuideName = textBox_guideName.Text;
if (isUpdate)
{
guide.GuideId = Convert.ToInt32(label_guideId.Text);
log.Debug("Attempting to update guide " + guide.GuideId + ": " + guide.GuideName);
dao.Update(guide, conn);
label_message.Text = "Updated Guide: " + guide.GuideName;
log.Debug("Update guide successful");
}
else
{
log.Debug("Attempting to save new guide: " + guide.GuideName);
dao.Create(guide, conn);
label_message.Text = "Created New guide: " + guide.GuideName;
log.Debug("Create new guide successful");
this.Close();
}
}
catch (Exception ex)
{
log.Error("Error saving/updating guide", 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 Guide InsertTestGuide(SQLiteConnection conn, int i)
{
GuideDAO guideDAO = new GuideDAO();
string name = GetRandomName() + i;
Guide g = new Guide() { GuideName = name };
guideDAO.Create(g, conn);
g.GuideId = guideDAO.Read("SELECT * FROM guides WHERE guide_id = (SELECT max(guide_id) FROM guides)", conn)[0].GuideId;
return g;
}
private void AddGuide(Guide guide)
{
guides.Add(guide);
guides = new BindingList<Guide>(guides.Distinct().ToList());
listBox_Guides.DataSource = guides;
}
public void TestReadGuides()
{
Guide g1 = new Guide();
Guide g2 = new Guide();
Guide g3 = new Guide();
g1.GuideName = "Guide 1";
g2.GuideName = "Guide 2";
g3.GuideName = "Guide 3";
Assert.IsTrue(dao.Create(g1, conn));
Assert.IsTrue(dao.Create(g2, conn));
Assert.IsTrue(dao.Create(g3, conn));
List<Guide> list1 = dao.Read("SELECT * FROM guides", conn);
Assert.IsTrue(list1.Count == 3);
List<Guide> list2 = dao.Read("SELECT * FROM guides WHERE guide_name = 'Guide 2'", conn);
Assert.IsTrue(list2.Count == 1);
}
public void TestUpdateGuide()
{
Guide gIn = new Guide();
Guide gOut;
Guide gUpdated;
gIn.GuideName = "Guide Update";
Assert.IsTrue(dao.Create(gIn, conn));
gOut = dao.Read("SELECT * FROM guides WHERE guide_name = 'Guide Update'", conn)[0];
gOut.GuideName = "Guide Updated";
Assert.IsTrue(dao.Update(gOut, conn));
gUpdated = dao.Read("SELECT * FROM guides WHERE guide_name = 'Guide Updated'", conn)[0];
Assert.IsTrue(gOut.GuideName == gUpdated.GuideName);
}
private Guide InsertTestGuide(SQLiteConnection conn)
{
GuideDAO guideDAO = new GuideDAO();
Guide g = new Guide() { GuideName = "HuntTestGuide" };
guideDAO.Create(g, conn);
g.GuideId = guideDAO.Read("SELECT * FROM guides WHERE guide_name = 'HuntTestGuide'", conn)[0].GuideId;
return g;
}
