Working With dotConnect for Oracle in ASP.NET Core

Introduction

dotConnect for Oracle is a fast ORM for Oracle from Devart that is built on top of ADO.NET and provides you an opportunity to connect to and work with Oracle databases from your .NET or .NET Core applications. It is a fast, scalable data access framework that can be used in WinForms, ASP.NET, etc.

The article discusses the striking features of dotConnect for Oracle and shows how to work with it in ASP.NET Core.

Prerequisites

To be able to work with the code examples demonstrated in this article, you should have the following installed in your system:

• Visual Studio 2019 Community Edition
• .NET 5
• Oracle database
• dotConnect for Oracle

You can download .NET 5.0 runtime from here.

You can download Visual Studio 2019 from here.

You can download Oracle Express Edition from here.

You can download a copy of dotConnect for Oracle from here.

Create a New ASP.NET Core 5.0 Project in Visual Studio 2019

Assuming that the necessary software has been installed in your computer to be able to work with Entity Developer, follow the steps outlined below to create a new ASP.NET Core Web API project.

• First off, open the Visual Studio 2019 IDE.
• Next, click "Create a new project" once the IDE has loaded.
• In the "Create a new project" screen, select “ASP.NET Core Web API” as the project template.

• Click the "Next" button
• Specify the project name and location where it should be stored in your system
• Optionally, click the "Place solution and project in the same directory" checkbox.
• Next, click the "Create" button
• In the "Create a new ASP.NET Core Web Application" dialog window that is shown next, select "API" as the project template.
• In the “Additional Information” screen, select .NET 5.0 as the framework version.

• You should disable the "Configure for HTTPS" and "Enable Docker Support" options by disabling the respective checkboxes.
• Since we'll not be using authentication in this example, specify authentication as "No Authentication."
• Finally, click on the "Create" button to finish the process.

dotConnect For Oracle: Features and Benefits

Microsoft’s ADO.NET is a managed framework that can be used to connect to and work with several databases. You can take advantage of ADO.NET to connect to any database for which a database driver is available. As long as ADO.NET can communicate with that database driver, you’re good to go!

dotConnect for Oracle is a high-performance ORM enables data provider that works on top of ADO.NET to provide you a complete solution for performing CRUD operations against Oracle databases. It supports several Oracle features that include Advanced Queuing, Change Notifications, Alerts, Pipes, Direct Path Loading, etc.

Some of the key features of dotConnect for Oracle include the following:

• High performance
• Easy deployment
• Support for Entity Framework
• 100% managed code
• Easy to deploy
• Easy to update to a new version via NuGet packages
• Support for load balancing and batch processing
• Support for asynchronous command execution
• Excellent data binding capabilities

Create a Database Table

The following is the syntax for creating a new table in Oracle. Note how the schema name and constraints are specified.

xxxxxxxxxx

CREATE TABLE schema_name.table_name (

    column_1 data_type column_constraint,

    column_2 data_type column_constraint,

    ...

    table_constraint

 );

The following code snippet can be used to create a new table called product in Oracle.

xxxxxxxxxx

CREATE TABLE product

  (

    product_id NUMBER PRIMARY KEY,

    product_name VARCHAR2(50) NOT NULL,

    quantity NUMBER NOT NULL

  );

Creating OracleConnection

To connect to the Oracle database, you should provide the database credentials. This information is stored inside a connection string. The connection string comprises the server name, user Id, password, etc.

You can create OracleConnection in two different ways, i.e., design time and run time. You can create an OracleConnection at design time from the Toolbox inside the Visual Studio IDE. To create an instance of OracleConnection at run-time, you can use the following code snippet:

xxxxxxxxxx

OracleConnection oracleConnection = new OracleConnection();

oracleConnection.Server = "DemoXServer";

oracleConnection.UserId = "scott";

oracleConnection.Password = "tiger";

You should include the following namespace in your program:

xxxxxxxxxx

using Devart.Data.Oracle;

Reading Data in Connected Mode

The following code snippet illustrates how you can read data using dotConnect for Oracle.

xxxxxxxxxx

String connectionString = “User Id=scott;Password=tiger;Server=DemoXServer;”;

using (OracleConnection oracleConnection = new OracleConnection (connectionString))

{

 OracleCommand oracleCommand = new OracleCommand();

 oracleCommand.CommandText = "SELECT * FROM product";

 oracleCommand.Connection = oracleConnection;

 using (OracleDataReader oracleDataReader = oracleCommand.ExecuteReader())

 {

   for (int i = 0; i < oracleDataReader.FieldCount; i++)

       Console.Write(oracleDataReader.GetName(i).ToString() + "\t");

   Console.WriteLine("");

   while (reader.Read())

   {

       for (int i = 0; i < oracleDataReader.FieldCount; i++)

          Console.Write(oracleDataReader.GetValue(i).ToString() + "\t");

       Console.WriteLine();

   }

 }

}

Note that you should close the data reader once you're done using it. To do this, you can call the Close method on the data reader instance or put the data reader inside a using block.

Retrieving Data in the Disconnected Mode

To retrieve data in a disconnected model in ADO.NET, you'd typically be using Data Adapter, DataSet, and DataTable. Devart OracleDataTable and OracleDataSet are adept at working in a disconnected mode; they have advanced features that would help you to work in the disconnected mode seamlessly. The following code snippet illustrates how you can work with OracleDataTable in the disconnected mode.

xxxxxxxxxx

String connectionString = “User Id=scott;Password=tiger;Server=DemoXServer;”;

OracleDataTable dataTable = new OracleDataTable("SELECT * FROM product", connectionString);

   try

   {

       dataTable.FetchAll = true; 

       dataTable.Active = true;

       foreach (DataRow dataRow in dataTable.Rows)

       {

          foreach (DataColumn dataColumn in dataTable.Columns)

          {

              Console.Write(dataRow[dataColumn] + "\t");

          }

          Console.WriteLine();

       }

   }

   finally

   {

      dataTable.Active = false;

   }

Refer to the preceding code snippet. The FetchAll property when set to true implies that the data would be retrieved entirely from the server. Note the value of the FetchAll property is false by default which in turn implies that only minimal records will be requested at a time.

Updating Data

You can update data in two different ways: by updating the record directly in the connected mode, or, read the data to be updated, update it in memory, and then take advantage of the UPDATE statement to update the record(s).

To update data in your oracle database, you can take advantage of the UPDATE statement as shown in the following code snippet:

xxxxxxxxxx

String connectionString = “User Id=scott;Password=tiger;Server=DemoXServer;”;

OracleConnection oracleConnection = new OracleConnection (connectionString);

OracleCommand oracleCommand = new OracleCommand();

oracleCommand.CommandText = “UPDATE product SET product_name = ‘Dell Laptop’ WHERE product_id = 1”;

oracleCommand.Connection = oracleConnection;

try 

{

  oracleConnection.Open();

  int x = oracleCommand.ExecuteNonQuery();

  Console.WriteLine(x + “ rows were affected.”);

}

catch 

{

  Console.WriteLine("Error encountered...");

}

finally 

{

  oracleConnection.Close();

}

The preceding code snippet can be used to update data in the product table using dotConnect for Oracle.

Inserting Data Into the Database

You can insert data both in the connected mode as well as the disconnected mode. In the connected mode, you can insert your data directly when the connection is open. In the disconnected mode, you can store the record(s) to be inserted in a Dataset and then use a DataAdapter to update the record(s) at one go.

You can write the following code to insert data to the product table using dotConnect for Oracle.

String connectionString = “User Id=scott;Password=tiger;Server=DemoXServer;”;

OracleConnection oracleConnection = new OracleConnection (connectionString);

OracleCommand oracleCommand = new OracleCommand();

oracleCommand.CommandText = "INSERT INTO product (product_id, product_name, quantity) VALUES (1,'Lenovo Laptop',25)";

oracleCommand.Connection = oracleConnection;

try 

{

  oracleConnection.Open();

  int x = oracleCommand.ExecuteNonQuery();

  Console.WriteLine(x + " rows were affected.");

}

catch 

{

  Console.WriteLine("Error encountered...");

}

finally 

{

  oracleConnection.Close();

}

Summary

dotConnect for Oracle is a high-performance data provider for Oracle and is available in three editions, i.e., Professional, Mobile, and Express. You can know more on dotConnect for Oracle from their online documentation.