Working With LOB Data In Oracle And ASP.NET 5

LOBs enable you to store vast quantities of data in a database. Oracle provides excellent support for working with LOB data. This article talks about what LOBs are, their purpose, and how to work with them in Oracle. For connecting to the Oracle database, we'll take advantage of dotConnect for Oracle.

Pre-requisites

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
dotConnect for Oracle

You can download Visual Studio 2019 from here.

You can download .NET 5.0 from here.

You can download a dotConnect for Oracle from here.

You can download Oracle Express Edition from here.

What are LOBs?

Long-lived objects (LOBs) are a collection of data types that are adept at storing vast quantities of data. LOBs in the database are kept inside database tablespaces to save space while allowing for quick access to the data. You can use SQL data types such as BLOB, CLOB, and NCLOB to define internal LOBs in a database. Specifically, LOB data types can be used for storing and processing vast blocks of organized data, such as big blocks of character strings or large blocks of unstructured data.

Large Character and Large Binary Data Types in Oracle

Oracle supports both large character and large binary data types. There are two types of large character datatypes, namely CLOB and NCLOB. While the former can store character data, the latter can store Unicode character set data. Both can store data up to (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB).

Similarly, there are two types of large binary datatypes in Oracle, namely BLOB and BFILE. While the former can store unstructured binary data up to (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB), BFILE is adept at storing binary data in external files up to a size of 4 gigabytes.

Types of LOBs

LOBs are classified into two categories: internal and external. While internal LOBs such as BLOBs, CLOBs, and NCLOBs are stored in a database, external LOBs or BFILEs are binary data saved in operating-system files that are not part of the database tablespaces.

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

Assuming that the necessary software has been installed on your computer, 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, .NET 5.0 is 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.

Create the Database Table(s)

Assuming you’ve installed Oracle on your computer, you can use the following script to create a new database table named Contact in Oracle.

Create Table Contact(
    Id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50),
    Photo Blob,
    CONSTRAINT Id_Pk PRIMARY KEY (Id) 
);

The following screenshot illustrates the columns of the Contact database table as viewed in Oracle SQL Developer.

Figure 1

We’ll use this table in the sections that follow.

Creating an OracleConnection

First off, you should provide the Oracle database credentials in your application to establish a connection to the database. You can save this information inside of a connection string and make it configurable.

The connection string contains information such as the name of the database server, user Id, password, etc. You can create an OracleConnection in two distinct ways: during the design and run phases. You may build an OracleConnection during the design phase by using the Visual Studio Integrated Development Environment Toolbox.

To create an instance of OracleConnection at run-time, you can use the following code snippet,

OracleConnection oracleConnection = new OracleConnection();
oracleConnection.Server = "DemoXServer";
oracleConnection.UserId = "joydip";
oracleConnection.Password = "mypass1@3";

You should include the following namespace in your program,

using Devart.Data.Oracle;

Upload a Blob Object to an Oracle Database

To upload a blob to an Oracle database, you should follow these steps,

Open a file on the disk containing the image you want to upload
Create a new OracleConnection instance to connect to the Oracle database
Open the OracleConnection
Create a temporary blob
Transfer the data to the server
Write the Insert query to insert the blob onto the Oracle database table
Execute the Insert query to update the Oracle database table

You can use the following source code to upload a blob object to your Oracle database.

void UploadBlobObject(string filename) {
    FileStream stream = new FileStream(filename, FileMode.Open, FileAccess.Read);
    BinaryReader reader = new BinaryReader(stream);
    try {
        using(OracleConnection connection = new OracleConnection()) {
            connection.UserId = "joydip";
            connection.Password = " mypass1@3";
            connection.Server = "DemoXServer";
            if (connection.State != ConnectionState.Open) connection.Open();
            OracleLob oracleLob = new OracleLob(connection, OracleDbType.Blob);
            int streamLength = (int) stream.Length;
            oracleLob.Write(reader.ReadBytes(streamLength), 0, streamLength);
            OracleCommand command = new OracleCommand("INSERT INTO Contact (FirstName, LastName, Photo) " + "VALUES('Joydip','Kanjilal', :Picture)", connection);
            OracleParameter oracleParameter = command.Parameters.Add("Picture", OracleDbType.Blob);
            oracleParameter.OracleValue = oracleLob;
            command.ExecuteNonQuery();
        }
    } catch {
        throw;
    }
}

You can see 1 record inserted in Oracle SQL Developer.

Working with LOB Data in Oracle
Figure 2

Download a Blob Object from an Oracle Database

To download a blob from an Oracle database, you should follow these steps:

Create a new OracleConnection instance to connect to the Oracle database
Open the OracleConnection
Create an OracleCommand instance to execute queries
Create an OracleDataReader instance to read data from the Oracle database table
Loop through all records of the Oracle database table
Obtain an OracleLob instance from the OracleDataReader
If the OracleLob instance is not null, generate a file name
Create a new file in the generated filename
Transfer the data from the OracleLob instance to a byte array
Close the stream and reader instances

You can use the following source code to download a lob object from an Oracle database table onto your file system.

void DownloadBlobObject() {
    try {
        using(OracleConnection connection = new OracleConnection()) {
            connection.UserId = "system";
            connection.Password = "sa123#";
            connection.Server = "DemoXServer";
            OracleCommand command = new OracleCommand("SELECT * FROM Contact", connection);
            connection.Open();
            using(OracleDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.Default)) {
                int ctr = 1;
                while (reader.Read()) {
                    OracleLob oracleLob = reader.GetOracleLob(reader.GetOrdinal("Photo"));
                    if (!oracleLob.IsNull) {
                        string fileName = "Image-" + ctr.ToString();
                        FileStream stream = new FileStream(@ "D:\" +
                            fileName + ".bmp", FileMode.Create);
                        byte[] buffer = new byte[oracleLob.Length];
                        oracleLob.Read(buffer, 0,
                            (int) oracleLob.Length);
                        stream.Write(buffer, 0,
                            (int) oracleLob.Length);
                        stream.Close();
                    }
                }
            }
        }
    } catch {
        throw;
    }
}

Summary

You can take advantage of Binary Large Objects (also called BLOB) to persist binary data in an Oracle database. You can take advantage of a BLOB to store documents, images, and other binary data in your Oracle database table. You can leverage CLOB for storing large texts as well.