public List<RouteModel> GetRoutes() { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); List<RouteModel> result = new List<RouteModel>(); List<PointsOfInterestModel> pois = GetPointsOfInterest(); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand( "SELECT RouteID, RouteName, RouteDesc, From_PointOfInterestID, To_PointOfInterestID FROM dbo.Route;", connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { RouteModel model = new RouteModel(); model.RouteID = Convert.ToInt32(reader["RouteID"]); model.RouteName = reader["RouteName"].ToString(); model.RouteDescription = reader["RouteDesc"].ToString(); model.FromID = (int)reader["From_PointOfInterestID"]; model.From = pois.First(p => p.PointsOfInterestID == (int)reader["From_PointOfInterestID"]).PointsOfInterestName; model.ToID = (int)reader["To_PointOfInterestID"]; model.To = pois.First(p => p.PointsOfInterestID == (int)reader["From_PointOfInterestID"]).PointsOfInterestName; result.Add(model); } } reader.Close(); } return result; }
public JsonResult SaveRoute(string data) { RouteViewModel input = Newtonsoft.Json.JsonConvert.DeserializeObject<RouteViewModel>(data); List<string> errors = new List<string>(); if (string.IsNullOrWhiteSpace(input.RouteName)) { errors.Add("Route name is required"); } if (input.FromID <= 0) { errors.Add("Starting location is required"); } if (input.ToID <= 0) { errors.Add("Destination location is required"); } if (input.FromID == input.ToID) { errors.Add("Starting and destination location should be different"); } if (input.CheckPoints != null) { foreach (var item in input.CheckPoints) { if (string.IsNullOrWhiteSpace(item.CheckPointName)) { errors.Add("All check points should have a name"); } } } if (errors.Count > 0) { return Json(new { success = false, errors = errors }); } var model = new RouteModel(); model.RouteName = input.RouteName; model.RouteDescription = input.RouteDesc; model.FromID = input.FromID; model.ToID = input.ToID; List<CheckPointsModel> checkPoints = new List<CheckPointsModel>(); foreach (var item in input.CheckPoints) { checkPoints.Add(new CheckPointsModel() { CheckPointName = item.CheckPointName, CheckPointDesc = item.CheckPointDesc }); } _routeService.SaveRoute(model, checkPoints); return Json(new { success = true }); }
public bool SaveRoute(RouteModel model, List<CheckPointsModel> checkPoints) { try { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); string query = "INSERT INTO dbo.Route (RouteName, RouteDesc, From_PointOfInterestID, To_PointOfInterestID) " + "VALUES (@RouteName, @RouteDesc, @From_PointOfInterestID, @To_PointOfInterestID) "; using (SqlConnection cn = new SqlConnection(connectionString)) using (SqlCommand cmd = new SqlCommand(query, cn)) { cmd.Parameters.Add("@RouteName", SqlDbType.VarChar, 50).Value = model.RouteName; cmd.Parameters.Add("@RouteDesc", SqlDbType.VarChar, 1000).Value = model.RouteDescription; cmd.Parameters.Add("@From_PointOfInterestID", SqlDbType.Int).Value = model.FromID; cmd.Parameters.Add("@To_PointOfInterestID", SqlDbType.Int).Value = model.ToID; cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); } var routes = GetRoutes(); if (routes != null) { var routeID = routes.OrderByDescending(r => r.RouteID).First().RouteID; foreach (var item in checkPoints) { item.RouteID = routeID; SaveCheckPoint(item); } } } catch (Exception) { return false; } return true; }