[c#] 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?

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

Basically in this case, System.Data.OracleClient need access to some of the oracle dll which are not part of .Net. Solutions:

• Install Oracle Client , and add bin location to Path environment varaible of windows OR
• Copy oraociicus10.dll (Basic-Lite version) or aociei10.dll (Basic version), oci.dll, orannzsbb10.dll and oraocci10.dll from oracle client installable folder to bin folder of application so that application is able to find required dll

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);
}
}

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 + ")");