/// <summary>
/// Cleans the and reloads the table containing geo data
/// </summary>
public static void CleanAndReloadDb()
{
List <GeoPlace> geoList = GetGeoPlacesFromEmbeddedFile();
using (var conn = new NpgsqlConnection(PGConnection))
{
conn.Open();
// Re-create table
string createTableQuery = "DROP TABLE IF EXISTS cities;CREATE TABLE cities(id varchar(250) NOT NULL,shortname varchar(250) NOT NULL,name varchar(250) NOT NULL,lat numeric(9, 6) NOT NULL,lng numeric(9, 6) NOT NULL,population bigint NOT NULL)WITH(OIDS = FALSE);";
using (NpgsqlCommand command = new NpgsqlCommand(createTableQuery, conn))
{
command.ExecuteNonQuery();
}
// populate table
int batchSize = 50;
foreach (IEnumerable <GeoPlace> geoBatch in geoList.Batch(batchSize))
{
// Prepare batch command
// 1 command length can be about 400-450
StringBuilder sb = new StringBuilder(batchSize * 500);
foreach (GeoPlace geo in geoBatch)
{
sb.AppendFormat("insert into cities(id, shortname, name, lat, lng, population) values('{0}','{1}','{2}',{3},{4},{5});", SqlUtil.EscapeString(geo.ID), SqlUtil.EscapeString(geo.ShortName), SqlUtil.EscapeString(geo.Name), geo.Location.Lat, geo.Location.Lon, geo.Population);
}
string cmdText = sb.ToString();
using (NpgsqlCommand command = new NpgsqlCommand(cmdText, conn))
{
command.ExecuteNonQuery();
}
}
}
}