sharing a database connection between applications in c#

Question

I was was wondering if anyone knows if it is possible to have multiple applications share the same database connection

e.g if I have an application which opens a connection to a database then I start another application which needs to connect to the same database - so instead of having 2 connections open, I use the connection from the first app which is already open thereby only having one connection open

the reason I am asking this is because I am developing a system which is built from seperate exe's instead of one big one - but they all access the same database

any help would be appreciated

thanks

Answer 1

You'll probably want to create a separate assembly containing the logic to connect to the database (via configurable connection string) and reference the assembly for each project that needs to use it. Note, they won't be 'sharing' the same physical database connection, rather you'll be sharing the code and logic between the two applications, it will still be two separate database connections which you can't get around but it's also not a bad thing, it's just the way it has to be :) The only way you could share the same connection is if the two apps were actually in the same app and I gather that's not what your talking about since you mentioned it has two executable's, so two separate programs.

Answer 2

Check this

http://stackoverflow.com/questions/1660502/share-a-connection-string-between-multiple-web-projects

Answer 3

Connection pooling should work for you. If multiple exes need a database connection at the same time, you'll want more than one connection, but once those connections are released back to the pool, another exe can pick them up and re-use them.

I think this is often included in the ODBC/database drivers without any extra effort on your part, but it has never become an issue for me so I'm not sure.

Answer 4

IMHO you can sharing the same connection string, but sharing the same physical DB connection between 2 applications unaware of eachother would not be such a good idea. How would you handle locking, concurrency, transaction boundaries, etc?

If the reason for this is to reduce DB licensing costs or connection limits, it might be worthwhile to look at a shorter connection lifespan (i.e. apps connect and disconnect from the DB as needed, rather than keeping the connection open for the lifespan of the app)

If you really want to pursue sharing of a connection, you might look at splitting your app into having a single common back end (e.g. Windows or WCF service) which manages a singleton db connection.

Answer 5

You should not (and cannot) share a connection between two applications. You can share the same connection string, and turn on Connection Pooling, which may re-use connections for you, but you shouldn't depend on it.

As Capital G said, you should put all your database access logic in a separate assembly/layer, or even a WCF Service / Web Service.