CODEWITHSUNDEEP
c#asp.netsql-serveroracle-database
Neo
Neo

Reputation: 696

Oracle to SQL Server database migration for an ASP.NET project

I have to perform an Oracle to SQL Server database migration on an ASP.NET project so I'm basically working on the C# code to modify the db related classes/methods to fit the new database.

In particular, there is a use in the code of the line

DbProviderFactory myFactory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");

I don't know how to replace the string argument of the GetFactory method above to fit an SQL Server database.

Should I do something like what's below?

DbProviderFactory myFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");

Upvotes: 2

Views: 1359

Answers (1)

rene
rene

Reputation: 42494

You have full control over what you enter as the parameter in GetFactory.

What you enter maps to settings of the <DbProviderFactories> in your config file, which is the combined set of machine.config and web.config.

In my machine.config I have:

<system.data>
    <DbProviderFactories>
      <add invariant="System.Data.SqlServerCe.3.5" name="Microsoft SQL Server Compact Data Provider"  description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
      <add invariant="System.Data.SqlServerCe.4.0" name="Microsoft SQL Server Compact Data Provider 4.0"  description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
      <add invariant="MySql.Data.MySqlClient" name="MySQL Data Provider"  description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
    </DbProviderFactories>
</system.data>

But depending on if you have installed other Database providers you might also find

<add invariant="IBM.Data.DB2.iSeries" name="IBM DB2 for i .NET Provider" invariant="IBM.Data.DB2.iSeries" description=".NET Framework Data Provider for IBM i" type="IBM.Data.DB2.iSeries.iDB2Factory, IBM.Data.DB2.iSeries, Version=12.0.0.0, Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26" />

These entries are not limited to the machine.config. You can also add them to your web.config. You only have to make sure the value of the invariant name is unique (across web.config and machine.config). Or you could clear a factory if the one in the machine.config doesn't do what you want.

On top of those in the the config there are 4 build-in providers that are loaded by default (this are the invariant names)

  • System.Data.Odbc
  • System.Data.OleDb
  • System.Data.OracleClient
  • System.Data.SqlClient

So if you ask what factory name to use, it depends on what you have installed but if you're looking for the Sql Server client System.Data.SqlClient should work.

If you want to make it really confusing for your peers (and probably yourself) you could add this to your web.config this (for a .Net 4.0 application):

<system.data>
    <DbProviderFactories>
      <!-- remove everything predefined -->
      <clear />
      <!-- add the providers you want to use -->
      <!-- Notice how the name is totally unrelated now
           to the actual dbfactory that will be returned
       -->
      <add invariant="Oracle.DataAccess.Client" 
           name="A trick to use SqlServer without code change" 
           description="fubar" 
           type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    </DbProviderFactories>
</system.data>

In this case you would not have to change anything in your code. Your app will from now on use the SqlServer factory.

tl;dr;

You're looking for System.Data.SqlClient if you want to use the build-in DbFactory for Sql Server that comes with the .Net Framework you use. Or add your own config with a project specific invariant name and link it to an DBFactory of choice by configuring that in your web.config

Upvotes: 1

Related Questions