public void NewPathTripAreaCommanHelper(RoutePath routePath, NpgsqlCommand command)
{
command.Parameters.AddWithValue("route_path_id", routePath.RoutePathId);
command.Parameters.AddWithValue("route_id", routePath.RouteId);
command.Parameters.AddWithValue("reason_id", routePath.ReasonId);
command.Parameters.AddWithValue("note", routePath.Note);
command.Parameters.AddWithValue("created_by_user_id", routePath.CreatedByUserId);
command.Parameters.AddWithValue("updated_by_user_id", routePath.UpdatedByUserId);
command.Parameters.AddWithValue("geo_path", routePath.Geomtry);
command.Parameters.AddWithValue("created", routePath.Created);
command.Parameters.AddWithValue("updated", routePath.Updated);
}
public List <RoutePath> RoutePathByCreatedByUser(int userId, out String errorMessage)
{
errorMessage = null;
if (userId <= 0)
{
errorMessage = "Invaild user Id (0)";
return(null);
}
List <RoutePath> routePaths = new List <RoutePath>();
using (var connection = new NpgsqlConnection(ConfigParser.ConnString))
{
try
{
connection.Open();
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = connection;
command.CommandText = "SELECT route_path_id, route_id, reason_id, note, created_by_user_id, updated_by_user_id, st_asgeojson(geo_path, 15 ,0), created,updated FROM route_path WHERE created_by_user_id = (@userId)";
command.Parameters.AddWithValue("userId", userId);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
RoutePath routePath = new RoutePath();
routePaths.Add(routePath.RoutePathMaker(reader));
}
return(routePaths);
}
catch (Exception error)
{
Console.WriteLine(error);
errorMessage = error.ToString();
throw;
}
}
}
public int AddNewRoutePath(RoutePath routePath, out String errorMessage)
{
errorMessage = null;
using (var connection = new NpgsqlConnection(ConfigParser.ConnString))
{
try
{
connection.Open();
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = connection;
command.CommandText = "Insert Into route_path (route_id,reason_id,note,created_by_user_id,updated_by_user_id,geo_path,created,updated ) values (@route_id,@reason_id,@note,@created_by_user_id,@updated_by_user_id,st_geomfromgeojson(@geo_path),@created,@updated ) Returning route_path_id";
NewPathTripAreaCommanHelper(routePath, command);
return(Convert.ToInt32(command.ExecuteScalar()));
}catch (Exception error)
{
Console.WriteLine(error);
errorMessage = error.ToString();
throw;
}
}
}