Connecting to an Oracle database using .Net and Oracle's 11G Client

by Heathesh 22. December 2009 20:05

Firstly, if you don't have the Oracle 11G client you will need to download it here (WARNING: The download is about 487MB):
http://download.oracle.com/otn/nt/oracle11g/win32_11gR1_client.zip

You may have to register on Oracle's site to be able to download it, a bit of a bummer but it is necessary.

Once you've downloaded it, I would advise installing the "Administrator" version of the client, as this will give you all the tools etc. you required to work with Oracle.

After it's been installed, you will need to setup your TNSNAMES.ORA file to include any and all databases you will be connecting to. You can find your TNSNAMES.ORA file in the following folder:

    [Your chosen or set installation folder]\product\11.1.0\client_1\network\admin

If you don't see a TNSNAMES.ORA file there, don't worry, simply create a new text document and call it "TNSNAMES.ORA". If you don't have a TNSNAMES.ORA file or don't want to use it, you will still be able to connect to the database but your connection string will need to look something like this (I personally prefer using the TNSNAMES file):

    Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;

Now, although I have worked with Oracle extensively I have never written a TNSNAMES.ORA file or entry from scratch, I normally ask a DBA or someone who can already connect to the relevant database to either send me the TNSNAMES.ORA entry for the relevant database, or to send me their TNSNAMES.ORA file. A normal entry in the TNSNAMES.ORA looks like so:

    # USE HASH FOR COMMENTS
    ORACLE_SID=
      (DESCRIPTION =
        (FAILOVER = ON)
        (LOAD_BALANCE = ON)
        (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1522))
        (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1522))
        (CONNECT_DATA =
          (SERVICE_NAME = ORACLE_SERVICE_NAME)
          (SERVER = DEDICATED)
          (FAILOVER_MODE =
            (TYPE = session)
            (METHOD = basic)
            (DELAY = 5)
            (RETRIES = 100)
          )
        )
      )


Of course all the info in the example is garbage, but you should get the basic idea. Once you've got your TNSNAMES.ORA entry setup, you will be able to connect to the database by doing the following:

1. In your Visual Studio project, add a reference (found under the .Net tab) to "Oracle.DataAccess". This should have been installed when you installed the "Administrator" version of the Oracle client.
2. Next add the following using to your code:

    using Oracle.DataAccess.Client;

3. You should then be able to connect to your database using the following code (depending on whether or not you created your TNSNAMES.ORA file, your connection string might be the longer one indicated above):

    using (OracleConnection connection = new OracleConnection("Data Source=ORACLE_SID;User Id=user;Password=password;"))
    {
        connection.Open();

        using (OracleCommand command = new OracleCommand())
        {
            //ADD CODE TO RUN RELEVANT COMMAND
        }

        connection.Close();
    }


That should be it, happy coding!

Tags: , , ,

Development | .Net | Oracle

Comments are closed



Powered by BlogEngine.NET 1.5.0.7 (with enhancements by Heathesh)
Theme by Mads Kristensen (with tweeks by Heathesh)

Certifications

Microsoft Certified Professional

Microsoft Certified Technology Specialist

Answer Questions

 

Tag cloud

Calendar

<<  July 2017  >>
MoTuWeThFrSaSu
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar

http://heathesh.com