Is it possible to create a common database class in VB.NET?

Question

We have 3 databases providers we use to connect to our databases: DB2, MS SQL, and Interbase. I would like to create a single generic database wrapper class that can be used to talk to all three just by passing in the correct connection string, username, password, and the provider desired.

I don't want to have to add references and import all three providers in the database class. Is this possible?

I have done this before in Java using the Class.forName() function.

Answer 1

can you use framework 3.5 sp1?

if yes, you should look at Linq to Entity

Answer 2

If you do not want to have references to the individual providers in your application, you will need to handle this a little differently.

There are two main options I see - the first (and easiest) would be to use a dependency injection framework to just plugin the appropriate provider at runtime. This is simple, clean, and works well.

You could do it yourself without that, though. Just make a general purpose base class that provides the interface, and, for each provider, make a separate assembly (so the references are separated) that implements this base class. You can then use Activator.CreateInstance to create an instance of the appropriate type at runtime.

Answer 3

There is an abstract factory built into .NET 2.0 or later, an example of its use would be:

Dim factory As System.Data.Common.DbProviderFactory
factory = System.Data.Common.DbProviderFactories.GetFactory("System.Data.SqlClient")

Dim conn As System.Data.Common.DbConnection = factory.CreateConnection()
conn.ConnectionString = "connectionString"
conn.Open()

There are methods on DbProviderFactory like CreateCommand, CreateDataAdapter, etc.

Answer 4

To expand on Patrick McDonald's answer, you can store the provider name and connection string in the <connectionStrings> section of your application configuration file. Then you don't need to have the providers hardcoded in the application:

ConnectionStringSettings c = ConfigurationManager.ConnectionStrings["MyConnectionName"];
if (c != null)
{
   DbProviderFactory factory = DbProviderFactories.GetFactory(c.ProviderName);
   IDbConnection connection = factory.CreateConnection();
   connection.ConnectionString = c.ConnectionString;
   ...
}

Where your application configuration file contains a connectionStrings section something like:

<connectionStrings>
  <add name="MyConnectionName" providerName="System.Data.SqlClient" connectionString="Data Source=serverName;Initial Catalog=DBName;Integrated Security=True"/>
</connectionStrings>