Working With Stored Procedures Using dotConnect for Oracle

Introduction

A stored procedure comprises a set of SQL statements and PL/SQL constructs that are grouped together. Stored procedures, as well as functions, can have input, output as well as input/output parameters. dotConnect for Oracle provides excellent support for working with stored procedures.

It is a fast, scalable 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. You can use this framework in your WinForms, WPF, ASP.NET, and ASP.NET Core applications.

The article talks briefly about the striking features of dotConnect for Oracle and shows how to work with stored procedures and dotConnect for Oracle in ASP.NET Core 5.

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 the location where it should be stored in your system.
• Optionally, click the "Place solution and project in the same directory" checkbox.
• 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. dotConnect for Oracle is a high-performance ORM that sits on top of the ADO.NET data provider and enables you to perform CRUD operations against Oracle databases. It is a managed framework that is high performant and is easy to deploy supports load balancing, batch processing, asynchronous command execution, and data binding. It also supports several Oracle features that include Advanced Queuing, Change Notifications, Alerts, Pipes, Direct Path Loading, etc.

Create a Database Table

The following is the syntax for creating a new table in Oracle. Note how 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 the Stored Procedure

To create a stored procedure in Oracle, use the following piece of code:

xxxxxxxxxx

CREATE PROCEDURE product_insert(pid INTEGER, pname VARCHAR, quantity INTEGER) AS

BEGIN

  INSERT INTO product(product_id, product_name, quantity) VALUES (pid, pname, qty);

END;

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;

Executing a Stored Procedure Using the OracleCommand Class

You can execute an OracleProcedure in the following ways:

• Using a PL/SQL block and the CommandText property of OracleCommand.
• By setting the OracleCommand.CommandType property to System.Data.CommandType.StoredProcedure.

The following source code illustrates how you can execute a stored procedure to read data from the database table we've created earlier.

xxxxxxxxxx

OracleConnection connection = new OracleConnection("Server=DemoXServer; User Id=scott; Password = tiger;");

connection.Open();

OracleCommand command = new OracleCommand();

command.Connection = connection;

command.CommandType = System.Data.CommandType.StoredProcedure;

command.CommandText = "getallproducts";

command.ParameterCheck = true;

OracleDataTable dataTable = new OracleDataTable(command, connection);

dataTable.Fill();

Working With Oracle Stored Procedures and DataSets

You can work with Oracle Stored Procedures with DataSets in two ways: using the DataSet Wizard as well as programmatically. If your stored procedure returns a result set, you can use this result set to generate a table in a typed data set.

Assuming you've created a DataSet already, you can use the following code to bind data to your data controller.

xxxxxxxxxx

dataSet.Fill();

DataTable dataTable = dataSet.Tables[0];

dataGridView.DataSource = dataTable;

You can use the DataSet Wizard to update, insert, or delete data.

Working With Table-Valued Functions and dotConnect for Oracle

Table-valued functions in Oracle are functions return collections of rows. These functions return collections and can be queried similar to database tables. The following are the advantages of Table-value functions:

• Can be used to merge session-specific data from tables.
• Can be used to emulate a parameterized view.
• Can be used to improve the performance of parallelized functions.

The following code snippet illustrates how you can work with table-valued functions in Oracle using dotConnect for Oracle to retrieve the top 5 records from the product table in the database.

using (OracleConnection connection = new OracleConnection("Data Source=DemoXServer; User Id=scott; Password=tiger; DescribeStoredProcedure=false;")) 

 {

    connection.Open();

    using (OracleCommand command = 

    connection.CreateCommand("get_product_function", 

    CommandType.StoredProcedure)) 

    {

       command.IsTableValuedFunction = true;

       command.Parameters.Add("row_ctr", 5);

       List<object[]> data = new List<object[]>();

       using (OracleDataReader reader = cmd.ExecuteReader()) 

       {

          while (reader.Read())

          data.Add(reader.GetValues());

       }

    }

 }

Summary

Stored procedures are PL/SQL blocks that perform one or more specific tasks that enable you to reuse a set of PL/SQL statements if and when needed. You can know more on dotConnect for Oracle from their online documentation.