View Only

Connect C# .NET 7 app on Linux on Power to Oracle on AIX

By Janani Janakiraman posted Thu February 02, 2023 04:39 PM

Like

.NET 7 support on Linux on Power enables a user to run .NET Applications on a Linux Partition on Power hardware. This blog demonstrates how to connect a sample .NET 7 application running on a RHEL 8.7 (ppc64le) partition to an Oracle Server running on an IBM AIX partition on Power.

Before you begin

What you need setup by your Oracle Database administrator

Set up the Oracle Server on an AIX Partition.
Install the Oracle Client on a Linux (ppc64le) partition on an IBM Power system. In this example we are using the Instant Client.

Note: The Server and Client don’t need to be on the same Power hardware.

What You Need to Know

Obtain this information from your Oracle Database administrator:
- The host name or IP address of the Oracle Database server.
- The service name that identifies the database you want to connect to.
- The Oracle listener port.
- The Oracle Database username and password.

From the client machine, test that you can access Oracle Server:

# This example shows the commands being run as the "oracle" user

# Set ORACLE_HOME to directory where the client is installed
export ORACLE_HOME=/home/oracle/instantclient_19_3

# Append ORACLE_HOME to LD_LIBRARY_PATH and PATH
export LD_LIBRARY_PATH=$ORACLE_HOME:${LD_LIBRARY_PATH}
export PATH=$ORACLE_HOME:${PATH}

# Set TNS_ADMIN
TNS_ADMIN=$ORACLE_HOME/network/admin

cd ${ORACLE_HOME}

# username and password are the database username and password.
# machine_name is the host name or IP address of the Oracle® database server.
# port is the Oracle® listener port.
# database_name is the service name associated with the database you want to access.
sqlplus username/password@//machine_name:port/database_name

Note: If you are unable to connect to Oracle with SQL*Plus, contact your Oracle Database administrator. If you cannot access your Oracle database with SQL*Plus, you will not be able to access the database with the OCI driver.

On the Client

Download, install, and test the ODBC drivers by following these steps:

Install the unixODBC driver:

yum install -y unixODBC.ppc64le unixODBC-devel.ppc64le

Confirm that you have unixODBC installed and configured:

$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Configure ODBC by setting the details of your datasource. Below are sample values. Modify based on your Oracle setup.

$ cat /etc/odbc.ini
[OracleODBC-19]
Description="Oracle ODBC driver for Oracle 19"
Driver=Oracle 19 ODBC driver
DSN=OracleODBC-19c
ServerName=NETDB_PDB

The 'Driver' value is the path to the Oracle libsqora.so.19.1 library

$ cat /etc/odbcinst.ini
[Oracle 19 ODBC driver]
Description=Oracle ODBC driver for Oracle 19
Driver=/home/oracle/instantclient_19_3/libsqora.so.19.1
Setup=
FileUsage=
CPTimeout=
CPReuse=
UsageCount=2

Set permission on the ODBC ini files so that they are owned by the user and group of the “user” account. Below is an example:

chown oracle /etc/odbcinst.ini
chgrp oracle /etc/odbcinst.ini
chown oracle /etc/odbc.ini
chgrp oracle /etc/odbc.ini

$ ls -al /etc/odbc*
-rw-r--r--. 1 oracle oracle 133 Jan 26 23:12 /etc/odbc.ini
-rw-r--r--. 1 oracle oracle 177 Jan 26 22:49 /etc/odbcinst.ini

Install the Oracle ODBC driver and system DSN:

odbcinst -i -d -f /etc/odbcinst.ini
odbcinst -i -s -l -f /etc/odbc.ini

Test your system DSN installation by listing your installed data sources:
```
odbcinst -s -q
```
You should see an output like:
```
$ odbcinst -s -q
[OracleODBC-19]
```

Use the isql tool to test a connection to the OracleODBC-19 database via ODBC:

isql -v OracleODBC-19  <user> <pwd>
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT table_name FROM user_tables

Now you should have the client machine configured to communicate with Oracle over ODBC. Next we move on to testing if a .NET application on the Oracle client on the Linux on Power machine can access data in the Oracle Database.

Install .NET 7 on the Linux on Power server

Install dotnet7 using yum (the server in this example is running RHEL 8.7):

yum install dotnet-sdk-7.0

Sample .NET 7 application connecting to Oracle via ODBC

Create the console application using the dotnet new command:
```
dotnet new console -o oracle_odbc_sample
```

Add the System.Data.Odbc package using the dotnet add command. This will add package reference in csproj file.

# Change directory to odbc_sample
cd oracle_odbc_sample

# Add the System.Data.Odbc Package to the project
dotnet add package System.Data.Odbc

Copy the code below into the Program.cs file. This sample connects to the Oracle Server on the AIX partition and reads all rows and columns from BLOGS table and displays the same on the console output.

Note: Be sure to modify the program to supply the connection parameters for your Oracle Server. Also, modify the program to retrieve data from tables on your Oracle Server.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;

namespace ConsoleApplication1
{
    class Program
    {
	public static string user = <DB User>;
	public static string pwd = <DB Password>;
	public static string driver = "Oracle 19 ODBC driver";

    // Default Port is 1521
	public static string dsn = "<Hostname/IP of Oracle Server>:1521/<< Datastore >>";

        static void Main(string[] args)
        {
            string MyConString = "DRIVER=" + driver + ";uid=" + user + ";pwd=" + pwd + ";DSN=" + dsn + ";";

            OdbcConnection DbConnection = new OdbcConnection(MyConString);
            DbConnection.Open();

            OdbcCommand DbCommand = DbConnection.CreateCommand();

            DbCommand.CommandText = "SELECT * FROM BLOGS";
            OdbcDataReader DbReader = DbCommand.ExecuteReader();

            int fCount = DbReader.FieldCount;

            Console.Write( ":" );
            for ( int i = 0; i < fCount; i ++ )
            {
                String fName = DbReader.GetName(i);
                Console.Write( fName + ":" );
            }
            Console.WriteLine();

            while( DbReader.Read())
            {
                Console.Write( ":" );
                for (int i = 0; i < fCount; i++)
                {
                    String col = DbReader.GetString(i);
                    Console.Write(col + ":");
                }
                Console.WriteLine();
            }
            DbReader.Close();
            DbCommand.Dispose();
            DbConnection.Close();
        }
    }
}

Build the .NET project:
```
dotnet build
```

Run the application:

dotnet run

The program should display the content of the BLOGS table as shown below:

$ dotnet run

:BLOGID:NAME:URL:

:3:Connect to IBMi:https://community.ibm.com/community/user/powerdeveloper/blogs/janani-janakiraman/2022/12/15/dotnet7-power-linux-to-ibmi-odbc:

:4:Blog of Blogs:https://community.ibm.com/community/user/powerdeveloper/blogs/linda-alkire-kinnunen/2023/01/14/dotnet-on-ibm-power-resources-for-developers:

Sample .NET 7 application connecting to Oracle via Entity Framework

Connect C# .NET 7 app on Linux on Power to Oracle on AIX using Entity Framework Core

Create the console application using the dotnet new command:
```
dotnet new console -o EFOracle
```

Add the Oracle.EntityFramework package using the dotnet add command. This will add package reference in csproj file.

# Change directory to EFOracle
cd EFOracle

# Add the Oracle.EntityFramework Package to the project
dotnet add package Oracle.EntityFramework -v 7.21.9

Copy the code below into the Program.cs file. This sample connects to Oracle® Server on the AIX partition and inserts, updates and deletes one row and reads all rows and columns from BLOGS table and displays the same on console output after insert, update and delete.

Note: Be sure to modify the program to supply the connection parameters for your Oracle® server. Also modify the program to retrieve data from tables on your Oracle® server.

using Oracle.EntityFrameworkCore;
using System;
using System.Linq;

public class Program
{
        public static void DumpDatabaseSnapshot(BloggingContext db)
        {
                // Read
                Console.WriteLine("\nDumping database snapshot...");

                var blogs = db.BLOGS
                        .OrderBy(b => b.BLOGID)
                        .ToList();

                Console.WriteLine("Number of records = {0}", blogs.Count);

                foreach(Blog b in blogs)
                {
                        Console.WriteLine("BlogId = {0}, Url = {1}", b.BLOGID, b.URL);
                        if (b.Posts != null)
                        {
                                foreach(Post p in b.Posts)
                                {
                                        Console.WriteLine("----->PostId = {0}, Title = {1}, Content = {2}", p.POSTID, p.TITLE, p.CONTENT);
                                }
                        }
                }

                Console.WriteLine("\n");
        }

        public static void Main(String[] args)
        {
                using var db = new BloggingContext();

                DumpDatabaseSnapshot(db);

                // Create
                Console.WriteLine("Inserting a new blog");

                var blog = new Blog { NAME = "Oracle EFCore", URL = "http://blogs.msdn.com/adonet" };
                db.BLOGS.Add(blog);
                db.SaveChanges();


                DumpDatabaseSnapshot(db);

                blog = db.BLOGS
                        .OrderBy(b => b.BLOGID)
                        .First();

                // Update
                Console.WriteLine("Updating the blog and adding a post, blogid = {0}", blog.BLOGID);
                blog.URL = "https://devblogs.microsoft.com/dotnet";
                blog.Posts = new List<Post>();
                blog.Posts.Add(
                new Post { TITLE = "Hello World", CONTENT = "I wrote an app using EF Core!" });
                db.BLOGS.Update(blog);
                db.SaveChanges();

                DumpDatabaseSnapshot(db);

                // Delete
                Console.WriteLine("Delete the blog");
                db.Remove(blog);
                db.SaveChanges();

                DumpDatabaseSnapshot(db);
        }
}

Copy the code below into the Model.cs file. This file contains the Blogs and Posts classes and the connection string to connect to the database.


using Oracle.EntityFrameworkCore;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

public class BloggingContext : DbContext
{
    public DbSet BLOGS { get; set; }
    public DbSet POSTS { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseOracle("User Id=JANANI;Password=pw4janani;Data Source=netdb_pdb");//, b => b.UseOracleSQLCompatibility("11"));
        //optionsBuilder.LogTo(Console.WriteLine);
    }
}
public class Blog
{
    public int BLOGID { get; set; }
    public string NAME { get; set; }
    public string URL { get; set; }

    public List Posts { get; set; }
}

public class Post
{
    public int POSTID { get; set; }
    public string TITLE { get; set; }
    public string CONTENT { get; set; }

    public int BLOGID { get; set; }
    public Blog Blog { get; set; }
}

Build the .NET project:
```
dotnet build
```

Run the application:

dotnet run

The program should display the output after insert, update and delete like below:

 $ dotnet run

Dumping database snapshot...
Number of records = 2
BlogId = 21, Url = http://blogs.msdn.com/adonet
BlogId = 22, Url = http://blogs.msdn.com/adonet


Inserting a new blog

Dumping database snapshot...
Number of records = 3
BlogId = 21, Url = http://blogs.msdn.com/adonet
BlogId = 22, Url = http://blogs.msdn.com/adonet
BlogId = 23, Url = http://blogs.msdn.com/adonet


Updating the blog and adding a post, blogid = 21

Dumping database snapshot...
Number of records = 3
BlogId = 21, Url = https://devblogs.microsoft.com/dotnet
----->PostId = 3, Title = Hello World, Content = I wrote an app using EF Core!
BlogId = 22, Url = http://blogs.msdn.com/adonet
BlogId = 23, Url = http://blogs.msdn.com/adonet

Delete the blog

Dumping database snapshot...
Number of records = 2
BlogId = 22, Url = http://blogs.msdn.com/adonet
BlogId = 23, Url = http://blogs.msdn.com/adonet

Conclusion

With the ability to connect from .NET applications on Linux on Power to data residing on an Oracle Server on an AIX partition on Power, IBM Power customers will have the ability to modernize their apps while running on the same Power platform as their AIX business databases. Applications can now be moved closer to the data on their Power servers.

Additional authors

Special thanks to my co-authors, @Sapana Khemkar and @Alhad Deshpande for their valuable input.

Reference

https://www.ibm.com/docs/en/netcoolconfigmanager/6.4.2.0?topic=zccair-zlinux-rhel-suse-configuring-oracle-datasource-use-odbc

#Featured-area-3
#Featured-area-3-home

Programming Languages on Power