0

I have read the accepted answer in the following post: ExecuteReader requires an open and available Connection. The connection's current state is Connecting

I am thinking about creating a class that will use an interface (dbConnection) to connect to an appropriate database i.e. SQL Server (SQLConnection) or Oracle (OracleConnection). The class will have methods for open,close,dispose etc plus a method to find the connection String. The class will allow an application to use the dbConnection interface to connect to either an SQL server database, an Oracle database or an Access database. The application in question connects to a number of databases.

I don't believe this breaks any rules stated in the accepted answer e.g. there are no shared variables. However, I am wandering if this is bad practice because of the reasons stated in the answer.

I will post some code if required.

Community
  • 1
  • 1
w0051977
  • 15,099
  • 32
  • 152
  • 329
  • Any reason you can't use the existing base classes such as [`DbConnection`](http://msdn.microsoft.com/en-us/library/system.data.common.dbconnection.aspx) and [`DbCommand`](http://msdn.microsoft.com/en-us/library/system.data.common.dbcommand)? – Oded Jul 21 '12 at 18:26
  • @Oded, thanks. The app in question acts like a data warehouse (though it is not technically a data warehouse). It connects to many databases to extract information. For example, if a user searches for a person the search will be done in a sales database, finance database,marketing database etc. The marketing database could be an Oracle database and the finance database could be an SQL database. – w0051977 Jul 21 '12 at 18:44
  • OK, so these base classes should be right for you as a "generic" data-access layer. That's what they were designed for (so you can pass in specific implementations, such as for SQL Server or Oracle or whatever). – Oded Jul 21 '12 at 18:45
  • @Oded, the program at runtime decides whether to connect to the sales database or marketing database or finance database etc. I thought it might be a good idea to encapsulate all this functionality in classes. However, I am now questioning this idea. – w0051977 Jul 21 '12 at 18:46
  • @Oded, I posted my last response before I saw your last comment. Are you saying that I should create a connection class for this functionality? – w0051977 Jul 21 '12 at 18:49
  • My point is that this functionality already _exists_ (that is, you don't need to create an interface, just use these classes in your code for data access). You will need to decide what exact type to pass in to your classes so you can connect to each database correctly. – Oded Jul 21 '12 at 18:54
  • @Oded, I tend to agree with you. I will encapsulate the functionality to decide which database to connect to, but I will use the existing interface to actually connect and disconnect. Can you write an answer so that I can accept it? – w0051977 Jul 21 '12 at 18:59

1 Answers1

0

There are classes in the System.Data.Common namespace that are there for exactly what you plan to use interfaces for, so in this regard you do not need to write such interfaces.

From the MSDN page for the namespace:

The classes in System.Data.Common are intended to give developers a way to write ADO.NET code that will work against all .NET Framework data providers.

So, all you are left to do is write your generic data access code using these classes, and elsewhere write code that will pass in the actual providers to it.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • Could you clarify what you mean by "write your generic data access code" – w0051977 Jul 21 '12 at 19:42
  • @w0051977 - Generic, as in for general use. That is, not specialized to the different databases. – Oded Jul 21 '12 at 19:44
  • I could argue that: connect(),disconnect(),dereference() etc are all generic as they are not specific to a particular database. In your comment you advise against using a generic connection class. – w0051977 Jul 21 '12 at 19:46
  • @w0051977 - I am talking about using `DbConnection`, that is **not** database specific, in your data access code. You will still need to use the correct connection strings etc. – Oded Jul 21 '12 at 19:53
  • I have asked a follow on question here: http://stackoverflow.com/questions/11631299/net-connection-pool-teritory. Could you answer if you have time? – w0051977 Jul 24 '12 at 12:55