| 

.NET C# Java Javascript Exception

14

Oracle and .NET: Tales from the dark ages

Each time when I tried to load data from an Oracle database it was a pretty terrible experience.

I remember that I struggle to find the right Oracle driver and even when everything was installed the strange TNS ora config file popped up and nothing worked.

It can be simple…

2 weeks ago I had the pleasure to load some data from a Oracle database and discovered something beautiful: Actually, I can be pretty simple today.

The way to success:

1. Just ignore the System.Data.OracleClient-Namespace

The implementation is pretty old and if you go this route you will end up with the terrible “Oracle driver/tns.ora”-chaos mentioned above.

2. Use the Oracle.ManagedDataAccess:

Just install the official NuGet package and you are done. The single .dll contains all the bits to connect to an Oracle database. No driver installation additional software is needed. Yay!

The NuGet package will add some config entries in your web.config or app.config. I will cover this in the section below.

3. Use sane ConnectionStrings:

Instead of the wild Oracle TNS config stuff, just use (a more or less) sane ConnectionString.

You can either just use the same configuration you would normally do in the TNS file, like this:

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;

Or use the even simpler “easy connect name schema” like this:

Data Source=username/password@myserver//instancename;

DbProviderFactories & ODP.NET

As I mentioned earlier after the installation your web or app.config might look different.

The most interesting addition is the registration in the DbProviderFactories-section:

...
<system.data>
 <DbProviderFactories>
 <remove invariant="Oracle.ManagedDataAccess.Client"/>
 <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver"
 type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
 </DbProviderFactories>
 </system.data>
...

I covered this topic a while ago in an older blogpost, but to keep it simple: It also works for Oracle!

		private static void OracleTest()
 {
 string constr = "Data Source=localhost;User Id=...;Password=...;";

 DbProviderFactory factory = DbProviderFactories.GetFactory("Oracle.ManagedDataAccess.Client");

 using (DbConnection conn = factory.CreateConnection())
 {
 try
 {
 conn.ConnectionString = constr;
 conn.Open();

 using (DbCommand dbcmd = conn.CreateCommand())
 {
 dbcmd.CommandType = CommandType.Text;
 dbcmd.CommandText = "select name, address from contacts WHERE UPPER(name) Like UPPER('%' || :name || '%') ";

 var dbParam = dbcmd.CreateParameter();
 // prefix with : possible, but @ will be result in an error
 dbParam.ParameterName = "name";
 dbParam.Value = "foobar";

 dbcmd.Parameters.Add(dbParam);

 using (DbDataReader dbrdr = dbcmd.ExecuteReader())
 {
 while (dbrdr.Read())
 {
 Console.WriteLine(dbrdr[0]);
 }
 }
 }
 }
 catch (Exception ex)
 {
 Console.WriteLine(ex.Message);
 Console.WriteLine(ex.StackTrace);
 }
 }
 }

MSSQL, MySql and Oracle - via DbProviderFactories

The above code is a snippet from my larger sample demo covering MSSQL, MySQL and Oracle. If you are interested just check this demo on GitHub.

Each SQL-Syntax teats parameter a bit different, so make sure you use the correct syntax for your target database.

Bottom line

Accessing a Oracle database from .NET doesn’t need to be a pain nowadays.

Be aware that the ODP.NET provider might surface higher level APIs to work with Oracle databases. The dbProviderfactory-approach helped us for our simple “just load some data”-scenario.

Hope this helps.

.net web csharp asp.net javascript html5 asp.net-mvc jquery css iis
Weitere News:
10 Meinungen
0
It is really good to see that finally, I got the answer of which I searching for a while, actually, I have been searching a website design company who offer affordable logo design packages as well because my brother needs to start their own business as soon as possible so I decided to search a company for him.
0
Incredible detail! I like it you each post moreover, share it to my friends. An obligation of appreciation is all together for sharing such a wonderful read. Logo Design For Business
0
Such a decent sort of stuff here, and I have additionally bookmarked it for more updates, You got a very valuable post I've been directed here perusing in the midst of for an hour or close and I much like it this news. Socks Savile Row
0
The SQL Server dialect of SQL is called T-SQL, or Transact SQL. It adheres to the ANSI standard fairly success coursework writing well. It is fully ACID compliant, and Microsoft has been quite aggressive in recent years to continue to add features to stay aggressive with different RDBMSs in addition to the basic elements that practically every RDBMS has.
0
Explained Well in detail. It is something I was searching for this to understand things easily. Thank you for sharing such an awesome post.
Water Treatment Equipment Supplier
0
My name is Bao Fang and i am the CEO at Fedic Crafts Co.Ltd. It is the leading manufacturer of promotional gifts and products like, The badges, keychains, medals, fridge magnet and others, comes in a variety of shapes, sizes and colors.
We also make customized products as per you requirements like custom refrigerator magnets, Custom metal keyrings, Lapel pin badges,Promotional fridge magnets
0
This post helped me a lot thanks, We are a leading Metal injection mold parts suppliers, we’re offering a wide array of manual machines that can be highly used for R&D purposes and secondary operations whenever needed.
0
Very informative thanks, We have specialized in the manufacturing of high-quality products and is presenting superlative water pump sets, an engine for marine, an engine for diesel generators, and etc. China diesel generator set
0
Thanks for resolving this issue, We are the leading water treatment chemicals supplier and manufacturer of China. Being a water treatment chemicals supplier and manufacturer, we take it as our priority to make and deliver high-quality chemicals to all our customers. So, if you are looking for the best water treatment chemicals manufacturers or a water treatment chemicals supplier, then we welcome you.
Schreibe einen Kommentar:
Themen:
iis css jquery asp.net-mvc html5 javascript asp.net csharp web .net
Entweder einloggen... ...oder ohne Wartezeit registrieren
Benutzername
Passwort
Passwort wiederholen
E-Mail
TOP TECHNOLOGIES CONSULTING GmbH