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;
}
