none
How to access Oracle data base from C# RRS feed

  • Question

  • Hi ,

    I want to access Oracle database from my .Net application. I am using C# as code behind......

    Plz provide help....

    Thanks & Regards

    Ali.......

    Friday, July 20, 2007 5:48 AM

Answers

  • We have used the Oracle Data Provider (ODP.NET)

     

    We have then created a class for selecting/updating entries in table. 

     

    You need to set up the ConnectionString first.

    The Select method returns a DataTable.

    The Update method returns the number of rows that were updated - you need to pass the exact same SELECT statement as you entered into the Select method along with the DataTable that has been modified.  ODP.NET takes care of any changes.

     

    You need to add a reference to ODP.NET in your app then the code goes something like:

     

    using Oracle.DataAccess.Client;

    .

    .

    public static class OracleDatabase

    {

     

    private static string _connectionString;

    private static string ConnectionString

    {

    get

    {

    if (_connectionString == null)

    {

    throw new Exception("No connection string specified");

    }

    else

    {

    return _connectionString;

    }

    }

    set { OracleDatabase._connectionString = value; }

    }

     

    public static DataTable Select(string sql)

    {

    DataTable dt;

    OracleDataAdapter oda = null;

    OracleConnection conn = null;

    try

    {

    conn = new OracleConnection(ConnectionString);

    conn.Open();

    oda = new OracleDataAdapter(sql, conn);

    dt = new DataTable();

    oda.Fill(dt);

    return dt;

    }

    catch (Exception e)

    {

    throw new Exception("Error trying to select data: " + e.Message,e);

    }

    finally

    {

    if (oda != null)

    {

    oda.Dispose();

    }

    if (oda != null)

    {

    conn.Dispose();

    }

    }

    }

     

     

    public static int Update(string sql, DataTable d)

    {

    OracleDataAdapter oda = null;

    OracleCommandBuilder cb = null;

    OracleConnection conn = null;

    try

    {

    conn = new OracleConnection(ConnectionString);

    conn.Open();

    oda = new OracleDataAdapter(sql, conn);

    cb = new OracleCommandBuilder(oda);

    return oda.Update(d);

    }

    catch (Exception e)

    {

    throw new Exception("Error trying to update data: " + e.Message,e);

    }

    finally

    {

    if (cb != null)

    {

    cb.Dispose();

    }

    if (oda != null)

    {

    oda.Dispose();

    }

    if (conn != null)

    {

    conn.Dispose();

    }

    }

    }

    Good luck.

    Friday, July 20, 2007 10:56 AM

All replies

  • I am not working with Oracle and cannot provide Oracle specific-code, but next link contains general information how to do this

     

    http://support.microsoft.com/search/default.aspx?catalog=LCID%3D1033&spid=6242&query=how+to+oracle&adv=&ast=1%2C2%2C3&mode=a&cat=False&kt=ALL&title=false&mdt=&pwt=False&res=20&1033comm=1

    Friday, July 20, 2007 10:20 AM
    Moderator
  • We have used the Oracle Data Provider (ODP.NET)

     

    We have then created a class for selecting/updating entries in table. 

     

    You need to set up the ConnectionString first.

    The Select method returns a DataTable.

    The Update method returns the number of rows that were updated - you need to pass the exact same SELECT statement as you entered into the Select method along with the DataTable that has been modified.  ODP.NET takes care of any changes.

     

    You need to add a reference to ODP.NET in your app then the code goes something like:

     

    using Oracle.DataAccess.Client;

    .

    .

    public static class OracleDatabase

    {

     

    private static string _connectionString;

    private static string ConnectionString

    {

    get

    {

    if (_connectionString == null)

    {

    throw new Exception("No connection string specified");

    }

    else

    {

    return _connectionString;

    }

    }

    set { OracleDatabase._connectionString = value; }

    }

     

    public static DataTable Select(string sql)

    {

    DataTable dt;

    OracleDataAdapter oda = null;

    OracleConnection conn = null;

    try

    {

    conn = new OracleConnection(ConnectionString);

    conn.Open();

    oda = new OracleDataAdapter(sql, conn);

    dt = new DataTable();

    oda.Fill(dt);

    return dt;

    }

    catch (Exception e)

    {

    throw new Exception("Error trying to select data: " + e.Message,e);

    }

    finally

    {

    if (oda != null)

    {

    oda.Dispose();

    }

    if (oda != null)

    {

    conn.Dispose();

    }

    }

    }

     

     

    public static int Update(string sql, DataTable d)

    {

    OracleDataAdapter oda = null;

    OracleCommandBuilder cb = null;

    OracleConnection conn = null;

    try

    {

    conn = new OracleConnection(ConnectionString);

    conn.Open();

    oda = new OracleDataAdapter(sql, conn);

    cb = new OracleCommandBuilder(oda);

    return oda.Update(d);

    }

    catch (Exception e)

    {

    throw new Exception("Error trying to update data: " + e.Message,e);

    }

    finally

    {

    if (cb != null)

    {

    cb.Dispose();

    }

    if (oda != null)

    {

    oda.Dispose();

    }

    if (conn != null)

    {

    conn.Dispose();

    }

    }

    }

    Good luck.

    Friday, July 20, 2007 10:56 AM
  • Thanks Yar................

    Thanks & Regards

    Ali

    Wednesday, July 25, 2007 10:37 AM