Mobilize.Net Staff OlmanQuesada Posted March 22, 2018 Mobilize.Net Staff Report Share Posted March 22, 2018 Oracle Data provider (ODP.Net) in a migrated project 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! 1 Link to comment Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now