public UserLocation GetLastKnownUserLocation(string userName) { //THIS IS AWFUL!!! UserLocation location = new UserLocation(); String sql = "SELECT Longitude, Latitude, Altitude, Confidence, UserName FROM Locations WHERE LocationId IN (SELECT LastKnownLocation FROM Users WHERE UserName = @userName)"; MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["pineapplePiranha"].ConnectionString); using (conn) { conn.Open(); MySqlCommand cmd = new MySqlCommand(sql, conn); cmd.Parameters.Add(new MySqlParameter("userName", userName)); MySqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { location.Longitude = reader.GetDecimal(0); location.Latitude = reader.GetDecimal(1); location.Altidude = reader.GetDecimal(2); location.Confidence = reader.GetDecimal(3); location.UserName = reader.GetString(4); Console.Out.WriteLine(location.Longitude); Console.Out.WriteLine(location.Latitude); Console.Out.WriteLine(location.Altidude); Console.Out.WriteLine(location.Confidence); Console.Out.WriteLine(location.UserName); } } return location; }
private int GetLocationId() { int result = 0; //THIS IS AWFUL!!! UserLocation location = new UserLocation(); String sql = "SELECT MAX(LocationId) as ID FROM Locations"; MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["pineapplePiranha"].ConnectionString); using (conn) { conn.Open(); MySqlCommand cmd = new MySqlCommand(sql, conn); result = (int)cmd.ExecuteScalar(); } return ++result; }
private bool SetLastKnownUserLocation(UserLocation location, int locationId) { bool completed = false; //THIS IS AWFUL!!! String sql = @"INSERT INTO Locations (LocationId, Longitude, Latitude, Altitude, Confidence, UserName) VALUES(@id, @longitude, @latitude, @altitude, @confidence, @userName); UPDATE Users SET LastKnownLocation = @id WHERE UserName = @userName;"; MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["pineapplePiranha"].ConnectionString); using (conn) { conn.Open(); MySqlCommand cmd = new MySqlCommand(sql, conn); cmd.Parameters.Add(new MySqlParameter("id", locationId)); cmd.Parameters.Add(new MySqlParameter("longitude", location.Longitude)); cmd.Parameters.Add(new MySqlParameter("latitude", location.Latitude)); cmd.Parameters.Add(new MySqlParameter("altitude", location.Altidude)); cmd.Parameters.Add(new MySqlParameter("confidence", location.Confidence)); cmd.Parameters.Add(new MySqlParameter("userName", location.UserName)); int result = cmd.ExecuteNonQuery(); if (result > 0) { completed = true; } } return completed; }