0

I tried to google and then searched some already answered questions on stackoverflow, but with no success.

I need several databases (e.g. MySQL, MSSQL, Firebird) with same structure. How do I create databases with same schema simultaneously, so I don't waste time for each? And also how do I map them to one Entity Model (with one *.edmx model)? Or at least share generated types between models?

P.S. I develop using C#. Thanks in advance!

Edit 1: I found this question, but no answer Using entity framework with both SQL Server and SQLite databases simultaneously

Community
  • 1
  • 1
GaaRa
  • 520
  • 6
  • 21
  • Not as easy as you might think. Different DBs treat things differently. Data types for one thing can vary between DBs. – System Down Nov 18 '13 at 19:32
  • I know that, but maybe there's some tool that'll help at least mirror the database? – GaaRa Nov 18 '13 at 19:34
  • Mirror how? If you use a data type that only exists in one DB or is used differently in another DB, how would an automated process know what to do with it? – System Down Nov 18 '13 at 19:35
  • And if you're using only the most basic data types (that are common to all DBs you are using) then you can just use the same DB creation scripts for all. – System Down Nov 18 '13 at 19:36
  • there are lots of common types, or types that correspond to others, and also queries may differ. So I thought there might be some tool that has mapping between those and can convert for example script files for other db – GaaRa Nov 18 '13 at 19:44
  • The problem is not that two DBs have different names for the same thing, but that the two implement them differently. Varchar for instance differs between Oracle and MSSQL despite them having the same name. – System Down Nov 18 '13 at 19:49
  • okay, what if I generate them with code first? Would it be 'good' tone for my needs? (I'm afraid of veeery long coldstart, need to have connection to all db's simultaneously) – GaaRa Nov 18 '13 at 19:53
  • That's something you'll have to experiment with – System Down Nov 18 '13 at 19:55

2 Answers2

0

I Dont have the reputation to add this as a comment but it sounds like an interesting challenge,

just a few thoughts.

Entity framework I have previously tried mapping two databases to one EDMX using EF4.0 (database first) but unfortunately all roads led me to creating two models. one for each database.

Databases: Given the different database platforms, is it worth considering a SQL script for each database ? so include a copy of the scripts in your app and execute on startup or when required.

This would mean a database first approach but if the db schema is unchanging it might work.

IE create database, create user, create tables etc.... for each db Type.

Wombelite
  • 302
  • 1
  • 6
0

If you which to have contexts point to different Databases. then you should provide the connection information and not use App.config ConnectionStrings.

YOUR content should have a constructor that allows CONNECTION info. It can still have the old way there.

public abstract class YOURDbContext : DbContext, IContextOptions {

    //ctors
    protected YOURDbContext(string connectionName)
        : base(connectionName) {
    }
    // YOU NEED THIS CONSTRUCTOR
    protected YOURDbContext(DbConnection dbConnection, bool contextOwnsConnection)
        : base(dbConnection, contextOwnsConnection) {
    }

Each instance of thr context can be connected to a different database. You have more than 1 context at once.

you will need a Function PER database type to make sure the connectionInfo is constructured properly.

here is an Example get the DBConnection for SQLServer. Repeat for other providers.

     public DbConnection GetSqlConn4DbName(string dataSource, string dbName) {
        var sqlConnStringBuilder = new SqlConnectionStringBuilder();
        sqlConnStringBuilder.DataSource = String.IsNullOrEmpty(dataSource) ? DefaultDataSource : dataSource;
        sqlConnStringBuilder.IntegratedSecurity = true;
        sqlConnStringBuilder.MultipleActiveResultSets = true;

        var sqlConnFact = new SqlConnectionFactory(sqlConnStringBuilder.ConnectionString);
        var sqlConn = sqlConnFact.CreateConnection(dbName);
        return sqlConn;
    }
phil soady
  • 11,043
  • 5
  • 50
  • 95