public void addPatron(Patron pat)
{
if (this.OpenConnection() == true)
{
String query1 = "INSERT INTO patron VALUES ('" +
pat.Id + "', '" +
pat.FirstName + "', '" +
pat.LastName + "', '" +
pat.MiddleInitial + "', '" +
pat.Phone + "')";
String query2 = "INSERT INTO address VALUES ('" +
pat.Id + "', '" +
pat.StreetNum + "', '" +
pat.AddressLine1 + "', '" +
pat.AddressLine2 + "', '" +
pat.City + "', '" +
pat.State + "', '" +
pat.Zip + "')";
String query3 = "INSERT INTO previousvisits VALUES ('" +
pat.Date + "', '" +
pat.NumChildren + "', '" +
pat.NumAdults + "', '" + "')";
MySqlCommand cmd1 = new MySqlCommand(query1, connection);
MySqlCommand cmd2 = new MySqlCommand(query2, connection);
MySqlCommand cmd3 = new MySqlCommand(query3, connection);
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
cmd3.ExecuteNonQuery();
this.CloseConnection();
}
}
static void Main(string[] args)
{
//Establish a connection with DBConnect class to access patron table
DBConnect db = new DBConnect();
Address myAddress = new Address();
PreviousVisit pVisit = new PreviousVisit();
Patron myPatron = new Patron(2, "Joe", "Bloggs", "S", "5550134", myAddress,pVisit);
//holds quiried data. First element will contain cells of first column from table. Second element contains second column and so on....
List<Patron> list = new List<Patron>();
list = db.SelectPatron("SELECT * FROM patron WHERE firstName = 'Rob'");
for (int x = 0; x < list.Count; x++)
{
Console.WriteLine(list[x].Id);
Console.WriteLine(list[x].FirstName);
Console.WriteLine(list[x].LastName);
Console.WriteLine(list[x].MiddleInitial);
Console.WriteLine(list[x].Phone);
Console.WriteLine(list[x].StreetNum);
Console.WriteLine(list[x].AddressLine1);
Console.WriteLine(list[x].AddressLine2);
Console.WriteLine(list[x].City);
Console.WriteLine(list[x].State);
Console.WriteLine(list[x].Zip);
Console.WriteLine(list[x].NumChildren);
Console.WriteLine(list[x].NumAdults);
Console.WriteLine(list[x].Date.Month+"/"+list[x].Date.Day+"/"+list[x].Date.Year);
Console.WriteLine(" ");
}
db.deletePatron(2);
/*list = db.SelectPatron("SELECT * FROM patron WHERE firstName = 'Rob'");
foreach (var i in list)
{
Console.WriteLine(i);
}
for (int x = 0; x < list.Length; x++)
{
for(int y=0;y<list[x].Count;y++){
Console.WriteLine(list[x].ElementAt(y));
}
}*/
//db.Query("INSERT INTO patron (patron_id, firstName, lastName, middleInitial, phone) VALUES ('10', 'Tom', 'Maey', 'ff', '2353744')");
Console.ReadLine();
}
//Select statement
public List<Patron> SelectPatron(String query)
{
//Create a list to store the result
List<Patron> list = new List<Patron>();
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
String id = dataReader.GetString(0);
String fName = dataReader.GetString(1);
String lName = dataReader.GetString(2);
String mInitial = dataReader.GetString(3);
String phone = dataReader.GetString(4);
String stNum = "";
String addrLine1 = "";
String addrLine2 = "";
String city = "";
String state = "";
String zip = "";
connection2.Open();
String query2 = "SELECT * FROM address WHERE patron_id = '" + id + "'";
MySqlCommand cmd2 = new MySqlCommand(query2, connection2);
MySqlDataReader dataReader2 = cmd2.ExecuteReader();
while (dataReader2.Read())
{
//THIS NESTED LOOP WILL NOT WORK PROPERLY IF TWO PEOPLE HAVE THE SAME PATRON_ID (which shouldn't exist anyways)
//The loop will assign the address of the last person with the last patron id to the object being stored
stNum = dataReader2.GetString(1);
addrLine1 = dataReader2.GetString(2);
addrLine2 = dataReader2.GetString(3);
city = dataReader2.GetString(4);
state = dataReader2.GetString(5);
zip = dataReader2.GetString(6);
}
int numChild=0;
int numAdult=0;
DateTime date = new DateTime();
connection3.Open();
String query3 = "SELECT * FROM previousvisits WHERE patron_id = '" + id + "'";
MySqlCommand cmd3 = new MySqlCommand(query3, connection3);
MySqlDataReader dataReader3 = cmd3.ExecuteReader();
// MessageBox.Show("jjd" + date.ToString() + " " + numChild + " " + numAdult);
while (dataReader3.Read())
{
date = (DateTime)dataReader3.GetValue(1);
numChild = (int)dataReader3.GetValue(2);
numAdult = (int)dataReader3.GetValue(3);
}
connection2.Close();
dataReader2.Close();
connection3.Close();
dataReader3.Close();
Address address = new Address(Int32.Parse(id), stNum, addrLine1, addrLine2, city, state, zip);
PreviousVisit prevVis = new PreviousVisit(Int32.Parse(id),numChild,numAdult,date);
Patron patron = new Patron(Int32.Parse(id), fName, lName, mInitial, phone, address,prevVis);
list.Add(patron);
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
//return list to be displayed
return list;
}
else
{
MessageBox.Show("Cannot connect to server.");
return list;
}
}