how to connect to multiple databases during run time

facepoker2000 · Post by **facepoker2000** » Tue 29 Sep 2009 17:33

During run time, my application needs to connect to several databases (all of them with the same table structure).

I would greatly appreciate it if anyone could answer the following questions:

1. Using the Entity Framework, do I need to create one EDM (.edmx) for every database I want to connect to during run time?

2. Using the Entity Framework, given an "Entities" object, is it possible to simply modify it's connection string to connect to a different database?

3. In LINQ to PostgreSQL, do i need to create one "datacontext" object for each database I want to connect to during run time?

4. In LINQ to PostgreSQL, given a "DataContext" object, is it possible to simply modify it's connection string to connect to a different database?

drichter · Post by **drichter** » Wed 30 Sep 2009 08:09

Hi,

no guarantee for correctness:

2. An entity itself does not have a unique connection-string. Only it's DataContext has...

3. If they're identical this would'nt make sense.. just instantiate the same with another ConnectionString..

4. I guess you want to read data from one database and commit it to another? Mhm... very interesting question...
This would be a cool thing to create a 100% data-identical database..

AndreyR · Post by **AndreyR** » Wed 30 Sep 2009 10:18

1. There is no need to create two identical .edmx models. The only thing - you should remove the Schema attribute
if you have different schemas.
2. You'll have to get the (contextInstance.Connection as EntityConnection).StoreConnection.ConnectionString property.
Basically, it's a bit simpler to create a new ObjectContext with different connection string.
3. Again, the same answer - if you have several databases with the same schema, there is no need to create
this amount of DataContexts. But you'll have to manually remove the schema name from the Table attribute.
4. No. You'll have to recreate the DataContext with a new connection string.
This is associated with peculiarities of internal implementation.

facepoker2000 · Post by **facepoker2000** » Wed 30 Sep 2009 19:46

AndreyR wrote:1. There is no need to create two identical .edmx models. The only thing - you should remove the Schema attribute
if you have different schemas.
2. You'll have to get the (contextInstance.Connection as EntityConnection).StoreConnection.ConnectionString property.
Basically, it's a bit simpler to create a new ObjectContext with different connection string.
3. Again, the same answer - if you have several databases with the same schema, there is no need to create
this amount of DataContexts. But you'll have to manually remove the schema name from the Table attribute.
4. No. You'll have to recreate the DataContext with a new connection string.
This is associated with peculiarities of internal implementation.

I have 2 databases: db1 and db2. Using the Entity Data Model Wizard, I created a model for db1 (hence, creating a db1Entities class). My other database, db2, has exactly the same structure as db1. I want to use my db1Entities class to connect to my db2 database. I've tried changing the connection string (I changed the database name) and creating a new db1Entities object with this new connection string but for some reason I cannot execute my queries. Everything compiles but during run time, when i get to the part where I have to execute a query, the program crashes.

Also, for some reason, specifying Devart.Data.PostgreSql as the Data Provider in the connection string doesn't work. I get this error:

"An unhandled exception of type 'System.Data.EntityException' occurred in System.Data.Entity.dll

Additional information: The underlying provider failed on ConnectionString."

So I used System.Data.SqlClient. I was successful at changing the connection string but I run into trouble when i want to execute my queries.

The following is db1's connection string entry in app.config:

The connection string I created for db2 which I passed to the db1Entities constructor is:

metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="Data Source=localhost;Initial Catalog=db2;Integrated Security=False;User ID=postgres;Password=poker"

AndreyR · Post by **AndreyR** » Thu 01 Oct 2009 12:01

You cannot switch between providers without recreating ObjectContext.
As I have already mentioned, the better way is to create a new ObjectContext with different connection string.