public string SaveRoute(RouteInfo ri, string editor)
{
try
{
RestoreSqlConnectionManager();
if (!_inTransaction)
SqlTransactionManager.Begin();
// 1. Save route info
if (ri.RouteId == null || ri.RouteId == "")
{
// Insert a new route
ri.RouteId = (string)SqlHelper.ExecuteScalar(_connString,
CommandType.Text, _sqlInsertRoute,
new SqlParameter[] {
new SqlParameter("@Name", ri.Name),
new SqlParameter("@Type", ri.Type),
new SqlParameter("@Description", ri.Description),
new SqlParameter("@Editor", editor)
});
}
else
{
// Update an existing route
// Delete route steps
SqlHelper.ExecuteNonQuery(_connString, CommandType.Text, _sqlDeleteRouteSteps,
new SqlParameter[] {
new SqlParameter("@RouteId", ri.RouteId)
});
// Update route info
SqlHelper.ExecuteNonQuery(_connString, CommandType.Text, _sqlUpdateRoute,
new SqlParameter[] {
new SqlParameter("@Name", ri.Name),
new SqlParameter("@Type", ri.Type),
new SqlParameter("@Description", ri.Description),
new SqlParameter("@Editor", editor),
new SqlParameter("@RouteId", ri.RouteId)
});
}
// 2. Save route steps
foreach (var si in ri.Steps)
{
SqlHelper.ExecuteNonQuery(_connString, CommandType.Text, _sqlInsertRouteSteps,
new SqlParameter[] {
new SqlParameter("@RouteId", ri.RouteId),
new SqlParameter("@Condition", si.Condition.ToUpper() == "PASS" ? 1 : 0),
new SqlParameter("@FromStation", ConvertStationNameToId(si.FromStation)),
new SqlParameter("@ToStation", ConvertStationNameToId(si.ToStation)),
new SqlParameter("@Editor", editor)
});
}
if (!_inTransaction)
SqlTransactionManager.Commit();
return ri.RouteId;
}
catch (Exception ex)
{
if (!_inTransaction)
SqlTransactionManager.Rollback();
throw ex;
}
finally
{
if (!_inTransaction)
{
SqlTransactionManager.Dispose();
SqlTransactionManager.End();
}
BackupSqlConnectionManager();
}
}
public RouteInfo GetRoute(string routeId)
{
try
{
RestoreSqlConnectionManager();
RouteInfo ri = new RouteInfo();
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@RouteId", routeId)
};
// 1. Get route steps
DataTable dt = SqlHelper.ExecuteDataFill(_connString,
CommandType.Text, _sqlGetRouteSteps, param);
ri.Steps = new List<StepInfo>(dt.Rows.Count);
foreach (DataRow r in dt.Rows)
{
StepInfo si = new StepInfo();
si.Condition = (int)r["Condition"] == 1 ? "PASS" : "FAIL";
si.FromStation = r["FromStation"] as string;
si.ToStation = r["ToStation"] as string;
ri.Steps.Add(si);
}
// 2. Get route info
dt = SqlHelper.ExecuteDataFill(_connString,
CommandType.Text, _sqlGetRoute, param);
DataRow dr = dt.Rows[0];
ri.RouteId = routeId;
ri.Name = dr["Name"] as string;
ri.Type = dr["Type"] as string;
ri.Description = dr["Description"] as string;
return ri;
}
finally
{
BackupSqlConnectionManager();
}
}