Npgsql connection string, dynamic database querying

Question

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?

Shay Rojansky · Accepted Answer · 2016-10-26 12:19:25Z

3

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.

answered Oct 26, 2016 at 12:19

Shay Rojansky

16.9k3 gold badges44 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Gould Over a year ago

Thanks for stopping by Shay! There are a few different years (all justified) but I shouldn't post them on a public domain. I'll take a look into connection pooling, seems like it may be only option. Cheers!

Collectives™ on Stack Overflow

Npgsql connection string, dynamic database querying

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related