public int InsertNewLine(DsBusWeb.LinesRow dr) { ExecuteNonQuery(string.Format("INSERT INTO Lines(LineName, Culture) VALUES({0}, {1})", OleDbStrHelper.getParamStr(dr.LineName), OleDbStrHelper.getParamStr(dr.Culture))); return(Convert.ToInt32(ExecuteScalar("SELECT MAX(LineID) FROM Lines"))); }
public int GetStopIDByOwnerAndStopID(int stopID, string owner) { _db.ExecuteCommand.CommandText = string.Format("SELECT StopID FROM Stops WHERE StopID = {0} AND Owner = {1}", OleDbStrHelper.getParamStr(stopID), OleDbStrHelper.getParamStr(owner)); return(Convert.ToInt32(_db.ExecuteScalar())); }
/// <summary> /// Move all stops under these duplicated lines, then delete these duplicated lines. /// </summary> /// <param name="dupLines">List of duplicated lines</param> /// <param name="primaryLine">The primary line which will be attached within these moving stops.</param> private void DoMerging(List <DsBusWeb.vwDupLineDetailRow> dupLines, DsBusWeb.vwDupLineDetailRow primaryLine) { if (dupLines.Count > 0) { #region do merging try { _db.BeginTrans(); foreach (var line in dupLines) { _db.ExecuteCommand.CommandText = string.Format("UPDATE Line2Stop SET LineID = {0} WHERE LineID = {1}", OleDbStrHelper.getParamStr(primaryLine.LineID), OleDbStrHelper.getParamStr(line.LineID)); _db.Execute(); // Move all stop under the line to the primary line. _db.ExecuteCommand.CommandText = string.Format("DELETE FROM Lines WHERE LineID = {0}", OleDbStrHelper.getParamStr(line.LineID)); _db.Execute(); // Delete the duplicate line. line.Delete(); // Marked as deleted row means it had been merged. } _db.CommitTrans(); } catch { _db.RollBack(); throw; } finally { _db.Close(); } #endregion } }
public DsBusWeb.LinesDataTable GetLinesList(DateTime stpBgnDT, DateTime stpEndDT) { DsBusWeb.LinesDataTable dt = new DsBusWeb.LinesDataTable(); FillDt(string.Format("SELECT l.*, (SELECT COUNT(1) FROM Line2Stop l2s WHERE l2s.LineID = l.LineID) AS StopCount FROM Lines l WHERE l.LineID IN (SELECT l2s.LineID FROM Line2Stop l2s INNER JOIN Stops s ON l2s.StopID = s.StopID WHERE s.DateCreated BETWEEN {0} AND {1}) ORDER BY l.LineName", OleDbStrHelper.getParamStr(stpBgnDT), OleDbStrHelper.getParamStr(stpEndDT)), dt); return(dt); }
public DsBusWeb.LinesDataTable GetLinesList(double Longitude, double Latitude, double radius, string owner) { DsBusWeb.LinesDataTable dt = new DsBusWeb.LinesDataTable(); FillDt(string.Format("SELECT DISTINCT l.* FROM (Lines l INNER JOIN Line2Stop l2s ON l.LineID = l2s.LineID) INNER JOIN (SELECT StopID FROM Stops WHERE (Owner = {3}) OR (ABS(Longitude - {0}) < {2} AND ABS(Latitude - {1}) < {2})) tmp ON l2s.StopID = tmp.StopID ORDER BY LineName", OleDbStrHelper.getParamStr(new decimal(Longitude)), OleDbStrHelper.getParamStr(new decimal(Latitude)), OleDbStrHelper.getParamStr(new decimal(radius)), OleDbStrHelper.getParamStr(owner)), dt); return(dt); }
public void LogAUsageCase(DsBusWeb.UsageStatisticRow dr) { try { ExecuteNonQuery(string.Format("INSERT INTO UsageStatistic(UserCode, Longitude, Latitude, Radius, Device) VALUES({0}, {1}, {2}, {3}, {4})", OleDbStrHelper.getParamStr(dr.UserCode), OleDbStrHelper.getParamStr(new decimal(dr.Longitude)), OleDbStrHelper.getParamStr(new decimal(dr.Latitude)), OleDbStrHelper.getParamStr(new decimal(dr.Radius)), OleDbStrHelper.getParamStr(dr.Device) )); } catch (Exception ex) { Trace.WriteLine(ex); } }
public DsBusWeb.StopsDataTable GetStopsByLineID(int lineID, string owner) { DsBusWeb.StopsDataTable dt = new DsBusWeb.StopsDataTable(); FillDt(string.Format("SELECT s.*, sr.RatingGood, sr.RatingBad FROM (Stops s INNER JOIN Line2Stop l2s ON s.StopID = l2s.StopID) INNER JOIN StopRating sr ON s.StopID = sr.StopID WHERE l2s.LineID={0} ORDER BY StopName", OleDbStrHelper.getParamStr(lineID)), dt); if (!string.IsNullOrEmpty(owner)) { foreach (var dr in dt) { if (dr.Owner == owner) { dr.StopName = string.Format("{0}(*)", dr.StopName); } } } return(dt); }
public int InsertNewStop(DsBusWeb.StopsRow dr) { _db.Open(); _db.BeginTrans(); int stopID = 0; try { _db.ExecuteCommand.CommandText = string.Format("INSERT INTO Stops(StopName, Longitude, Latitude, CreatorLongitude, CreatorLatitude, Owner, Culture) VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6})", OleDbStrHelper.getParamStr(dr.StopName), OleDbStrHelper.getParamStr(new decimal(dr.Longitude)), OleDbStrHelper.getParamStr(new decimal(dr.Latitude)), OleDbStrHelper.getParamStr(new decimal(dr.CreatorLongitude)), OleDbStrHelper.getParamStr(new decimal(dr.CreatorLatitude)), OleDbStrHelper.getParamStr(dr.Owner), OleDbStrHelper.getParamStr(dr.Culture)); _db.Execute(); _db.ExecuteCommand.CommandText = "SELECT MAX(StopID) FROM Stops"; stopID = Convert.ToInt32(_db.ExecuteScalar()); _db.ExecuteCommand.CommandText = string.Format("INSERT INTO StopRating(StopID) VALUES({0})", OleDbStrHelper.getParamStr(stopID)); _db.Execute(); _db.CommitTrans(); } catch { _db.RollBack(); throw; } finally { _db.Close(); } return(stopID); }
/// <summary> /// Delete duplicate stop info /// </summary> /// <param name="dupStops">List of duplicated stops</param> private void DoMerging(List <DsBusWeb.vwDupStopDetailRow> dupStops) { if (dupStops.Count > 0) { #region do merging try { _db.BeginTrans(); foreach (var stop in dupStops) { _db.ExecuteCommand.CommandText = string.Format("DELETE FROM Line2Stop WHERE StopID = {0}", OleDbStrHelper.getParamStr(stop.StopID)); _db.Execute(); // Move all stop under the line to the primary line. _db.ExecuteCommand.CommandText = string.Format("DELETE FROM StopRating WHERE StopID = {0}", OleDbStrHelper.getParamStr(stop.StopID)); _db.Execute(); // Delete the duplicate line. _db.ExecuteCommand.CommandText = string.Format("DELETE FROM Stops WHERE StopID = {0}", OleDbStrHelper.getParamStr(stop.StopID)); _db.Execute(); // Delete the duplicate line. } _db.CommitTrans(); } catch { _db.RollBack(); throw; } finally { _db.Close(); } #endregion } }
public void UpdateLine(DsBusWeb.LinesRow dr) { ExecuteNonQuery(string.Format("UPDATE Lines SET LineName={0} WHERE LineID={1}", OleDbStrHelper.getParamStr(dr.LineName), OleDbStrHelper.getParamStr(dr.LineID))); }
public void DeleteLineByID(int lineID) { try { _db.BeginTrans(); _db.ExecuteCommand.CommandText = string.Format("DELETE FROM StopRating WHERE StopID IN (SELECT StopID FROM Line2Stop WHERE LineID = {0})", OleDbStrHelper.getParamStr(lineID)); _db.Execute(); _db.ExecuteCommand.CommandText = string.Format("DELETE FROM Line2Stop WHERE LineID = {0}", OleDbStrHelper.getParamStr(lineID)); _db.Execute(); _db.ExecuteCommand.CommandText = "DELETE FROM Stops WHERE StopID NOT IN (SELECT StopID FROM Line2Stop)"; _db.Execute(); _db.ExecuteCommand.CommandText = string.Format("DELETE FROM Lines WHERE LineID = {0}", OleDbStrHelper.getParamStr(lineID)); _db.Execute(); _db.CommitTrans(); } catch { _db.RollBack(); throw; } finally { _db.Close(); } }
public void RateStopBad(int stopID) { ExecuteNonQuery(string.Format("UPDATE StopRating SET RatingBad=RatingBad + 1 WHERE StopID={0}", OleDbStrHelper.getParamStr(stopID))); }
public void InsertNewLine2StopRelation(DsBusWeb.Line2StopRow dr) { ExecuteNonQuery(string.Format("INSERT INTO Line2Stop(LineID, StopID) VALUES({0}, {1})", OleDbStrHelper.getParamStr(dr.LineID), OleDbStrHelper.getParamStr(dr.StopID))); }
public void UpdateStop(DsBusWeb.StopsRow dr) { ExecuteNonQuery(string.Format("UPDATE Stops SET StopName={0} WHERE StopID={1}", OleDbStrHelper.getParamStr(dr.StopName), OleDbStrHelper.getParamStr(dr.StopID))); }