Tuesday, October 13, 2009

Using the new ODP.Net to access Oracle from C# with simple deployment

http://splinter.com.au/blog/?p=156
Still tracking comments as of 13-Oct-2009

Microsoft has deprecated System.Data.OracleClient, so here's a simple example of using the new ODP.NET Oracle.DataAccess.Client, with the following benefits:
* Simple Xcopy deployment
* No Oracle [instant] client installation needed
* No TnsNames.Ora file needed

Firstly, download the ~200mb ODP.Net from Oracle. Don't freak out, you won't need to redistribute *all* this with your app, just ~30megs worth. I used this version:http://www.oracle.com/technology/software/tech/windows/odpnet/index1110710beta.htmlYou'll probably want to get the latest version from here:http://www.oracle.com/technology/tech/windows/odpnet/index.html

Grab Oracle.DataAccess.dll from ODAC1110710beta.zip\stage\Components\oracle.ntoledb.odp_net_2\11.1.0.7.10\1\DataFiles\filegroup4.jar. Copy it into the same folder as your project's Program.cs. In Visual Studio, right click references and add the Oracle.DataAccess.dll.

Write some code like this in your app:

using Oracle.DataAccess.Client; // This goes up the top
...
string connstring =
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1527))" +
"(CONNECT_DATA=(SID=mysid)));User Id=myuserid;Password=mypassword;";
using (OracleConnection conn = new OracleConnection(connstring))
{
conn.Open();
string sql = "select distinct owner from sys.all_objects order by owner";
using (OracleCommand comm = new OracleCommand(sql, conn))
{
using (OracleDataReader rdr = comm.ExecuteReader())
{
while (rdr.Read())
{
Console.WriteLine(rdr.GetString(0));
}
}
}
}


You'll then need the following dll's to be placed in the same folder as your EXE:
* oci.dll (called 'oci.dll.dbl' in ODAC1110710beta.zip\stage\Components\ oracle.rdbms.rsf.ic\11.1.0.7.0\1\DataFiles\filegroup2.jar)
* Oracle.DataAccess.dll (in ODAC1110710beta.zip\stage\Components\ oracle.ntoledb.odp_net_2\11.1.0.7.10\1\DataFiles\filegroup4.jar)
* oraociicus11.dll (in ODAC1110710beta.zip\stage\Components\ oracle.rdbms.ic\11.1.0.7.0\1\DataFiles\filegroup3.jar)
* OraOps11w.dll (in ODAC1110710beta.zip\stage\Components\ oracle.ntoledb.odp_net_2\11.1.0.7.10\1\DataFiles\filegroup3.jar)

You may need the following dll's, but I didn't. I'd get them anyway, just to be safe, as some people say they're needed:
* orannzsbb11.dll (in ODAC1110710beta.zip\stage\Components\oracle.ldap.rsf.ic\11.1.0.7.0\1\DataFiles\filegroup1.jar)
* oraocci11.dll (in ODAC1110710beta.zip\stage\Components\ oracle.rdbms.rsf.ic\11.1.0.7.0\1\DataFiles\filegroup3.jar)
* ociw32.dll (called 'ociw32.dll.dbl' in ODAC1110710beta.zip\stage\Components\ oracle.rdbms.rsf.ic\11.1.0.7.0\1\DataFiles\filegroup2.jar)

If you get the exception 'The provider is not compatible with the version of Oracle client', don't stress, simply retrace your steps and make sure you get ALL those DLL's from the same ODP.Net / ODAC distribution to avoid version number conflicts, and put them all in the same folder as your EXE.

If you download a newer version of ODP.Net, the jar files that enclose the dll's may have moved, so you'll have to manually search through them all, don't worry it won't take *too* long!

Good luck!

Comments:

21-Aug-2009
Don't forget to rename the ".dll.dbl" files to ".dll"; otherwise you'll get the exception.Thank you Chris. This was very helpful.

11-Sep-2009
Thankyou Chris, this solution is great and works really well. In fact, my multithreaded app (which does a LOT of queries) performs 57% faster when using your redist pack

7-Oct-2009

Hi Chris, thanks for the tip. this is really great.

I do however have a question, we only include the dll and not referencing it through the project, i.e. but instantiate it using the System.Data.provider factories, then we do need to use gacutil to install it in the global assemblycache. Is that correct?

SO as an example if I refer this dll in my config as:
Now if I call using the .NET standard classes (System.Data), I can instantiate the oracle connection/command etc by getting its provider factory through below call:

DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[_dbName].ProviderName);

where _dbName just some variable of the database name.

However, this means that the DLL stills needs to be installed in the GAC using gacutil.exe.
THanks in advance for reading ths.

7-Oct-2009
Hi 'Kts',I believe you're on the right track there. That does give you the flexibility to use the same code for any database (sql, oracle, postgres, etc). But i think you'd then have to work with the System.Data.Common.* types (eg System.Data.Common.DbConnection). These types are a bit more generic and may have less of the oracle-specific data types, however if you can get it to work then its a great idea!Also, i'm personally not a fan of the GAC but if it works for you then go for it.

13-Oct-2009

Hi ChrisThanks for the informative write up. I downloaded the XCOPY version of Oracle 11g ODAC 11.1.0.7.20 and with that the only large file is the oraociei11.dll which is 114MB and looks like that is reqd in the EXE folder. Did you notice the same thing ?
thanksSunit

13-Oct-2009
Hi Sunit,I wasn't aware of an XCOPY version. Give it a try with only that file, maybe it'll work? Let us know if it works.114megs is pretty big though, the files i described above are only ~30megs.Still, maybe it supports features that my bare-bones method doesn't. Could be worth remembering!Cheers

No comments:

Post a Comment