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; }