/// <summary>
/// Get all routes.
/// </summary>
/// <returns>The requested list filled with information.</returns>
public List <Route> GetAll()
{
string sql = string.Empty;
Route route = null;
RouteElement elem = null;
List <Route> routes = new List <Route>();
Logger.LogDebug(this, "[CLASS].GetAll()");
try
{
Connect();
sql = @"SELECT
" + RouteManager.SQL_FIELDS_SELECT + @"
FROM
" + RouteManager.SQL_TABLE + @"
ORDER BY
name";
using (SQLiteDataReader reader = ExecuteReader(sql))
{
while (reader.Read())
{
route = RouteManager.ReadEntityRecord(reader);
if (route != null)
{
routes.Add(route);
}
}
}
// Get route element status information
foreach (Route rout in routes)
{
sql = @"SELECT
" + RouteManager.SQL_BLOCK_FIELDS_SELECT + @"
FROM
" + RouteManager.SQL_BLOCK_TABLE + @"
WHERE
routeid = @routeid";
SetParameter("routeid", rout.ID);
using (SQLiteDataReader reader = ExecuteReader(sql))
{
while (reader.Read())
{
elem = RouteManager.ReadSubentityRecord(reader);
if (elem != null)
{
rout.Elements.Add(elem);
}
}
}
}
// Get from/to elements
foreach (Route rout in routes)
{
if (rout.FromBlockID > 0)
{
rout.FromBlock = OTCContext.Layout.ElementManager.GetByID(rout.FromBlockID);
}
if (rout.ToBlockID > 0)
{
rout.ToBlock = OTCContext.Layout.ElementManager.GetByID(rout.ToBlockID);
}
}
return(routes);
}
catch (Exception ex)
{
Logger.LogError(this, ex);
throw;
}
finally
{
Disconnect();
}
}
/// <summary>
/// Returns the specified route from database.
/// </summary>
/// <param name="id">The route unique identifier (DB).</param>
/// <returns>The requested <see cref="Route"/> instance filled with all data.</returns>
public Route GetByID(Int64 id)
{
string sql = string.Empty;
Route item = null;
RouteElement subitem = null;
Logger.LogDebug(this, "[CLASS].GetByID({0})", id);
try
{
Connect();
// Get the route
sql = @"SELECT
" + RouteManager.SQL_FIELDS_SELECT + @"
FROM
" + RouteManager.SQL_TABLE + @"
WHERE
id = @id";
SetParameter("id", id);
using (SQLiteDataReader reader = ExecuteReader(sql))
{
if (reader.Read())
{
item = RouteManager.ReadEntityRecord(reader);
}
}
if (item == null)
{
return(null);
}
// Get the element status information
sql = @"SELECT
" + RouteManager.SQL_BLOCK_FIELDS_SELECT + @"
FROM
" + RouteManager.SQL_BLOCK_TABLE + @"
WHERE
routeid = @routeid";
SetParameter("routeid", id);
using (SQLiteDataReader reader = ExecuteReader(sql))
{
while (reader.Read())
{
subitem = RouteManager.ReadSubentityRecord(reader);
if (subitem != null)
{
item.Elements.Add(subitem);
}
}
}
// Get from/to elements
if (item.FromBlockID > 0)
{
item.FromBlock = OTCContext.Layout.ElementManager.GetByID(item.FromBlockID);
}
if (item.ToBlockID > 0)
{
item.ToBlock = OTCContext.Layout.ElementManager.GetByID(item.ToBlockID);
}
return(item);
}
catch (Exception ex)
{
Logger.LogError(this, ex);
throw;
}
finally
{
Disconnect();
}
}