Using Multiple Databases or One Database?

Topics: Data Access Application Block
Aug 30, 2007 at 8:00 PM
If I understand DAAB correctly, accessing multiple databases will require to create database instance for each database, how to create a class which can access any of the database instance? For example, I have 2 database instances: Db1 and Db2. I want to create a class which will have coding like this:

'Db can be Db1 or Db2 depending on value passed to the class
Db.ExecuteReader("select * from customers")
Aug 30, 2007 at 8:11 PM
Edited Aug 30, 2007 at 8:26 PM

I'm afraid you're confusing classes with instances. You can have two connection strings with different names in the configuration file, let's say "marketing" and "accounting", and create Database instances using the DatabaseFactory providing the appropriate name for the context where the instance will be used.
Actually, Database is an abstract class so the actual classes of the instances that you will get will be determined by the configuration; eg if your connection string describes a SQL Server database, you will get an instance of SqlDatabase. But you shouldn't care about this and write the code against the API exposed by the Database class unless you really need the SQL Server specific features.

Aug 30, 2007 at 9:29 PM
Hi Fernando,

Let's say that I have the "marketing" and "accounting" connection strings: "marketing" is pointing to a MS SQL SERVER database named marketing and "accounting" is pointing to a MS SQL SERVER database named accounting.

dim Db1 = new Database.CreateDatabase("marketing")
dim Db2 = new Database.CreateDatabase("accounting")

My understanding of the above statements:
- a SqlDatabase instance Db1 will be created and sql server data provider will be the data provider for this instance.
- another SqlDatabase instance Db2 will be created and sql server data provider will be the data provider for this instance.

Now I want to some have some functions which will take Db1 or Db2 as parameter so I can have a generic statement like this:


instead of doing something like this:

if database instance is Db1 then

I'm not sure whether it is possible in .net or not. I think this may need to use something called Reflection which I have not learned yet.
Aug 30, 2007 at 10:22 PM

Your functions will take a Database instance and invoke ExecuteReader on them, regardless of which particular instance gets passed as a parameter... You don't need reflection for this.

Using C# syntax:

// "somewhere"
Database db1 = DatabaseFactory.CreateDatabase("marketing");
Database db2 = DatabaseFactory.CreateDatabase("accounting");

// some logic

// invoke functions

// the method
void DoMyFunction(Database db)
// some logic


// some more logic

Aug 30, 2007 at 10:31 PM
Hi Fernando,

Thanks. I think this is similar to this:

Aug 31, 2007 at 2:44 PM
I'm working on a project where I have to connect to at least 8 different databases, which could grow to dozens.

All of my data access objects have a constructor where I can pass the connection string.


Public Class ExampleDAO
Private connString As String = Globals.DefaultConnString

Public Sub New()
'Accepts the default connection string
End Sub

Public Sub New(ByVal connectString As String)
connString = connectString
End Sub

Public Function SaveDataTable(ByVal ds As DataSet, ByVal tableName As String) As Integer
Dim _db As Database = MyDbFactory.CreateDatabase(connString)
create commands here
Return _db.UpdateDataSet(ds, tableName, insertCmd, updateCmd, deleteCmd, UpdateBehavior.Standard)
End Function
End Class

Public Class MyDbFactory

Shared ReadOnly connectionStringFormat As String = "Data Source={0};Initial Catalog={1};UID={2};PWD={3}"
Shared ReadOnly dbProviderFactory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient")

Public Shared Function CreateDatabase(ByVal dsn As String, ByVal db As String, ByVal id As String, ByVal password As String) As Database
Dim connectionString As String = String.Format(connectionStringFormat, dsn, db, id, password)
Return New GenericDatabase(connectionString, dbProviderFactory)
End Function

Public Shared Function CreateDatabase(ByVal connString As String) As Database
Return New GenericDatabase(connString, dbProviderFactory)
End Function
End Class

That's how I solved my connection string problem. For me, storing all of the connection strings in the XML configuration files was not the best option.

Sep 11, 2007 at 12:29 AM
Hi Jim,

Am I correct that the purpose of the MyDbFactory class is to eliminate the need to construct the connection string in the calling coding?

Since you're not storing the connection strings in the XML configuration files, are you storing all of them in registries? Or are you storing one connection string in the XML files and then using it to retrieve other connection strings on a table?