1

I want to migrate a database from Firebird to MSSQL using linq2db in C#.

I thought I could load the structure using T4 Models from Firebird and then create the tables and BulkCopy the the data to MSSQL.

So far so good, but it is copying data back to Firebird instead of to MSSQL

here is my app.config:

<connectionStrings>
    <add name="Firebird__" connectionString="Data Source=localhost;Initial Catalog=MyDatabase;User Id=xxx;Password=yyy" providerName="Firebird" />
    <add name="MSSQL__" connectionString="Data Source=192.168.1.x,12345;Initial Catalog=myOtherDatabase;User Id=yyy;Password=xxx" providerName="MSSQL" />
  </connectionStrings>
  <system.data>
        <DbProviderFactories>
            <remove invariant="FirebirdSql.Data.FirebirdClient" />
            <add name="FirebirdClient Data Provider" invariant="FirebirdSql.Data.FirebirdClient" description=".NET Framework Data Provider for Firebird" type="FirebirdSql.Data.FirebirdClient.FirebirdClientFactory, FirebirdSql.Data.FirebirdClient" />
        </DbProviderFactories>
    </system.data></configuration>

Then in my program I'm using this:

using (var db = new FirebirdDB())
        {
        var employeeQuery =
              from m in db.employee
              orderby m.NAME
              select m;

            liste = employeeQuery.ToList();
        }
      //using (var db_MSSQL = new MSSQL())
        using (var db_MSSQL = new FirebirdDB("MSSQL__")) 
        {
            db_MSSQL.CreateTable<MSSQL_.MA_DATEN_NAME>();
            //db_MSSQL.BulkCopy(liste);

I read the last using statement here (How to use more than one SQLite databases using LinqToDB)

always the same problem, program uses the firebird connection and not the mssql. any ideas?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Flo s
  • 51
  • 5
  • Quote: `using (var db_MSSQL = new FirebirdDB("MSSQL__"))` - but if you ask program to use Firebird DB - then it will use it. – Arioch 'The Oct 24 '18 at 11:52
  • that makes sense (I tried it, cause of the other SO article). now I changed to using(var db_MSSQL = new MSSQL("MSQL__")) but depending on the order in app.config I get error 'DataProvider is not provided for configuration: ' altough I think my app.config is correct. – Flo s Oct 24 '18 at 12:06
  • I know next to nothing about C# but I see your app config does not even mentioning MS SQL providers (in factories or maybe any other method to obtain one). Maybe by C# ideology it should implicitly pull down some global Windows MSSQL provider, dunno. Or, if there is declarative ideology, then perhaps "you can not use variable that you did not declared yet" - and same approach with enlisting providers in config files, dunno about C# how it is structured – Arioch 'The Oct 24 '18 at 12:25
  • Did initially copying proceed into the same source Firebird database or into some new separate FB DB ? – Arioch 'The Oct 24 '18 at 13:36
  • same source Firebird database! – Flo s Oct 24 '18 at 14:03
  • That's shady... If I am not mistaken, 1) you fed Firebird .Net provider with incorrect connection string (by specifying wrong name constant), at very-very least it *was* incorrect due to specifying wrong (for FB) `providerName`; 2) then FB.Net provider one way or another figured the C.S. is incorrect and it can not connect using it; 3) then FB.Net provider did not explicitly failed, did not thrown a C# exception, but instead made an implicit no-name-specified connection, quite PHP-way... Am I correct? does this (silently working around obvious errors) conform to C# ideology or contradict it? – Arioch 'The Oct 24 '18 at 16:03
  • yep, no exception, just did what it should not do, write to the wrong db... – Flo s Oct 24 '18 at 16:22
  • Then perhaps you open bug ticket on the Firebird Net Provider if you sure this contradict how C# database providers are obliged to act on wrong connection configs ? – Arioch 'The Oct 25 '18 at 09:54

1 Answers1

1

now I found the answer, thanks Arioch!

App.config

the providerName is obviously very important and I needed "SqlServer.2012"

<connectionStrings>
<add name="Firebird__" connectionString="Data Source=localhost;Initial Catalog=MyDatabase;User Id=yy;Password=xx" providerName="Firebird" />
<add name="MSSQL__" connectionString="Data Source=192.168.1.x,12345;Initial Catalog=MyOtherDatabase;User Id=yy;Password=xx" providerName="SqlServer.2012" />
</connectionStrings>

in my program:

this did the trick: you give the connection a name with the parameter "name" in your connection settings, which makes sense if you have more than one...

using (var db_MSSQL = new MSSQL("MSSQL__"))
        {
            db_MSSQL.CreateTable<MSSQL_.MA_DATEN_NAME>();
Flo s
  • 51
  • 5