/// <summary> /// Returns all entities. /// </summary> /// <returns></returns> public IEnumerable <Route> Get() { #if DEBUG var stopwatch = Stopwatch.StartNew(); Console.Write($"Fetching routes..."); #endif var routes = new List <Route>(); using (var reader = _connection.BeginBinaryExport("COPY route TO STDOUT (FORMAT BINARY)")) { while (reader.StartRow() > 0) { var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer); routes.Add(new Route() { Id = reader.ReadStringSafe(), AgencyId = reader.ReadStringSafe(), ShortName = reader.ReadStringSafe(), LongName = reader.ReadStringSafe(), Description = reader.ReadStringSafe(), Type = (RouteTypeExtended)reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer), Url = reader.ReadStringSafe(), Color = reader.ReadIntSafe(), TextColor = reader.ReadIntSafe(), VehicleCapacity = reader.ReadIntSafe() }); } } #if DEBUG stopwatch.Stop(); Console.WriteLine($" {stopwatch.ElapsedMilliseconds} ms"); #endif return(routes); }
/// <summary> /// Returns all entities. /// </summary> /// <returns></returns> public IEnumerable <Shape> Get() { #if DEBUG var stopwatch = new System.Diagnostics.Stopwatch(); stopwatch.Start(); Console.Write($"Fetching shapes..."); #endif var shapePoints = new List <Shape>(); using (var reader = _connection.BeginBinaryExport("COPY shape TO STDOUT (FORMAT BINARY)")) { while (reader.StartRow() > 0) { var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer); shapePoints.Add(new Shape() { Id = reader.ReadStringSafe(), Latitude = reader.Read <double>(NpgsqlTypes.NpgsqlDbType.Real), Longitude = reader.Read <double>(NpgsqlTypes.NpgsqlDbType.Real), Sequence = (uint)reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer), DistanceTravelled = reader.ReadDoubleSafe() }); } } shapePoints = shapePoints.OrderBy(x => x.Id).ThenBy(x => x.Sequence).ToList(); #if DEBUG stopwatch.Stop(); Console.WriteLine($" {stopwatch.ElapsedMilliseconds} ms"); #endif return(shapePoints); }
/// <summary> /// Returns all entities. /// </summary> /// <returns></returns> public IEnumerable <Frequency> Get() { #if DEBUG var stopwatch = new System.Diagnostics.Stopwatch(); stopwatch.Start(); #endif var frequencies = new List <Frequency>(); using (var reader = _connection.BeginBinaryExport("COPY frequency TO STDOUT (FORMAT BINARY)")) { while (reader.StartRow() > 0) { var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer); frequencies.Add(new Frequency() { TripId = reader.ReadStringSafe(), StartTime = reader.ReadStringSafe(), EndTime = reader.ReadStringSafe(), HeadwaySecs = reader.ReadStringSafe(), ExactTimes = reader.ReadIntSafe() == 1 }); } } #if DEBUG stopwatch.Stop(); Console.WriteLine($"Fetch frequencies: {stopwatch.ElapsedMilliseconds} ms"); #endif return(frequencies); }
/// <summary> /// Returns all entities. /// </summary> /// <returns></returns> public IEnumerable <FareRule> Get() { #if DEBUG var stopwatch = new System.Diagnostics.Stopwatch(); stopwatch.Start(); #endif var fareRules = new List <FareRule>(); using (var reader = _connection.BeginBinaryExport("COPY fare_rule TO STDOUT (FORMAT BINARY)")) { while (reader.StartRow() > 0) { var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer); fareRules.Add(new FareRule() { FareId = reader.ReadStringSafe(), RouteId = reader.ReadStringSafe(), OriginId = reader.ReadStringSafe(), DestinationId = reader.ReadStringSafe(), ContainsId = reader.ReadStringSafe() }); } } #if DEBUG stopwatch.Stop(); Console.WriteLine($"Fetch farerules: {stopwatch.ElapsedMilliseconds} ms"); #endif return(fareRules); }
public IEnumerable <Trip> Get() { #if DEBUG var stopwatch = new System.Diagnostics.Stopwatch(); stopwatch.Start(); Console.Write($"Fetching trips..."); #endif var trips = new List <Trip>(); using (var reader = _connection.BeginBinaryExport("COPY trip TO STDOUT (FORMAT BINARY)")) { while (reader.StartRow() > 0) { var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer); trips.Add(new Trip() { Id = reader.ReadStringSafe(), RouteId = reader.ReadStringSafe(), ServiceId = reader.ReadStringSafe(), Headsign = reader.ReadStringSafe(), ShortName = reader.ReadStringSafe(), Direction = (DirectionType?)reader.ReadIntSafe(), BlockId = reader.ReadStringSafe(), ShapeId = reader.ReadStringSafe(), AccessibilityType = (WheelchairAccessibilityType?)reader.ReadIntSafe() }); } } #if DEBUG stopwatch.Stop(); Console.WriteLine($" {stopwatch.ElapsedMilliseconds} ms"); #endif return(trips); }
public void readListFromDatabase(string connectionstring) { using (var conn = new NpgsqlConnection(connectionstring)) { conn.Open(); conn.TypeMapper.UseNetTopologySuite(); CMapTileList aMapTileList = new CMapTileList(); // note that it is overkill to do bulk import for two objects, but as example... using (var reader = conn.BeginBinaryExport("COPY public.tiletest(id_tile_250, the_geom ) TO STDOUT (FORMAT BINARY)")) { while (reader.StartRow() > 0) { long aMapId = reader.Read <long>(NpgsqlDbType.Bigint); Polygon aPolygon = reader.Read <Polygon>(NpgsqlDbType.Geometry); Coordinate aCoordinate = aPolygon.Coordinates[0]; CMapTile aCmapTile = new CMapTile((long)aCoordinate.X, (long)aCoordinate.Y); aCmapTile.TileID = aMapId; aMapTileList.theMapList.Add(aCmapTile); } reader.Cancel(); } conn.Close(); } }
/// <summary> /// Returns all entities. /// </summary> /// <returns></returns> public IEnumerable <FareAttribute> Get() { #if DEBUG var stopwatch = new System.Diagnostics.Stopwatch(); stopwatch.Start(); #endif var fareAttributes = new List <FareAttribute>(); using (var reader = _connection.BeginBinaryExport("COPY fare_attribute TO STDOUT (FORMAT BINARY)")) { while (reader.StartRow() > 0) { var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer); fareAttributes.Add(new FareAttribute() { FareId = reader.ReadStringSafe(), Price = reader.ReadStringSafe(), CurrencyType = reader.ReadStringSafe(), PaymentMethod = (PaymentMethodType)reader.ReadIntSafe(), Transfers = (uint?)reader.ReadIntSafe(), TransferDuration = reader.ReadStringSafe(), AgencyId = reader.ReadStringSafe() }); } } #if DEBUG stopwatch.Stop(); Console.WriteLine($"Fetch fareattributes: {stopwatch.ElapsedMilliseconds} ms"); #endif return(fareAttributes); }
public List <NetworkNode> GetNodes(List <long> nodeIds = null) { var nodes = new List <NetworkNode>(); string commandText = nodeIds == null ? $"COPY {SchemaPrefix}nodes (id, latitude, longitude) TO STDOUT (FORMAT BINARY)" : $"COPY (select id, latitude, longitude from {SchemaPrefix}nodes where id in ({string.Join(",", nodeIds)})) TO STDOUT (FORMAT BINARY)"; using (var reader = _conn.BeginBinaryExport(commandText)) { while (reader.StartRow() > -1) { var node = default(NetworkNode); node.Id = reader.Read <long>(); node.Latitude = reader.Read <double>(); node.Longitude = reader.Read <double>(); nodes.Add(node); } } return(nodes); }
public int Export() { var sum = 0; unchecked { using (var exporter = _conn.BeginBinaryExport("COPY data TO STDOUT (FORMAT BINARY)")) while (exporter.StartRow() != -1) { for (var col = 0; col < 10; col++) { sum += exporter.Read <int>(NpgsqlDbType.Integer); } } } return(sum); }
public void CloseDuringCopy() { // TODO: Check no broken connections were returned to the pool using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn); conn.BeginBinaryImport("COPY data (field_text, field_int4) FROM STDIN BINARY"); } using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn); conn.BeginBinaryExport("COPY data (field_text, field_int2) TO STDIN BINARY"); } using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn); conn.BeginRawBinaryCopy("COPY data (field_text, field_int4) FROM STDIN BINARY"); } using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn); conn.BeginRawBinaryCopy("COPY data (field_text, field_int4) TO STDIN BINARY"); } using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn); conn.BeginTextImport("COPY data (field_text, field_int4) FROM STDIN"); } using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn); conn.BeginTextExport("COPY data (field_text, field_int4) TO STDIN"); } }
/// <summary> /// Returns all stop times. /// </summary> /// <returns></returns> public IEnumerable <StopTime> Get() { #if DEBUG var stopwatch = new System.Diagnostics.Stopwatch(); stopwatch.Start(); #endif var stopTimes = new List <StopTime>(); using (var reader = _connection.BeginBinaryExport("COPY stop_time TO STDOUT (FORMAT BINARY)")) { while (reader.StartRow() > 0) { var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer); stopTimes.Add(new StopTime() { TripId = reader.ReadStringSafe(), ArrivalTime = TimeOfDay.FromTotalSeconds(reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer)), DepartureTime = TimeOfDay.FromTotalSeconds(reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer)), StopId = reader.ReadStringSafe(), StopSequence = (uint)reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer), StopHeadsign = reader.ReadStringSafe(), PickupType = (PickupType?)reader.ReadIntSafe(), DropOffType = (DropOffType?)reader.ReadIntSafe(), ShapeDistTravelled = reader.ReadStringSafe(), PassengerBoarding = (int?)reader.ReadIntSafe(), PassengerAlighting = (int?)reader.ReadIntSafe(), ThroughPassengers = (int?)reader.ReadIntSafe(), TotalPassengers = (int?)reader.ReadIntSafe(), ContinuousPickup = (ContinuousPickup?)reader.ReadIntSafe(), ContinuousDropOff = (ContinuousDropOff?)reader.ReadIntSafe(), }); } } stopTimes = stopTimes.OrderBy(x => x.TripId).ThenBy(x => x.StopSequence).ToList(); #if DEBUG stopwatch.Stop(); Console.WriteLine($"Fetch stoptimes: {stopwatch.ElapsedMilliseconds} ms"); #endif return(stopTimes); }
/// <summary> /// Returns all entities. /// </summary> /// <returns></returns> public IEnumerable <Stop> Get() { #if DEBUG var stopwatch = new System.Diagnostics.Stopwatch(); stopwatch.Start(); Console.Write($"Fetching stops..."); #endif var stops = new List <Stop>(); using (var reader = _connection.BeginBinaryExport("COPY stop TO STDOUT (FORMAT BINARY)")) { while (reader.StartRow() > 0) { var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer); stops.Add(new Stop() { Id = reader.ReadStringSafe(), Code = reader.ReadStringSafe(), Name = reader.ReadStringSafe(), Description = reader.ReadStringSafe(), Latitude = reader.Read <double>(NpgsqlTypes.NpgsqlDbType.Real), Longitude = reader.Read <double>(NpgsqlTypes.NpgsqlDbType.Real), Zone = reader.ReadStringSafe(), Url = reader.ReadStringSafe(), LocationType = (LocationType?)reader.ReadIntSafe(), ParentStation = reader.ReadStringSafe(), Timezone = reader.ReadStringSafe(), WheelchairBoarding = reader.ReadStringSafe(), LevelId = reader.ReadStringSafe(), PlatformCode = reader.ReadStringSafe() }); } } #if DEBUG stopwatch.Stop(); Console.WriteLine($" {stopwatch.ElapsedMilliseconds} ms"); #endif return(stops); }