Connecting to Oracle Database through C#?

I need to connect to a Oracle DB (external) through Visual Studio 2010. But I dont want to install Oracle on my machine. In my project I referenced: System.Data.OracleClient. But its not fulfilling the need. I have an "Oracle SQL Developer IDE" in which I run SQL queries against oracle db.

I have this code so far:

 private static string GetConnectionString()
    {
        String connString = "host= serverName;database=myDatabase;uid=userName;pwd=passWord";
        return connString;
    }

 private static void ConnectingToOracle()
    {
        string connectionString = GetConnectionString();
        using (OracleConnection connection = new OracleConnection())
        {
            connection.ConnectionString = connectionString;
            connection.Open();
            Console.WriteLine("State: {0}", connection.State);
            Console.WriteLine("ConnectionString: {0}",
                              connection.ConnectionString);

            OracleCommand command = connection.CreateCommand();
            string sql = "SELECT * FROM myTableName";
            command.CommandText = sql;

            OracleDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string myField = (string)reader["MYFIELD"];
                Console.WriteLine(myField);
            }
        }
    }

So far I read these blogs:

http://st-curriculum.oracle.com/tutorial/DBXETutorial/index.htm

http://blogs.msdn.com/b/kaevans/archive/2009/07/18/connecting-to-oracle-from-visual-studio.aspx

So far I have not downloaded anything from Oracle. What steps should I take to make this happen?

Solution 1:

First off you need to download and install ODP from this site http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

After installation add a reference of the assembly Oracle.DataAccess.dll.

Your are good to go after this.

using System; 
using Oracle.DataAccess.Client; 

class OraTest
{ 
    OracleConnection con; 
    void Connect() 
    { 
        con = new OracleConnection(); 
        con.ConnectionString = "User Id=<username>;Password=<password>;Data Source=<datasource>"; 
        con.Open(); 
        Console.WriteLine("Connected to Oracle" + con.ServerVersion); 
    }

    void Close() 
    {
        con.Close(); 
        con.Dispose(); 
    } 

    static void Main() 
    { 
        OraTest ot= new OraTest(); 
        ot.Connect(); 
        ot.Close(); 
    } 
}

Solution 2:

You can use Oracle.ManagedDataAccess NuGet package too (.NET >= 4.0, database >= 10g Release 2).

Solution 3:

Using Nuget

1. Right click Project, select Manage NuGet packages...
2. Select the Browse tab, search for Oracle and install Oracle.ManagedDataAccess

3. In code use the following command (Ctrl+. to automatically add the using directive).

4. Note the different DataSource string which in comparison to Java is different.

   // create connection
   OracleConnection con = new OracleConnection();
   
   // create connection string using builder
   OracleConnectionStringBuilder ocsb = new OracleConnectionStringBuilder();
   ocsb.Password = "autumn117";
   ocsb.UserID = "john";
   ocsb.DataSource = "database.url:port/databasename";
   
   // connect
   con.ConnectionString = ocsb.ConnectionString;
   con.Open();
   Console.WriteLine("Connection established (" + con.ServerVersion + ")");
   

Solution 4:

The next approach work to me with Visual Studio 2013 Update 4 1- From Solution Explorer right click on References then select add references 2- Assemblies > Framework > System.Data.OracleClient > OK and after that you free to add using System.Data.OracleClient in your application and deal with database like you do with Sql Server database except changing the prefix from Sql to Oracle as in SqlCommand become OracleCommand for example to link to Oracle XE

OracleConnection oraConnection = new OracleConnection(@"Data Source=XE; User ID=system; Password=*myPass*");
public void Open()
{
if (oraConnection.State != ConnectionState.Open)
{
oraConnection.Open();
}
}
public void Close()
{
if (oraConnection.State == ConnectionState.Open)
{
oraConnection.Close();
}}

and to execute some command like INSERT, UPDATE, or DELETE using stored procedure we can use the following method

public void ExecuteCMD(string storedProcedure, OracleParameter[] param)
{
OracleCommand oraCmd = new OracleCommand();
oraCmd,CommandType = CommandType.StoredProcedure;
oraCmd.CommandText = storedProcedure;
oraCmd.Connection = oraConnection;

if(param!=null)
{
oraCmd.Parameters.AddRange(param);
}
try
{
oraCmd.ExecuteNoneQuery();
}
catch (Exception)
{
MessageBox.Show("Sorry We've got Unknown Error","Connection Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}