Querying Oracle with C# and odp.net

Oracle recommends odp.net (Oracle Data Provider for .net) for opening and managing connections to an Oracle Database via .NET development environment. Odp.net has its own connection and command objects as well as dataadapter and datareader structures optimized for Oracle database. Here you can find the simple way of connecting and running a command on an Oracle Database v10.2.0.3.

First of all, ODAC should be downloaded from Oracle 's odp.net download page. After installing the software according to the installation instructions you can follow the following snapshots to include .dll file in Microsoft Visual C# IDE. After that we are able to use the classes in that dll.




After importing the .dll file successfully you should easily use the classes.

/*
 * Defining variables
 */

// Connection Object 
private Oracle.DataAccess.Client.OracleConnection ocnn;

// Command object
private Oracle.DataAccess.Client.OracleCommand ocmd;

// Structures to manipulate data
private Oracle.DataAccess.Client.OracleDataAdapter oda;
private Oracle.DataAccess.Client.OracleDataReader odr;
private DataSet ods;


// ------------------------------------------------------------
// After all the definitions are made a sample try-catch block 
// that writes the sysdate to a label should be as follows
// ------------------------------------------------------------

try
    {
 // Creating the connection object
 ocnn = new Oracle.DataAccess.Client.OracleConnection();

 // Defining the connection string to connect to the specified database
 // User id and Password informations are obvious. Data Source value is the TNS alias of the database
 // There can be different values that can be used. Next example will cover another value.
 ocnn.ConnectionString = "User id=epeker;Password=********;Data Source=GRID_10.4.46.70";

 // Opening the connection and information is written as a label text
 ocnn.Open();
 lblerr.Text = "connected";

 // Now we need a command object that we want to execute on the database
 ocmd = new Oracle.DataAccess.Client.OracleCommand();

 // we defşne the previously created connection as the connection property of the command
 ocmd.Connection = ocnn;

 // commandText will be run on the database
 ocmd.CommandText = "select sysdate from dual";

 // with ExecuteReader() method of the command object we run the command and return the result to a reader object.
 odr = ocmd.ExecuteReader();

 // one or more rows could be fetched from the database
 // so we are readering from the reader of all rows
 while (odr.Read())
 {
     lblerr.Text = lblerr.Text + '-' + odr[0].ToString();
 }

    }
    catch (Exception ex)
    {
 // if an exception raises we catch it here and write to our info label
 lblerr.Text = ex.Message;
    }
    finally 
    { 
 // finally, we cleaning and tidying the memory and the database connections
 if (ocnn != null)
 {
   if (ocnn.State != ConnectionState.Closed)
   {
       ocnn.Close();
       ocnn = null;
   }
 }
    }


// ------------------------------------------------------------------------------------------------
// This block uses standart Net8 connection string instead of a TNS alias and also demonstrates
// the bind variable usage
// ------------------------------------------------------------------------------------------------

try
    {
 ocnn = new Oracle.DataAccess.Client.OracleConnection();
 ocnn.ConnectionString = "User id=epeker;Password=********;Data Source=(DESCRIPTION= " +
    " (ADDRESS= " +
    " (PROTOCOL=TCP)" +
    " (HOST=10.4.46.70)" +
    " (PORT=1521))" +    
    " (CONNECT_DATA=(SERVICE_NAME=GRID)))";
 ocnn.Open();
 lblerr.Text = "connected";
 
 ods = new DataSet();
 oda = new Oracle.DataAccess.Client.OracleDataAdapter();

 ocmd = new Oracle.DataAccess.Client.OracleCommand();                
 string str_sql = "select * from v$datafile where file# = :f_number ";

 ocmd.CommandType = CommandType.Text;
 ocmd.CommandText = str_sql;
 ocmd.Connection = ocnn;
 
 oda.SelectCommand = ocmd;
 
 // this parameter can also be set at the command level
 oda.SelectCommand.Parameters.Add("f_number",
   Oracle.DataAccess.Client.OracleDbType.Int32,15).Value=2;                

 // another difference is dataset object
 // in the previous example we used datareader to read from the database which fetches the data row by row basis
 // Fill() method of the dataadapter object fetches all the recordset to the client side at one time
 oda.Fill(ods);

 // i will not cover the details of the dataadapter and dataset here
 lblerr.Text = ods.Tables[0].Rows[0]["name"].ToString(); 
    }
    catch (Exception ex)
    {
 lblerr.Text = ex.Message;
    }
    finally
    {
 if (ocnn != null)
 {
     if (ocnn.State != ConnectionState.Closed)
     {
  ocnn.Close();
  ocnn = null;
     }
 }
    }

// ----------------------------------------------------------------------------
// last example demonstrates how to call a Oracle stored procedure with odp.net
// ----------------------------------------------------------------------------

try
    {
     ocnn = new Oracle.DataAccess.Client.OracleConnection();
 ocnn.ConnectionString = "User id=epeker;Password=********;Data Source=GRID_10.4.46.70;";
 ocnn.Open();
 lblerr.Text = "connected";

 oda = new Oracle.DataAccess.Client.OracleDataAdapter();
 ods = new DataSet();

 ocmd = new Oracle.DataAccess.Client.OracleCommand();
 ocmd.Connection = ocnn;
 ocmd.CommandText = "epeker.p_return_cursor";

 // this time command type is a StoredProcedure
 ocmd.CommandType = CommandType.StoredProcedure;

 // this time parameters have directions
 ocmd.Parameters.Add("c_return", 
   Oracle.DataAccess.Client.OracleDbType.RefCursor).Direction = ParameterDirection.Output;
 ocmd.Parameters.Add("i_var", 
   Oracle.DataAccess.Client.OracleDbType.Int32).Direction = ParameterDirection.Input;
 ocmd.Parameters["i_var"].Value = 2;

 oda.SelectCommand = ocmd;

 oda.Fill(ods);

 // gw_result is a gridview object of .NET framework
 // helps to view the recordset as a table
 gw_result.DataSource = ods.Tables[0];
 gw_result.Refresh();

    }
    catch (Exception ex)
    {
 lblerr.Text = ex.Message;
    }
    finally
    {
 if (ocnn != null)
 {
     if (ocnn.State != ConnectionState.Closed)
     {
  ocnn.Close();
  ocnn = null;
     }
 }
    }


/*
---------------------------------------------
--stored procedure used with the last example
---------------------------------------------

-- package needed for the ref_type declaration
CREATE OR REPLACE package EPEKER.pck_ref_type as 
  type ref_type is ref cursor; 
end pck_ref_type;

-- procedure itself
CREATE OR REPLACE procedure EPEKER.p_return_cursor 
  (c_return out epeker.pck_ref_type.ref_type, i_var in number)
is
begin

  open c_return for
    select * from v$datafile where file#=i_var;

  exception
    when others then
      rollback;

end p_return_cursor;
*/

Some other articles for .NET
odp.net homepage