| 

.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
29 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.
0
Hello! If you happen to look for writing help then it's time to apply to the essay writing service for help where you can get all the necessary information quickly. For instance, you can find there a piece of information about nursing essay writing service
0
Nice piece of information.
Sumi-tech, as an invar alloy machining parts manufacturer, is a recognized leader in high-performance specialty alloy based materials and process solutions for applications in consumer electronics, medical, industrial, energy, transportation, defense and aerospace markets.
0
Thanks for posting this helped me a lot, We are one of the leading LED Spot Light Supplier, we provide high-quality lights with well-assembled chips.
0
Glad to go through this post. I was searching for this problem to get it solved asap. I am going to share this post with a few friends who want help.
Microwavable Plastic Containers with Lids
0
Great discussion and Organic Moisturizing Face Cream, we have professionals and experienced team of researchers and skin specialists working to formulate skin care and hair products which are internationally tested to ensure the quality of the products.
0
Tutu Appis the one of the best ios,android App store to get the tons of free app and game. Here the latest version of TutuApp of free.
0
The code you provided helped us a lot thankyou, If you are looking for the best quality makeup products in the best prices then you are at the right place Makeup brush manufacturer
0
I was looking for this code thanks for providing, We deal in all types of trailer food trucks, especially Food trucks, we are a leading Food truck manufacturers in China.
0
Great post thanks for sharing, Best quality Hospital overbed table are manufactured here at Tuocheng tech
0
Useful code, Thanks.Best quality plastic food containers wholesale are available here at Huan Xiang Co., Ltd
0
Your post really helpful for me I found my database error through your Blog post, The username was incorrect in MySQL, If you need regarding to coursework writing help so just contact us
0
Resolved my issue finally with the help of this, As a no.1 voltage protector manufacturer we guarantee that your electrical equipment remains save.
0
I was searching for this code for a long time, Thank you. Install the latest CNC cutting control system from a well-known Manufacturer.
0
Good work, Custom Injection Molded Partsfrom the leading manufacturer and supplier.
0
informational post, piezoelectric manufacturersin China.
0
I found a great website about homogeneous PVC flooring roll manufacturer and supplier.
0
Impressive work, We have skilled professionals who work with the mission to surpass your expectations. Commercial Cleaning Service New York You can find more information about our service on our official website.
0
The information is very good, I think it is difficult because I have just started driving directions
0
Very interesting information, I really like it because it can add insight for me more broadly, thank you very much for this extraordinary information. Tutu app
tutuapp download
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