. Net Program remote connection to Oracle Database

. Net remote connection to the Oracle database seems simple, but it still encountered many problems in practice. Share with you the experience I have learned about this thing today.

There are many ways to access the oracle database. I provide two methods: OleDbConnection and. NET Managed Provider for Oracle. The Code is as follows:

1. Connection code

Use OleDbConnection as follows:

_OleDbConnection = new OleDbConnection(connectionString);_OleDbConnection.Open();

The OracleConnection connection method is as follows:

_OracleConnection = new OracleConnection(connectionString);_OracleConnection.Open();

Note that OleDbConnection does not support Oracle 9i data types such as Clob. OracleConnection must be used.

See INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider

If OracleConnection is used, you must install. NET Managed Provider for Oracle.

.NET Managed Provider for Oracle

2. connection string

The remote connection string can be written in this way.

Connection string of OleDbConnection:

Provider = MSDAORA; host = 192.168.1.1; data source = MyTest; user id = system; password = xxx

Provider = MSDAORA; indicates that the oledb provider is an oracle database.

Host is the IP address of the server where the remote oracle database is located. If the Port is not the default 1521 Port, add Port = xxxx;

Data source is the name of the oracle database to be accessed

User id and password.

 

. NET Managed Provider for Oracle connection string:

Server = 192.168.1.1; data source = MyTest; user id = system; password = xxx

 

 

3. host firewall settings

My Oracle is installed on windows 2003 server. To ensure security, you must set a firewall.

I opened port 2003 in windows 1521 and found that I could not connect to the database. I captured the packet and checked that the oracle client connected to port 1521 again.

After checking the information, oracle's network listener only plays an intermediary role. When the customer connects to it, it finds the corresponding database instance process based on the configuration, and then allocates a new database connection, the connection port is transmitted to the client by the network listener. After that, the client no longer deals with the network listener, but with the oracle.exe process. This new connection port is unpredictable and will be blocked by the firewall.

To solve this problem, we can find a way to use shared sockets. This method should be a common method. For details, see

Enabling Oracle service port connection 2003 TNS timeout on windows server 1521 system firewall

However, I am a little lazy, because I only use windows Firewall, windows Firewall can open all ports to a process. Therefore, you only need to configure the windows Firewall as shown in the following figure to access the firewall.

 

 

 

 

4. Character Set of the client and server

Garbled characters are displayed when Chinese characters are inserted. You need to set both the client and the server's NLS_LANG

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

Windows

Modify registry

NLS_LANG (9i) under HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOME0)
NLS_LANG (10g) under HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_OraDb10g_home1)