Several ways to connect to Oracle

How To reference Data.OracleClient.dll

Since from. NET 4.0, Microsoft has removed the OracleClient.dll from the framework, so to use, it needs to be in VS2010 to change the project's. NET Framework from. NET 4.0 to 3.5, solution-by-reference, add Reference, and then in the. NET can find System.Data.OracleClient.dll, click OK, OK.

This can always be used, in fact, the bad news began:

One: Via System.Data.OracleClient (need to install Oracle client and configure Tnsnames.ora)

Local use of Oracle refers to connecting and using the Oracle database in the host where the Oracle database is installed. This approach is undesirable from the standpoint of security and load balancing. This is used only as an experiment.

There is actually no difference between the locally connected code and the remote connection, except that its data source points to the client Network service name on the server side. For the database installation of this article, it points to the network service name defined in the E:\Oracle_Server\oracle\ora92\network\admin\tnsnames.ora file (see "Oracle Client Installation and remote connection configuration "article).

For this type of local connection, there can be no standalone installation of client tools on the server side, otherwise the data source will only go to match the standalone client's Tnsnames.ora file, even if the corresponding network service name is not found, and will no longer match the server-side service name. It's not known. NET component, or intentionally, to encourage remote connection and use of Oracle.

The main code to establish the connection is as follows:

......

Using System.Data.OracleClient;

......

The "Remotedb" here corresponds to the Client Network service name configured in the "Installation and remote connection configuration for Oracle Client" article

    OracleConnection conn=         new OracleConnection ("data source=remotedb; User Id=scott; Password=scott; " );    Conn. Open ();    ......

1. Add a namespace System.Data.OracleClient reference
2. Using System.Data.OracleClient;
3.

string " User Id=ifsapp; Password=ifsapp;data Source=race; "  New  OracleConnection (connstring); Try {    Conn. Open ();    MessageBox.Show (Conn. State.tostring ());} Catch (Exception ex) {    showerrormessage (ex). Message.tostring ());} finally {    Conn. Close ();}

Two: Via System.Data.OracleClient (need to install Oracle client without configuration Tnsnames.ora)

Connect to Oracle using the Client Network service name

Based on the hierarchical needs of business logic and database entities, it is generally required to connect and use the Oracle database in a client machine different from the Oracle database host. This situation is either through the client access to the database through the C/s application, or in the B/s application, remote connection to the Oracle database server on the Web server side.

Connecting to Oracle remotely via the Client Network service name requires that the Oracle Client tool be installed in the client machine (the installation type can be installed without selecting "Administrator" mode, but only with runtime support, providing basic network service configuration tools for the application, etc.).

The statement that establishes the connection is simple, the key point is the setting of the data source. The data source here does not refer to the database name in the SQL Server connection string, but rather to the client Network service name (see the previous article on client installation for the relevant content). For the Oracle installation of this article, the data source corresponds to the E:\Oracle_Client\oracle\ora92\network\admin\ The name of the network service in the Tnsnames.ora configuration file (the database and the standalone client tools are installed in different directories on the same machine for easy experimentation). When a connection is implemented, the corresponding entry in the Client Network service configuration file is looked up according to the value of data source to obtain connection information such as the database server host address, port, global database name, and so on.

1. Add a namespace System.Data.OracleClient reference
2. Using System.Data.OracleClient;
3.

 stringConnString ="User Id=system; Password=123;data source= (DESCRIPTION = (address_list= (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521)) (Conne Ct_data = (service_name = ORCL )))"; using(OracleConnection conn =NewOracleConnection (connstring)) {Conn.                Open (); using(OracleCommand cmd =NewOracleCommand ("SELECT * FROM Student", conn)) {OracleDataAdapter Sdat=NewOracleDataAdapter (CMD); DataSet DS=NewDataSet (); Sdat. Fill (DS,"P"); Dgvdata.datasource=ds. tables["P"]; }            }

three : Through System.Data.OleDb and Microsoft's Oracle driver

1. Add a namespace System.Data.OracleClient reference
2. Using System.Data.OleDb;
3.

string " provider=msdaora.1; User Id=ifsapp; Password=ifsapp;data source= (DESCRIPTION = (address_list= (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))) (CO Nnect_data = (service_name = RACE)))"new  OleDbConnection (connstring); Try {    Conn. Open ();    MessageBox.Show (Conn. State.tostring ());} Catch (Exception ex) {    showerrormessage (ex). Message.tostring ());} finally {    Conn. Close ();}

Note:
A.XP operating system has installed Microsoft's Oracle driver C:\Program Files\Common Files\System\Ole Db\msdaora.dll
B. This driver requires three files from the Oracle client (Oraocixe10.dll, Oci.dll, Ociw32.dll) to be placed under System32

Four: Connect using ODP
1. Download and install Odp.net (http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html)
2. When the installation is complete, a sequence of files is generated.
3. Locate the installation directory, open the folder%oracle_home%\network\admin below this to create a Tnsnames.ora file, its contents can be referenced under the sample directory below the configuration
Oracle.race =
(description=
(address_list=
(address=
(PROTOCOL=TCP)
(host=127.0.0.1)
(port=1521)
)
)
(Connect_data=
(Sid=race)
(server=dedicated)
)
)
Oracle.race is the name of the connection string and can be arbitrarily taken. The string after the equals sign can be copied in the TNS descriptor after the database is connected in the Enterprise Manager Console tool.
4. Referencing the Oracle.dataaccess namespace
5. Using Oracle.DataAccess.Client;
6. Sample code:
String connstring = "DATA source=oracle.race; PERSIST SECURITY info=true; USER Id=ifsapp;password=ifsapp ";
OracleConnection conn = new OracleConnection (connstring);
Try
{
Conn. Open ();
OracleCommand cmd = new OracleCommand (cmdtext,conn);
OracleDataReader reader = cmd. ExecuteReader ();
This. Datagridview1.datasource = reader;
This. Datagridview1.databind ();
}
catch (Exception ex)
{
Showerrormessage (ex. Message.tostring ());
}
Finally
{
Conn. Close ();
}

V: Using third-party drivers
Third party driver has devart, download drive http://www.devart.com/dotconnect/oracle/, but is commercial version, need to purchase license or hack
Connection format User Id=myusername; Password=mypassword; Host=ora; Pooling=true; Min Pool size=0; Max Pool size=100; Connection lifetime=0;
1. Referencing the Devart.Data.Oracle namespace
2. Using Devart.Data.Oracle;
3.
OracleConnection conn = new OracleConnection ();
Conn. ConnectionString = "";
Conn. Unicode = true;
Conn. UserId = "Ifsapp";
Conn. Password = "Ifsapp";
Conn. Port = 1521;
Conn. Server = "127.0.0.1";
Conn. Sid = "RACE";
Try
{
Conn. Open ();
Execute queries, etc
}
catch (Exception ex)
{
Showerrormessage (ex. Message.tostring ());
}
Finally
{
Conn. Close ();
}

Several ways to connect to Oracle