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
