private static void AddUpdateDeleteInventories(ZipArchiveEntry inventoriesEntry) { Console.WriteLine("inventories.csv is {0} bytes", inventoriesEntry.Length); IEnumerable<Inventory> inventories = null; using (var entryStream = inventoriesEntry.Open()) { Console.WriteLine("Starting inventories at {0:hh:mm:ss.fff}", DateTime.Now); var stopwatch = new Stopwatch(); stopwatch.Start(); var reader = new StreamReader(entryStream); var csv = new CsvReader(reader); csv.Configuration.RegisterClassMap(new InventoryMap()); StringBuilder invForImport = new StringBuilder(); inventories = csv.GetRecords<Inventory>().Where(i => !i.IsDead); var inventoryCollection = new InventoryCollection(); inventoryCollection.SetItems(inventories); var importer = new NpgsqlBulkImporter(ConfigurationManager.ConnectionStrings["spurious"].ConnectionString, stopwatch); importer.BulkImport("inventories", inventoryCollection); stopwatch.Stop(); Console.WriteLine("Finished inventories at {0:hh:mm:ss.fff}, taking {1}", DateTime.Now, stopwatch.Elapsed); } }
static void Main(string[] args) { // Massive mem use here somewhere - about 1 GB var userProfile = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile); var gmlDoc = new XmlDocument(); gmlDoc.Load(Path.Combine(userProfile, ConfigurationManager.AppSettings["GeographyFile"])); var ns = new XmlNamespaceManager(gmlDoc.NameTable); ns.AddNamespace("gml", "http://www.opengis.net/gml"); ns.AddNamespace("fme", "http://www.safe.com/gml/fme"); var nodes = gmlDoc.SelectNodes("/gml:FeatureCollection/gml:featureMember", ns); var boundaries = new List<BoundaryItem>(); foreach (XmlNode node in nodes) { boundaries.Add(new BoundaryItem(ns, node)); } var collection = new BoundaryItemCollection(); collection.SetItems(boundaries); var importer = new NpgsqlBulkImporter(ConfigurationManager.ConnectionStrings["spurious"].ConnectionString); importer.BulkImport("subdivisions", collection); Console.WriteLine("Bulk update complete"); using (var wrapper = new NpgsqlConnectionWrapper(ConfigurationManager.ConnectionStrings["spurious"].ConnectionString)) { wrapper.Connection.Open(); var rowsGeoUpdated = wrapper.ExecuteNonQuery("update subdivisions s set (boundry) = ((select ST_FlipCoordinates(ST_GeomFromGML(boundary_gml, 4269)) from subdivisions ss where s.id = ss.id))"); Console.WriteLine($"Updated {rowsGeoUpdated} rows geo data from GML data"); } Console.WriteLine("Node count {0}", nodes.Count); }
private static void AddUpdateDeleteProducts(ZipArchiveEntry entry) { IEnumerable<Product> products = null; using (var entryStream = entry.Open()) { Console.WriteLine("products.csv is {0} bytes", entry.Length); Console.WriteLine("Starting products at {0:hh:mm:ss.fff}", DateTime.Now); var stopwatch = new Stopwatch(); stopwatch.Start(); var reader = new StreamReader(entryStream); var csv = new CsvReader(reader); csv.Configuration.RegisterClassMap(new ProductMap()); products = csv.GetRecords<Product>().Where(p => !p.IsDead); var productCollection = new ProductCollection(); productCollection.SetItems(products); var importer = new NpgsqlBulkImporter(ConfigurationManager.ConnectionStrings["spurious"].ConnectionString, stopwatch); importer.BulkImport("products", productCollection); stopwatch.Stop(); Console.WriteLine("Finished products at {0:hh:mm:ss.fff}, taking {1}", DateTime.Now, stopwatch.Elapsed); } }
private static void AddUpdateDeleteStores(ZipArchiveEntry entry) { IEnumerable<Store> stores = null; using (var entryStream = entry.Open()) { Console.WriteLine("stores.csv is {0} bytes", entry.Length); Console.WriteLine("Starting stores at {0:hh:mm:ss.fff}", DateTime.Now); var storeTimer = new Stopwatch(); storeTimer.Start(); var reader = new StreamReader(entryStream); var csv = new CsvReader(reader); csv.Configuration.RegisterClassMap(new StoreMap()); stores = csv.GetRecords<Store>().Where(s => !s.IsDead); var storesCollection = new StoreCollection(); storesCollection.SetItems(stores); var importer = new NpgsqlBulkImporter(ConfigurationManager.ConnectionStrings["spurious"].ConnectionString, storeTimer); importer.BulkImport("stores", storesCollection); using (var wrapper = new NpgsqlConnectionWrapper(ConfigurationManager.ConnectionStrings["spurious"].ConnectionString)) { wrapper.Connection.Open(); var rowsGeoUpdated = wrapper.ExecuteNonQuery("update stores s set (location) = ((select ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) from stores ss where s.id = ss.id))"); Console.WriteLine($"Updated {rowsGeoUpdated} rows geo data from lat/long data"); } storeTimer.Stop(); Console.WriteLine("Finished stores at {0:hh:mm:ss.fff}, taking {1}", DateTime.Now, storeTimer.Elapsed); } }