Switching between databases in Entity Framework

We may sometimes come across using multiple databases with the same schema, for certain functionality like archiving data for future references, taking the backup of records from the main databases. This post will let you know how to dynamically switch between databases while using Entity Framework. I am using Entity Framework 4 for this tutorial. I assume that you have basic understanding in creating the .Edmx file and perform basic operations with Entity Framework. If not, I would recommend you to have a brief understanding on those.

Now let us see the steps to do the same.

1) Create an .edmx file and generate model from your database. In this case you can consider any one of the dbs with the same schema to generate the model.

Generate Model From Database

Generate Model From Database using one of the databases

 

 

 

 

 

 

 

 

2) Add the required number of connection strings in the App.config. The connection string’s name will be used for dynamically selecting the databases.

Connection strings

App.Config connection strings

 

 

 

 

3) In your code, where you create the object to the data context, you also need to provide the connection string in the constructor, that can be dynamically selected by the end user. The other Entity Framework code remains the same. The sample for the same can be found below:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Configuration;

namespace SpanDBs

{

class Program

{

static void Main(string[] args)

{

try

{

string connectionString = string.Empty;

Console.WriteLine(“Enter 1 to use SampleDatabase , 2 to use SampleDatabase2”);

int ch = Int32.Parse(Console.ReadLine());

if (ch == 1)

{

connectionString = ConfigurationManager.ConnectionStrings[“SampleDatabaseEntities”].ConnectionString;

}

else if (ch == 2)

{

connectionString = ConfigurationManager.ConnectionStrings[“SampleDatabaseEntities1”].ConnectionString;

}

else

{

Console.WriteLine(“Invalid input”);

}

using (SampleDatabaseEntities context = new SampleDatabaseEntities(connectionString))

{

Console.WriteLine(“The List of offices:”);

var records = (from ent in context.Offices

select ent.Name).ToList();

foreach (var name in records)

{

Console.WriteLine(name);

}

Console.WriteLine(“To insert a new office, type the countryname followed by office name:”);

Console.WriteLine(“Enter the Country Name”);

string country = Console.ReadLine();

Console.WriteLine(“Enter the Office Name”);

string Office = Console.ReadLine();

long exist = (from ent in context.Countries

where ent.Name == country

select ent.Id).FirstOrDefault();

if (exist > 0 && exist != null)

{

Office obj = new Office();

obj.Name = Office;

obj.CountryId = exist;

context.AddToOffices(obj);

context.SaveChanges();

}

else

{

Country con = new Country();

con.Name = country;

context.AddToCountries(con);

context.SaveChanges();

exist = (from ent in context.Countries

where ent.Name == country

select ent.Id).FirstOrDefault();

Office obj = new Office();

obj.Name = Office;

obj.CountryId = exist;

context.AddToOffices(obj);

context.SaveChanges();

}

Console.WriteLine(“The new office is successfully added”);

}

Console.Read();

}

catch (Exception ex)

{

Console.WriteLine(ex.InnerException == null ? ex.Message : ex.InnerException.Message);

}

}

}

}

The sample project illustrating this can be downloaded from : https://docs.google.com/file/d/0B915XE_zbo18cWVORXk5ajg5VHM/edit?usp=sharing

Note:

1) To create a database backup in your SQL server, connect to your SQL server instance >> Select the database, whose back up you want to take >>Right click >> Tasks >> Generate back up of the database >> A wizard will open, where you can name the back up and save your backup file in the location of your choice.

2) To restore the created back up, Right click on the database tree in your SQL server instance >> Click Restore Database >>A Wizard will appear, where you can select the back up file from the location you have previously stored >> Name the database and Select the database to be restored and click ok (as shown in figure below).

RestoreDB

Restore Database in SQL Server

 

 

 

 

 

 

 

Hope it solves your purpose. Happy Learning Folks!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s