Search code examples
c#postgresqlnpgsql

Npgsql connection string, dynamic database querying


I have the following connection string:

NpgsqlConnection conn = new NpgsqlConnection($"Server={_server};Port={_port};User ID={_userid};Password={_password};Database={database}");

Where database is selected from a collection of countrycodes (GB, US, DE etc) and the corresponding database is the queried.

The countrycode is selected from a series of methods that determine where their latitude and longitude (UTM) lie on the map (ie 51.503471, -0.119586 will output: GB because it's within the bounding box of Great Britain). The lat/lon values are passed in by the user, so they will likely be different each query, and possibly in different countries (and thus a different database).

The rest of the connection data stays the same, the server/userid etc will not be changing, but the database being queried may change each time the user submits a lat/lon.

Is there a way to hold a connection open without a specified database in Postgres/Npgsql, or will I have to reopen (and close at the end of the query) the connection each time a query is submitted?


Solution

  • No, a PostgreSQL connection is always a connection to a specific database - you can't switch a database retaining the same connection.

    Is there a particular reason to separate data into different databases? Separate schemas within the same database may serve the same purpose without forcing you to reconnect etc. If you must use different databases, connection pooling may mitigate the performance impact of constantly closing/opening connections.