Connecting to an oracle database from microsoft .net

Work has changed for me slightly over the past few years, and I’ve moved into the world of Project Management. I’ve however got a chance again now to get my hands ‘dirty’ with some .NET coding – its been a few year since I’ve done any in anger – so I’m trying to gain a refresh into the current platform and its technology. Expect a few .NET 101 posts to follow this one.

We’re looking significantly to adopt .NET Core as we’ve got a great greenfield project on the horizon. However much of our infrastructure is based on Oracle and without a .NET Core supported route, I think we might have to fall back to .NET Framework 4.6.X, at least in the short term.

This blog post is to look at how .NET apps can connect to, specifically, Oracle Databases.

Pre-Requisites

You need to install Oracle Data Access Components (ODAC) with Developer Tools for Visual Studio (from here). Extract the zip, and click setup.exe and follow the defaults.

1. Connecting via a Windows Console App (.NET Framework 4.6.1)

  • Open Visual Studio 2015, and click File->New->Project.
  • Then select Visual C#->Windows and Console Application, confirming that .NET Framework 4.6.1 is selected from the drop-down menu.

    image

  • Once the project has been created, add 2 new packages via NuGet (Tools->NuGet Package Manager->Manage NuGet Packages for Solution…)
    image
  • Click Browse from the top menu bar, and then search from EntityFramework by Microsoft (its likely to be listed near the top anywhere). Select and install.
  • Then search for ODP and install the ‘Oracle.ManagedDataAccess.EntityFramework’ by Oracle.
  • Next, close down NuGet Package Manager and right click on the project and Add New Item…
  • From here select Data and ADO.NET Entity Data Model
    image
  • Then select ‘Code First from Database’
  • To create a new Connection I’ve found adding the details the installed tnsnames.ora file to be the best way – I’m not in a position to share mine, but there should be examples out there on the Internet.
  • Select the appropriate connection, enter the username and password and click Next.
  • I then select all the tables that are available in the database.
  • Click Finish. This will generate a number of files, that should relate primarily to the tables you’ve selected.
  • Then enter the following code into your program (note, the name of the database may alter depending on what you enter in the ADO.NET file – I think it defaults to Model – my example below uses GLJ). The syntax for the query you’re running may also alter depending on your data columns.
    image

Running this provides a list of modules that we have stored in a test database from Oracle.

2. Connecting to Oracle from a .NET Core Application

I’m really not expecting this to work – I’m not aware of any announcement about Oracle and .NET Core support at this time – certainly my Google searches haven’t been that fruitful. The closest I’ve got is this discussion on the OTN. And looking back over the @OracleDotNet twitter feed I can’t see anything having been announced.

Anyway, back to Visual Studio 2015 and this time New->Project. And then Visual C#->.NET Core and Console Application (.NET Core).

Then using NuGet to add the 2 packages as per the previous guide for a standard Console App – even trying to add the Microsoft EntityFramework at this time produces a compiler error. However I can search for ‘Microsoft.EntityFrameworkCore’ and I get a library that seems to be fine. Trying to install the Oracle ODP Framework though gives the expected compiler issue.

At this point its probably worth abandoning this attempt.

3, Connecting to Oracle from a .NET Core App on .NET Framework

So for this attempt I’m going to try a web application because something that interests me (but I haven’t yet got my head round) is the 3 option listed for ASP.NET Core Web Application (.NET Framework).
image

I select Web Application to get something running quickly. I then added the Microsoft Entity Framework Core Library, via NuGet as mentioned in Step 2. I then also added the ODP Oracle Entity Framework (as mentioned in Step 1, this time without issue).

However clicking into Add New Item – the Data option previously used isn’t on show.

4, Connecting to Oracle from a ASP.NET MVC on .NET Framework

Switching to create a New ASP.NET MVC application on the .NET Framework and following the steps above (but using Entity Framework rather than the dotnet core version) does produce the option to have a Data object added – and following the steps detailed in Stage 1 seems to work.

The next step I guess for this would be to try the dotnet core options with a MS SQL Server database and see what the support is like. However, it looks like we’ll still be on .NET 4.6.X until some Oracle DB support comes to dotnet core.

Related Reading:

https://csharp.today/entity-framework-6-database-first-with-oracle/

https://docs.efproject.net/en/latest/platforms/aspnetcore/existing-db.html

https://community.oracle.com/thread/3903545

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s