C # Remote connection to Oracle database (via System.Data.OracleClient)

To connect to an Oracle database using the System.Data.OracleClient library in. Net

1. Download and install the instant Client

Instant client can be downloaded to the Oracle website at

There are three versions of the Windows,instant client:

1) Instant Client for Microsoft Windows (32-bit)

2) Instant Client for Microsoft Windows (64-bit) Itanium

3) Instant Client for Microsoft Windows (x64)

You can see your computer's processor information by clicking Properties in the right-click menu on My Computer

Since my computer is a Win7 64-bit system, I downloaded a third package

Once the download is complete, unzip and add the address of the bin directory inside to the environment variable path.

Then add the following attribute to the environment variable:

(I put the extracted path to the D drive, the extracted folder is named Instantclient_12_1)

Nls_lang=simplified Chinese_china. Zhs16gbk

Tns_admin=d:\instantclient_12_1

Ld_library_path=d:\instantclient_12_1

2. Redhat system with Oracle installed with XSHELL4 remote connection

You need to enter the following:

1) Name: Make a name for yourself

2) Protocol: Select SSH

3) Host: Destination Host IP

4) port number: port (22)

5) login username/password

Attached: the command to log in to the database in Linux is

Sqlplus User name/password @ database name

3. Get the connection string

Find an Oracle installation address

Linux command: Echo $ORACLE _home

Tnsnames.ora is located

$ORACLE _home\network\admin

After you open Tnsnames.ora with Vim, you see that the Oracle database is configured as follows:

XTCSJK = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 171.0.0.132) (PORT = 1521)) (Connect_data =            (SERVER = dedicated)     (service_name = TestDB)) )

Based on this file, you can write the connection string that connects to the Oracle database.

data source=     (description=         (ADDRESS _list=             (ADDRESS=                  (PROTOCOL=TCP)                   (host=171.0.0.132)                   (port=1521)              )         )          (connect_data=              (SERVICE_NAME=TESTDB)         )      ); persist security info=true; user id= user name; password= Password

4. Program code

Note: Running the following code requires

1) Manually add a reference to the library System.Data.OracleClient

2) in the assembly → right-click → properties → build, change the target platform from AnyCPU to x64

Otherwise, the exception will be reported badimageformatexception (compatibility issue)

3) If you do not complete the installation of the instant client or the configuration of the environment variable, the runtime will report the exception information:

System.Data.OracleClient requires Oracle client software version 8.1.7 or later

Using system;using system.collections.generic;using system.linq;using system.text;using  system.threading.tasks;//method1 the library that the function calls, you need to add the reference manually using system.data.oracleclient;namespace  oracletest{    class program    {         //read time information from the Oracle database         static void  Readfromoracletest ()         {             string conn = string. Concat (                           @ "Data source=",                            @ "     (description=",                            @ "         (address_list=",                            @ "             (address= ",                           @ "                  (protocol=tcp) ",                             @ "                 (host=171.0.0.132) ", &NBsp;                          @ "                  (port=1521) ",                           @ "             ) ",                            @ "        ",                           @ "          (connect_data= ",                           @ "              (SERVICE_NAME=TESTDB) ",                            @ "        ",                            @ "    );,                           @ "Persist Security  Info=True; ",                           @ "user id= user name;",                           @ " password= Password "                           );             //OracleConnection  is marked as obsolete              oracleconnection oc = new oracleconnection (conn);             try             {                 oc. Open ();                 // oraclecommand  is marked as obsolete                  oracLecommand cmd = oc. CreateCommand ();                 cmd.CommandText =  "Select sysdate from dual";                 oracledatareader odr = cmd. ExecuteReader ();                 while  (ODR. Read ())                 {                      console.writeline (ODR. Getoracledatetime (0). ToString ());                 }             }             catch  (Exception ex)             {                  Console.WriteLine (ex. Message);            }             finally             {                 oc. Close ();            }         }        static void main (string[]  args)         {             readfromoracletest ();             consolE.readline ();         }    }} 

5. Running the sample

END