David Shifflet's Snippets

< Back to Index

Oracle ODP.NET and .NET CORE

March 4, 2018 by David Shifflet -

For this example we are going to:

• Create a .Net Core Console Application (.NET Core can run on platforms other than Windows, like Linux!)
• Use the ODP.NET Managed Data Access for .NET core and connect to an oracle database to get some data and display it in the console.

First - Get ODP.NET Managed Data Access Beta for .NET Core

Go here and download it:
http://www.oracle.com/technetwork/topics/dotnet/downloads/odpnetcorebeta-4077982.html
At some point this will be on Nuget so I would check there first

Create the Console App

Open up Visual Studio 2017 do the following:

Create a new project - Choose .NET Core - Select Console App. For the name I used "OdpNetCoreExample"

If ODP.NET for .NET Core is not on Nuget, yet...
In the solution's folder create a directory called assemblies. Copy the Oracle.ManagedDataAccess.dll from the zip you downloaded from Oracle to this directory.

Add a reference to that file. (Click browse)

Now we probably want to store our connection string in something other than the code.

In .Net core the configuration has changed. We will need to add some nuget packages. So in the Package Manager Console run the following:

install-package Microsoft.Extensions.Configuration.Json

(If you want to use XML and not Json, there is a similar one for XML I think it's called Microsoft.Extensions.Configuration.Xml)

We want to add a new file called "appSettings.json" to our project. This is similar to the "app.config" from previous version. In the properties for the file change the build action to "Content" and the Copy to Output Directory to "Always".

Edit the "appSettings.json" file so it contains the connection string. Make it look like:

{
  "ConnectionStrings": {
    "NorthwindDb": "DATA SOURCE=TNS_DOCKER;USER ID=northwind;PASSWORD=northwind;enlist=dynamic;"
  }
}

Before .NET core the apps would automatically load the app config files. With .NET core we have to do that ourselves. So open the Program.cs and make it look like.

using System;
using System.IO;
using Microsoft.Extensions.Configuration;
using Oracle.ManagedDataAccess;

namespace OdpNetCoreExample
{
    class Program
    {
        public static IConfiguration Configuration { get; set; }

        static void Main(string[] args)
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appSettings.json");
            Configuration = builder.Build();
            Console.WriteLine(Configuration.GetConnectionString("NorthwindDb"));
        }
    }
}

Now is a good time to make sure the config files are wired up. Run the app and you should see the connection string in the output.

Now we are going to create a method that connects to the database and gets a list of the categories. So Add this method:

        static void GetData(string connectionString)
        {
            using (var connection = new OracleConnection(connectionString))
            {
                connection.Open();
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "select category_name from northwind.categories";
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(reader["category_name"]);
                        }
                    }
                }
                connection.Close();
            }
        }		

Finally call the method from Main.

        static void Main(string[] args)
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appSettings.json");
            Configuration = builder.Build();
            GetData(Configuration.GetConnectionString("NorthwindDb"));
        }		

Run It

Run it and you should see a list of categories.

Summary

The only thing really different is the configuration files and the storing of the connection strings, which is a .NET Core thing.

With ODP.NET everything is pretty much the same and very familiar.

All of this code is available at David Shifflet's GitHub.