Writing code that can work with multiple database objects

Question

I basically have two different databases that have different tables in them, but they both have "Table1" with the exact same structure.

var db = new ???;
if(mode == "PRODUCTION"){
    db = new Database1("Connection string for Database1");
}
else{
    db = new Database2("Connection string for Database2");
}

var result = db.Table1.Where(a=>a.Value==1).First();

How can I make the above work so I can assign "result" from two different databases (depending on "mode") without writing two different definitions for "result"?

If you find a solution it won't be safe, as the structures for these tables can change independently. — djechlin
– djechlin, Commented Mar 20, 2013 at 20:55

Justin Niessner · Accepted Answer · 2013-03-20 20:55:37Z

3

Store the connection strings separately in your config file. You can then swap the connection strings at runtime to point the database object to the right database:

if(mode == "PRODUCTION")
{
    db = new Database(ConfigurationManager.ConnectionStrings["production-key"]);
}
else
{
    db = new Database(ConfigurationManager.ConnectionStrings["dev-key"]);
}

answered Mar 20, 2013 at 20:55

Justin Niessner

246k40 gold badges416 silver badges546 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

sooprise Over a year ago

My (limited) understanding of linq-to-sql tells me that you need to specify the type of database being instantiated. So like var db = db1(connectionString) will require a db1 specific connection string. Am I wrong?

Justin Niessner Over a year ago

@sooprise - That's no the case. If the databases have the same schema, you can use the same type and just swap connection strings.

sooprise Over a year ago

By "same schema" do you mean two databases with exactly the same tables all with the same structure?

Blorgbeard Over a year ago

@sooprise it will work up until the point that you try to access a table that doesn't exist or has a different schema than expected.

sooprise Over a year ago

Got it got it, this is cool, I'm trying it now. Will "Check" solution once I get my code working. Thanks everyon.

Hogan · Accepted Answer · 2013-03-20 21:06:50Z

2

Justin's point is a good one, here is another -

Why do this at the db level?

Something like this would work

var table;

if(mode == "PRODUCTION"){
    db = new Database1("Connection string for Database1");
    table = db.table1;
}
else{
    db = new Database1("Connection string for Database2");
    table = db.table1
}

var result = table.Where(a=>a.Value==1).First();

If you don't have the same exact db then you would need to do something like this (you could also add an interface to db1 and db2 to return commonElements -- as you wish.

class commonElements {
   /// some code
}

public commoneElements GetCommon(Database1 inDB1) {
   /// some code
}

public commoneElements GetCommon(Database2 inDB2) {
   /// some code
}

commonElements common;
if(mode == "PRODUCTION"){
    db1 = new Database1("Connection string for Database1");
    common = GetCommon(db1);
}
else{
    db2 = new Database2("Connection string for Database2");
    common = GetCommon(db2);
}

var result = common.Where(a=>a.Value==1).First();

edited Mar 20, 2013 at 21:06

answered Mar 20, 2013 at 20:57

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

2 Comments

Blorgbeard Over a year ago

var table; is not valid. What type is table? The two datacontexts will have different classes for table1, even if the definitions are the same.

Hogan Over a year ago

@Blorgbeard - yes point taken, I like Justin was assuming the same object type was used for both dbs if they are the same. If not then a common accessor function would need to be implemented see edit

Collectives™ on Stack Overflow

Writing code that can work with multiple database objects

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related