Oracle Database Acess in .NET

I wanted to import a Oracle database backup to a test environment and used .NET to talk with it.

First import the database backup:
C:> imp system/orcl@odbdev file= dbbackup.dmp fromuser=orms touser=orms

Configure .NET Data Provider for Oracle:
Data Source: Oracle Database (Oracle Client);
Data Provider: .NET Framework Data Provider for Oracle;
Server Name: DBServer (This is the service name configured in tnsname.ora which is set using oracle client tools)
User Name:[User]
Password: [Password]

Connection String:

<add key="OracleDBConnString" value="user ID=[USER];Password=[PASSWORD];data source=ODBDEV;" />

Both Oracle and Microsoft make their Oracle data providers available for free. Microsoft Oracle data provider is available in .NET 1.1 and .NET 2.0 framework, but it still requires Oracle client software installed; Oracle Data Provider for .NET (ODP.NET) is included with the Oracle database installation. The recommendation is use ODP.NET since it's optimized for Oracle by Oracle, and we did find ODP.NET running faster than Microsoft's in our test.

To use ODP.NET, you need to add reference of Oracle.DataAccess.dll from GAC, import the name space, and the rest is identical to the regular ADO.NET with SQL Server:

using System.Configuration;
using Oracle.DataAccess.Client;

public class DAL
{
    public static DataTable GetAllUsers()
    {
         DataTable dtUser = new DataTable();
         string connString= ConfigurationSettings.AppSettings("OracleDBConnString");
         string sqlText = "SELECT * FROM Users";
         try
         {      
             using (OracleConnection conn = New OracleConnection(connString))
             {
                  OracleAdapter adapter= new OracleDataAdapter(sqlText, conn);
                  adapter.Fill(dtUser);
             }
         }
         catch (Exception ex)
         {
             ErrorLog.Write(ex);
         }

         return dtUser;
    }
}