//adds a child to the table.
public void addChild(child childToAdd, Int32 soldierID)
{
string sql = string.Format("insert into children (soldier_id, spouse_id, f_name, mid_name, l_name, maid_name) values ({0}, {1}, '{2}', '{3}', '{4}', '{5}');",
soldierID, childToAdd.spouseID, childToAdd.name.first, childToAdd.name.middle, childToAdd.name.last, childToAdd.name.maiden);
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
}
private void buttonAddChild_Click(object sender, EventArgs e)
{
child c = new child();
c.name.first = textBoxChildFirstName.Text;
c.name.middle = textBoxChildMiddleName.Text;
c.name.last = textBoxChildLastName.Text;
c.name.maiden = textBoxChildMaidenName.Text;
try
{
c.spouseID = ((comboMother)comboBoxMother.SelectedItem).mother.id;
}
catch
{
c.spouseID = 0;
}
string[] childLine = { c.ToString() };
ListViewItem newItem = new ListViewItem(childLine);
newItem.Tag = c;
listViewChildren.Items.Add(newItem);
//clear
textBoxChildFirstName.Clear();
textBoxChildLastName.Clear();
textBoxChildMaidenName.Clear();
textBoxChildMiddleName.Clear();
comboBoxMother.SelectedIndex = 0;
}
//returns a list of all the soldiers in the database.
public List<Soldier> Soldiers()
{
List<Soldier> soldiers = new List<Soldier>();
string readSoldiers = "select * from soldiers;";
SQLiteCommand readcommand = new SQLiteCommand(readSoldiers, m_dbConnection);
SQLiteDataReader reader = readcommand.ExecuteReader();
while (reader.Read())
{
//all of the info in the soliers table
Soldier newSoldier = new Soldier();
newSoldier.id = Convert.ToInt32(reader["id"]);
newSoldier.soldierName.first = reader["f_name"].ToString();
newSoldier.soldierName.middle = reader["mid_name"].ToString();
newSoldier.soldierName.last = reader["l_name"].ToString();
newSoldier.soldierName.maiden = reader["maid_name"].ToString();
newSoldier.birthDate.day = Convert.ToInt32(reader["b_date_day"]);
newSoldier.birthDate.month = Convert.ToInt32(reader["b_date_month"]);
newSoldier.birthDate.year = Convert.ToInt32(reader["b_date_year"]);
newSoldier.birthLocation.city = reader["b_loc_city"].ToString();
newSoldier.birthLocation.county = reader["b_loc_county"].ToString();
newSoldier.birthLocation.state = reader["b_loc_state"].ToString();
newSoldier.birthLocation.country = reader["b_loc_country"].ToString();
newSoldier.deathDate.day = Convert.ToInt32(reader["d_date_day"]);
newSoldier.deathDate.month = Convert.ToInt32(reader["d_date_month"]);
newSoldier.deathDate.year = Convert.ToInt32(reader["d_date_year"]);
newSoldier.deathLocation.city = reader["d_loc_city"].ToString();
newSoldier.deathLocation.county = reader["d_loc_county"].ToString();
newSoldier.deathLocation.state = reader["d_loc_state"].ToString();
newSoldier.deathLocation.country = reader["d_loc_country"].ToString();
newSoldier.cemetery = reader["c_name"].ToString();
newSoldier.cemeteryLatitude = reader["c_latitude"].ToString();
newSoldier.cemeteryLongitude = reader["c_longitude"].ToString();
newSoldier.cemeteryLocation.city = reader["c_loc_city"].ToString();
newSoldier.cemeteryLocation.county = reader["c_loc_county"].ToString();
//newSoldier.cemeteryLocation.state = "Illinois";
newSoldier.cemeteryLocation.state = reader["c_loc_state"].ToString();
//newSoldier.cemeteryLocation.country = "USA";
newSoldier.cemeteryLocation.country = reader["c_loc_country"].ToString();
//read spouses
string readSpouses = String.Format("select * from spouses where soldier_id={0};", newSoldier.id.ToString());
readcommand = new SQLiteCommand(readSpouses, m_dbConnection);
SQLiteDataReader spousesReader = readcommand.ExecuteReader();
while (spousesReader.Read())
{
spouse newSpouse = new spouse();
newSpouse.name.first = spousesReader["f_name"].ToString();
newSpouse.name.middle = spousesReader["mid_name"].ToString();
newSpouse.name.last = spousesReader["l_name"].ToString();
newSpouse.name.maiden = spousesReader["maid_name"].ToString();
newSpouse.id = Convert.ToInt32(spousesReader["id"]);
newSpouse.marriageDate.day = Convert.ToInt32(spousesReader["m_date_day"]);
newSpouse.marriageDate.month = Convert.ToInt32(spousesReader["m_date_month"]);
newSpouse.marriageDate.year = Convert.ToInt32(spousesReader["m_date_year"]);
newSpouse.marriageLocation.city = spousesReader["m_loc_city"].ToString();
newSpouse.marriageLocation.county = spousesReader["m_loc_county"].ToString();
newSpouse.marriageLocation.state = spousesReader["m_loc_state"].ToString();
newSpouse.marriageLocation.country = spousesReader["m_loc_country"].ToString();
newSoldier.spouses.Add(newSpouse);
}
//read children
string readChildren = String.Format("select * from children where soldier_id={0};", newSoldier.id.ToString());
readcommand = new SQLiteCommand(readChildren, m_dbConnection);
SQLiteDataReader childrenReader = readcommand.ExecuteReader();
while (childrenReader.Read())
{
child newChild = new child();
newChild.name.first = childrenReader["f_name"].ToString();
newChild.name.middle = childrenReader["mid_name"].ToString();
newChild.name.last = childrenReader["l_name"].ToString();
newChild.name.maiden = childrenReader["maid_name"].ToString();
newChild.soldierID = Convert.ToInt32( childrenReader["soldier_id"]);
newChild.spouseID = Convert.ToInt32( childrenReader["spouse_id"]);
newSoldier.children.Add(newChild);
}
//read residences
string readResidences = String.Format("select * from residences where soldier_id={0};", newSoldier.id.ToString());
readcommand = new SQLiteCommand(readResidences, m_dbConnection);
SQLiteDataReader resReader = readcommand.ExecuteReader();
while (resReader.Read())
{
residence newRes = new residence();
newRes.moveInDate.day = Convert.ToInt32(resReader["b_date_day"]);
newRes.moveInDate.month = Convert.ToInt32(resReader["b_date_month"]);
newRes.moveInDate.year = Convert.ToInt32(resReader["b_date_year"]);
newRes.moveOutDate.day = Convert.ToInt32(resReader["e_date_day"]);
newRes.moveOutDate.month = Convert.ToInt32(resReader["e_date_month"]);
newRes.moveOutDate.year = Convert.ToInt32(resReader["e_date_year"]);
newRes.place.city = resReader["city"].ToString();
newRes.place.county = resReader["county"].ToString();
newRes.place.state = resReader["state"].ToString();
newRes.place.country = resReader["country"].ToString();
newSoldier.residences.Add(newRes);
}
//read ranks
string readPositions = String.Format("select * from ranks where soldier_id={0};", newSoldier.id.ToString());
readcommand = new SQLiteCommand(readPositions, m_dbConnection);
SQLiteDataReader posReader = readcommand.ExecuteReader();
while (posReader.Read())
{
newSoldier.servicePositions.Add(posReader["rank"].ToString());
}
//read troops
string readTroops = String.Format("select * from troops where soldier_id={0};", newSoldier.id.ToString());
readcommand = new SQLiteCommand(readTroops, m_dbConnection);
SQLiteDataReader troopsReader = readcommand.ExecuteReader();
while (troopsReader.Read())
{
newSoldier.serviceTroops.Add(troopsReader["troop"].ToString());
}
//read superiors
string readSuperiors = String.Format("select * from superiors where soldier_id={0};", newSoldier.id.ToString());
readcommand = new SQLiteCommand(readSuperiors, m_dbConnection);
SQLiteDataReader supReader = readcommand.ExecuteReader();
while (supReader.Read())
{
newSoldier.serviceSuperiors.Add(supReader["superior"].ToString());
}
newSoldier.serviceAddedText = reader["service_text"].ToString();
newSoldier.residenceAddedText = reader["residence_text"].ToString();
newSoldier.markerText = reader["marker_text"].ToString();
newSoldier.pensionNumber = reader["pension_number"].ToString();
newSoldier.pensionText = reader["pension_text"].ToString();
newSoldier.sources = reader["sources"].ToString();
newSoldier.addedText = reader["added_text"].ToString();
soldiers.Add(newSoldier);
}
return soldiers;
}
