. Net connection to oracle

C #. net connection to oracle

This article describes how C #. net connects to the Oracle database.
. Net programming we usually use SQL Server databases, but sometimes we have to use. net + Oracle's weird combination. Microsoft and Oracle provide their own support libraries. Two different methods are described here.
In addition, this article provides a method to remove a program from the Oracle client, which is more practical in the windows form application to be released to the customer.

The first method is supported by Microsoft's. net framework.

To use this method, first add the reference "System. data. oracleClient ", Note: If your solution target framework is ". NET Framework 4 Client Proflie ", you cannot add this reference, because OracleClient support is no longer provided in this target Framework. In addition, Microsoft does not recommend using this reference, because OracleClient will not be supported in future. NET Framework.

Let's talk less about it. Let's see how to use it. After adding a reference, you can reference namespace: using System. Data. OracleClient;
Use the following code

Oracle. cs

// Set the database connection OracleConnection conn = new OracleConnection ("Data Source = [Data Source]; User Id = [User name]; Password = [Password];"); string strSQL = "[SQL statement to be executed]"; OracleCommand myComm = new OracleCommand (strSQL, conn); try {conn. open (); // create an OracleDataReader object to connect to the form OracleDataReader myRead = myComm. executeReader (); // Business Code // close the reader object myRead. close (); // Close the connection conn. close ();}

The above data source is the name of the oracle connection configured in the local oracle database. You can use the oracle Connection Manager for configuration or directly edit the tnsnames. ora file of the oracle client. For example:

TESTLINK =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 128.1.2.42)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = orcl)    )  )

In this example, TESTLINK is the data source name.

Method 2: Oracle DataAccess Client provided by Oracle

To use this method, go to the Oracle homepage to download ODAC for. net. Download. A total of 211 MB.
After the download is complete, decompress the package and install it. Then you can add the reference using Oracle. DataAccess. Client in your solution;
The specific usage is similar to the previous one. As follows:

Oracle. cs

String connstring = "Data Source = [Data Source]; User Id = [User name]; Password = [Password];"; using (OracleConnection conn = new OracleConnection (connstring) {conn. open (); string strSQL = "[SQL statement]"; using (OracleCommand comm = new OracleCommand (strSQL, conn) {using (OracleDataReader rdr = comm. executeReader () {// if (! Rdr. hasRows) {MessageBox. show ("this database is empty"); return;} // cyclically traverses the result set while (rdr. read () {// Business Code} // close the rdr of the reader object. close () ;}// Close the connection conn. close ();}

Although this method does not have to worry about losing support in the future, we will certainly feel the trouble, because in addition to the oracle client, we also need to install an ODAC. In this case, the first method is quite troublesome, because it is also inseparable from the support of the Oracle client. We always need to configure the database connection with the Oracle client before using our program. If there are many machines to deploy, this is really a tedious task.

Free from worries about configuring data sources

We can observe what the data source has done. It specifies the real URL, port number, database name, and other information. So can we tell the client in the connection string that we don't need to configure the data source? In fact, this is indeed the case. Change the connection string in the above example to the following format:

Oracle. cs

"Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = [real URL]) (PORT = [PORT number]) "+" (CONNECT_DATA = (SID = [database name]); User Id = [User name]; Password = [Password]; ";

Re-compile and execute the program and find that the connection is normal! It is more comfortable than the previous two examples. This not only reduces the workload of configuring data sources. In this way, we can provide additional functions such as connecting to a temporary database based on the database address entered on the interface.
But don't worry. The problem still exists. If this program is a client software to be released to the client's computer for running, does it mean that the client does not have a computer to install an Oracle client? This is obviously not a good idea...

Remove the program from the Oracle client!

Let's recall how JAVA connects to Oracle. An ojdbc14.jar solves all the problems. What annoying Oracle client is irrelevant to me. So since JAVA can, it is unreasonable. net. How many classes of the 200 MB large ODAC are directly used to connect to the database? Why is the JDBC driver a little bit while the. net driver?
In fact, there is really no need for so many things. The actually used dynamic link libraries are just a few MB.
Let's see how it works. First, decompress odtwithodac111_unzip zip. Now we get a lot of jar packages. Select All jar packages and decompress them all .....
Now you get countless dll dynamic connection library files. Search for the dynamic connection library file with the following file names:

oci.dlloraociicus11.dllorannzsbb11.dllOracle.DataAccess.dllOraOps11.dll

Copy these files to the directory where the compiled executable files are located, and then package and release them. Try a computer without an oracle client!

Test your database on a computer without an Oracle client

Sometimes, a problem occurs after the program is released, and the database cannot be connected. You want to determine whether the database connection problem exists or whether your program is faulty. But the customer's machine does not have an Oracle client installed. What should I do? Although you can also bring a 40 MB real-time client to your portable mailbox, it is always troublesome.
Here is a simple method.

There is also

　　orasqlplusic11.dll

Copy this file from the Oracle client.

sqlplus.exe

Put them together in the directory where your executable files are located. Now you can use the SQL * Plus command line to test your database on a client that does not have an Oracle client installed.