public List<Camera> GetCameras() { var cameras = new List<Camera>(); try { _connection.Open(); var sql = string.Format("SELECT * from Roadwatch_Cameras order by ID"); using (var cmd = new MySqlCommand(sql, _connection)) { using (var r = cmd.ExecuteReader()) { while (r.Read()) { var camera = new Camera { Area = r.GetString("Area"), Id = r.GetInt32("Id"), Junction = r.GetString("Junction"), Url = r.GetString("Url") }; cameras.Add(camera); } } } } catch (Exception ex) { var stackFrame = new StackFrame(); var methodBase = stackFrame.GetMethod(); Database.InsertErrorToDb(methodBase.Name, ex.Message, ex.ToString()); } finally { _connection.Close(); } return cameras; }
public List<Camera> GetCamerasNew(string id) { var cameras = new List<Camera>(); var sql = ""; try { _connection.Open(); sql = string.Format("SELECT * from Roadwatch_Cameras order by ID"); using (var cmd = new MySqlCommand(sql, _connection)) { using (var r = cmd.ExecuteReader()) { while (r.Read()) { var camera = new Camera { Area = r.GetString("Area"), Id = r.GetInt32("Id"), Junction = r.GetString("Junction"), Url = r.GetString("Url"), Fav = false }; cameras.Add(camera); } } } sql = string.Format("SELECT * from Roadwatch_CamFav Where Device_Id like '" + id + "' order by ID"); var favCams = new List<Camera>(); using (var cmd = new MySqlCommand(sql, _connection)) { using (var r = cmd.ExecuteReader()) { while (r.Read()) { var camera = new Camera { Id = r.GetInt32("Camera_Id") }; var q = (from c in cameras where c.Id == camera.Id select c).FirstOrDefault(); cameras.Remove(q); favCams.Add(camera); } } } foreach (var c in favCams) { sql = string.Format("SELECT * from Roadwatch_Cameras Where Id like '" + c.Id + "' order by ID"); using (var cmd = new MySqlCommand(sql, _connection)) { using (var r = cmd.ExecuteReader()) { while (r.Read()) { var camera = new Camera { Area = "Favourites", Id = r.GetInt32("Id"), Junction = r.GetString("Junction"), Url = r.GetString("Url"), Fav = true }; cameras.Insert(0, camera); } } } } } catch (Exception ex) { var stackFrame = new StackFrame(); var methodBase = stackFrame.GetMethod(); Database.InsertErrorToDb(methodBase.Name, ex.Message, ex.ToString()); } finally { _connection.Close(); } return cameras; }