Using Entity FrameWork with Multiple MS SQLSERVER Databases

Question

I searched back and forth but didn't seem to understand what I needed. I apologize if this has been answered recently. Forwarding to the discussion will benefit me.

This is a script. I was instructed to switch from Microsoft Visual Foxpro (MS departs for support until 2015) .Net C # by my boss. For the sake of a good foundation and adoption of best practices, I decided to first study, combine the relevant information, and then start coding. This is the second year.

We are a bureau company that offers salary processing services for more than 50 clients. Each client has its own database. Databases have tables with completely identical structures.

I am newbie. Completely new in the world .net.

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

Can someone point me to a "good read" where I can implement the Entity Framework with over 50 indentical databases. Each database is completely independent and has nothing to do with the others. When a user logs in, they choose for which client they need to process the payroll, then EF points to this database.

Answer 1

To work with data from the EF database, two different pieces of information are required:

1) Database schema: it is included as compiled code in your application and usually cannot be changed at runtime.

2) Connection string: it is provided at runtime, usually from a configuration file.

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

The item 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 environment that help you create connection strings for you:

MSDN: EntityConnectionStringBuilder Class

MSDN: Connection String Builders

Answer 2

Hrmmm I really like EF Code First, but I'm not sure if it fits what you are doing. How often does your circuit change?

Should you use EF?

EF Benefits

If the schema changes somewhat regularly, the Migrations part of EF Code First can save you a lot of time and effort, because you can often get rid of SQL scripts for schema updates - the schema changes end in your source repository, and the rest of your code. You will start here:

stack overflow

I also really like how easy it is to configure EF and how easy it is to write LINQ queries against it and return exactly the POCOs that I created from the database.

But EF may not be the best way.

Other ORMs to consider

Many other ORMs support LINQ and POCOs with better support for existing databases (there are things that can be quite difficult to display in EF Code First) - and existing support for asynchronous operation (EF is now at 5.0; has async) - (update: EF6 is the last one, and its support for async is great. Its massive removal is horrible, although it should be avoided like the plague, you need to switch to plain SQL).

In particular, NHibernate is a beast on stage for existing db support, but it's a bit of configuration complexity and what seems to be a political struggle has caused documentation that runs across different versions and forks.

Much simpler: " Micro ORMs " - this link has been shortlisted since 2011, but if you get bored you will find 30 or so .Net. Some generate better or less optimal queries, some of them are completely absent, some make you write SQL (do not use them) - you will have to poke to decide what is right for you. This may be a larger research task, but I suspect that a lightweight configuration and a small learning curve for one of these best suits that you are trying to do.

Answer your specific question

Talk to all client Dbs at once

If you simultaneously connect to all 50 databases from one application, you need to create an instance of 50 DbContexts, for example:

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

Assuming you walked around creating small wrapper classes, for example:

 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 (the standard part of any EF project).

Speak only one at a time

If you use only one application, any EF tutorial will be .

The only major trick is to migrate these Dbs when changing the schema. There are two main approaches. In any case, you take the backup and restore it locally so that you can test your migrations against them ( update-database -f -verbose ). If you do not compromise data errors, such as changing a column to NOT NULL, and looking for a local test instance does not have zeros, one client did, kaboom. Once you earn them, you decide how you want to update Production. There are many ways to do this, starting from writing a special fast-forward / rewind tool (or searching for it) using SQL scripts tested on git, hiring a database administrator, or much easier:

Obvious - SQL Script

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

My crazy way for small dbs numbers

Add entries for each db to Web.Config and create a project configuration for each of them, for example, "DbDeployClient1", "DbDeployClient2", etc. In each of them, create a construct, for example, DbDeployClient1, and then add it to your DbContext Class:

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

This allows you to quickly switch to the DbDeploy configuration and migrate directly from Visual Studio to the target database. Obviously, if you do this, you need to temporarily open the port, it is advisable only to allow in your IP address the instance of SQL Server that you are transferring. One of the subtleties is that you get clear errors from your migration right there and complete rollback, without any real work - everything that supports rollback is part of EF. And one developer can do this without a bunch of other bottlenecks. But he has many opportunities to reduce risk and improve automation.

Answer 3

It is very simple

I have had,

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

already in the autogenerated Model.Context.cs folder edmx

To connect to multiple databases at run time, I created another constructor that takes a connection string as a parameter, as shown below in the same Model.Context.cs file

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

Now I have added another 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 the database, I call the method below, passing connetionString name as parameter

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