How to Connect to Oracle in .NET with C#

| ACTOR_ID: number(22)
| FIRST_NAME: varchar(45)
| LAST_NAME: varchar(45)
| LAST_UPDATE: timestamp

public class Actor
    {
        public int ActorId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime LastUpdate { get; set; }
    }

public static DataTable GetActorTableDirectModes()
 {
     try
     {
         OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
         oraCSB.Direct = true;
         oraCSB.Server = "127.0.0.1";
         oraCSB.Port = 1521;
         oraCSB.ServiceName = "XE";
         oraCSB.UserId = "TestUser";
         oraCSB.Password = "TestPassword";
         oraCSB.LicenseKey = "**********";
         DataTable table;
         using (var myConnection = new OracleConnection(oraCSB.ConnectionString))
         {
             myConnection.Open();
             string query = "SELECT * FROM actor WHERE ROWNUM <= 10";
             OracleDataAdapter adapter = new OracleDataAdapter(query, myConnection);
             DataSet dataSet = new DataSet();
             adapter.Fill(dataSet, "actor");
             table = dataSet.Tables["actor"];
             myConnection.Close();
         }
         return table;
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.Message);
         return null;
     }
 }

public static DataTable GetActorTableUsingSSL()
{
    try
    {
        DataTable table = new DataTable();
        Devart.Data.Oracle.OracleConnectionStringBuilder oraCSB = new Devart.Data.Oracle.OracleConnectionStringBuilder();
        oraCSB.Server = "127.0.0.1";
        oraCSB.Port = 1521;
        oraCSB.ServiceName = "XE";
        oraCSB.UserId = "TestUser";
        oraCSB.Password = "TestPassword";
        oraCSB.MaxPoolSize = 150;
        oraCSB.ConnectionTimeout = 30;
        oraCSB.SslKey = "/server.key";
        oraCSB.SslCert = "/server.crt";
        oraCSB.LicenseKey = "**********";
        OracleConnection myConnection = new OracleConnection(oraCSB.ConnectionString);

        OracleCommand command = new Devart.Data.Oracle.OracleCommand("SELECT * FROM actor WHERE ROWNUM <= 10;", myConnection);
        myConnection.Open();
        using (OracleDataReader reader = command.ExecuteReader())
        {
            table.Load(reader);
        }
        myConnection.Close();
        return table;
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
        return null;
    }
}

public static DataTable GetActorTableUsingSSH()
{
	try
	{
    	DataTable table = new DataTable();
    	OracleConnection myConnection = new OracleConnection("" +
        	"Direct=True;" +
        	"Host=ssh://127.0.0.1;" +
        	"Port=1521;" +
        	"ServiceName=XE;" +
        	"UserID=TestUser;" +
        	"Password=TestPassword;" +
        	"LicenseKey=**********"
        	);
    	myConnection.SshOptions.AuthenticationType = Devart.Data.Oracle.SshAuthenticationType.Password;
    	myConnection.SshOptions.Host = "OracleSSH";
    	myConnection.SshOptions.User = "sshUser";
    	myConnection.SshOptions.Password = "sshPassword";
    	OracleCommand command = new Devart.Data.Oracle.OracleCommand("SELECT * FROM actor WHERE ROWNUM <= 10", myConnection);
    	myConnection.Open();
    	using (OracleDataReader reader = command.ExecuteReader())
    	{
        	table.Load(reader);
    	}
    	myConnection.Close();
    	return table;
	}
	catch (Exception ex)
	{
    	Console.WriteLine(ex.Message);
    	return null;
	}
}

Proxy authentication allows middle-tier applications to control security by preserving database user identities and privileges. Oracle enables the creation of a proxy database user, which connects and authenticates against the database on behalf of database users. Proxy authentication is not supported in Direct mode. Here's an example using our code:

public static DataTable GetDataActorProxy()
{
	try
	{
    	DataTable table;
    	using (OracleConnection proxyConnection = new
            	OracleConnection("" +
            	"Host=127.0.0.1;" +
            	"Port=1521;" +
            	"ServiceName=XE;" +
            	"UserId=TestUser;" +
            	"ProxyUserId=ProxyUser;" +
            	"ProxyPassword=ProxyPassword;" +
            	"LicenseKey=**********"
            	))
    	{
        	proxyConnection.Open();

        	string query = "SELECT * FROM actor WHERE ROWNUM <= 10";
        	OracleDataAdapter adapter = new OracleDataAdapter(query, proxyConnection);
        	DataSet dataSet = new DataSet();
        	adapter.Fill(dataSet, "actor");
        	table = dataSet.Tables["actor"];
        	proxyConnection.Close();
    	}
    	return table;
	}
	catch (Exception ex)
	{
    	Console.WriteLine(ex.Message);
    	return null;
	}
}

The OracleCredential class allows providing a password for connecting to Oracle more securely than specifying it in plain text in a connection string or assigning the System.String instance to the OracleConnection.Password property. The class uses the SecureString class to store and specify the password. Here's an example:

public static DataTable GetDataActorOracleCredential(string userName, SecureString securePwd)
{
	try
	{
    	string connectionString = "" +
        	"Host=127.0.0.1;" +
        	"Port=1521;" +
        	"ServiceName=XE;" +
        	"UserId=TestUser;" +
        	"Password=TestPassword;" +
        	"LicenseKey=**********";
    	DataTable table;
    	using (var connection = new OracleConnection(connectionString))
    	{
        	connection.Open();
        	connection.Credential = new OracleCredential(userName, securePwd);
        	string query = "SELECT * FROM actor WHERE ROWNUM <= 10";
        	OracleDataAdapter adapter = new OracleDataAdapter(query, connection);
        	DataSet dataSet = new DataSet();
        	adapter.Fill(dataSet, "actor");
        	table = dataSet.Tables["actor"];
        	connection.Close();
    	}
    	return table;
	}
	catch (Exception ex)
	{
    	Console.WriteLine(ex.Message);
    	return null;
	}
}

You can also use Unicode connection strings to ensure that the data retrieved from and sent to an Oracle database is in Unicode format, which supports a wide range of characters. Here's an example:

public static DataTable GetDataActorUnicode()
{
	try
	{
    	string connectionString = "" +
        	"Host=127.0.0.1;" +
        	"Port=1521;" +
        	"ServiceName=XE;" +
        	"UserId=TestUser;" +
        	"Password=TestPassword;";
    	DataTable table;
    	using (var connection = new OracleConnection(connectionString))
    	{
        	connection.Open();
        	connection.Unicode = true;
        	string query = "SELECT * FROM actor WHERE ROWNUM <= 10";
        	OracleDataAdapter adapter = new OracleDataAdapter(query, connection);
        	DataSet dataSet = new DataSet();
        	adapter.Fill(dataSet, "actor");
        	table = dataSet.Tables["actor"];
        	connection.Close();
    	}
    	return table;
	}
	catch (Exception ex)
	{
    	Console.WriteLine(ex.Message);
    	return null;
	}
}

You can then choose the connection mode that best suits your use case.

Test the project

It's necessary to be able to visualize the returned data. In Solution Explorer, click Controllers, select Add New Class, and add the ActorsController class. In the Oracle_ASP.NET_ASP.NET.Controllers namespace, modify the class using this code:

[ApiController]
[Route("api/[controller]/[action]")]
public class ActorsController : ControllerBase
{
    [HttpGet]
    public IEnumerable GetActors()
    {
        List actorList = new();
        DataTable dataTable = DatabaseConnectionClass.GetActorTableDirectModes();

        foreach (DataRow row in dataTable.Rows)
        {
            Actor obj = new Actor
            {
                ActorId = Convert.ToInt32(row["actor_id"]),
                FirstName = Convert.ToString(row["first_name"]),
                LastName = Convert.ToString(row["last_name"]),
                LastUpdate = Convert.ToDateTime(row["last_update"])
            };
            actorList.Add(obj);
        }

        return actorList;
    }
}

When you run the project, click the drop-down for GetActors, select Try it now, and then click Execute. After that, the desired list will be displayed:

Great! We have successfully established the connection to the Oracle database.

Now, connect the .NET application to the Oracle database via EF Core. Let's see how to implement it using dotConnect for Oracle.

First, add a separate project to the solution to illustrate the case. To do this, right-click the current Oracle_ASP.NET solution in the Visual Studio Solution Explorer and go to Add > New project. In our case, this additional project is called Oracle_EF_Core_ASP.NET, and we added it in the same way as we did at the beginning of this tutorial. Right-click it and set it as a startup project.

Then, ensure that you have the EF Core tools installed in your development environment. You can install them globally using the .NET CLI from the command line:

dotnet tool install --global dotnet-ef

Next, open NuGet Package Manager for the project in Visual Studio, select Devart.Data.Oracle.EFCore, and click Install.

To install Scaffold-DbContext via the Package Manager Console, navigate to Tools > Package Manager Console and select Oracle_EF_Core_ASP.NET as the project. When the console opens, execute the following command:

Install-Package Microsoft.EntityFrameworkCore.Tools

Once installed, you can use Scaffold-DbContext to create the Models folder and generate DbContext and entity classes for the Oracle database. Execute the following command from the Package Manager Console:

Scaffold-DbContext
"DataSource=127.0.0.1;Port:1521;ServiceName=XE;UserId=TestUser;Password=
TestPassword;LicenseKey=your_license_key;" -provider 
Devart.Data.Oracle.Entity.EFCore -OutputDir Models 

Remember to provide the relevant credentials for your password. After that, dbContext will be renamed to ModelContext, and the entity classes in the table should be generated. They will be available in Solution Explorer.

Let's use the Actors class again. We'll query this entity table using LINQ. Right-click the Oracle_EF_Core_ASP.NET project, select the option to add a class, and add DatabaseConnectionClass as we did earlier. Then, paste this code into the class:

public class DatabaseConnectionClass
{
    private readonly ModelContext _context;

    public DatabaseConnectionClass()
    {
        _context = new ModelContext();
    }

    public List GetActors()
    {
        return _context.Actors.Take(10).ToList();
    }
}

We used ModelContext to retrieve the entity of actors and returned it as a list. Like before, we need a way to visualize the data, so add a new class called ActorsController into the Controllers folder. Modify the class with the code below in the Oracle_EF_Core_ASP.NET.Controllers namespace:

 [ApiController]
    [Route("api/[controller]/[action]")]
    public class ActorsController : ControllerBase
    {
        private readonly DatabaseConnectionClass _context;

        public ActorsController()
        {
            _context = new DatabaseConnectionClass();
        }

        [HttpGet]
        public IEnumerable GetActors()
        {
            return _context.GetActors();
        }
    }

Run the application by pressing F5, go to the GetActors endpoint, click Try it out, and then Execute. You will see the query results in the response body, as shown previously.

If you want to learn more about working with EF Core, refer to the Entity Framework documentation, which includes instructions for executing INSERT, UPDATE, and DELETE operations.

Another way to create an EF Core model from the Oracle database is through Entity Developer.

Close the Visual Studio instance, then download and install Entity Developer. After that, open the Oracle_EF_Core project in Visual Studio again. Next, right-click the solution and navigate to Add > New Item.

Then, in the model pane, select Data and click Devart EF Core Model.

In the Entity Developer Model wizard, select Database First and click Next.

The Setup data connection properties window pops up. Fill in the details of your Oracle database connection.

Click Next to proceed further. Then, select Generate From Database and click Next.

Next, choose the database objects you want to scaffold. Since we have been using the Actors table, you can select all of them.

Now, define the naming convention you want the property names in the database object to follow. We will leave it as the default and click Next.

On the next page, set up model properties like the namespace and entity container. The only thing that will change on this page is the Target Framework. We are using .NET 8, so choose that (or the framework your project uses) and click Next.

After that, you will be asked to choose the contents of the Model diagram. You can use all entities, split the entities by database, or do a custom selection. For this tutorial, we will use All Entities, so select this option and click Next.

Then, choose code generation templates for the model. Here, you can define different parameters you want the object to follow. We will use the default settings.

After that, your model will be fully created. There is a checkbox to download Devart.Data.Oracle.EFCore, but since we have previously added it to the project, you can clear it and click Finish.

Check the created model.

Video tutorial: How to connect .NET console application to Oracle database

This article provides detailed guides on connecting a .NET application to Oracle databases in various ways. It explores multiple connection methods, including Direct Mode, SSH, secure SSL/TLS, and using Entity Framework Core with Scaffold-DbContext and Entity Developer

These are just a few of the things dotConnect can do for you. To experience a seamless, high-performance data connectivity solution tailored for .NET developers, download dotConnect for Oracle.