Beispiel #1
0
        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();
            }
        }
Beispiel #2
0
        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();
        }
Beispiel #3
0
        //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;
            }
        }