0

I have to create an application which needs to connect to multiple and different types of databases. For example, I can have 3 SQL Server DBs, 5 DB2 DBs and 1 Informix DB which I need to connect to perform any CRUD operations. Now, I don't want to Open and Close the DB connection for each DB call, instead, I would like to keep a single connection, open, for each database. So, if I have 9 databases, I would have 9 unique connections only. I thought of creating a Singleton pattern but I am not sure if that would work in this scenario. I have seen some references on creating Singleton DB connections but I want to know if there is any better approach which I can implement for my requirement. Please let me know if I could improve my question and provide some more details.

Souvik Ghosh
  • 4,456
  • 13
  • 56
  • 78
  • 1
    What benefit do you get from having a singleton connection? I doubt if you get any performance improvements and in any case it doesn't seem like a good programming practice. See this : http://stackoverflow.com/questions/1557592/is-using-a-singleton-for-the-connection-a-good-idea-in-asp-net-website – Shai Aharoni Jul 24 '16 at 09:00
  • @ShaiAharoni yes I know. Recently I faced some performance issue so I had to keep the connection open for some high frequency DB operations. It was not Singleton though. Hope, you got what I am trying to achieve. I want to understand if there is any better approach to implement this. Note- My application will be deployed as a REST service which might be used by n number of clients. – Souvik Ghosh Jul 24 '16 at 09:05
  • Its not wise to keep the connection open for SQL server. If you need to improve the performance of query then try adding indexing to you SQL table. – Murtoza Jul 24 '16 at 09:16
  • @Murtoza Indexing does not help to reduce the cost of opening/closing the connection – Souvik Ghosh Jul 24 '16 at 09:18
  • Just think about this. Your DB2 connection is serving a request from a client and another client executes another request for the same database, with a single connection the second client should wait the end of the first request? – Steve Jul 24 '16 at 09:18
  • @Steve Actually there should not be any waiting. I am worried about too many DB requests (Insert, Update, Delete) as I won't have much control of how the clients would use it. Think about this- each client firing Update query for about 1000 records where my application opens and closes the connection each time for each record. Normally, what I would have done is, 1. open connection 2. run update 1000 times 3. close connection. – Souvik Ghosh Jul 24 '16 at 09:27
  • You can try [Connection Pooling](http://stackoverflow.com/questions/16416467/having-sql-connection-open-for-a-long-time) feature – Murtoza Jul 24 '16 at 09:35
  • @Souvik Ghosh - Update 1000 records ? one by one? – Lucian Bumb Jul 24 '16 at 09:49

0 Answers0