Jump to content

Oracle Data provider (ODP.Net) in a migrated project


OlmanQuesada

Recommended Posts

  • Mobilize.Net Staff

Old Visual Basic 6 applications interacting with Oracle databases may rely on the MSDAORA driver in the connection string to establish such communication when that code is converted to .NET, the Visual Basic Upgrade Companion (VBUC) will keep the same connection string and therefore be use the old MSDAORA driver. But, in .NET we can take advantage of the Oracle Data Provider (ODP.Net) technology developed by Oracle instead.

The following VB6 code shows a database connection though the MSDAORA provider:

Dim oConn As ADODB.Connection

Set oConn = New ADODB.Connection

oConn.ConnectionString = "Provider=MSDAORA.1;Password=" & sPassword & ";User ID = " & sUser & "; Data Source= " & sServer & ";Locales Identifier=1033"

oConn.Open

 

When this code is converted to .NET using the VBUC the code look like this:

DbConnection oConn = UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateConnection();

oConn.ConnectionString = "Provider=MSDAORA.1;Password=" + sPassword + ";User ID = " + sUser + "; Data Source= " + sServer + ";Locales Identifier=1033";

//UPGRADE_TODO: (7010) The connection string must be verified to fullfill the .NET data provider connection string requirements. More Information: https://www.mobilize.net/vbtonet/ewis/ewi7010

oConn.Open();

Note: ADODB component is migrated to ADO.NET using System.Data.Common and helper classes.

 

As you can see the migrated application is still using the MSDAORA provider.

If your final goal is taking full advantage of the .NET technology, you may want to replace that provider for the ODP.Net developed by Oracle. In this case, you need to go to the Oracle Data Provider .NET download page (http://www.oracle.com/technetwork/topics/dotnet/index-085163.html) and choose the required version of this .NET component.

After installing and configuring the ODP.NET component on your machine you will have to make some minor adjustments to the migrated code:

1. Add the Oracle.DataAccess.Client factory

Mobilize helper classes use a DVProviderFactory to create the right ADO.NET object according to the database connection provider in use:

  • OleDb providers will use the System.Data.OleDb namespace. This is valid for MS-Access files and any OleDb provider like the MSDAORA one.
  • ODBC providers will use the System.Data.ODBC namespace
  • SqlServer can use the System.Data.SqlClient namespace
  • Oracle providers for .NET will use Oracle.DataAccess.Client namespace that comes with the ODP.NET installer. If this assembly is not installed an exception will raise at runtime.

 

To use the Oracle.DataAccess.Client, find the method Load DefaultFactorySettings that comes in the AdoFactoryManager class from the UpgradeHelpers.DB.Essentials helper project and uncomment-out the line:

factorySection.Add("Oracle", new FactoryConfigurationElement("Oracle", "Oracle.DataAccess.Client", DatabaseType.Oracle, false));

and comment-out this line:

factorySection.Add("Oracle", new FactoryConfigurationElement("Oracle", "Oracle.DataAccess.Client", DatabaseType.Oracle, false));

 So, this method should looks like this:

         private static void LoadDefaultFactorySettings(Dictionary<string, FactoryConfigurationElement> factorySection)

        {

            factorySection.Add("Access", new FactoryConfigurationElement("Access", "System.Data.OleDb", DatabaseType.Access, true));

            factorySection.Add("SQLServer", new FactoryConfigurationElement("SQLServer", "System.Data.SqlClient", DatabaseType.SQLServer, false));

            //New Changes

            factorySection.Add("Oracle", new FactoryConfigurationElement("Oracle", "Oracle.DataAccess.Client", DatabaseType.Oracle, false));

            //factorySection.Add("Oracle", new FactoryConfigurationElement("Oracle", "System.Data.OracleClient", DatabaseType.Oracle, false));

            factorySection.Add("ODBC", new FactoryConfigurationElement("ODBC", "System.Data.Odbc", DatabaseType.Access, false));

        }

 

With these changes, any ADO.NET object (DBCommands, DBConnections, etc) created using the UpgradeHelpers.DB.AdoFactoryManager.GetFactory() will be instantiated using the types defined in Oracle.DataAccess.Client namespace.

 

2. Correct the Connection String

As illustrated in the VB6 code above, the connection string is using an OLEDb provider (MSDAORA), so we need to change that string to send the parameters required by the ODP.NET provider:

 

string conStr = "Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST="+ sServer + ")(PORT="+ sPort + ")))(CONNECT_DATA=(SID="+ sSID + ")(SERVER=shared)))";

conStr = conStr + ";" + "User Id=" + sUser + ";Password=" + sPassword;

DbConnection oConn = UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateConnection();

oConn.ConnectionString = conStr;

oConn.Open();

 

Basically, these are the only changes in your code to establish an Oracle DataBase connection using ODP.NET.

 

happy migration!

  • Like 1
Link to comment
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue. Terms of Use