Using Entity FrameWork with multiple MS SQLSERVER Databases

Question

I have searched back and forth but seemingly could not get a hold of what I need. I am sorry if this has been answered of late. A redirection to the discussion will do me good.

This is the scenario. I have been instructed to move from Microsoft Visual Foxpro (MS is withdrawing support come 2015) to .Net C# by my boss. For the sake of good foundation and adoption of best practices, I have decided to first learn, piece pertinent information together, then start coding. This is the second year.

We are a bureau company that offer payroll processing outsource services to over 50 clients. Each client currently has their own database. The databases have tables with completely identical structures.

I am a newbie. Totally new to .net world.

I had started off with raw SQL using datatables, datareaders but in my research I got some discussions discouraging this. Many were of the view that Entity Framework should serve the purpose. But one is allowed to mix approaches especially when complex queries are involved.

Can someone point me to some 'good read' where I can implement Entity Framework with over 50 indentical databases. Each database is totally independent and has nothing to dowith any other. When the user logs in, they select which client they need to process payroll for, then EF points to that database.

Answer 1

EF needs 2 different pieces of information to work with data from a database:

1) The database schema: This is included as compiled code in your application and cannot normally be changed at runtime.

2) The connection string: This is provided at runtime, normally from a config file.

In your case, all the databases have the same schema, so you can just model one database and it will work for all the others.

The piece you want to change is the connection string. This tells EF how to find the database and can be provided at runtime.

There is an overload of the DbContext constructor which takes a connection string as a parameter: MSDN: DbContext Constructor (String)

And there are even classes in the framework that help create connection strings for you:

MSDN: EntityConnectionStringBuilder Class

MSDN: Connection String Builders

Answer 2

It is very simple

I had,

//WMSEntities is conection string name in web.config 
//also the name of Entitiframework
public WMSEntities() : base("name=WMSEntities") 
        {
        }

already in autogenerated Model.Context.cs of edmx folder

To connect to multiple database in runtime, I created another constructor that takes connection string as parameter like below in same file Model.Context.cs

   public WMSEntities(string connStringName)
            : base("name=" + connStringName)
        {
        }

Now, I added other connection string in Web.Config for example

  <add name="WMSEntities31" connectionString="data source=TESTDBSERVER_NAME;
       initial catalog=TESTDB;userid=TestUser;password=TestUserPW/>

<add name="WMSEntities" connectionString="data source=TESTDBSERVER_NAME12;
     initial catalog=TESTDB12;userid=TestUser12;password=TestUserPW12/>

Then, when connecting to database I call below method passing connetionString name as parameter

  public static List<v_POVendor> GetPOVendorList(string connectionStringName)
  {    
      using (WMSEntities db = new WMSEntities(connectionStringName))
      {               
          vendorList = db.v_POVendor.ToList();                 

      }
  }

Answer 3

Hrmmm I happen to really like EF Code First but I'm not certain it suits what you're doing. How often does your schema change?

Should You Be Using EF?

Advantages of EF

If the schema changes somewhat regularly, the Migrations part of EF Code First might save you a lot of time and effort because you can often do away with SQL scripts for schema upgrades - schema changes end up in your source repository with the rest of your code instead. You'd start here:

https://stackoverflow.com/a/8909092/176877

I also happen to really like how easy EF is to setup, and how easy it is to write LINQ queries against it and return exactly the POCOs I built from the DB.

But EF might not be the best fit.

Other ORMs to consider

Many other ORMs support LINQ and POCOs with better support for existing databases (there are things that can be pretty difficult to map in EF Code First), --and existing support for asynchronous operation (EF is on 5.0 right now; 6.0 has async)-- (update: EF6 is the latest and its async support is great. Its bulk delete is terrible though and should be avoided like plague, drop to plain SQL for that).

In particular NHibernate is the beast on the scene for existing db support, but it's a bit of a configuration chore and what appears to be political infighting has caused the documentation to be conflicting for different versions and forks of it.

Much simpler are many "Micro ORMs" - that link is to a short list from 2011 but if you poke around you'll find 30 or so in .Net. Some generate better or less optimal queries, some none at all, some make you write SQL (don't use those) - you'll have to poke around to decide which is for you. This can be a bigger research task but I suspect the easy configuration and small learning curve for one of these best suits what you're trying to do.

Answer to your specific question

Talk to All client Dbs at once

If you're connecting to all 50 databases from one app at the same time you'll need to instantiate 50 DbContexts like:

var dbClient1 = new DbClient1();
var dbClient2 = new DbClient2();

Assuming you went around making little wrapper classes like:

public class DbClient1 : CoreDbContext
{
    public DbClient1()
        : base("DbClient1") // Means use the connection string named "DbClient1" in Web.Config

Where CoreDbContext is the main EF class in your Project that extends DbContext (standard part of any EF project).

Talk to just one at a time

If you're using just the one per app then any EF tutorial will do.

The only major trick will be migrating those Dbs when schema changes occur. Two basic approaches there. Either way you grab a backup and restore a copy of them locally so you can test your migrations against them (update-database -f -verbose). If you don't you risk data errors like changing a column to NOT NULL and finding your local test instance had no nulls, one client's did, kaboom. Once you get them working, you're onto deciding how you want to update Production. There are a lot of ways you might do this ranging from writing a custom roll-forward/back tool (or finding one) with SQL scripts checked into git, hiring a DBA, or much simpler:

The Obvious - SQL Script

Dump the migration to SQL (update-database -script) and run it against the actual production database.

My Crazy Way for Small Numbers of Dbs

Add entries for each db to Web.Config, and create a Project Configuration for each of them like "DbDeployClient1," "DbDeployClient2," etc. In each of those make a build define like DbDeployClient1, and then add this to your DbContext class:

public CoreDbContext()
#if DbDeployClient1
    : base("DbDeployClient1")
#elseif DbDeployClient2
    : base("DbDeployClient2")
    // etc
#endif
{

That allows you to quickly switch to your DbDeploy config and run the migration directly from Visual Studio against the target database. Obviously if you're doing this you'll need to temporarily open a port, preferably only allowing in your IP, on the actual SQL Server instance you're migrating. One nicety is you get clear errors from your migration right there, and full rollback capability, without any real work - all that rollback support you're leveraging is just part of EF. And one dev can do it without a bunch of other bottlenecks. But it has a lot of opportunities to reduce risk and improve automation.