using Odp.net to access Oracle databases in. NET (no client deployment method)Category: C # database2012-01-05 15:34 6330 people read reviews (1) favorite reports Oracle database. Netcomponentsdllodp.net is a database access class library provided by Oracle with guaranteed functionality and efficiency, as well as a very handy feature: On the client side, you can use the copy directly without having to install the Oracle client.
The following is reproduced from: http://blog.ywxyn.com/index.php/archives/326
Because Microsoft will deprecated System.Data.OracleClient.dll in. NET Framework4, and in terms of access efficiency and speed, System.Data.OracleClient.dll compared to Oracle.DataAccess.dll, Microsoft really does not have the advantage of Oracle-provided class libraries, so I gave up the use of System.Data.OracleClient.dll for years, instead of Odp.net. However, the advantages of odp.net are not only these, but also:
1. Oracle on the server is not available on the installation client (assuming application server is separate from DB server)
2, do not need to configure Tnsnames.ora file
Of course, the main reason I choose Odp.net is performance. This article lists the contrast between the two. Technical Comparison:ODP.NET Versus Microsoft OracleClient
I'll show you how to use Odp.net in a new project. Environment configuration: A machine, running C # program, no Oracle database installed or any Oracle products such as clients; B machine running a oracle9i database, no other Oracle products installed
To download the Odp.net file first, you can download Oracle Data Access Components (ODAC) Downloads on this page, and i downloaded Oracle 11g ODAC 11.1.0.7.20 with Oracle Devel Oper Tools for Visual Studio this release.
Do not install after download, will Oracle.DataAccess.dll file from odtwithodac1110720.zip\stage\components\oracle.ntoledb.odp_net_2\ 11.1.0.7.10\1\datafiles\filegroup4.jar the file, then copy it to the project and add the reference Oracle.DataAccess.dll.
Write the following code:
Using Oracle.DataAccess.Client;
...
String connstring =
"Data source= (description= (address= (protocol=tcp) (host=192.168.0.100) (port=1527))" +
"(Connect_data= (SID=ORCL))); User Id=sys; Password=sys; "; /This can also be placed in Web. config.
using (OracleConnection conn = new OracleConnection (connstring))
{
Conn. Open ();
String sql = "SELECT * from users";
using (OracleCommand comm = new OracleCommand (SQL, conn))
{
using (OracleDataReader RDR = Comm. ExecuteReader ())
{
while (RDR. Read ())
{
Console.WriteLine (RDR. GetString (0));
}
}
}
}
After the code is written, you also need to remove several DLL files from the downloaded compressed package.
1, Oci.dll (inside the jar file called ' Oci.dll.dbl ', take it out and remove it. Dbl in odtwithodac1110720.zip\stage\components\ oracle.rdbms.rsf.ic\ 11.1.0.7.0\1\datafiles\filegroup2.jar)
2, Oraociicus11.dll (in Odtwithodac1110720.zip\stage\components\ oracle.rdbms.ic\11.1.0.7.0\1\datafiles\ Filegroup3.jar)
3, OraOps11w.dll (in Odtwithodac1110720.zip\stage\components\ oracle.ntoledb.odp_net_2\11.1.0.7.10\1\datafiles\ Filegroup3.jar)
4, Oraons.dll (in Odtwithodac1110720.zip\stage\components\ Oracle.ons.ic\11.1.0.7.10\1\datafiles\filegroup1.jar)
The following three people say that need, some people say no need, anyway also not bad these three, continue it:
5, Orannzsbb11.dll (in odtwithodac1110720.zip\stage\components\oracle.ldap.rsf.ic\11.1.0.7.0\1\datafiles\ Filegroup1.jar)
6, Oraocci11.dll (in Odtwithodac1110720.zip\stage\components\ oracle.rdbms.rsf.ic\11.1.0.7.0\1\datafiles\ Filegroup3.jar)
7, Ociw32.dll (inside the jar file called ' Ociw32.dll.dbl ', take it out and remove it. Dbl in odtwithodac1110720.zip\stage\components\ Oracle.rdbms.rsf.ic\11.1.0.7.0\1\datafiles\filegroup2.jar)
Finally copy this DLL to the project, CS to and exe a folder, b/S has a dedicated bin directory.
Of course, the use of a new technology will inevitably encounter some errors: Here's what I met:
1, run the time to encounter this exception prompt ' The provider is not compatible with the version of the Oracle client ', do not be nervous, check the above used DLL is complete OK.
2. "The requested. Net Framework data provider was not found. may not be installed. "This error is because Oracle.DataAccess.dll is not found in Machine.config and the following code is placed in the
<DbProviderFactories></DbProviderFactories>
Ok between them.
<add name= "Oracle data Provider for. NET" invariant= "Oracle.DataAccess.Client" description= "Oracle data Provider for. NET "Type=" Oracle.DataAccess.Client.OracleClientFactory, Oracle.dataaccess, version=2.111.7.20, Culture=neutral, publickeytoken=89b483f429c47342 "/>
Note: If the download is not ODTwithODAC1110720, it is possible that the DLL's location is not as mentioned above, it is necessary to find themselves: (
The above is the basic method of use, there are a few points to note the place (part of the content reproduced from http://alderprogs.blogspot.com/2009/04/deploying-odpnet-with-oracle-instant.html):
1: In the DLLs listed above, Where Oracle.DataAccess.dll and OraOps11w.dll are odp.net corresponding files, the rest of the DLLs are oracleinstantclient corresponding files, odp.net and instantclient version must be consistent, otherwise error will occur , as long as the version is correct, you can download oracleinstantclient as required (because instantclient in several versions, Basic,basic_lite, etc.), and then replace these several DLL files
2: Each version of the ODTWITHODAC package, its DLL location is not the same, in the new version of the package, listed above the DLL, some DLLs have been removed (such as Orannzsbb11.dll), And Oracle.DataAccess.dll also according to the version of. NET is divided into several (in ODTwithODAC112030 for example, which contains 2.x and 4.0 two versions, respectively, corresponding to VS2005 and 2010, the development needs to take the corresponding package), when using the correct DLL file is taken
3: For the connection string, the above is used:
Data source= (description= (address= (protocol=tcp) (host=192.168.0.100) (port=1527)) (Connect_data= (SID=ORCL)))
This is a more typical tnsname notation, in addition to this notation, Odp.net also supports simply writing the data source as: [//]host[:p Ort][/service_name]
For example, the example above can be written as data SOUCE=192.168.0.100:1527/ORCL
In addition, here you can also refer to the configuration of the Tnsnames.ora in the connection (on the premise of Oracle installed), there are two methods: Set the environment variable tns_admin directly in the program, point it to Network\admin, or set the environment variable Oracle_ HOME, the program will automatically go to%oracle_home%\network\admin under Find Tnsnames.ora
4: Before database connection and query modification, there are some environment variable settings may affect the running results of the program, and if the machine has previously installed Oracle, some of its settings will affect the operation of the program, so, before the program opens the connection, you can use the following environment variables as appropriate To ensure that the results of the program run correctly (note that this part is reproduced from the English material, some of the variable values for the Chinese environment may have problems, please amend as appropriate)
Environment.setenvironmentvariable ("Ora_tzfile", null);
Environment.setenvironmentvariable ("Nls_lang", "American_america". Al32utf8 ");
Environment.setenvironmentvariable ("Nls_date_format", "DD-MON-RR");
Environment.setenvironmentvariable ("Nls_time_format", "HH.MI". Ssxff AM ");
Environment.setenvironmentvariable ("Nls_timestamp_format", "Dd-mon-rr hh.mi". Ssxff AM ");
Environment.setenvironmentvariable ("Nls_timestamp_tz_format", "Dd-mon-rr hh.mi". Ssxff AM TZR ");
Using Odp.net to access Oracle databases in. NET (no client deployment method)
