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!

Calling a Stored Procedure from Entity Framework

Dear readers,

In the previous posts on entity framework, we saw some basic tutorials on programming using Entity Framework. This post is about calling a stored procedure using Entity Framework. I assume that you have idea in creating .edmx and write stored procedures in SQL server.

1) Write a stored procedure and execute it in your data base, In my case I have written a simple SP to delete the records with odd Numbered Primary Keys from a table.

0StoredProc

Stored Procedure

2) Then Go to your application and open the .edmx file>> right click and select Update Model from database.

1UpdateModelFromDB

Update Model From Database

3) An update wizard will open. In which you can select the stored procedure that you would like to execute through Entity Framework >> Click finish.

2AddTheStoredProc

Add the SP from Update Model Wizard

4) Now open the model browser by right clicking the .edmx file and selecting Model browser. You will now be able to see the your stored procedure under (Second red box in model browser) yourmodel.store tree view. You will be able to see another parent tree just above that in the name of your model, traverse to Entity Container >> Function imports (first red box in the model browser).

3ModelBrowser

Open the Model Browser

5) Right click Function Imports and select “Add Function import“. The below wizard will open in which you need to give a name to sp and then select the SP that you want to call from your code. Provide the return parameters. if any(In my case I am not using any return type in my SP). Click “ok“.

5AddFuctionWizard

Open the Add Function Import Wizard

6) Go to the fuction from where you want to execute it and call the ExecuteFunction method by passing the params as string array, if any. In the code snippet below, I have called my “DeleteOddAddress” SP.

6ContextExecuteFunction

Call the Execute function from the code

The result for this execution returns 0 records since I do not have any odd numbered address ids in my table.

Result Console

Result Console

Hope you find this useful. Happy Learning Folks!!!