How to Connect to Oracle in .NET With C#

This tutorial provides a deep dive into connecting a .NET application to an Oracle database.

We're going to use Oracle Database, a powerful and feature-rich relational database management system renowned for its scalability, performance, and robust security features. As a leading choice for enterprise-level applications, Oracle Database is ideal for critical business solutions as it supports high transaction volumes and large datasets. Its advanced capabilities, such as Real Application Clusters (RAC) and comprehensive data management tools, ensure high availability and reliability.

Why choose dotConnect for Oracle?

dotConnect for Oracle is an ideal data provider for all Oracle-related operations. It offers on-the-fly connector creation, flexible configuration, seamless integration into Visual Studio, and enhanced ORM support.

Requirements

• Visual Studio 2022: The IDE of choice. Download and install it if you don't have it. We're going to use the community version, so you can get it as well.
• dotConnect for Oracle: A high-performance ADO.NET data provider for Oracle with enhanced ORM support and database connectivity features.
• Entity Developer: An ORM designer for .NET ORM Frameworks with powerful code generation.
• Sakila database: A sample database for learning and testing. Download the folder and unzip the file to use it.

Download and activate dotConnect for Oracle

30-day free trial version

Download and install dotConnect for Oracle directly on your machine, or install the Devart.Data.Oracle NuGet package.

No license key is required, and you can start exploring the product immediately.

Full version

After purchasing the full version, go to your profile's Licenses page. Choose your product and click Details. Here, you'll find the license details and the Activation Key.

To activate a connection in your application, add the License Key to your connection string.

Create a .NET project

To begin working with dotConnect for Oracle, you first need to create a simple console application that connects to an Oracle database. The process involves initializing a new project and installing the required NuGet packages.

1. Launch Visual Studio and select Create a new project.

2. Select Console App (.NET Core) or Console App (.NET Framework), depending on your preference, and click Next.

3. Name the project, specify the path to its folder, and click Create. In this tutorial, we'll use OracleTest as the project name.

4. In Solution Explorer, right-click the created project and select Manage NuGet Packages.

5. Go to the Browse tab, find and install the Devart.Data.Oracle package. Optionally, you can run the following command in the terminal:

dotnet add package Devart.Data.Oracle

Connect C# to Oracle in the Direct mode

This example demonstrates a basic connection to an Oracle database. Depending on your requirements, you may need to add more functionality, such as executing queries or handling various types of exceptions.

using Devart.Data.Oracle;

class Program
{
    static void Main(string[] args)
    {
        string connectionString = "" +
            "Host=127.0.0.1;" +
            "Port=1521;" +
            "Service Name=orcl;" +
            "User Id=TestUser;" +
            "Password=TestPassword;" +
            "Direct=True;" +
            "License Key=**********;";

        try
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                Console.WriteLine("Connection successful!");
            }
        }
        catch (OracleException)
        {
            Console.WriteLine("Connection failed!");
        }

        Console.WriteLine("\nPress any key to exit...");
        Console.ReadKey();
    }
}

The connection string contains the required information to connect to the database, such as the server, user ID, password, and database name.

Connect using SSL/TLS

To establish an SSL connection to an Oracle database with the specified connection string parameters, modify your code as follows:

using Devart.Data.Oracle;

class Program
{
    static void Main(string[] args)
    {
        string connectionString = "" +
            "Host=127.0.0.1;" +
            "Port=1521;" +
            "Service Name=orcl;" +
            "User Id=TestUser;" +
            "Password=TestPassword;" +
            "Direct=True;" +
            "SslKey=/server.key;" +
            "SslCert=/server.crt;" +
            "License Key=**********;";

        try
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                Console.WriteLine("Connection successful!");
            }
        }
        catch (OracleException)
        {
            Console.WriteLine("Connection failed!");
        }

        Console.WriteLine("\nPress any key to exit...");
        Console.ReadKey();
    }
}

Connect using SSH

And if you need to establish an SSH connection to an Oracle database with the specified connection string parameters, modify your code as follows:

using Devart.Data.Oracle;

class Program
{
    static void Main(string[] args)
    {
        string connectionString = "" +
            "Host=ssh://127.0.0.1;" +
            "Port=1521;" +
            "Service Name=orcl;" +
            "User Id=TestUser;" +
            "Password=TestPassword;" +
            "License Key=**********;";

        try
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                // Configure SSH options
                connection.SshOptions.AuthenticationType = SshAuthenticationType.PublicKey;
                connection.SshOptions.User = "sshUser";
                connection.SshOptions.Host = "sshServer";
                connection.SshOptions.PrivateKey = "C:\\client.key";

                connection.Open();
                Console.WriteLine("Connection successful!");
            }
        }
        catch (OracleException)
        {
            Console.WriteLine("Connection failed!");
        }

        Console.WriteLine("\nPress any key to exit...");
        Console.ReadKey();
    }
}

Connect using Server Explorer

You can also connect to a database using Visual Studio's built-in Data Explorer:

1. Go to the Server Explorerwindow, right-click Data Connections, and then select Add Connection.

2. In the Add Connection dialog, click Change to specify the data source.

3. In the Change Data Source dialog, select dotConnect for Oracle from the list from the list of installed data providers and click OK.

4. In the Add Connection dialog, select Oracle as the data source and provide your server credentials.

5. To verify the connection, click Test Connection.

Once you are connected, you can browse tables, run queries, and manage data directly in Visual Studio.

Connect with EF Core using Entity Developer

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.

Connect with EF Core using Scaffold-DbContext

To connect to an Oracle database using Entity Framework Core (EF Core) and scaffold its context, follow these step-by-step instructions. This process generates the required EF Core model classes and a DbContext based on the existing Oracle database schema.

Install the required NuGet packages

In Solution Explorer, right-click the project and select Manage NuGet Packages. Then, you need to open the Package Manager Console. To do this, go to Tools > NuGet Package Manager > Package Manager Console.

Scaffold DbContext

In Package Manager Console, run the following command to scaffold DbContext and entity classes from the Oracle database. Replace the connection string with your relevant connection details.

Scaffold-DbContext "Host=127.0.0.1;Port=1521;Service Name=orcl;User Id=TestUser;Password=TestPassword;Direct=True;License Key=**********;" Devart.Data.Oracle.Entity.EFCore -OutputDir Models

After running the scaffold command, EF Core generates the DbContext class and entity classes in the specified output directory (e.g., Models). Review the generated code to make sure it matches your database schema.

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

Conclusion

This tutorial 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.